Detail Records into Summary Format via Stored Procedure?

Question

Post reply

Detail Records into Summary Format via Stored Procedure?

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

gsaunders SSC-Addicted Points: 457 More actions · Answer 1

This was suggested by someone which works:

SELECT EmployeeName, EmployeeNo,

SUM(CASE WHEN dw=1 THEN RegHours ELSE 0 END) AS Sun_RegHours,

SUM(CASE WHEN dw=2 THEN RegHours ELSE 0 END) AS Mon_RegHours,

SUM(CASE WHEN dw=3 THEN RegHours ELSE 0 END) AS Tues_RegHours,

SUM(CASE WHEN dw=4 THEN RegHours ELSE 0 END) AS Wed_RegHours,

SUM(CASE WHEN dw=5 THEN RegHours ELSE 0 END) AS Thur_RegHours,

SUM(CASE WHEN dw=6 THEN RegHours ELSE 0 END) AS Fri_RegHours,

SUM(CASE WHEN dw=0 THEN RegHours ELSE 0 END) AS Sat_RegHours,

SUM(RegHours) AS Total_RegHours,

SUM(CASE WHEN dw=1 THEN OtherHours ELSE 0 END) AS Sun_OtherHours,

SUM(CASE WHEN dw=2 THEN OtherHours ELSE 0 END) AS Mon_OtherHours,

SUM(CASE WHEN dw=3 THEN OtherHours ELSE 0 END) AS Tues_OtherHours,

SUM(CASE WHEN dw=4 THEN OtherHours ELSE 0 END) AS Wed_OtherHours,

SUM(CASE WHEN dw=5 THEN OtherHours ELSE 0 END) AS Thur_OtherHours,

SUM(CASE WHEN dw=6 THEN OtherHours ELSE 0 END) AS Fri_OtherHours,

SUM(CASE WHEN dw=0 THEN OtherHours ELSE 0 END) AS Sat_OtherHours,

SUM(OtherHours) AS Total_OtherHours

FROM (

SELECT EmployeeName, EmployeeNo, RegHours, OtherHours,

DATEPART(dw,DateEnt) AS dw

FROM EmployeeTime

) x GROUP BY EmployeeName, EmployeeNo

Does this look like the best way of doing it?

Does anyone have a recommendation of something they think is better?

Also I noticed the GROUP BY has an x in front of it. I have never noticed this before... without it generates an error. What does this x mean?

Thanks

Garadin One Orange Chip Points: 29613 More actions · Answer 2

Give this article a read.

http://www.sqlservercentral.com/articles/T-SQL/63681/

The X is the derived table alias, doesn't have anything to do with the GROUP BY.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Lowell SSC Guru Points: 323741 More actions · Answer 3

the x is an alias; when you have a select in parenthesis, you need an alias in order to reference it;if you change the "x" to "mySubQuery" it makes more sense when you reread it.

select mySubQuery.* from

(

select stuff

from mytable

group by otherstuff

) mySubQuery

where mySubQuery.stuff = @myfildet

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gsaunders SSC-Addicted Points: 457 More actions · Answer 4

Lowell (12/9/2008)
the x is an alias; when you have a select in parenthesis, you need an alias in order to reference it;if you change the "x" to "mySubQuery" it makes more sense when you reread it.
select mySubQuery.* from
(
select stuff
from mytable
group by otherstuff
) mySubQuery
where mySubQuery.stuff = @myfildet

I was just getting ready to post to say it was the alias... I was having a brain fart.

Thanks.

gsaunders SSC-Addicted Points: 457 More actions · Answer 5

Garadin (12/9/2008)
Give this article a read.
http://www.sqlservercentral.com/articles/T-SQL/63681/
The X is the derived table alias, doesn't have anything to do with the GROUP BY.

I'll give that link a look... I have never had to do Cross Tabs or pivots in the past with SQL so this will be good to read.

In regards to the "X" I realized it was the table alias just moments after posting it... it just looked funny as I never use an x for an alias.

Thanks.

gsaunders SSC-Addicted Points: 457 More actions · Answer 6

This is even cleaner to me:

SELECT EmployeeName, EmployeeNo,

SUM(CASE WHEN DATEPART(dw,DateEnt)=1 THEN RegHours ELSE 0 END) AS SunRegHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=2 THEN RegHours ELSE 0 END) AS MonRegHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=3 THEN RegHours ELSE 0 END) AS TueRegHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=4 THEN RegHours ELSE 0 END) AS WedRegHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=5 THEN RegHours ELSE 0 END) AS ThuRegHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=6 THEN RegHours ELSE 0 END) AS FriRegHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=7 THEN RegHours ELSE 0 END) AS SatRegHours,

SUM(RegHours) AS TotalRegHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=1 THEN OtherHours ELSE 0 END) AS SunOtherHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=2 THEN OtherHours ELSE 0 END) AS MonOtherHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=3 THEN OtherHours ELSE 0 END) AS TueOtherHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=4 THEN OtherHours ELSE 0 END) AS WedOtherHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=5 THEN OtherHours ELSE 0 END) AS ThuOtherHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=6 THEN OtherHours ELSE 0 END) AS FriOtherHours,

SUM(CASE WHEN DATEPART(dw,DateEnt)=7 THEN OtherHours ELSE 0 END) AS SatOtherHours,

SUM(OtherHours) AS TotalOtherHours

GROUP BY EmployeeName, EmployeeNumber

Unless I need to order by a different field like LastName which means I either need to include it in the Group.