7 day range - 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)

7 day range

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-07 : 03:28:29

[code]SELECT IDno,
SUM(CASE WHEN do_date>= Dateadd(day, +0, '20090101') and do_date< Dateadd(day, +8, '20090101')then quantity END)day7,
SUM(CASE WHEN do_date>= Dateadd(day, +8, '20090101') and do_date< Dateadd(day, +15, '20090101')then quantity END)day14,
SUM(CASE WHEN do_date>= Dateadd(day, +15, '20090101') and do_date< Dateadd(day, +22, '20090101')then quantity END)day21,
SUM(CASE WHEN do_date>= Dateadd(day, +22, '20090101') and do_date< Dateadd(day, +29, '20090101')then quantity END)day28,
SUM(CASE WHEN do_date>= Dateadd(day, +29, '20090101') and do_date< Dateadd(MONTH, +1, '20090101')then quantity END)EOM
FROM testing
WHERE do_date>= '20090101' and do_date < '20090201'
GROUP BY IDno[/code]
Erm....can this query shorten and fits into pivot?

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 03:31:16

why don't you just use the PIVOT operator ?

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-07 : 03:32:56

because i stuck at the datediff part...still thinking how to make it in between 7 days
**dizzy**

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 03:37:58

use a derived table or CTE

something like this


select *
from
(
select
    IDno,
    quantity,
    Day = case when do_date>= Dateadd(day, +0, '20090101') and do_date< Dateadd(day,  +8, '20090101' then 1
               when do_date>= Dateadd(day, +8, '20090101') and do_date< Dateadd(day, +15, '20090101' then 2
               .  . .
               end
from testing
where  .. ..
) d
 PIVOT
(
  sum(quantity)
  for Day in ([1], [2], . . .)   
) p

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-07 : 03:43:02

quote:
select
IDno,
quantity,
Day = case when do_date>= Dateadd(day, +0, '20090101') and do_date< Dateadd(day, +8, '20090101' then 1
when do_date>= Dateadd(day, +8, '20090101') and do_date< Dateadd(day, +15, '20090101' then 2
. . .
end
from testing
where .. ..

Basically you already done pivot*...but pivot again...i was thinking where USER can choose date different and date range...not only 7 days but also 4 or 5 day..
red = add own knowledge

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 03:51:20

Not really pivoting in the derived table, just transforming the do_date to a value 1 to 5 to ease the use of PIVOT query.

And further more you can change the value in red depending on user input and still have 5 different age and don't need to change the PIVOT section.


Day = case when do_date>= Dateadd(day, +0, '20090101') and do_date< Dateadd(day,  +8, '20090101' then 1
           when do_date>= Dateadd(day, +8, '20090101') and do_date< Dateadd(day, +15, '20090101' then 2

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-07 : 03:54:54

hmmm icic...btw is there anyway to eliminate away the multiple case statement?

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 03:56:52

maybe unless it is fixed 7 days increment or else let know if you can think of a way. I can't

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-07 : 04:01:59

i still blur blur to use CTE...so i think i can't make it...so maybe lets us wait peso and guys to help out?

AMITOFO+AMEN+YA ALLAH Hope the query works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources