Avatar of mebaby333
mebaby333
Flag for United States of America asked on

SQL 2005 grouping

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?
Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
mebaby333

8/22/2022 - Mon
Scott Pletcher

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
                      (dbo.Raw_Booking.TStamp >= '20130101' AND dbo.Raw_Booking.TStamp < '20140101') 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) AND
                      (SUM(dbo.Raw_Booking.Actual_Hrs) <> 0)
mebaby333

ASKER
The query currently adds the worked time for the whole year of 2013 by day and includes the wc ... but I am looking to take this data and further deal with it by adding only these workcenters by date....

I did try to run the query and it has been executing for 5 minutes... :(
Scott Pletcher

That code would potentially allow more uses on indexes than the original query, and at any rate should never be less efficient overall.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
mebaby333

ASKER
I don't understand what you mean... your query will return what I am looking for? I am confused....
ASKER CERTIFIED SOLUTION
Jim P.

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher

LOL, jimpen duplicates my query except making it less efficient and gets points, LOL.
mebaby333

ASKER
Oh... sorry ... lol
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
mebaby333

ASKER
I appreciated the help Scott but I tried your query and it did not run....
Scott Pletcher

I wasn't aware of that.  Not sure if it was a syntax error or what.  Naturally w/o sample data I couldn't test run the code.

I mainly copied your code and adjusted it to better do what you wanted.
mebaby333

ASKER
I'm sure there is a lot more you could teach me on this lol... many possible points :)
Your help has saved me hundreds of hours of internet surfing.
fblack61