Solved

SQL Server 2008 Sum and Groupby, Rollup

Posted on 2009-05-08
10
1,549 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now