• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

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?
0
mebaby333
Asked:
mebaby333
2 Solutions
 
Scott PletcherSenior DBACommented:
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)
0
 
mebaby333Author Commented:
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... :(
0
 
Scott PletcherSenior DBACommented:
That code would potentially allow more uses on indexes than the original query, and at any rate should never be less efficient overall.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
mebaby333Author Commented:
I don't understand what you mean... your query will return what I am looking for? I am confused....
0
 
Jim P.Commented:
Try these changes:

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	IsNull(dbo.Raw_Booking.Actual_Hrs,0) > 0
AND	dbo.Raw_Booking.ClockCode LIKE 'MO%'
AND	YEAR(dbo.Raw_Booking.TStamp) >= 2013
AND	dbo.Actual.WC In ('WC[R]PRODUCTION', 'WC[R]BOOT/CLOSE', 'WC[R]BRAIDING', 'WC[R]POTTING', 'WC[R]REWORK')
GROUP BY dbo.Raw_Booking.ClockCode, CAST(CONVERT(Varchar(10), dbo.Raw_Booking.TStamp, 101) as datetime), dbo.Actual.WC
HAVING	SUM(IsNull(dbo.Raw_Booking.Actual_Hrs,0)) <> 0

Open in new window


Really what should happen is change the Actual.WC to a numeric value that is a lookup from another table. The CAST and CONVERT process is a quick way to take off the time in a datetime. The IsNull function replaces a null value with some default.

Another DB design suggestion is to build/modify to have a default value on every field. Nulls will mess with you.
0
 
Mark WillsTopic AdvisorCommented:
I think you might benefit from the "with" clause

Have a read of : http://msdn.microsoft.com/en-us/library/ms175972(v=sql.90).aspx

When you have computed or derived fields as part of the query, it is sometimes faster to get all that detail first, and then use your various checks and tests.

Now, a couple of observations...

1) The first and biggest is the "having".
     if you check for >0 then how could it ever be NULL or 0 ?
2) The next is the joins.
     if checking for 'MO%' then do you need the inner join as well ?
     if the inner join means only 'MO%' then kill the "LIKE" line
3) Using functions within the where
     always best to avoid if possible, YEAR() is not so bad and can use it
4) I think adding in the WC in "where" you missed brackets
     either add  in brackets for the "OR", or use "IN"

With cte_week as
(
  SELECT     dbo.Raw_Booking.ClockCode AS MOnum
           , dbo.Actual.WC
           , DATEADD(DAY, DATEDIFF(DAY, 0, dbo.Raw_Booking.TStamp), 0) AS wDate
           , dbo.Raw_Booking.Actual_Hrs AS Runtothours

  FROM       dbo.Raw_Booking 
  INNER JOIN dbo.FS_MOOrder2 ON dbo.Raw_Booking.ClockCode = dbo.FS_MOOrder2.MONumber  -- Is this really needed ? 
  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 in ('WC[R]PRODUCTION','WC[R]BOOT/CLOSE','WC[R]BRAIDING','WC[R]POTTING','WC[R]REWORK'))
)
SELECT    MOnum, WC, wDate, sum(Runtothours) as TotHours
FROM      cte_week
GROUP BY  MOnum, WC, wDate

Open in new window


EDIT:

Thought I would add in some reading about indexes / where / functions for you to better understand...

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/A_2531-SARGable-functions-in-SQL-Server.html

Highly recommended...
0
 
Scott PletcherSenior DBACommented:
LOL, jimpen duplicates my query except making it less efficient and gets points, LOL.
0
 
mebaby333Author Commented:
Oh... sorry ... lol
0
 
mebaby333Author Commented:
I appreciated the help Scott but I tried your query and it did not run....
0
 
Scott PletcherSenior DBACommented:
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.
0
 
mebaby333Author Commented:
I'm sure there is a lot more you could teach me on this lol... many possible points :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now