Solved

SQL 2005 grouping

Posted on 2013-11-19
12
360 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

733 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