Getting sum of all transactions by month-end

Question

Post reply

Getting sum of all transactions by month-end

callen-557064

SSC-Addicted

Points: 460
More actions
April 21, 2021 at 5:52 am

#3873809

I have a table that is a list of inventory transactions. For simplicity sake, the table has these columns:
Location, Item Number, Trx Date, Trx Amount
I'm trying to create a query that gets the accumulation of all transactions for each month in a year.
For example, let's assume these transactions:
I'm trying to create a query that would group the data by location and month-end date so the results would look like this:
Instead of the month-end date, I could also use a concept like fiscal period like 202008, 202009, etc.
If there are no transactions in a month, I'd still want the month to appear. For example, if there was no transaction in October, it would show 1,100 as the value.
There would be a date through which the query would be run. For example, the user would enter 202012 if using fiscal periods and the query would return the results for each month leading up to 202012.
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Vedat ÖZER SSC Enthusiast Points: 103 More actions · Answer 1

Merhaba,

Inceleyin.

DECLARE @BASLAMA DATETIME    ='2020-01-01'
DECLARE @BITIS   DATETIME    ='2021-12-31'




DECLARE @TABLOM TABLE
(
LOCATION     NVARCHAR(100),
ITEM         NVARCHAR(100),
TRXDATE      DATETIME,
TRXAMOUNT    FLOAT
);

INSERT INTO @TABLOM
VALUES ('HOME','ABC','2020-01-03','500'),
       ('HOME','ABC','2020-09-30','600'),
   ('HOME','ABC','2021-01-31','400'),
   ('HOME','ABC','2021-02-28','700');


;WITH n1 AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))t(n))--10
      , n2 AS (SELECT n.n FROM n1 AS n CROSS JOIN n1)--100
      , n3 AS (SELECT n.n FROM n2 AS n CROSS JOIN n2)--10000
      , nums AS (SELECT 0 num UNION ALL SELECT Row_Number() OVER (ORDER BY(SELECT NULL)) num FROM n3) 
     , Calendar (BegDt, EndDt) AS (
                  SELECT 
                        DateAdd(DAY, nums.num, @BASLAMA) 
                      , DateAdd(MILLISECOND, -3, DateAdd(DAY, 1, DateAdd(week, nums.num,@BASLAMA)))
                  FROM nums
                  ),

AY AS (   SELECT TOP 100 PERCENT
        CAST(YEAR(c.BegDt) AS INT) YIL,CAST(MONTH(c.BegDt) AS INT) AY,MAX(c.BegDt) GUN
      
   FROM 
      Calendar c
   WHERE
      c.BegDt <= @BITIS
  GROUP BY MONTH(c.BegDt),YEAR(c.BegDt))
  

SELECT  ISNULL(T.LOCATION,'HOME') LOCATION,DETAY.GUN,ISNULL(T.TUTAR,0) TUTAR,SUM(ISNULL(T.TUTAR,0)) OVER(PARTITION BY ISNULL(T.LOCATION,'HOME') ORDER BY DETAY.GUN ) BAKIYE  FROM AY AS DETAY

LEFT JOIN 
(
SELECT LOCATION, 
      YEAR(TRXDATE) YIL, 
  MONTH(TRXDATE) AY,
  SUM(TRXAMOUNT) TUTAR 

FROM @TABLOM 

GROUP BY 
      YEAR(TRXDATE),
  MONTH(TRXDATE),
  LOCATION 
  
) AS T ON T.AY = DETAY.AY AND T.YIL = DETAY.YIL


ORDER BY 2

Allah bize yeter, O ne güzel vekildir.

vedatoozer@gmail.com

callen-557064 SSC-Addicted Points: 460 More actions · Answer 2

callen-557064

SSC-Addicted

Points: 460

April 21, 2021 at 2:03 pm

#3873954

Thank you. I'll give it a shot.

Steve Collins SSCrazy Points: 2069 More actions · Answer 3

;with
loc_cte(loc) as (
    select distinct location
    from @tablom),
n(n) as (
    select n 
    from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n)),
tally(dt) as (
    select top(datediff(month, @baslama, @bitis)+1) 
           dateadd(month, (row_number() over (order by (select null)))-1, 
                   datefromparts(year(@baslama), month(@baslama), 1))
    from n n1, n n2, n n3)
select l.loc, eomonth(t.dt) gun, isnull(tb.trxamount, 0) tutar,
       sum(tb.trxamount) over (partition by l.loc order by t.dt) bakiye
from loc_cte l
     cross join tally t
     left join @tablom tb on l.loc=tb.location
                             and tb.trxdate>=t.dt
                             and tb.trxdate<=eomonth(t.dt)
order by l.loc, t.dt;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können