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
Solved

SQL Server 2008 Sum and Groupby, Rollup

Posted on 2009-05-08
10
1,559 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

837 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