SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using Date and time range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phate06
Starting Member

17 Posts

Posted - 06/10/2013 :  11:05:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/10/2013 :  11:54:26  Show Profile  Reply with Quote
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/10/2013 :  12:23:14  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/10/2013 :  14:54:27  Show Profile  Reply with Quote
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.
Go to Top of Page

phate06
Starting Member

17 Posts

Posted - 06/11/2013 :  03:49:26  Show Profile  Reply with Quote
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]


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/11/2013 :  04:01:59  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/11/2013 :  08:24:22  Show Profile  Reply with Quote
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'


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000