How do I get ytd totals in group by statement.

Question

0.00/5 (No votes)

See more:

I need YTD Totals in below mention query i can FY totals but i am failing to get YTD Totals (YTD period Apr to till date). Please help.

What I have tried:

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)

Posted 8-Aug-24 3:29am

mrrobottelg

Add a Solution

Comments

RedDk 8-Aug-24 13:56pm

I'll help you but you'll have to provide the merest amount of data and some structure to hold it. Because as-is, all I can do is look at an error message from ssmse: "Msg 208, Level 16, State 1, Line 4 Invalid object name 'FY_Sale_data'". Ok?

mrrobottelg 9-Aug-24 4:06am

Sample data.
https://sendanywhe.re/Y36SOLEH

Expected result
https://sendanywhe.re/MR265YFX

CHill60 9-Aug-24 6:23am

Put sample data and expected result into your question, not on a link outside this site (which incidentally is blocked for me at work)

RedDk 9-Aug-24 15:01pm

I suppose what I'm doing by pasting this link here in CP is saying "go here for answers to TSQL questions" but I'm pressed for time:
https://learn.microsoft.com/en-us/answers/questions/797847/how-to-get-wtd-mtd-and-qtd-in-sql-server
Do ask TSQL questions here in CP forums. I've found following the leader @ MS to be somewhat frustrating and often akin to talking to a parrot that knows me. Here I'm a stranger.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RAMASWAMY EKAMBARAM · Answer 1 · 2024-08-10T23:58:00

I am unable to follow your requirement and the link for the expected result is reported as invalid.
Assuming it is for financial year (01-Apr-2024 to 31-Mar-2025) if you want something like this:
Apr-2024 - (sum of bills from 01-apr-2024 to 30-apr-2024)
May-2024 - (sum of bills from 01-apr-2024 to 31-may-2024)
...
Mar-2025 - (sum of bills from 01-apr-2024 to 31-mar-2025)
The example is in PostgreSQL but the query should be similar in SQL Server. The monthwise totals for the random date / amount are also shown for easy validation of the YTD values:

with 
months_cte as
(
	select '2024-04-30'::date month_end union
	select '2024-05-31'::date month_end union
	select '2024-06-30'::date month_end union
	select '2024-07-31'::date month_end 
),
bills_cte as
(
	select '2024-04-01'::date bill_date, 1125 bill_amount union
	select '2024-04-19'::date, 916 union
	select '2024-04-24'::date, 1632 union
	select '2024-05-04'::date, 1962 union
	select '2024-05-16'::date, 1468 union
	select '2024-05-29'::date, 1279 union
	select '2024-06-06'::date, 863 union
	select '2024-06-17'::date, 1969 union
	select '2024-06-26'::date, 1213 union
	select '2024-07-07'::date, 1310 union
	select '2024-07-18'::date, 1576 union
	select '2024-07-23'::date, 1831 union
	select '2024-07-27'::date, 1003 
)
select to_char(month_end, 'Mon-yyyy') || '-YTD'  month_end, 
	sum(case when bill_date <= month_end then bill_amount else 0 end) bill_sum
from months_cte inner join bills_cte on true
group by to_char(month_end, 'Mon-yyyy')  || '-YTD'
	union 
select to_char(bill_date, 'Mon-yyyy') || '-TOTAL'  , sum(bill_amount) month_sum
from bills_cte 
group by to_char(bill_date, 'Mon-yyyy') || '-TOTAL'
;

Output:

Monthwise Total
Apr-2024-TOTAL	        3673
May-2024-TOTAL	        4709
Jun-2024-TOTAL	        4045
Jul-2024-TOTAL	        5720

YTD - from 01-APR-2024 to end of applicable month
Apr-2024-YTD	        3673
May-2024-YTD	        8382
Jun-2024-YTD	       12427
Jul-2024-YTD	       18147