Query to merge duplicate records

Hello,

I have the following Query:

1 declare @.StartDatechar(8)
2 declare @.EndDatechar(8)
3 set @.StartDate ='20070601'
4 set @.EndDate ='20070630'
5 SELECT Initials, [Position], DATEDIFF(mi,[TimeOn],[TimeOff])AS ProTime
6 FROM LogTableWHERE
7 [TimeOn]BETWEEN @.StartDateAND @.EndDateAND
8 [TimeOff]BETWEEN @.StartDateAND @.EndDate
9 ORDER BY [Position],[Initials]ASC

The query returns the following data:

Position Initials ProTime
---------------- --- ----
ACAD JJ 127
ACAD JJ 62
ACAD KK 230
ACAD KK 83
ACAD KK 127
ACAD TD 122
ACAD TJ 127

What I'm having trouble with is the fact that I need to return a results that has the totals for each set of initials for each position. For Example, the final output that I'm looking to get is the following:

Postition Initials ProTime

ACAD JJ 189
ACAD KK 440
ACAD TD 122
ACAD TJ 127

Any assistance greatly appreciated.

Use the GROUP BY syntax, and SUM() the time diffs.

Your query will look like this:

SELECT Initials, [Position], SUM(DATEDIFF(mi,[TimeOn],[TimeOff])) AS ProTime
FROM LogTableWHERE
[TimeOn]BETWEEN @.StartDateAND @.EndDateAND[TimeOff]BETWEEN @.StartDateAND @.EndDate

GROUP BY Initials, Position

Leave off the ORDER BY from your statement - you see that the SQL is grouping by the columns you wish to merge and summing the variable data.

|||

Try:

SELECT Initials, [Position], SUM(DATEDIFF(mi,[TimeOn],[TimeOff]))AS ProTime
FROM LogTable

WHERE
[TimeOn]BETWEEN @.StartDateAND @.EndDateAND
[TimeOff]BETWEEN @.StartDateAND @.EndDate

GROUP BY Initials, [Position]
ORDER BY [Position],[Initials]ASC

|||

That was it! I knew it was going to be simple, but I could not "see the forest for the trees".

Thanks

Dan