Group date by self define range

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)

Group date by self define range

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-08 : 22:11:50

[code]
SELECT SUM(mon1select * from st_trx
SELECT COALESCE(CASE WHEN do_date>='2007-01-01 00:00:00.000' and do_date<=DATEADD(DAY, +30,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon1,
COALESCE(CASE WHEN do_date>=DATEADD(DAY, +30,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*2,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon2,
COALESCE(CASE WHEN do_date>=DATEADD(DAY, +30*2,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*3,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon3,
COALESCE(CASE WHEN do_date>=DATEADD(DAY, +30*3,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*4,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon4,
COALESCE(CASE WHEN do_date>=DATEADD(DAY, +30*4,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*5,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon5,
COALESCE(CASE WHEN do_date>=DATEADD(DAY, +30*5,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*6,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon6,
COALESCE(CASE WHEN do_date>=DATEADD(DAY, +30*6,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*7,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon7,
COALESCE(CASE WHEN do_date>=DATEADD(DAY, +30*7,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*8,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon8,
COALESCE(CASE WHEN do_date>=DATEADD(DAY, +30*8,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*9,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon9,
COALESCE(CASE WHEN do_date>=DATEADD(DAY, +30*9,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*10,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon10,
COALESCE(CASE WHEN do_date>=DATEADD(DAY, +30*10,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*11,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon11,
COALESCE(CASE WHEN do_date>=DATEADD(DAY, +30*11,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*12,'2007-01-01 00:00:00.000')THEN SUM(quantity)END, 0)AS mon12
FROM ST_TRX
WHERE do_date>='2007-01-01 00:00:00.000' and do_date<='2007-12-31 00:00:00.000' and st_code = '1011'
GROUP BY do_date
[/code]
Hi, although this query may looks ok but it is returning multiple rows of data, how do i make it to return oni 1 rows?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 22:26:26

don't group by do_date

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-08 : 22:58:02

[code]
SELECT SUM(CASE WHEN do_date>='2007-01-01 00:00:00.000' and do_date<=DATEADD(DAY, +30,'2007-01-01 00:00:00.000')THEN quantity END)AS mon1,
SUM(CASE WHEN do_date>=DATEADD(DAY, +30,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*2,'2007-01-01 00:00:00.000')THEN quantity END)AS mon2,
SUM(CASE WHEN do_date>=DATEADD(DAY, +30*2,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*3,'2007-01-01 00:00:00.000')THEN quantity END)AS mon3,
SUM(CASE WHEN do_date>=DATEADD(DAY, +30*3,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*4,'2007-01-01 00:00:00.000')THEN quantity END)AS mon4,
SUM(CASE WHEN do_date>=DATEADD(DAY, +30*4,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*5,'2007-01-01 00:00:00.000')THEN quantity END)AS mon5,
SUM(CASE WHEN do_date>=DATEADD(DAY, +30*5,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*6,'2007-01-01 00:00:00.000')THEN quantity END)AS mon6,
SUM(CASE WHEN do_date>=DATEADD(DAY, +30*6,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*7,'2007-01-01 00:00:00.000')THEN quantity END)AS mon7,
SUM(CASE WHEN do_date>=DATEADD(DAY, +30*7,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*8,'2007-01-01 00:00:00.000')THEN quantity END)AS mon8,
SUM(CASE WHEN do_date>=DATEADD(DAY, +30*8,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*9,'2007-01-01 00:00:00.000')THEN quantity END)AS mon9,
SUM(CASE WHEN do_date>=DATEADD(DAY, +30*9,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*10,'2007-01-01 00:00:00.000')THEN quantity END)AS mon10,
SUM(CASE WHEN do_date>=DATEADD(DAY, +30*10,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*11,'2007-01-01 00:00:00.000')THEN quantity END)AS mon11,
SUM(CASE WHEN do_date>=DATEADD(DAY, +30*11,'2007-01-01 00:00:00.000') and do_date<=DATEADD(DAY, +30*12,'2007-01-01 00:00:00.000')THEN quantity END)AS mon12
FROM ST_TRX
WHERE do_date>='2007-01-01 00:00:00.000' and do_date<='2007-12-31 00:00:00.000' and st_code = '1'
GROUP BY st_code
[/code]
solve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources