Sum of 2 fields between specific dates

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

General SQL Server Forums

New to SQL Server Programming

Sum of 2 fields between specific dates

Author

Topic

tyakimov
Starting Member

4 Posts

Posted - 2011-01-29 : 14:54:41

Hi
I have 2 tables - Table1 containing shiftStart/end like:

ShiftStart, ShiftEnd
----------------------------------
1-jan-2011 1:30, 1-jan-2011 7:30
2-jan-2011 1:30, 2-jan-2011 17:33

and Table2 with measurement points:
IntervalStart, PD, MD, UD
------------------------------
1-jan-2011 1:30, 4, 5, 7
1-jan-2011 2:30, 4, 5, 7
1-jan-2011 4:30, 4, 5, 7
2-jan-2011 1:30, 4, 5, 7
3-jan-2011 1:30, 4, 5, 7

I want to sum (aggregate) fields from table2, which have values between Start/end from the first table
For one field I use that SQL:

select ShiftStart,ShiftEnd,
(
select sum(PD) from Table2
WHERE (IntervalStart between ShiftStart and ShiftEnd)
) as SUM_PD
from Table1

It is fine, but if I add more fields to aggregate, it becomes VERY slow:
select ShiftStart,ShiftEnd,
(
select sum(PD) from Table2
WHERE (IntervalStart between ShiftStart and ShiftEnd)
) as SUM_PD,
(
select sum(MD) from Table2
WHERE (IntervalStart between ShiftStart and ShiftEnd)
) as SUM_MD
from Table1

And I have more than 3 fields for aggregation
What is the right way to do that?

Any advice is welcome
Thanks

Mungbeans
Starting Member

7 Posts

Posted - 2011-01-29 : 16:35:22

Does this work? I think I might be misunderstanding your question.

select ShiftStart, ShiftEnd, sum(PD)
from #table2
join #table1 on intervalstart between shiftstart and shiftend
group by ShiftStart, ShiftEnd

tyakimov
Starting Member

4 Posts

Posted - 2011-01-29 : 17:58:40

With a join it works good, especially if I sum more values
In fact my first table contains all the dates in one column, like:

CheckIn_times
--------------
1-jan-2011 1:30
1-jan-2011 7:30
2-jan-2011 1:30
2-jan-2011 17:33

and each timestamp is ShiftEnd for the previous on ShiftStart for the current record. From that table I created temporary one with 2 columns ShiftStart, ShiftEnd.
My question is - can I make my sums using directly Table1 (sum between 1 and 2 time, between 2 and 3 time and so on) avoiding temporary table with two columns ShiftStart, ShiftEnd?

Mungbeans
Starting Member

7 Posts

Posted - 2011-01-29 : 18:51:29

Could you give us some sample data to work with? Eg:

create table table2 (
CheckIn_Times datetime,
PD int,
MD int,
UD int
)

insert into table2 values ('20110101 2:30', 4, 5, 7)
insert into table2 values ('20110101 4:30', 4, 5, 7)
insert into table2 values ('20110102 1:30', 4, 5, 7)
insert into table2 values ('20110103 1:30', 4, 5, 7)

tyakimov
Starting Member

4 Posts

Posted - 2011-01-30 : 02:42:02

Here is an example:
Handover table
create table Handover(HandoverDate datetime, Emp nvarchar(15) )
insert into Handover (HandoverDate) values ('2011-01-01 6:30','John')
insert into Handover (HandoverDate) values ('2011-01-01 15:01','Adam')
insert into Handover (HandoverDate) values ('2011-01-01 23:00','Eva')
insert into Handover (HandoverDate) values ('2011-01-02 7:00','John')
insert into Handover (HandoverDate) values ('2011-01-02 14:30','Eva')

Measurements table
create table Measurements(
TimeStamp datetime,
PD int,
MD int,
UD int
)

insert into Measurements values ('20110101 2:30', 4, 5, 7)
insert into Measurements values ('20110101 4:30', 4, 5, 7)
insert into Measurements values ('20110102 1:30', 4, 5, 7)
insert into Measurements values ('20110103 1:30', 4, 5, 7)

I want to get the following resultset - for each period between two HandoverDates from Handover table the sum of measurements from Measurements table, i.e. how many PD, MD, UD happened between 1 and 2 records from Handover, between 2 and 3 records, 3 and 4 and so on

Emp, ShiftStart, ShiftEnd, SUM_PD, Sum_MD, SUM_UD
---------------------------------------------------------------------
'John', '2011-01-01 06:30','2011-01-01 15:01', 8, 10, 14
'Adam', '2011-01-01 15:01','2011-01-01 23:00', 0, 0, 0
'Eva', '2011-01-01 23:00','2011-01-02 7:00', 0, 0, 0
'John', '2011-01-02 07:00','2011-01-02 14:30' 4, 5, 7

One approach is to create TempTable from Handover containing ShiftStart, ShiftEnd in a row, and after that Join in as Mungbeans suggested
I created the temp table like that:

SELECT h.HandOverDate as ShiftStart,
(select top 1 handoverdate from handover h2
where h2.handoverdate > h.handoverdate
order by h2.handoverdate) as ShiftEnd
INTO #CCR_Shift
FROM HandOver

The question is: Is my tempTable creation optimal or Can I avoid temp table?

Thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-30 : 03:19:38

Check this if it works for you, however the existing DB structure dont seems to be optimal

Select
Emp
,ShiftStart
,ShiftEnd
,(select SUM(PD) from Measurements where [timestamp] between A.ShiftStart and A.ShiftEnd) as TotalPD
,(select SUM(MD) from Measurements where [timestamp] between A.ShiftStart and A.ShiftEnd) as totalMD
,(select SUM(UD) from Measurements where [timestamp] between A.ShiftStart and A.ShiftEnd) as totalUD

From(
SELECT H.emp, h.HandOverDate as ShiftStart,
(select top 1 handoverdate from handover h2
where h2.handoverdate > h.handoverdate
order by h2.handoverdate) as ShiftEnd
from Handover h)A

chanluongbus
Starting Member

5 Posts

Posted - 2011-01-30 : 12:33:19

Try the following. I assumed that table1 and table2 both have dates. If not you need to find a common join for table1 and table2.

select ShiftStart,ShiftEnd,SUM_PD_MD.PD,SUM_PD_MD.MD
from Table1 left outter join
(
select ShiftStart,ShiftEnd sum(PD) as PD, sum(MD) as MD from Table2
WHERE (IntervalStart between ShiftStart and ShiftEnd
group by ShiftStart,ShiftEnd
) as SUM_PD_MD on SUM_PD_MD.ShiftStart=Table1.ShiftStart and SUM_PD_MD.ShiftEnd=Table1.ShiftEnd

Go to my blog for more detial on SQL.

SSRS Devloper
http://ssrsdeveloper.blogspot.com/

CL

tyakimov
Starting Member

4 Posts

Posted - 2011-01-31 : 02:04:02

Thanks, MIK_2008
It works fine, but as you suspected, the performance is not good - more than 4 times slower that if with temp table
Regards
TYakimo

quote:
Originally posted by MIK_2008

Check this if it works for you, however the existing DB structure dont seems to be optimal

Select
Emp
,ShiftStart
,ShiftEnd
,(select SUM(PD) from Measurements where [timestamp] between A.ShiftStart and A.ShiftEnd) as TotalPD
,(select SUM(MD) from Measurements where [timestamp] between A.ShiftStart and A.ShiftEnd) as totalMD
,(select SUM(UD) from Measurements where [timestamp] between A.ShiftStart and A.ShiftEnd) as totalUD

From(
SELECT H.emp, h.HandOverDate as ShiftStart,
(select top 1 handoverdate from handover h2
where h2.handoverdate > h.handoverdate
order by h2.handoverdate) as ShiftEnd
from Handover h)A

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-07 : 06:47:04

what is the version of SQL Server are you using ?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources