Need YTD Total and FY Total in Group by.

Question

Hello Everyone.

I Need YTD Totals in below mentiond results that is from Apr to Aug. I Have tried below query. Please do the needful.

1.txt

Expected result as below.

with lfy as (
select format(Billdate,'MMM') Mnth,sum(Netamt)Val,
	sum(case when billdate between '4/1/2023' and '8/31/2023' then Netamt end)YTD_total
from FY_Sale_Data where Fyear='2023-24'group by format(Billdate,'MMM')
),
cfy as (
select format(Billdate,'MMM') Mnth,sum(Netamt)Val,
	sum(case when billdate between '4/1/2024' and cast(billdate as date) then Netamt end)YTD_total
from FY_Sale_Data where Fyear='2024-25'  group by format(Billdate,'MMM')
)
select 
Mnth = CASE GROUPING(lfy.Mnth) WHEN 1 THEN 'FYTotal' ELSE lfy.Mnth END,
sum(lfy.Val)[2023-24],
isnull(sum(cfy.Val),0)[2024-25]
from lfy
left join cfy on lfy.Mnth=cfy.Mnth
group by rollup(lfy.Mnth)

Answer 1

Here is a solution. Since there were many rows in the sample data, I've truncated the INSERT statements.

CREATE TABLE #tempie (Month     char(3)  NOT NULL,
                      Amount    decimal(20,2) NOT NULL,
                      Billdate  date     NOT NULL PRIMARY KEY)
INSERT #tempie(Month, Amount, Billdate)
   VALUES ('Apr', 53710520.67, '1-Apr-23'),
          ('Apr', 70191273.32, '2-Apr-23'),
          ('Apr', 41530766.96, '3-Apr-23'),
          ('Apr', 36528427.09, '4-Apr-23'),
          ...
          ('Jun', 30032922.4, '25-Jun-24'),
          ('Jun', 34834711.24, '28-Jun-24'),
          ('May', 23067946.65, '13-May-24')
go
; WITH prepare AS (
    SELECT rowno   = isnull((Month(Billdate) + 8) % 12, 14),
           Overall = isnull(datename(Month, Billdate), 'FY Total'),
           "23-24" = SUM(CASE WHEN Billdate >= '20230401' AND Billdate < '20240401' THEN Amount END),
           "24-25" = SUM(CASE WHEN Billdate >= '20240401' AND Billdate < '20250401' THEN Amount END)
    FROM   #tempie
    GROUP  BY GROUPING SETS ((datename(Month, Billdate), Month(Billdate)), ())
    UNION ALL
    SELECT rowno = 13, Overall = 'YTD Total',
           "23-24" = SUM(CASE WHEN Billdate >= '20230401' AND 
                                   Billdate < dateadd(YEAR, -1, dateadd(DAY, 1, eomonth(sysdatetime()))) 
                              THEN Amount 
                         END),
           "23-24" = SUM(CASE WHEN Billdate >= '20240401' AND 
                                   Billdate <  dateadd(DAY, 1, eomonth(sysdatetime())) 
                              THEN Amount 
                         END)
    FROM  #tempie
)
SELECT Overall, "23-24", "24-25"
FROM   prepare
ORDER  BY rowno
go
DROP TABLE #tempie

Answer 2

Hi @mrrobottelg

If you mean YDT total and FY total for current year and last year, then try this:

SELECT ISNULL(FORMAT(Billdate,'MMM'),'Total') Mnth
      ,SUM(CASE WHEN Fyear='2023-24' THEN Netamt END) AS [2023-24_FY]
	  ,SUM(CASE WHEN Fyear='2023-24' AND MONTH(Billdate) BETWEEN 4 AND 8 THEN Netamt END) AS [2023-24_YTD]
	  ,SUM(CASE WHEN Fyear='2024-25' THEN Netamt END) AS [2024-25_FY]
	  ,SUM(CASE WHEN Fyear='2024-25' AND MONTH(Billdate) BETWEEN 4 AND 8 THEN Netamt END) AS [2024-25_YTD]
FROM FY_Sale_Data
GROUP BY ROLLUP(FORMAT(Billdate,'MMM')) 

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".