Solved

SQL Server 2008 Sum and Groupby, Rollup

Posted on 2009-05-08
10
1,566 Views
Last Modified: 2012-05-06
The script below returns the records I need, with a single record for each employee's transactions.
However, I want to do four more things and cannot get the script right:
1. I want the result set to group by Ladetail.fjobno and by employee name (must check for both Prempl.fstname + UPPER(RTRIM(Prempl.fname))   (which is named column lstname,
2. I want to get a SUM of LABMIN by employee as a new field named TOTLABOR,
3. I want that sum (TOTLABOR) to be divided by 60 so that I get a decimal total hours to two places appearing in TOTLABOR, and
4. The TOTLABOR field will aggregate by Ladetail.fjobno, with summation starting from zero for each employee each time Ladetail.fjobno changes.

I've made a few attempts at using GROUPBY and ROLLUP but I cannot get the syntax correct.

How should the script be modified to accomplish these four things?


If employee A has a LABMIN total of 123 minutes, I want the sum of that employee's LABMIN to appear in the new field TOTLABOR, as 2.05
It does not matter if TOTLABOR exists in each record as I do not want to lose the records making up the employee's total labor from the result set - just as long as TOTLABOR holds the same total number in each one of the employee's records in the table.
SELECT UPPER(RTRIM(Prempl.ffname)) as fstname,  										
   UPPER(RTRIM(Prempl.fname)) as lstname, Jodrtg.fuprodtime, 	
   Ladetail.fdate,Ladetail.fempno,Ladetail.fstatus,Ladetail.fcompqty,
   Ladetail.fjobno,Ladetail.flabtype,Ladetail.foperno,Ladetail.fpro_id,
   Ladetail.fscrpqty,Ladetail.fedatetime,Ladetail.fsdatetime,Ladetail.fjobno+STR(Ladetail.foperno,4) as Jobopkey,Ladetail.fchrglab, 
   Ladetail.fcEarnCode,Ladetail.fcDept, 
   CASE
   WHEN Ladetail.flabtype = 'P'  THEN Ladetail.Fchrglab * 60 
   ELSE DateDiff(mi, Ladetail.fsdatetime, Ladetail.fedatetime)*60 
   END AS LABSeconds, 
   Round(DateDiff(mi, Ladetail.fsdatetime, Ladetail.fedatetime),4) as LABMIN,
   SUBSTRING(CONVERT(CHAR(19),ladetail.fsdatetime,100),13,7) AS NSHOUR, 
   SUBSTRING(CONVERT(CHAR(19),ladetail.fedatetime,100),13,7) AS NEHOUR, 
   ladetail.fdate+(7-(cast(datepart(dw,ladetail.fdate) as int))) AS labweek 		
   FROM  ladetail 
   LEFT OUTER JOIN prempl ON Ladetail.fempno = Prempl.fempno 
   LEFT OUTER JOIN jodrtg ON Ladetail.fjobno = Jodrtg.fjobno AND Ladetail.foperno = Jodrtg.foperno 
   where ladetail.fjobno = '30915-0000'

Open in new window

0
Comment
Question by:glennes
  • 5
  • 4
10 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 24339592
Hi,

Any chance of supplying some sample data for this query in the form of create and insert statement(s)?

Cheers
  David
0
 
LVL 3

Expert Comment

by:GregTSmith
ID: 24339651
An example of the output you would expect (preferably with logic for how each column is determined) would be invaluable since I cannot see your data.

Even better would be 10 sample records that could be used to generate the above example.

In the meantime, I've simplified the query a bit, and removed some unnecessary stuff.
SELECT 
  UPPER(RTRIM(E.ffname)) [fstname], 
  UPPER(RTRIM(E.fname)) [lstname], 
  J.fuprodtime, 
  D.fdate, 
  D.fempno, 
  D.fstatus, 
  D.fcompqty, 
  D.fjobno, 
  D.flabtype, 
  D.foperno, 
  D.fpro_id, 
  D.fscrpqty, 
  D.fedatetime, 
  D.fsdatetime, 
  D.fjobno + STR(D.foperno, 4) [Jobopkey], 
  D.fchrglab, 
  D.fcEarnCode, 
  D.fcDept, 
  CASE 
    WHEN D.flabtype = 'P' THEN D.Fchrglab * 60 
    ELSE DATEDIFF(mi, D.fsdatetime, D.fedatetime) * 60 
  END [LABSeconds], 
  DATEDIFF(mi, D.fsdatetime, D.fedatetime) [LABMIN], 
  SUBSTRING(CONVERT(char(19), D.fsdatetime, 100), 13, 7) [NSHOUR], 
  SUBSTRING(CONVERT(char(19), D.fedatetime, 100), 13, 7) [NEHOUR], 
  D.fdate + (7 - DATEPART(dw, D.fdate)) [labweek] 
FROM 
  ladetail D 
  LEFT JOIN prempl E ON E.fempno = D.fempno 
  LEFT JOIN jodrtg J ON 
    J.fjobno = D.fjobno 
      AND J.foperno = D.foperno 
WHERE 
  D.fjobno = '30915-0000' 

Open in new window

0
 

Author Comment

by:glennes
ID: 24339734
Thanks, Greg, for polishing up the code!
I've attached a pdf of the result set for the query.

One note - the actual query when it's finished will not include the last WHERE statement, but will be able to prompt the user to input a range of dates and look for fjobno's that meet certain criteria.  In this case, there will be several fjobno's in the set rather than just the one you see in the attachment.  
ladetail.pdf
0
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!

 
LVL 3

Expert Comment

by:GregTSmith
ID: 24341150
The sample of the results of your query are helpful, but not what I was looking for...

Could you provide a simple example (even if only a few records) of the columns and data you would expect to see after the modifications you want?  Using both ends, I could help tweak the query to get you there.
0
 

Author Comment

by:glennes
ID: 24341228
OK, glad to.  It's on my office PC. I'll VPN into it tomorrow and generate a spreadsheet showing how it should look when it's done.
0
 

Author Comment

by:glennes
ID: 24353288
I've attached a spreadsheet. You can see the summation I'm talking about in column X for each row. For each employee name, first sum the entries in LABMIN for that employee, then divide the result by 60. Then put the result in TOTLABOR for each of the employee's records in the table.

I know how to do an ACCEPT statement in PL/SQL to prompt for user input, but I do not know how to do the equivalent in MS SQL Server. What I need to do is prompt the user for a date range, then use the date range for the query field 'fdate'. This will pull all employees and all jobs ('fjobno') between the two dates the user inputs.

Thanks again, Greg, for your great help!
ladetail.xls
0
 
LVL 3

Accepted Solution

by:
GregTSmith earned 500 total points
ID: 24355518
It's difficult to verify the syntax using the built-in tools since I don't have the tables, but please try this out.
SELECT 
  UPPER(RTRIM(E.ffname)) [fstname], 
  UPPER(RTRIM(E.fname)) [lstname], 
  J.fuprodtime, 
  D.fdate, 
  D.fempno, 
  D.fstatus, 
  D.fcompqty, 
  D.fjobno, 
  D.flabtype, 
  D.foperno, 
  D.fpro_id, 
  D.fscrpqty, 
  D.fedatetime, 
  D.fsdatetime, 
  D.fjobno + STR(D.foperno, 4) [Jobopkey], 
  D.fchrglab, 
  D.fcEarnCode, 
  D.fcDept, 
  CASE 
    WHEN D.flabtype = 'P' THEN D.Fchrglab * 60 
    ELSE DATEDIFF(mi, D.fsdatetime, D.fedatetime) * 60 
  END [LABSeconds], 
  DATEDIFF(mi, D.fsdatetime, D.fedatetime) [LABMIN], 
  SUBSTRING(CONVERT(char(19), D.fsdatetime, 100), 13, 7) [NSHOUR], 
  SUBSTRING(CONVERT(char(19), D.fedatetime, 100), 13, 7) [NEHOUR], 
  D.fdate + (7 - DATEPART(dw, D.fdate)) [labweek], 
  CONVERT(float, 
    SUM(DATEDIFF(mi, D.fsdatetime, D.fedatetime)) 
    OVER (
      PARTITION BY 
        D.fjobno, 
        UPPER(RTRIM(E.ffname)), 
        UPPER(RTRIM(E.fname))
    )
  ) / 60 [TOTLABOR] 
FROM 
  ladetail D 
  LEFT JOIN prempl E ON E.fempno = D.fempno 
  LEFT JOIN jodrtg J ON 
    J.fjobno = D.fjobno 
      AND J.foperno = D.foperno 
WHERE 
  D.fjobno = '30915-0000' 

Open in new window

0
 

Author Comment

by:glennes
ID: 24355682
Works perfectly!
How do I get the TOTLABOR column to round to two decimal places?
0
 
LVL 3

Expert Comment

by:GregTSmith
ID: 24355993
Use the ROUND function...

ROUND(<column / field>, 2)
0
 

Author Closing Comment

by:glennes
ID: 31579590
Thanks so much, Greg...
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search query matching words 20 38
point in time restore in SQL server 26 41
SQL Get Store Procedure Column Name As Row 5 39
Split string into 3 separate fields 5 18
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

685 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