CODEWITHSUNDEEP
sqlsql-server
newbieHQ
newbieHQ

Reputation: 69

sum up the data from today specific data to the next day data and the rest of the data

i would like to compute the data from a table where it consists date, hour and data ..there are a lot of data inside the table.. the hour is int datatype in the database and other (DATE.etc) are datatype varchar.. the example are as below:

DATE       hour  data1  data2
-------------------------------
01/01/2010  1    10860  1234    
01/01/2010  2    10861  1234    
01/01/2010  3    10862  1234    
01/01/2010  4    10863  567   
01/01/2010  5    10864  458    
02/01/2010  1    10865  3467    
02/01/2010  2    10866  7890    
02/01/2010  3    10867  863    
02/01/2010  4    10868  0    
02/01/2010  5    10868  698  
03/01/2010  1    10868  4693  
03/01/2010  2    10868  7853  
03/01/2010  3    10868  5987
....................etc

and from above data. i would like to sum the data from date: 01/01/2010, hour: 2 to the next day date:02/01/2010, hour:1 and of course date:02/01/2010, hour:2 to date:03/01/2010, hour:1 and the rest of the data .. meaning that the output of the data result would be as below:

DATE           sdata1  sdata2
-------------------------------
01/01/2010     54315   6960    
02/01/2010     54337   14144    
03/01/2010     21736   13840
...................etc

the datatype for date, data1 and data2 are varchar EXCEPT time is datatype int...

the sdata1 and sdata2 are the sum of the data and is there any other way that may sum up those data in condition from today hour:2 to the next day hour:2 ? thank you so much everyone ..thank you ..

those data are resulted from bulk insert of PLC data...

here is my own solution but it never work out!!

select SUM(CONVERT(int,data1)) AS sdata1,SUM(CONVERT(int,data2)) AS sdata2 from table where (CONVERT(datetime, date, 105) >= CONVERT(datetime,date,105) and CONVERT(int,hour) >= 2) and (CONVERT(datetime, date, 105) <= DATEADD(day, 1,CONVERT(datetime,date,105)) and CONVERT(int,hour) < 2) and month(CONVERT(datetime,Date,103))= '01' and year(CONVERT(datetime,Date,103))= '2010'

someone help me figure this out please ... my brain is burst ... @@

Upvotes: 1

Views: 182

Answers (2)

newbieHQ
newbieHQ

Reputation: 69

the desire result should be like this

DATE sdata1 sdata2

01/01/2010 54315 6960
02/01/2010 54337 14144
03/01/2010 21736 13840
...................etc

but from your query .it works well...thank you and just the end result is as below:

DATE sdata1 sdata2

02/01/2010 54315 6960
03/01/2010 54337 14144
04/01/2010 21736 13840
...................etc

meaning that the query sum up the data from 01/01/2010 until 02/01/2010 and output for 02/01/2010..hehe ..

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Here you go

set dateformat dmy
declare @t table(DATE  date,     hour int,  data1  int, data2 int)
insert into @t
select '01/01/2010',  1,    10860,  1234     union all
select '01/01/2010',  2,    10861,  1234     union all
select '01/01/2010',  3,    10862,  1234     union all
select '01/01/2010',  4,    10863,  567    union all
select '01/01/2010',  5,    10864,  458     union all
select '02/01/2010',  1,    10865,  3467     union all
select '02/01/2010',  2,    10866,  7890     union all
select '02/01/2010',  3,    10867,  863     union all
select '02/01/2010',  4,    10868,  0     union all
select '02/01/2010',  5,    10868,  698   union all
select '03/01/2010',  1,    10868,  4693   union all
select '03/01/2010',  2,    10868,  7853   union all
select '03/01/2010',  3,    10868,  5987 

select dateadd(day,case when hour>1 then 1 else 0 end,date),
sum(data1),sum(data2) 
from @t
group by dateadd(day,case when hour>1 then 1 else 0 end,date)

Upvotes: 1

Related Questions