Solved

SQL Server 2008 Sum and Groupby, Rollup

Posted on 2009-05-08
10
1,576 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:Glenn Stearns
[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
  • 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:Glenn Stearns
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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:Glenn Stearns
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:Glenn Stearns
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:Glenn Stearns
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:Glenn Stearns
ID: 31579590
Thanks so much, Greg...
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 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