Weeks - 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 2000 Forums

Transact-SQL (2000)

Weeks

Author

Topic

Adria
Starting Member

2 Posts

Posted - 2003-05-02 : 08:08:11

Hi.

so let me explain what i have:
structure:
date,product code, group, sales(quantity)....
would like to get
how many sales were made for each product grouped by weeks..

so perfect would be if i could get something like this
group 1
January
week1
product code sum(sales)
...........
week2
............

any ideas?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-02 : 09:05:11

DECLARE @tablex TABLE ([date] DATETIME, [product code] NVARCHAR(25), [group] NVARCHAR(25), quantity INTEGER)

INSERT INTO @tablex ([date],[product code],[group],quantity)
SELECT '20030101','a','group 1',1 UNION ALL
SELECT '20030101','a','group 2',2 UNION ALL
SELECT '20030108','a','group 1',3 UNION ALL
SELECT '20030108','a','group 2',4 UNION ALL
SELECT '20030115','a','group 1',5 UNION ALL
SELECT '20030115','a','group 2',6 UNION ALL
SELECT '20030122','a','group 1',7 UNION ALL
SELECT '20030122','a','group 2',8 UNION ALL
SELECT '20030129','a','group 1',9 UNION ALL
SELECT '20030129','a','group 2',10 UNION ALL
--
SELECT '20030101','b','group 1',1 UNION ALL
SELECT '20030101','b','group 2',1 UNION ALL
SELECT '20030108','b','group 1',1 UNION ALL
SELECT '20030108','b','group 2',1 UNION ALL
SELECT '20030115','b','group 1',1 UNION ALL
SELECT '20030115','b','group 2',1 UNION ALL
SELECT '20030122','b','group 1',1 UNION ALL
SELECT '20030122','b','group 2',1 UNION ALL
SELECT '20030129','b','group 1',1 UNION ALL
SELECT '20030129','b','group 2',1 UNION ALL
--
SELECT '20030201','a','group 1',2 UNION ALL
SELECT '20030201','a','group 2',2 UNION ALL
SELECT '20030208','a','group 1',2 UNION ALL
SELECT '20030208','a','group 2',2 UNION ALL
SELECT '20030215','a','group 1',2 UNION ALL
SELECT '20030215','a','group 2',2 UNION ALL
SELECT '20030222','a','group 1',2 UNION ALL
SELECT '20030222','a','group 2',2

SELECT [group], DATENAME(month,[date]) MonthName, SUM(quantity) SumOfQuantity
FROM @tablex
GROUP BY [group], DATENAME(month,[date])

SELECT [group], DATENAME(month,[date]) MonthName, Datepart(wk, [date]) WeekOfYear, [product code],SUM(quantity) SumOfQuantity
FROM @tablex
GROUP BY [group], DATENAME(month,[date]),Datepart(wk, [date]), [product code]
ORDER BY [group], Datepart(wk, [date]), [product code], SUM(Quantity)

SELECT [group], DATENAME(month,[date]) MonthName, DatePart(wk, [date])-DatePart(wk, DateAdd(d, 1-DatePart(d, [date]), [date]))+1 WeekOfMonth, [product code],SUM(quantity) SumOfQuantity
FROM @tablex
GROUP BY [group], DATENAME(month,[date]),DatePart(wk, [date])-DatePart(wk, DateAdd(d, 1-DatePart(d, [date]), [date]))+1, [product code]
ORDER BY [group], DatePart(wk, [date])-DatePart(wk, DateAdd(d, 1-DatePart(d, [Date]), [Date]))+1, [product code], SUM(Quantity)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources