Problem Grouping values - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Problem Grouping values

Author

Topic

bukworm
Starting Member

10 Posts

Posted - 2011-06-10 : 05:04:38

Hi all,

I have a table from where i have to take values for a employee performance on every 20 min basis. I am using Sql report builder 3 for the report. this is the query.

select
substring(convert(varchar,starttime.value,105), 11,2) as Hour,
substring(convert(varchar,starttime.value,120), 15,2) as Minut,
JobID,
area,
empnum

from jobdetails

where starttime >= convert(vchar,@date,105) and starttime < convert(vchar,@date,105)
group by starttime, jobid, area, empnum

there could be more than one area for each emp, i want it by jobid, which is unique for a job, but one job could be diffrent records with diffrent times, i want it to be grouped together as one record.

i am puttin this in matrix in the report

problem is starttime is not grouped as they are different for each job, and i get duplicate jobid's.

can any1 help?

thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-10 : 06:24:53

with cte as
(
select JobID,
area,
empnum ,
starttime = MIN(starttime)
from jobdetails
group by
JobID,
area,
empnum
)
select
substring(convert(varchar,starttime.value,105), 11,2) as Hour,
substring(convert(varchar,starttime.value,120), 15,2) as Minut,
JobID,
area,
empnum
from cte
where starttime >= convert(vchar,@date,105) and starttime < convert(vchar,@date,105)
group by starttime, jobid, area, empnum

or maybe

with cte as
(
select JobID,
area,
empnum ,
starttime = MIN(starttime)
from jobdetails
where starttime >= convert(vchar,@date,105) and starttime < convert(vchar,@date,105)
group by
JobID,
area,
empnum
)
select
substring(convert(varchar,starttime.value,105), 11,2) as Hour,
substring(convert(varchar,starttime.value,120), 15,2) as Minut,
JobID,
area,
empnum
from cte
group by starttime, jobid, area, empnum

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bukworm
Starting Member

10 Posts

Posted - 2011-06-13 : 10:00:56

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources