Date function Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date function Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-30 : 06:22:23

HI guys

I need some help

I have a built a query and i am trying to do a comparison of 2012 vs 2011
I would like to mention that the data we have is a month behind

So although we are in august 2012 we will have july 2012 data
Once in sept 2012 we have august 2012 data etc etc

currently my query is brining all of 2011 data through and ytd of 2012 . Which isn’t giving me an accurate comparison

Can some help me write a query,

I need the query to go along the lines of, if i run the report in august, it will grab the previous month data eg july , it will also grab up to July data of 2011 so i can do the comparison

I need this query to work everytime i run the report,

Someone helped me and i got the query along the lines of

--where (hst_date_processed >= dateadd(year, datediff(year, 0, getdate()) - 1, 0)
--and hst_date_processed <= dateadd(year, -1, getdate()))
--or hst_date_processed >= dateadd(year, datediff(year, 0, getdate()), 0)

But i am not sure if this is on the right lines or not

hst_date_processed format is displayed as 2012-06-01

i would appreciate any help available

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-30 : 06:34:18

This is one way...

DECLARE	@Today DATE = GETDATE();

DECLARE	@PreviousMonthStart DATE = DATEADD(MONTH, DATEDIFF(MONTH, '19000201', @Today), '19000101'),
	@PreviousMonthEnd DATE = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @Today), '19000101'),
	@OldMonthStart DATE = DATEADD(MONTH, DATEDIFF(MONTH, '19010201', @Today), '19000101'),
	@OldMonthEnd DATE = DATEADD(MONTH, DATEDIFF(MONTH, '19010101', @Today), '19000101');

SELECT		theOfficeColumn,
		SUM(CASE WHEN HST_Date_Processed >= @PreviousMonthStart AND HST_Date_Processed < @PreviousMonthEnd THEN theAggregatedColumn ELSE 0 END) AS ThisYear,
		SUM(CASE WHEN HST_Date_Processed >= @OldMonthStart AND HST_Date_Processed < @OldMonthEnd THEN theAggregatedColumn ELSE 0 END) AS PreviousYear
FROM		dbo.Table1
WHERE		HST_Date_Processed >= @OldMonthStart
		AND HST_Date_Processed < @PreviousMonthEnd
GROUP BY	theOfficeColumn

N 56°04'39.26"
E 12°55'05.63"

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-30 : 06:44:15

Hi Swepeso

Sorry for my lack of knowledge here

Can you explain to me how your query works ?

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-30 : 06:51:52

try this
declare @stdt datetime , @enddt datetime

select @stdt = convert(datetime,convert(varchar,month(GETDATE())-1)+'/01'+'/'+convert(varchar,year(GETDATE())))

, @enddt = dateadd(dd,-1,convert(datetime,convert(varchar,month(GETDATE())+1)+'/01'+'/'+convert(varchar,year(GETDATE()))))

where (hst_date_processed between @stdt and @enddt
or hst_date_processed between dateadd(YEAR,-1,@stdt) and dateadd(YEAR,-1,@enddt))

select @stdt , @enddt

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-30 : 06:53:49

Hi swepeso

this is my table

SELECT TOP 1000
[hst_merchnum]
,[hst_date_processed]
,[Net_Sales]
FROM [FDMS].[dbo].[Fact_Financial_History]

I want to sum Net_Sales by the year (which i have written below)

SUM(Case when year(hst_date_processed) = '2011' then [Net_Sales] else 0 end) as [NetSales_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],

How can i put your query in , which applies to the above ?

So for eg, above, figures should return jan - july 2011 net sales and also jan - july 2012 net sales

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-30 : 07:21:41

declare @stdt datetime , @enddt datetime

select @stdt = convert(datetime,convert(varchar,month(GETDATE())-1)+'/01'+'/'+convert(varchar,year(GETDATE())))

, @enddt = dateadd(dd,-1,convert(datetime,convert(varchar,month(GETDATE())+1)+'/01'+'/'+convert(varchar,year(GETDATE()))))

SELECT
SUM(Case when year(hst_date_processed) = '2011' then [Net_Sales] else 0 end) as [NetSales_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],
FROM [FDMS].[dbo].[Fact_Financial_History]
where (hst_date_processed between @stdt and @enddt
or hst_date_processed between dateadd(YEAR,-1,@stdt) and dateadd(YEAR,-1,@enddt))

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-30 : 07:34:12

Hi Shaggy

Sorry to bother you again,

i want to test his hst_merchnum ='878000028'
to make sure its bringing in the correct figures

where would i stick that within the query you provided ?

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-30 : 07:38:11

what is the executed output ? (columns needed in select) or post your whole query.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-30 : 07:43:27

Basically all i need to see the following below
eg
hst_merchnum NetSales_2011 NetSales_2012],
878000028 2500 5000

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-30 : 08:17:57

declare @stdt datetime , @enddt datetime

select @stdt = convert(datetime,convert(varchar,month(GETDATE())-1)+'/01'+'/'+convert(varchar,year(GETDATE())))

, @enddt = dateadd(dd,-1,convert(datetime,convert(varchar,month(GETDATE())+1)+'/01'+'/'+convert(varchar,year(GETDATE()))))

SELECT
hst_merchnum,
SUM(Case when year(hst_date_processed) = '2011' then [Net_Sales] else 0 end) as [NetSales_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],
FROM [FDMS].[dbo].[Fact_Financial_History]
where (hst_date_processed between @stdt and @enddt
or hst_date_processed between dateadd(YEAR,-1,@stdt) and dateadd(YEAR,-1,@enddt))
group by hst_merchnum

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-03 : 09:55:18

quote:
Originally posted by shaggy

declare @stdt datetime , @enddt datetime

select @stdt = convert(datetime,convert(varchar,month(GETDATE())-1)+'/01'+'/'+convert(varchar,year(GETDATE())))

, @enddt = dateadd(dd,-1,convert(datetime,convert(varchar,month(GETDATE())+1)+'/01'+'/'+convert(varchar,year(GETDATE()))))

SELECT
hst_merchnum,
SUM(Case when year(hst_date_processed) = '2011' then [Net_Sales] else 0 end) as [NetSales_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],
FROM [FDMS].[dbo].[Fact_Financial_History]
where (hst_date_processed between @stdt and @enddt
or hst_date_processed between dateadd(YEAR,-1,@stdt) and dateadd(YEAR,-1,@enddt))
group by hst_merchnum

This depends on the date setting of the session. Do not use this. Peso already showed a correct way.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources