May 23, 2012 at 6:57 am
Hi,
I am trying to get the sum for some columns(Data1,Data2,Data3) for every hour for a number of days. OfficeA and OfficeZ is my unique key. Based on the key, I want to get the sum of columns(Data1,Data2,Data3)for all Hour between 8h00 to 23h00
I use this with no success.
SELECT
OfficeA ,
OfficeZ,
Heure,
case when CAST(Data1 AS Int) < 0 then -1 else Sum(CAST(Data1 AS Int))
End AS Data1,
case when CAST(Data2 AS Int) < 0 then -1 else Sum(CAST(Data2 AS Int))
End AS Data2,
case when CAST(Data2 AS Int) < 0 then 0 else Sum(CAST(Data2 AS Int)) end AS Data2
FROM tbl_Data
GROUP BY
OfficeA ,
OfficeZ,
Heure,
case when CAST(Data1 AS Int) < 0 then -1 else Sum(CAST(Data1 AS Int))
End AS Data1,
case when CAST(Data2 AS Int) < 0 then -1 else Sum(CAST(Data2 AS Int))
End AS Data2,
case when CAST(Data2 AS Int) < 0 then 0 else Sum(CAST(Data2 AS Int)) end AS Data2
count([Day]) as [Number of Days]
Before
|OfficeA|OfficeZ|Day |Hour |Data1|Data2|Data3|
|091 |045 |Mon |08:00|24 |15 |18 |
|091 |045 |Tues|09:00|36 |12 |17 |
|091 |045 |Wed |10:00|24 |15 |18 |
|091 |045 |Thur|08:00|24 |15 |18 |
|091 |045 |Fri |08:00|24 |15 |18 |
|091 |045 |Mon |08:00|24 |15 |18 |
|091 |045 |Tues|08:00|24 |15 |18 |
|091 |045 |Wed |08:00|24 |15 |18 |
|091 |045 |Thur|08:00|24 |15 |18 |
Results I Wish to get for every hour between 08:00 and 23:00 for every combination of OfficeA and Office Z
|OfficeA|OfficeZ|Hour |Data1|Data2|Data3|Number of days|
|091 |045 |08:00|228 |132 |161 |9 |
|091 |045 |09:00|214 |134 |141 |9 |
|091 |045 |10:00|224 |124 |121 |9 |
Thanks!
May 23, 2012 at 7:12 am
I'm not clear on what you're trying to do with this:
case
when CAST(Data1 AS Int) < 0
then -1
else Sum(CAST(Data1 AS Int))
Are you trying to turn all negative numbers into -1, no matter what their actual value is, and then add them into the sum?
If so, it should be:
sum(case
when CAST(Data1 AS Int) < 0
then -1
else CAST(Data1 as Int))
Or are you trying to do something else?
Need to resolve that before moving on to the actual question.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 23, 2012 at 7:19 am
Hi,
Using the following , If the column value is negative then I display -1 else I want do the sum on that column.
case
when CAST(Data1 AS Int) < 0
then -1
else Sum(CAST(Data1 AS Int))
I change it with your suggestion
sum(case
when CAST(Data1 AS Int) < 0
then -1
else CAST(Data1 as Int))
...but the result is the same
May 23, 2012 at 9:18 am
I have to agree that your requirements as described are very confusing. In the sample data you posted you don't even have anything < 0. Perhaps if you could post ddl (create table statements), sample data (insert statements) and desired output based on your sample data.
I think I understand what you are trying to do but your code will not work. You are using a case to conditionally either return a constant or an aggregate. Honestly I am surprised that the code you posted would work.
Take a look at the first link in my signature on how to best post this information to get the best responses.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2012 at 9:40 am
drop table #office
go
create table #office
(office1 int,
dy char(3) not null,
hr int not null,
val int not null
)
go
insert into #office select 1,'mon',8,10
insert into #office select 1,'tue',8,10
insert into #office select 1,'wed',8,0
insert into #office select 1,'thu',8,10
insert into #office select 1,'fri',8,-18
insert into #office select 1,'mon',9,10
insert into #office select 1,'tue',9,-10
insert into #office select 1,'tue',10,-10
go
select * from #office
go
--This will thropw error as the val is nethier part of the aggregarte or group by
select office1,hr, case when val < 0 then -1 else SUM(val) end as val
from #office
group by office1,hr
go
--This is what you should be looking for..
select office1,hr, SUM(val) val
from #office
where val > 0
group by office1,hr
go
--Btw, why d you want sum to be -1 when it is less than 0.Or do you want to use - whenver your time is -ve
--This is what you should be looking for..
select office1,hr, SUM(case when val < 0 then -1 else val end ) val
from #office
group by office1,hr
go
--also what is count..Is that count based on office and hour or total count in table
The query you posted will not work because the val in the case is niether part of group by nor aggregate function
Please tell what clearly you want...based on your output..the second query should work for you...
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply