Solved

SQL 2005 grouping

Posted on 2013-11-19
12
357 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:Scott Pletcher
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:Scott Pletcher
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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
 
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:Scott Pletcher
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:Scott Pletcher
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

831 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