SQL Server Performance Forum – Threads Archive

How to determine particular date is in which week?

Hi there~ I need some help on this problem. How should I write the SQL coding so that I could know the order that I get on that particular date is in which week of the month? And how can i get the total order for Week 1, Week 2, Week 3, Week 4 for particular month? Example:
Order No. Order Date Amount
1 01.01.07 200.00
2 06.01.07 300.00
3 16.01.07 540.00
4 01.02.07 600.00 Desired Output:
Year Month Week Amount
07 1 1 500.00
07 1 2 540.00
07 2 1 600.00 I need the output like the above shown. Is there anyone can guide me on this? Thanks in advance.. ^_^

Hi, select DATEPART ( year, OrderDate) as Year,DATEPART ( Month, OrderDate) as Month,DATEPART ( week, OrderDate) as Week,Amount from yourtable order by orderdate Madhu

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by madhuottapalam Hi, select DATEPART ( year, OrderDate) as Year,DATEPART ( Month, OrderDate) as Month,DATEPART ( week, OrderDate) as Week,Amount from yourtable order by orderdate Madhu <hr height="1" noshade id="quote"></blockquote id="quote"> That doesn’t do any grouping! [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Try something like this: <pre id="code"> SELECT YEAR(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) , MONTH(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) , DATEPART(WEEK, (DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0))) , SUM(Amount) FROM <your_table> GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0) </pre id="code"> You probably need to check for the correct first day of the year. To do so, check CREATE FUNCTION in BOL. You’ll find there the ISOWeek example. – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

my mistake……… my apology Madhu

Ah, the problem is that the DATEPART(Week, <date&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> gives you the week number for the whole year, not the week within the month. Get the week number for the given date, DATEPART(WEEK, OrderDate) subtract the week number for the first of the month, - DATEPART(WEEK, YEAR(OrderDate) + ‘-‘ + MONTH(OrderDate) + ‘-1’) and add one: + 1 There is a way to use DATEADD instead of YEAR(OrderDate) + ‘-‘ + MONTH(OrderDate) + ‘-1′ – I’m pretty sure it’s documented in Frank Kalis’ article about dates, but I can’t seem to find it on this site.

Excellent catch!!! So, what about something like this?
DROP TABLE #t
CREATE TABLE #t
(
OrderNo INT
, OrderDate DATETIME
, Amount INT
) INSERT INTO #t SELECT 1, ‘20070101’, 200
UNION ALL SELECT 2, ‘20070106’, 300
UNION ALL SELECT 3, ‘20070116’, 300
UNION ALL SELECT 4, ‘20070201’, 600 SELECT
YEAR(MIN(OrderDate)) AS [Year]
, MONTH(OrderDate) AS [Month]
, dbo.ISOweek(MIN(OrderDate))
–
dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month]
, SUM(Amount) AS Amount
FROM #t
GROUP BY MONTH(OrderDate), dbo.ISOweek(OrderDate)
Year Month Week in month Amount
———– ———– ————- ———–
2007 1 1 500
2007 1 3 300
2007 2 1 600 (3 row(s) affected)
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

That’s a step in the right direction, but you know this type of question – they want it in a cross-tab fashion. Don’t you love it.

Hm, I don’t see much of a difference between the required output and mine. Apart from this formatting thingy, of course. But you’re right, who knows aynway… [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Btw, the GROUP BY can be simplified to
GROUP BY dbo.ISOweek(OrderDate) If that report spans more than 1 year, you get problems anyway. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

SELECT YEAR(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) , MONTH(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) , DATEPART(WEEK, (DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0))) , SUM(Amount) FROM <your_table> GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0) This example I have try already but when I try to run the coding, it shows an error message saying that "The year functions requires 1 argument". Any solutions? Thanks~ [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

Get the week number for the given date, DATEPART(WEEK, OrderDate) subtract the week number for the first of the month, - DATEPART(WEEK, YEAR(OrderDate) + ‘-‘ + MONTH(OrderDate) + ‘-1′) and add one: + 1 This solutions doesn’t seems working out for me. I dont know whether the way I type out is wrong/ what. Can you show me the proper SQL sample? Thanks~ [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

I tried to run your query, it does not gives me the same error message "The year functions requires 1 argument" but "OrderDate is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." Actually your YEAR(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) is correct there is nothing wrong with it. The problem is the GROUP BY. You need to
GROUP BY YEAR(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) ,
MONTH(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) ,
DATEPART(WEEK, (DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0))) By the way, what’s wrong with Frank’s solution ? Doesn’t it work for you ? The result is exactly what you posted.
KH

Or perhaps this is what you want ? DROP TABLE #t
CREATE TABLE #t
(
OrderNo INT
, OrderDate DATETIME
, Amount INT
) INSERT INTO #t
SELECT 1, ‘20070101’, 200UNION ALL
SELECT 2, ‘20070106’, 300UNION ALL
SELECT 3, ‘20070116’, 540UNION ALL
SELECT 4, ‘20070201’, 600UNION ALL
SELECT 5, ‘20061205’, 400 select[Year],
[Month] = month(dateadd(week, WeekNo, dateadd(year, [Year] – 1900, 0))),
WeekNo,
Amount
from
(
select[Year]= year(OrderDate),
WeekNo = datepart(week, OrderDate),
Amount= sum(Amount)
from#t
group by year(OrderDate), datepart(week, OrderDate)
) d
order by [Year], [Month], [WeekNo] /* RESULT
Year Month WeekNo Amount
———– ———– ———– ———–
2006 12 49 400
2007 1 1 500
2007 1 3 540
2007 2 5 600
*/ KH

Thanks KHTan for your advice.The coding manage to produce the outputs already but the week is wrong. Cause supposely date 01.02.07 is consider Week 1 for February, but is counted as Week 5. That is not my desired output. Any idea to solve again? ^_^

DROP TABLE #t
CREATE TABLE #t
(
OrderNo int,
OrderDate datetime,
Amount int
) INSERT INTO #t
SELECT 1, ‘20070101’, 200UNION ALL
SELECT 2, ‘20070106’, 300UNION ALL
SELECT 3, ‘20070116’, 540UNION ALL
SELECT 4, ‘20070201’, 600UNION ALL
SELECT 5, ‘20061205’, 400 select[Year],
[Month],
WeekNo = WeekNo – datepart(week, dateadd(month, [Month] – 1, dateadd(year, [Year] – 1900, 0))) + 1,
Amount
from
(
select[Year],
[Month] = month(dateadd(week, WeekNo, dateadd(year, [Year] – 1900, 0))),
WeekNo,
Amount
from
(
select[Year]= year(OrderDate),
WeekNo = datepart(week, OrderDate),
Amount= sum(Amount)
from#t
group by year(OrderDate), datepart(week, OrderDate)
) a
) b
order by [Year], [Month], [WeekNo] /* RESULT
Year Month WeekNo Amount
———– ———– ———– ———–
2006 12 2 400
2007 1 1 500
2007 1 3 540
2007 2 1 600
*/ KH

Thanks KHTan~ It can manage show the example desired output. But when I apply to my real data, let’s say the Order Date is 28th January 2007, it suppose to be week 5 order amount for January, but it sums up to February 1st week. How should I do so that I can get the order 28th January 2007 amount show in week 5? Any idea? Thanks a lot~ ^_^

Just out of curiosity… You have seen my reply:
SELECT
YEAR(MIN(OrderDate)) AS [Year]
, MONTH(OrderDate) AS [Month]
, dbo.ISOweek(OrderDate)
–
dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month]
, SUM(Amount) AS Amount
FROM #t
GROUP BY MONTH(OrderDate), dbo.ISOweek(OrderDate) I think it does what you want. If not, please tell me what’s wrong with it. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

SELECT YEAR(MIN(OrderDate)) AS [Year] , MONTH(OrderDate) AS [Month] , dbo.ISOweek(OrderDate) – dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month] , SUM(Amount) AS Amount FROM #t GROUP BY MONTH(OrderDate), dbo.ISOweek(OrderDate)
Sorry Frank~ I have try the coding but when i run it, it says "Invalid object name ‘dbo.ISOweek’". Any idea how to solve? Thanks a lot~ ^_^

You will find the definition of dbo.ISOWeek, when you look at the explanations of CREATE FUNCTION in BOL. Just copy and paste that to your database, execute it to create the function and there you go. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

CREATE FUNCTION in BOL? Mind I ask what is BOL? How can I see the explanations??

try this ver. Changes is in bold
DROP TABLE #t
CREATE TABLE #t
(
OrderNo int,
OrderDate datetime,
Amount int
) INSERT INTO #t
SELECT 1, ‘20070101’, 200UNION ALL
SELECT 2, ‘20070106’, 300UNION ALL
SELECT 3, ‘20070128’, 540UNION ALL
SELECT 4, ‘20070201’, 600UNION ALL
SELECT 5, ‘20061205’, 400 select[Year],
[Month],
WeekNo = WeekNo – datepart(week, dateadd(month, [Month] – 1, dateadd(year, [Year] – 1900, 0))) + 1,
Amount
from
(
select[Year],
[Month],
WeekNo,
Amount
from
(
select[Year]= year(OrderDate),
[Month] = month(OrderDate),
WeekNo = datepart(week, OrderDate),
Amount= sum(Amount)
from#t
group by year(OrderDate), month(OrderDate), datepart(week, OrderDate)
) a
) b
order by [Year], [Month], [WeekNo] /* RESULT Year Month WeekNo Amount
———– ———– ———– ———–
2006 12 2 400
2007 1 1 500
2007 1 5 540
2007 2 1 600
*/ KH

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Taurusgal83 CREATE FUNCTION in BOL? Mind I ask what is BOL? How can I see the explanations?? <hr height="1" noshade id="quote"></blockquote id="quote"> BOL is Books OnLine. aka SQL Server help file Here it is. Look for CREATE FUNCTION. It is there as Frank pointed <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Examples A. Scalar-valued user-defined function that calculates the ISO week In this example, a user-defined function, ISOweek, takes a date argument and calculates the ISO week number. For this function to calculate properly, SET DATEFIRST 1 must be invoked before the function is called. CREATE FUNCTION ISOweek (@DATE datetime) RETURNS int AS BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+’0104′) –Special cases: Jan 1-3 may belong to the previous year IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+’12’+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 –Special case: Dec 29-31 may belong to the next year IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 2<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) SET @ISOweek=1 RETURN(@ISOweek) END <hr height="1" noshade id="quote"></blockquote id="quote"> <hr noshade size="1"> KH

Thanks KHTan for your codings~ It works nicely.. Thanks a lot~ ^_^

quote:Originally posted by Taurusgal83 CREATE FUNCTION in BOL? Mind I ask what is BOL? How can I see the explanations??

Sorry for that. Sometimes one uses technical terms like these without even thinking about it that someone else doesn’t even know them. BOL is the common abbreviation for the SQL Server Books Online, aka SQL Servers help files. They are exceptionally good and usually the first source to consult in any case. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Use a CASE statement.
case when day(calldatetime) between 1 and 7 then 1 when day(calldatetime) between 8 and 14 then 2 when day(calldatetime) between 15 and 21 then 3 when day(calldatetime) between 22 and 28 then 4 else 5 end WeekNumber, and then include that whole CASE statement in your GROUP BY clause as well (but remove the WeekNumber alias in the GROUP BY).
Add flight search to your own site for free
www.skyscanner.net

SELECT YEAR(MIN(OrderDate)) AS [Year] , MONTH(OrderDate) AS [Month] , dbo.ISOweek(OrderDate) – dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month] , SUM(Amount) AS Amount FROM #t GROUP BY MONTH(OrderDate), dbo.ISOweek(OrderDate) Frank, after I do the "Create Function" part that KHTan give the sample to me, I try run the coding u give. When it runs, it show an error msg "Column ‘db :confused:

rder.OrderDate’ is invalid in the select list because it is not contained in either an aggregrate function or the Group By clause". So I try change the Group By to: SELECT YEAR(MIN(OrderDate)) AS [Year] , MONTH(OrderDate) AS [Month] , dbo.ISOweek(OrderDate) – dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month] , SUM(Amount) AS Amount FROM #t GROUP BY MONTH(OrderDate), OrderDate It can show the week correctly but 01/01/07 & 06/01/07 order amount won’t sum up. Any missing part need to add up?[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Frank’s script actually work. If you just copy and paste and it will run. Anyway, the query that you posted, you missed out the MIN() on the OrderDate. I have highlighted it in red
SELECT YEAR(MIN(OrderDate)) AS [Year] ,
MONTH(OrderDate) AS [Month] ,
dbo.ISOweek(MIN(OrderDate)) –
dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month] ,
SUM(Amount) AS Amount
FROM #t
GROUP BY MONTH(OrderDate), dbo.ISOweek(OrderDate) KH

Thanks KHTan for showing me the missing part of the coding.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Sorry Frank for the silly mistake i make.[:I] Thanks a lot for giving me another solutions for solving this coding problem that I faced. Really appreciate the help given by u guys~ [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

Guess how many times I am wrong, oversee the obvious and all things like that. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Haha.. Sure a lot of times one. But important is we learn from mistake right? [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Feel great on this forum cause can learn faster than I thought it would be. Thanks a lot for those who has been giving me suggestion of coding for my problems. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

]]>