Selective Sum - 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)

Selective Sum

Author

Topic

LadyReader
Starting Member

18 Posts

Posted - 2009-11-23 : 09:17:38

I want to sum the time individual pieces of equipment are in use in a 24-hour day. My records contain a field called Duration, which stores how long something is being processed on the equipment, and a code for the specific piece of equipment. I can sum these Durations but there is a wrinkle:

Several things could be processed on a piece of equipment at the same time. If item A is processed at noon for 3 hours and item B is also processed at noon on that same piece of equipment for 3 hours, the total time that piece of equipment is in use is 3 hours, not 6.

Note 1: If 2 items start at the same time, they always complete processing at the same time.

Note 2: Items that are processed at the same time have the same Priority.

How can I do this? More importantly, how do I approach this problem so I know how to solve similar ones in the future?

Thank you.

Thank you,
LadyReader

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-23 : 09:19:01

it will be easier if you can post your table DDL, sample records and expected result

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

LadyReader
Starting Member

18 Posts

Posted - 2009-11-23 : 09:24:24

Item 111
Priority 1
Duration 120 minutes
EquipmentCode 1

Item 112
Priority 1
Duration 120 minutes
EquipmentCode 1

Item 113
Priority 1
Duration 120 minutes
EquipmentCode 1

Item 121
Priority 2
Duration 60 minutes
EquipmentCode 1

Item 211
Priority 1
Duration 240 minutes
EquipmentCode 2

Results:
EquipmentCode 1 Sum=180 minutes
EquipmentCode 2 Sum=240 minutes

Thank you,
LadyReader

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 09:33:34

[code]SELECT EquipmentCode,
SUM(Duration) AS Total_Duration
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY EquipmentCode,Priority ORDER BY Item) AS Seq,*
FROM Table
)t
WHERE Seq=1
GROUP BY EquipmentCode
[/code]

LadyReader
Starting Member

18 Posts

Posted - 2009-11-23 : 10:52:02

Thank you very much Visakh. I modified that query a bit to filter by date and schedule_status and I believe that it works.

Can you please explain the following SQL components, which I am not familiar with?

Select OVER
Partition By

Thank you,
LadyReader

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 11:08:03

partition by splits the data into groups based on EquipmentCode,Priority values and then numbers them based on order of item. then we take only 1st record of each group to make sure we include the distinct equipemnt priority records

LadyReader
Starting Member

18 Posts

Posted - 2009-11-23 : 12:53:29

Visakh, my query is now:

Declare @Report_Date as datetime
-- Set @Report_Date = 11/20/2009
Set @Report_Date = cast(convert(varchar, @Report_Date, 101) + ' ' + '07:00' as datetime)

SELECT Furnace_Name,
SUM(Duration) AS Total_Duration
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Furnace_Name,Priority ORDER BY Item) AS Seq,*
FROM tblSchedule S
where Schedule_Start_Date >= @Report_Date
And Schedule_Start_Date < DateAdd(d, 1, @Report_Date)
And Schedule_Status = 1
And HTOperation_ID <> 17
)t
WHERE Seq=1
GROUP BY Furnace_Name
END

I thought it was working but I found an exception:
For the date specified (11/20/2009) it gives:

Furnace Total_Duration
1335 60
1336 60
1510 360
3002 120
3003 60

This seems correct but it is incomplete. The following 2 rows are also in my db:

Furnace Schedule_Start_Date Duration HTOperation_ID
1511 11/20/2009 7:00:00 AM 60 17
1511 11/20/2009 8:00:00 AM 240 1

The first of these rows would be filtered out by the where clause "And HTOperation_ID <> 17" but the second should be summed, and is, infact the only thing summed for Furnace 1511.

Can you determine what's wrong?

Thank you,
LadyReader

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 13:00:01

which is priority field? you've not shown that

LadyReader
Starting Member

18 Posts

Posted - 2009-11-23 : 13:08:01

Sorry: both of those rows have Priority 1 in Furnace 1511

Thank you,
LadyReader

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 13:09:48

and do you have any others with Priority 1 in Furnace 1511?

LadyReader
Starting Member

18 Posts

Posted - 2009-11-23 : 13:22:12

Sorry: both of those rows have Priority 1 in Furnace 1511

Thank you,
LadyReader

LadyReader
Starting Member

18 Posts

Posted - 2009-11-23 : 13:23:14

No, those were the only rows in Furnace 1511 that day.

(Please ignore the duplicate reply, I hit "refresh" accidentally)

Thank you,
LadyReader

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 13:28:52

whats the datatype of Schedule_Start_Date?

LadyReader
Starting Member

18 Posts

Posted - 2009-11-23 : 13:34:07

datetime

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 13:40:28

whats the value for Schedule_Status for above 2 records?

LadyReader
Starting Member

18 Posts

Posted - 2009-11-23 : 13:51:11

1511 11/20/2009 7:00:00 AM 17
1511 11/20/2009 8:00:00 AM 1

Thank you,
LadyReader

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 13:53:30

?? where's Schedule_Status in this?

LadyReader
Starting Member

18 Posts

Posted - 2009-11-23 : 13:57:17

Aha. That's the issue right there. Thank you! I just looked again and that second row has a Status = 0. I need to change my query. Thank you for all your help, especially the explanation. I will get the hang of Partitions soon....!

Thank you,
LadyReader

LadyReader
Starting Member

18 Posts

Posted - 2009-11-24 : 10:26:58

Visakh, one more question,if you'd be so kind:

An item's processing time may stretch over the day boundary. For example, something may start processing at 05:00 for 5 hours, ending at 10:00, which is 3 hours into the next business day. If I want to calculate the number of hours as above, I need to exclude those 3 hours from the first day's calculations but include it in the 2nd day's calculation. I have been trying to modify your query to do that but I am so far unable to see how to do so. I am getting "invalid syntax" errors when I try to subtract the "excess".

Can you assist, once again?

Thank you,
LadyReader

LadyReader
Starting Member

18 Posts

Posted - 2009-11-24 : 12:09:56

Thanks anyway, but I figured it out myself.

Thank you,
LadyReader

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources