Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2008 Sum and Groupby, Rollup

Posted on 2009-05-08
10
Medium Priority
?
1,584 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

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 ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

715 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