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

my mistake......... my apology

Madhu

Ah, the problem is that the DATEPART(Week, &lt;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. <br /><br />Get the week number for the given date,<br />DATEPART(WEEK, OrderDate)<br /><br />subtract the week number for the first of the month,<br />- DATEPART(WEEK, YEAR(OrderDate) + '-' + MONTH(OrderDate) + '-1')<br /><br />and add one:<br />+ 1<br /><br />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.

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.

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 &lt;your_table&gt; GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)<br /><br />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?<br /><br />Thanks~ [<img src='/community/emoticons/emotion-2.gif' alt='' />]

Get the week number for the given date,<br />DATEPART(WEEK, OrderDate)<br /><br />subtract the week number for the first of the month,<br />- DATEPART(WEEK, YEAR(OrderDate) + '-' + MONTH(OrderDate) + '-1')<br /><br />and add one:<br />+ 1<br /><br />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~ ^_^

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~ ^_^

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"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Taurusgal83</i><br /><br />CREATE FUNCTION in BOL? Mind I ask what is BOL? How can I see the explanations??<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />BOL is Books OnLine. aka SQL Server help file<br /><br />Here it is. Look for CREATE FUNCTION. It is there as Frank pointed<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Examples<br />A. Scalar-valued user-defined function that calculates the ISO week<br />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. <br /><br />CREATE FUNCTION ISOweek (@DATE datetime)<br />RETURNS int<br />AS<br />BEGIN<br /> DECLARE @ISOweek int<br /> SET @ISOweek= DATEPART(wk,@DATE)+1<br /> -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')<br />--Special cases: Jan 1-3 may belong to the previous year<br /> IF (@ISOweek=0) <br /> SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 <br /> AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1<br />--Special case: Dec 29-31 may belong to the next year<br /> IF ((DATEPART(mm,@DATE)=12) AND <br /> ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))&gt;= 2<img src='/community/emoticons/emotion-11.gif' alt='8)' />)<br /> SET @ISOweek=1<br /> RETURN(@ISOweek)<br />END<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><hr noshade size="1"><br /><font size="1">KH</font id="size1">

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

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 <br />FROM #t<br />GROUP BY MONTH(OrderDate), dbo.ISOweek(OrderDate)<br /><br /><br />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 'dbrder.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:<br /><br /><br />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 <br />FROM #t <br />GROUP BY MONTH(OrderDate), OrderDate<br /><br /><br />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='' />]

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='' />]