Been wracking my brains with this

Question

Post reply

Been wracking my brains with this

judo-467517

Newbie

Points: 6
More actions
July 28, 2011 at 10:13 pm

#390082

I have spent too long trying to figure this out and hopefully a fresh look at the problem will help.
I have data returned from a table that looks something like this when it is boiled down:
TimesheetKey SiteKey Date Duration GroupRule
1000 1 7/28/2011 480 1
1001 1 7/28/2011 600 1
1002 1 7/28/2011 480 2
I need to SELECT TimesheetKey(s), SUM(Duration), grouping by SiteKey, Date when the GroupRule is 1.
I need to SELECT TimesheetKey, SUM(Duration), grouping by SiteKey, Date, TimesheetKey when the GroupRule is 2.
I am looking for something like:
1000 1 7/28/2011 1080 1
1001 1 7/28/2011 1080 1
1002 1 7/28/2011 480 2
I was doing this using two separate but similar queries and UNIONing the results but the queries have gotten pretty big and I'd like to pass through the query once.
Is this possible or do I need to stick with the UNION?
Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

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

SQLServerMS SSC Veteran Points: 223 More actions · Answer 1

Hi Judo,

Execute the below query, you will be getting the result as you said

CREATE TABLE #TIMESHEET(TimesheetKey INT, SiteKey INT, Date VARCHAR(10), Duration INT, GroupRule INT)

INSERT INTO #TIMESHEET VALUES(1000, 1, '7/28/2011', 480, 1)

INSERT INTO #TIMESHEET VALUES(1001, 1, '7/28/2011', 600, 1)

INSERT INTO #TIMESHEET VALUES(1002, 1, '7/28/2011', 480, 2)

SELECT * FROM #TIMESHEET

SELECT

B.[TIMESHEETKEY]

, B.[SITEKEY]

, B.[DATE]

, A.[SUM_DURATION]

, A.[GROUPRULE]

FROM

(

SELECT

[GroupRule]

, SUM([DURATION]) [SUM_DURATION] FROM #TIMESHEET

GROUP BY GroupRule

) A

JOIN #TIMESHEET B

ON A.[GroupRule] = B.[GroupRule]

Have a nice day!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT TimesheetKey, SiteKey, [Date],

SUMDuration = SUM(Duration) OVER(PARTITION BY SiteKey, [Date], GroupRule), GroupRule

FROM #TIMESHEET

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQLServerMS SSC Veteran Points: 223 More actions · Answer 3

SELECT TimesheetKey, SiteKey, [Date],

SUMDuration = SUM(Duration) OVER(PARTITION BY GroupRule), GroupRule

FROM #TIMESHEET

Have a nice day!

judo-467517 Newbie Points: 6 More actions · Answer 4

I forgot to thank you guys. The PARTITION BY worked out greatly!