[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL 2005 grouping

Posted on 2013-11-19
12
Medium Priority
?
367 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 70

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 70

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
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.

 
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 1000 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 1000 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 70

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 70

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

649 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