How to calculate YTD from Given From date ?

Question

Post reply

How to calculate YTD from Given From date ?

raghuldrag

Hall of Fame

Points: 3160
More actions
July 26, 2016 at 6:36 am

#309848

Hi Friends,
I have a table like
Custmer bill Date location Bill_value
102 01-04-2011 A 12
102 05-04-2011 A 16
102 06-05-2011 A 20
102 07-07-2011 A 40
102 08-07-2011 A 15
i wanna create a sql report YTD wise customer billing based from july month
from date='01-jul-2011' and to date='31-jul-2011'
Customer locn current_month_value YTD_value
102 A 55 91
Note : here i wanna take a calculation financial year each year apr-11 to march-12
pl help me out?

Viewing 7 posts - 1 through 6 (of 6 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

maybe?....though my YTD is different than yours ?!?

SET DATEFORMAT DMY

CREATE TABLE #yourtable(

Custmer INTEGER NOT NULL

,Date DATETIME NOT NULL

,location VARCHAR(2) NOT NULL

,Bill_value INTEGER NOT NULL

);

INSERT INTO #yourtable(Custmer,Date,location,Bill_value) VALUES (102,'01-04-2011','A',12);

INSERT INTO #yourtable(Custmer,Date,location,Bill_value) VALUES (102,'05-04-2011','A',16);

INSERT INTO #yourtable(Custmer,Date,location,Bill_value) VALUES (102,'06-05-2011','A',20);

INSERT INTO #yourtable(Custmer,Date,location,Bill_value) VALUES (102,'07-07-2011','A',40);

INSERT INTO #yourtable(Custmer,Date,location,Bill_value) VALUES (102,'08-07-2011','A',15);

SELECT * FROM #yourtable

DECLARE @mth_st DATETIME = '20110701';

DECLARE @yr_st DATETIME = '20110401';

DECLARE @mth_nx DATETIME = DATEADD(MONTH,1,@mth_st);

DECLARE @yr_nx DATETIME = DATEADD(YEAR,1,@yr_st);

SELECT

Custmer,

location,

SUM(CASE WHEN [Date] >= @mth_st AND [Date] < @mth_nx THEN Bill_value ELSE 0 END) AS cur_mth,

SUM(CASE WHEN [Date] >= @yr_st AND [Date] < @yr_nx THEN Bill_value ELSE 0 END) AS ytd

FROM #yourtable

GROUP BY Custmer, location

DROP TABLE #yourtable

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Just a small correction to J Livingston's query to avoid scanning the whole table.

SELECT

Custmer,

location,

SUM(CASE WHEN [Date] >= @mth_st AND [Date] < @mth_nx THEN Bill_value ELSE 0 END) AS cur_mth,

SUM(Bill_value) AS ytd

FROM #yourtable

WHERE [Date] >= @yr_st AND [Date] < @yr_nx

GROUP BY Custmer, location

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Luis Cazares (7/26/2016)
Just a small correction to J Livingston's query to avoid scanning the whole table.
SELECT
Custmer,
location,
SUM(CASE WHEN [Date] >= @mth_st AND [Date] < @mth_nx THEN Bill_value ELSE 0 END) AS cur_mth,
SUM(Bill_value) AS ytd
FROM #yourtable
WHERE [Date] >= @yr_st AND [Date] < @yr_nx
GROUP BY Custmer, location

thanks Luis.....should have seen that one 🙂

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

raghuldrag Hall of Fame Points: 3160 More actions · Answer 4

Hi Friends,

Without setting a financial year start and end date I wanna get the output

i.e

the user takes a report from '01-jun-2010' and '30-jun-2010' (only input from the user),

we have to calculate financial year through the user input

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

raghuldrag (7/26/2016)
Hi Friends,
Without setting a financial year start and end date I wanna get the output
i.e
the user takes a report from '01-jun-2010' and '30-jun-2010' (only input from the user),
we have to calculate financial year through the user input

does this help ?

DECLARE @mth_st DATETIME = '20100601';

DECLARE @mth_nx DATETIME = DATEADD(MONTH,1,@mth_st);

DECLARE @year INT

IF (MONTH(@mth_st) >= 4)

SET @year = YEAR(@mth_st)

ELSE

SET @year = YEAR(@mth_st) - 1

DECLARE @yr_st DATETIME = DATEADD(MONTH, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @year - 1900, 0)) , 0))

DECLARE @yr_nx DATETIME = DATEADD(YEAR,1,@yr_st);

PRINT @mth_st;

PRINT @mth_nx

PRINT @yr_st;

PRINT @yr_nx;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

HappyGeek SSCoach Points: 18726 More actions · Answer 6

Since you are doing a report you want to have a look at this article here: http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/133147/

...