Date Weeks Questions - 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 2005 Forums

Transact-SQL (2005)

Date Weeks Questions

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-11 : 09:43:51

Hi Guys

I am using the following query to gather the number of sales made for the last 5 weeks:

SELECT
DATEPART(wk,ri.DateEntered) AS 'Week Number'
,COUNT(ri.receiptid)
FROM [dbo].[voiditem] vi (nolock)
INNER JOIN [dbo].[receiptitem] AS ri (nolock) ON vi.receiptitemid = ri.receiptitemid
INNER JOIN [dbo].[voidreason] AS vr (nolock) ON vi.enteredbyreasonid = vr.voidreasonid
WHERE ri.dateentered >= DATEADD(week, -5, GETDATE())
GROUP BY DATEPART(wk,ri.DateEntered)
ORDER BY DATEPART(wk,ri.DateEntered)

These gives me results similar to the following:
41 39342
42 84031
43 70247
44 56838
45 39064
46 11718

Is it possible to change the query so that it still displays the last 5 weeks worth of data but the following way:

1 39342
2 84031
3 70247
4 56838
5 39064
6 11718

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-11 : 09:58:36

[code]SELECT 6 + DATEDIFF(WEEK, GETDATE(), ri.DateEntered) AS [Week Number],
COUNT(ri.receiptid)
FROM dbo.voiditem AS vi (nolock)
INNER JOIN dbo.receiptitem AS ri (nolock) ON ri.receiptitemid = vi.receiptitemid
INNER JOIN dbo.voidreason AS vr (nolock) ON vr.voidreasonid = vi.enteredbyreasonid
WHERE ri.dateentered >= DATEADD(week, -5, GETDATE())
AND ri.dateentered < DATEADD(day, 1, GETDATE())
GROUP BY 6 + DATEDIFF(WEEK, GETDATE(), ri.DateEntered)
ORDER BY 6 + DATEDIFF(WEEK, GETDATE(), ri.DateEntered)[/code]

E 12°55'05.63"
N 56°04'39.26"