YTD calculations - 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

YTD calculations

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-02-05 : 12:04:25

Hi All,

I have to get the YTD of all orders received since Jan 1 for the year of Monday of the week for which the query is ran (@Start_Recd_Date). and end on Sunday of that week
Eg: If I run the query today (2/5) the YTD should be from (1/5) to (2/1).

Below is my query:

select 'YTD' YTD, isnull(count(*),0) Tot_Count, Type, Bill_Desc, product
from Channels with (nolock)
where order_Received_date >= '01/01/' + convert(varchar(4), case when Month(@Start_Recd_Date) = 1 then (year(@Start_Recd_Date)-1) else year(@Start_Recd_Date) end)
and order_Received_date < @Start_Recd_Date
Group by Bill_Desc, product) YTDTable

I do not know how do I put in Mon of the week and ending Sun.

Thanks,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-05 : 12:25:15

are you using sql 2005?

Petronas
Posting Yak Master

134 Posts

Posted - 2009-02-05 : 14:01:54

Hi Visakh16,

yes, I am using sql 2005. I have modified the above query to:

select 'YTD' YTD, isnull(count(*),0) Tot_Count, Type, Bill_Desc, product
from Channels with (nolock)
where year(order_Received_date) >= year (@start_recd_date)
and month(order_Received_date)<= month(@start_recd_date)
Group by Bill_Desc, product) YTDTable

but still cannot figure out how do I get the Mon and Sun restrictions.

Thanks for your help and your time,
Petronas

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-02-05 : 15:43:59

Hello !

Im not sure as to what you are looking for. If you are looking for the First Occurence of Monday & Last Occurence of Sunday in a month or begining & end of months, here is something that you can look at.

If this not what you are looking for, can please be a bit more explanative as to what you need.

regards,
Anil Kumar.

select
BeginingOfMonth =
dateadd(month,datediff(month,0,getdate()),0),
EndOfMonth =
dateadd(day,-1,dateadd(month,datediff(month,0,getdate())+1,0)),
FirstMondayInMonth =
dateadd(day, datediff(day, 0, dateadd(month,datediff(month,0,getdate()),0))+1/7*7, 0),
LastSundayInMonth =
dateadd(day, datediff(day, 0, dateadd(day,-1,dateadd(month,datediff(month,0,getdate())+1,0)))/7*7, -1),

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-06 : 08:58:17

may be this


select 'YTD' YTD, isnull(count(*),0) Tot_Count, Type, Bill_Desc, product
from Channels with (nolock) 
where order_Received_date >= dateadd(yy,datediff(yy,0,@start_recd_date),datepart(dd,@start_recd_date)-1) 
and order_Received_date<= dateadd(wk,datediff(wk,0,@start_recd_date),0)
Group by Bill_Desc, product

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources