Consolidate a sql select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Consolidate a sql select statement

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-04-16 : 16:53:23

the following is my query, what I would like to do is show 1 line for every person instead of potentiall many. Most of the information is redundent except for the dates and the count of the dates and mileage. What I would like to do is combine all the dates into one field and do a count on them for 1 tally as well as the mileage. Here is how the information come out currently per the query.
133RD F31LFVXG GASPERSZ 555555555 E3-6 3/28/2009 1 320
133RD F31LFVXG GASPERSZ 555555555 E3-6 3/29/2009 1 0
133RD F31LFVXG GASPERSZ 555555555 E3-6 3/30/2009 1 0
133RD F31LFVXG GASPERSZ 555555555 E3-6 3/31/2009 1 0
133RD F31LFVXG GASPERSZ 555555555 E3-6 4/1/2009 1 0
133RD F31LFVXG GASPERSZ 555555555 E3-6 4/3/2009 1 0
133RD F31LFVXG GASPERSZ 555555555 E3-6 4/4/2009 1 0
133RD F31LFVXG GASPERSZ 555555555 E3-6 4/5/2009 1 0

This is what I would like it to look like.
133RD F31LFVXG GASPERSZ 555555555 E3-6 3/28/2009, 3/29/2009, 3/30/2009, 3/31/2009, 4/1/2009, 4/2/2009, 4/3/2009, 4/4/2009, 4/5/2009 8 320
All the dates of that individual would be combined into one field and then counted to get the 8 and then the mileage sumed up as well, So this goes from 8 records down to one combined record.

Here is my sql query
select u.strUnit,
u.strPASCODE,
s.strName,
p.strSSN,
s.strGrade + '-' + s.strYrsSVC as PGYrs,
p.dtAttendance,
count(p.dtattendance) as TotalDays,
sum(intMileage) as Miles
from tblPersonnel as s INNER JOIN
tblUnits as u on u.strPascode = s.strPascode INNER JOIN
tblAssignedPersonnel as p on p.strSSN = s.strSSN
where p.bitPresent = 1 and intDrillStatus = 2 and dtPayProcessed IS Not Null and dtPaid is null and p.intUICID in (select intUICID from tblUIC where intTaskForceID = 7 and strUIC = 'F31LFVXG')
group by s.strName, p.strSSN, s.strGrade + '-' + s.strYrsSVC, u.strUnit, u.strPASCODE, p.dtAttendance

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-16 : 17:34:18

maybe this? what makes each row unique?

declare @t table (strUnit varchar(50), strPASCODE varchar(50, strName varchar(50, strSSN varchar(50), PGYrs varchar(50), dtAttendance datetime, TotalDays INT, Miles INT)
insert @t

select u.strUnit,
u.strPASCODE,
s.strName,
p.strSSN,
s.strGrade + '-' + s.strYrsSVC as PGYrs,
p.dtAttendance,
p.dtattendance,
intMileage
from tblPersonnel as s INNER JOIN
tblUnits as u on u.strPascode = s.strPascode INNER JOIN
tblAssignedPersonnel as p on p.strSSN = s.strSSN
where p.bitPresent = 1 and intDrillStatus = 2 and dtPayProcessed IS Not Null and dtPaid is null and p.intUICID in (select intUICID from tblUIC where intTaskForceID = 7 and strUIC = 'F31LFVXG')

SELECT STUFF((SELECT ',' + [dtAttendance]
FROM @t
WHERE strPASCODE = t.strPASCODE --and/or any other field that makes it unique
FOR XML PATH('')),1,1,'') as [newcol],
count(p.dtattendance) as TotalDays,
sum(intMileage) as Miles,
t.strUnit,
t.strPASCODE,
t.strName,
t.strSSN,
t.tPGYrs,
t.dtAttendance,
FROM @t t
group by t.strName, t.strSSN, t.PGYrs, t..strUnit, t.strPASCODE, t.dtAttendance

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources