SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Selective Sum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LadyReader
Starting Member

USA
18 Posts

Posted - 11/23/2009 :  09:17:38  Show Profile  Reply with Quote
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)

Singapore
17638 Posts

Posted - 11/23/2009 :  09:19:01  Show Profile  Reply with Quote
it will be easier if you can post your table DDL, sample records and expected result


KH
Time is always against us

Go to Top of Page

LadyReader
Starting Member

USA
18 Posts

Posted - 11/23/2009 :  09:24:24  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/23/2009 :  09:33:34  Show Profile  Reply with Quote
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
Go to Top of Page

LadyReader
Starting Member

USA
18 Posts

Posted - 11/23/2009 :  10:52:02  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/23/2009 :  11:08:03  Show Profile  Reply with Quote
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
Go to Top of Page

LadyReader
Starting Member

USA
18 Posts

Posted - 11/23/2009 :  12:53:29  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/23/2009 :  13:00:01  Show Profile  Reply with Quote
which is priority field? you've not shown that
Go to Top of Page

LadyReader
Starting Member

USA
18 Posts

Posted - 11/23/2009 :  13:08:01  Show Profile  Reply with Quote
Sorry: both of those rows have Priority 1 in Furnace 1511

Thank you,
LadyReader
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/23/2009 :  13:09:48  Show Profile  Reply with Quote
and do you have any others with Priority 1 in Furnace 1511?
Go to Top of Page

LadyReader
Starting Member

USA
18 Posts

Posted - 11/23/2009 :  13:22:12  Show Profile  Reply with Quote
Sorry: both of those rows have Priority 1 in Furnace 1511

Thank you,
LadyReader
Go to Top of Page

LadyReader
Starting Member

USA
18 Posts

Posted - 11/23/2009 :  13:23:14  Show Profile  Reply with Quote
No, those were the only rows in Furnace 1511 that day.

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

Thank you,
LadyReader
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/23/2009 :  13:28:52  Show Profile  Reply with Quote
whats the datatype of Schedule_Start_Date?
Go to Top of Page

LadyReader
Starting Member

USA
18 Posts

Posted - 11/23/2009 :  13:34:07  Show Profile  Reply with Quote
datetime
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/23/2009 :  13:40:28  Show Profile  Reply with Quote
whats the value for Schedule_Status for above 2 records?
Go to Top of Page

LadyReader
Starting Member

USA
18 Posts

Posted - 11/23/2009 :  13:51:11  Show Profile  Reply with Quote
1511 11/20/2009 7:00:00 AM 17
1511 11/20/2009 8:00:00 AM 1

Thank you,
LadyReader
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/23/2009 :  13:53:30  Show Profile  Reply with Quote
?? where's Schedule_Status in this?
Go to Top of Page

LadyReader
Starting Member

USA
18 Posts

Posted - 11/23/2009 :  13:57:17  Show Profile  Reply with Quote
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
Go to Top of Page

LadyReader
Starting Member

USA
18 Posts

Posted - 11/24/2009 :  10:26:58  Show Profile  Reply with Quote
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
Go to Top of Page

LadyReader
Starting Member

USA
18 Posts

Posted - 11/24/2009 :  12:09:56  Show Profile  Reply with Quote
Thanks anyway, but I figured it out myself.

Thank you,
LadyReader
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.48 seconds. Powered By: Snitz Forums 2000