Using Date and time range - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Using Date and time range

Author

Topic

phate06
Starting Member

17 Posts

Posted - 2013-06-10 : 11:05:00

HI There

I need to do a report which details sales on each week i.e 1-52, is there anyway I can use the datetime column to select a range so between 2013-01-01 00:00:00 and 2013-01-08 23:59:59 will be displayed as Week 1.

I have the other columns that I would like to display sales, and depot etc.. I just need to be able to show the accumalted daily sales for one week and show that between those 7 days it equals xxx which relates to Week 1 through to Week 52

Any ideas

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-10 : 11:54:26

Group by or use the range as DATEPART(dayofyear,YourDateColumn)+1. For example, to calculate what week today is in you would use this:

SELECT DATEPART(dayofyear,GETDATE())/7+1 AS CurrentWeek

Be aware that the weeks would run from 1-53, not from 1-52.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2013-06-10 : 12:23:14

The range of 2013-01-01 00:00:00 thru 2013-01-08 23:59:59 is 8 days.

How do you define your "week"? Does it begin on a certain day of the week? Is it just the first 7 days of the year for week 1, followed by the next 7 for week 2, etc.?

CODO ERGO SUM

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-10 : 14:54:27

quote:
Originally posted by phate06

HI There

I need to do a report which details sales on each week i.e 1-52, is there anyway I can use the datetime column to select a range so between 2013-01-01 00:00:00 and 2013-01-08 23:59:59 will be displayed as Week 1.

I have the other columns that I would like to display sales, and depot etc.. I just need to be able to show the accumalted daily sales for one week and show that between those 7 days it equals xxx which relates to Week 1 through to Week 52

Any ideas

By the way, this occurred to me only after I came back to this post to read MVJ's post; your first week consists of 8 days. Is that just a typo, or is that based on some rule?

The query I posted was under the assumption that you always want to start your week on the first day of the year, and every seven-day chunks starting on the first are counted as a week.

phate06
Starting Member

17 Posts

Posted - 2013-06-11 : 03:49:26

Hi there

Yes was a typo, I think i have foud a way using CASE e.g

CASE
WHEN IH.InvoiceDate >= '2012-12-31 00:00:00' and IH.InvoiceDate < '2013-01-06 23:59:59'
THEN (IH.TotalAmountInCurrency + IH.TotalVatAmountInCurrency)
ELSE 0
END AS [Week1]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 04:01:59

quote:
Originally posted by phate06

Hi there

Yes was a typo, I think i have foud a way using CASE e.g

CASE
WHEN IH.InvoiceDate >= '2012-12-31 00:00:00' and IH.InvoiceDate < '2013-01-06 23:59:59'
THEN (IH.TotalAmountInCurrency + IH.TotalVatAmountInCurrency)
ELSE 0
END AS [Week1]

this would cause any last second created records to be ignored. A more safer option is


WHEN IH.InvoiceDate >= '2012-12-31 00:00:00' and IH.InvoiceDate < '2013-01-07'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-11 : 08:24:22

quote:
Originally posted by phate06

Hi there

Yes was a typo, I think i have foud a way using CASE e.g

CASE
WHEN IH.InvoiceDate >= '2012-12-31 00:00:00' and IH.InvoiceDate < '2013-01-06 23:59:59'
THEN (IH.TotalAmountInCurrency + IH.TotalVatAmountInCurrency)
ELSE 0
END AS [Week1]

Three questions about this approach:

1. Does the week start on Monday? In your original post, you indicate it started on the first day of the year, rather than on any specific weekday.

2. If you use this approach, won't you have to write 52 or 53 case expressions for each year if you wanted to cover all the weeks?

3. It is better to use one of the following, preferably the second instead of what you are using even if you choose to use the case expression as you have written:

WHEN IH.InvoiceDate >= '2012-12-31 00:00:00' and IH.InvoiceDate <= '2013-01-06 23:59:59'
-- or 
WHEN IH.InvoiceDate >= '2012-12-31 00:00:00' and IH.InvoiceDate < '2013-01-07 00:00:00'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources