Solved

SQL 2005 grouping

Posted on 2013-11-19
12
352 Views
Last Modified: 2013-12-17
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
Comment
Question by:mebaby333
12 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39659536
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
 
LVL 5

Author Comment

by:mebaby333
ID: 39659555
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39659904
That code would potentially allow more uses on indexes than the original query, and at any rate should never be less efficient overall.
0
 
LVL 5

Author Comment

by:mebaby333
ID: 39660118
I don't understand what you mean... your query will return what I am looking for? I am confused....
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 250 total points
ID: 39671344
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 39671598
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39722389
LOL, jimpen duplicates my query except making it less efficient and gets points, LOL.
0
 
LVL 5

Author Comment

by:mebaby333
ID: 39724937
Oh... sorry ... lol
0
 
LVL 5

Author Comment

by:mebaby333
ID: 39724947
I appreciated the help Scott but I tried your query and it did not run....
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39725121
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
 
LVL 5

Author Comment

by:mebaby333
ID: 39725132
I'm sure there is a lot more you could teach me on this lol... many possible points :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now