December 16, 2009 at 10:48 pm
I have the following:
salesdate saleshour pens pencils
----------------------- ----------- --------------------------------------- ---------------------------------------
2008-06-02 00:00:00.000 -1 100 200
2008-06-02 00:00:00.000 8 100 200
2008-06-02 00:00:00.000 9 100 200
2008-06-02 00:00:00.000 10 100 200
2008-06-02 00:00:00.000 14 100 200
2008-06-02 00:00:00.000 15 100 200
2008-06-02 00:00:00.000 16 100 200
2008-06-02 00:00:00.000 -2 100 200
I need to add to 2 columns to get morning pencil sales and evening pen sales :
salesdate saleshour pens pencils pens_morning pencils_evening
----------------------- ----------- --------------------------------------- ------------- ------------ ---------------
2008-06-02 00:00:00.000 -1 100 200 300 800
2008-06-02 00:00:00.000 8 100 200 300 800
2008-06-02 00:00:00.000 9 100 200 300 800
2008-06-02 00:00:00.000 10 100 200 300 800
2008-06-02 00:00:00.000 14 100 200 300 800
2008-06-02 00:00:00.000 15 100 200 300 800
2008-06-02 00:00:00.000 16 100 200 300 800
2008-06-02 00:00:00.000 17 100 200 300 800
2008-06-02 00:00:00.000 -2 100 200 300 800
How do i achieve?
if isnull(object_id('tempdb..sample_data'),0)>0
drop table #sample_data
CREATE TABLE #sample_data
(
salesdate datetime NOT NULL ,
saleshour int NOT NULL ,
pens [decimal](18, 0) NOT NULL ,
pencils [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO
--== hours 8,9,10 morning hours; evening 14,15, 16, 17
--== -1 opening hour ; -2 closing hour
--==
--== will always have at leasat 3 records for a day : -1 and -2 and at least one other
---==
insert into #sample_data
select '06/02/2008' salesdate, -1 saleshour, 100 pens, 200 pencils UNION ALL
select '06/02/2008' salesdate, 8 saleshour, 100 pens, 200 pencils UNION ALL
select '06/02/2008' salesdate, 9 saleshour, 100 pens, 200 pencils UNION ALL
select '06/02/2008' salesdate, 10 saleshour, 100 pens, 200 pencils UNION ALL
select '06/02/2008' salesdate, 14 saleshour, 100 pens, 200 pencils UNION ALL
select '06/02/2008' salesdate, 15 saleshour, 100 pens, 200 pencils UNION ALL
select '06/02/2008' salesdate, 16 saleshour, 100 pens, 200 pencils UNION ALL
select '06/02/2008' salesdate, 17 saleshour, 100 pens, 200 pencils UNION ALL
select '06/02/2008' salesdate, -2 saleshour, 100 pens, 200 pencils
select
salesdate
, saleshour
, pens
, pencils
--== , sum ( pens) over (partition by ???) where saleshour = 8 9 10 as pens_morningsales
--==, sum (pencils) over (partition by ???) where saleshour = 14 15 16 17 as pencils_morning_sales
from #sample_data
drop table #sample_data
December 17, 2009 at 12:08 am
Hi, it is what you intended?
select
salesdate
,saleshour
,pens
,pencils
,ISNULL(sum ( CASE WHEN salesHour >= 0 AND salesHour < 12 THEN pens ELSE NULL END) over (partition by salesdate), 0) as pens_morningsales
,ISNULL(sum (CASE WHEN salesHour >= 12 THEN pencils ELSE NULL END) over (partition by salesDate), 0) as pencisl_eventingSales
from #sample_data
December 17, 2009 at 8:24 am
I got an error:
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'over'.
Also do I need a MAX DECODE or just MAX if I change to SUM over 2 days?
select
salesdate
,saleshour
,sum(pens) as sum_of_pens_for_2_days
,sum(pencils) as sum_of_pencils_for_2_days
,ISNULL(sum ( CASE WHEN salesHour >= 0 AND salesHour < 12 THEN pens ELSE NULL END) over (partition by salesdate), 0) as pens_morningsales
,ISNULL(sum (CASE WHEN salesHour >= 12 THEN pencils ELSE NULL END) over (partition by salesDate), 0) as pencisl_eventingSales
from #sample_data
group by salesdate, saleshour
December 17, 2009 at 10:12 am
TheHTMLDJ (12/17/2009)
I got an error:Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'over'.
On what version of SQL Server you are running the query? It must be at least SQL Server 2005.. In case you have 2005, then you must do some mistake when copying or revwirting the code. If I copy ou definition, and after my select, everything works fine for me.
Also do I need a MAX DECODE or just MAX if I change to SUM over 2 days?
select
salesdate
,saleshour
,sum(pens) as sum_of_pens_for_2_days
,sum(pencils) as sum_of_pencils_for_2_days
,ISNULL(sum ( CASE WHEN salesHour >= 0 AND salesHour < 12 THEN pens ELSE NULL END) over (partition by salesdate), 0) as pens_morningsales
,ISNULL(sum (CASE WHEN salesHour >= 12 THEN pencils ELSE NULL END) over (partition by salesDate), 0) as pencisl_eventingSales
from #sample_data
group by salesdate, saleshour
You want MAX of what exactly? And the SUM over 2 days, it means what.. Depends how you define the two days... It can be two different days in stored in DB, or over calendar days, even there is no record for such day?
In my example if you want in each line MAX of the day, then simply change the SUM to MAX.
December 17, 2009 at 5:20 pm
Pavel Pawlowski (12/17/2009)
TheHTMLDJ (12/17/2009)
I got an error:Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'over'.
On what version of SQL Server you are running the query? It must be at least SQL Server 2005.. In case you have 2005, then you must do some mistake when copying or revwirting the code. If I copy ou definition, and after my select, everything works fine for me.
I made the example on 2005 and mistakingly ran your query on 2000. Did not get error in 2005.
Also do I need a MAX DECODE or just MAX if I change to SUM over 2 days?
You want MAX of what exactly? And the SUM over 2 days, it means what.. Depends how you define the two days... It can be two different days in stored in DB, or over calendar days, even there is no record for such day?
In my example if you want in each line MAX of the day, then simply change the SUM to MAX.
I meant summing over 2 different dates. when you do this in context of grouping,
you have to MAX or SUM in order to avoid the NOT A GROUP Field message.
I avoided by adding SUM(pencils) in the CASE statement.
,ISNULL(sum (CASE WHEN salesHour >= 12 THEN SUM(pencils) ELSE NULL END) over (partition by salesDate), 0) as pencisl_eventingSales
from #sample_data
group by salesdate, saleshour
Thanks for your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply