troubleshooting Question

SQL 2005 grouping

Avatar of mebaby333
mebaby333Flag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
11 Comments2 Solutions393 ViewsLast Modified:
Currently I have this ....

SELECT     dbo.Raw_Booking.ClockCode AS MOnum, DATEADD(DAY, DATEDIFF(DAY, 0, dbo.Raw_Booking.TStamp), 0) AS Date,
                      SUM(dbo.Raw_Booking.Actual_Hrs) AS Runtothours, dbo.Actual.WC
FROM         dbo.Raw_Booking INNER JOIN
                      dbo.FS_MOOrder2 ON dbo.Raw_Booking.ClockCode = dbo.FS_MOOrder2.MONumber INNER JOIN
                      dbo.Actual ON dbo.Raw_Booking.ActualKey = dbo.Actual.ActualKey
WHERE     (dbo.Raw_Booking.Actual_Hrs > 0) AND (dbo.Raw_Booking.ClockCode LIKE 'MO%') AND (YEAR(dbo.Raw_Booking.TStamp) >= 2013) AND
                      (dbo.Actual.WC = 'WC[R]PRODUCTION') OR
                      (dbo.Actual.WC = 'WC[R]BOOT/CLOSE') OR
                      (dbo.Actual.WC = 'WC[R]BRAIDING') OR
                      (dbo.Actual.WC = 'WC[R]POTTING') OR
                      (dbo.Actual.WC = 'WC[R]REWORK')
GROUP BY dbo.Raw_Booking.ClockCode, DATEADD(DAY, DATEDIFF(DAY, 0, dbo.Raw_Booking.TStamp), 0), dbo.Actual.WC
HAVING      (SUM(dbo.Raw_Booking.Actual_Hrs) IS NOT NULL) OR
                      (SUM(dbo.Raw_Booking.Actual_Hrs) <> 0)

It produces this

MOnum                       Date                               Runtohours     WC
MO-55             &#9;10/7/2013 12:00:00 AM&#9;     241&#9;        WC[R]PRODUCTION
MO-551           &#9;11/15/2013 12:00:00 AM&#9;     177&#9;        WC[R]PRODUCTION
MO-552           &#9;1/10/2013 12:00:00 AM&#9;     297&#9;         WC[R]PRODUCTION
MO-554           &#9;4/1/2013 12:00:00 AM&#9;      98&#9;         WC[R]POTTING  
MO-553           &#9;5/20/2013 12:00:00 AM&#9;      27&#9;          WC[R]PRODUCTION
MO-5538         &#9;11/12/2013 12:00:00 AM  &#9;43&#9;         WC[R]PRODUCTION

It gives me the info I need and I do know it could run more efficiently, I am still sharpening skills and such but I am looking to take this info and get the sum of each date...

I am trying to get the total run time of specific work centers grouped by date... I have the work centers but how do I sum the total for each day?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros