I need some help with a query 1

[hoggle]

I can't seem to wrap my head around this one.
Basically this table tblProductionReporting records production entries based on shift, date, item, ect.

I need to sum a quantity based on shift, date, and item.

production reporting table layout:
itemNum, ShiftNum, ProductionTime (date/time), Quantity, and ScheduleID
and
ShiftNum references a Shift table that looks like:
ShiftNum, ShiftStart, ShiftEnd

Sample Data:
Shift table:
ShiftNum ShiftStart ShiftEnd
1 700 1500 (time is held as miltary number)
2 1500 2300
3 2300 700

Production Report table:
ItemNum ShiftNum Time Quantity
1001 1 07/30/03 7:34 AM 30
1001 1 07/30/03 8:19 AM 30

1001 2 07/30/03 6:00 PM 30

1001 3 07/30/03 11:34 PM 30
1001 3 07/31/03 1:34 AM 30
1001 3 07/31/03 5:34 AM 30
===== "Next" day ======
1001 1 07/31/03 9:34 AM 30

1001 2 07/31/03 8:34 PM 30
1001 2 07/31/03 9:34 PM 30

1001 3 07/31/03 11:59 PM 30
1001 3 08/01/03 3:34 AM 30
1001 3 08/01/03 6:34 AM 30
1001 3 08/01/03 6:54 AM 30

Needed Results:
ItemNum ShiftNum Date TotalQty
1001 1 07/30/03 60
1001 2 07/30/03 30
1001 3 07/30/03 90 **** problem area
1001 1 07/31/03 30
1001 2 07/31/03 60
1001 3 07/31/03 120 **** problem area


The problem is, is that our "day" doesn't begin until 7AM
but we have shifts running 24/7.

The problem occurs when I try to get sum for shift 3 (because they start at 11:00 PM and run until 7:00 AM)
I need to show the numbers they did based on the day when the shift started and sum those numbers until the shift end.

I can easily get shift 1 and 2 because the date for all their production is the same, so I just group by that.

Thanks for any suggestions

 

[SQLBill]

What about something like this for shift 3?

WHERE datefield >= CONVERT(VARCHAR(8), GETDATE()-1, 1) + ' 23:00:00'
AND datefield < CONVERT(VARCHAR(8), GETDATE(), 1) + ' 00:00:00'

-SQLBill

 

[hoggle]

that works, but only based on a certain date, I need to pull out all the totals for all the days in the table. This works to get the right total

select sum(ItemQuantity), scheduleID from tblProductionReporting
where productionTime between '08/01/03 23:00' AND '08/02/03 07:00'
and productionlineID = 'W06'
group by scheduleID

but the problem still remains as how I can pull the totals for &quot;all&quot; the dates in the table

 

[SQLBill]

This will work for 31 days prior to today's date (getdate() is today's date). Depending on how many days you need to go back from today, change the WHILE to reflect that many days + 1.

DECLARE
@a int
, @b int

SET @a = 1
SET @b = 0

WHILE @b < 32
SELECT
<code>
WHERE
WHERE datefield >= CONVERT(VARCHAR(8), GETDATE()-@a, 1) + ' 23:00:00'
AND datefield < CONVERT(VARCHAR(8), GETDATE()-@b, 1) + ' 00:00:00'

SET @a = @a + 1
SET @b = @b + 1

-SQLBill

 

[hoggle]

that will work! thanks for the help