January 20, 2009 at 2:29 pm
Hi all,
My query is supposed to bring back a single row with counts for the individual tasks assigned in the database. however the following query brings back multiple rows for the same date and i was wondering if someone here can point out how i can get the data back for the distinct date as i require?
thanks in advance.
SELECT
distinct
Cast(Convert(varchar, z.curr_time,107) as datetime) as CURR_time,
(select count (*) from dbo.v_ITSM_Tasks Task
WHERE incident.I_RecId = T_ParentLink_RecID
AND (
datepart(yy, GETDATE()) = datepart(yy, T_AssignedDateTime)
and datepart(mm, GETDATE()) = datepart(mm, T_AssignedDateTime)
and datepart(dd, GETDATE()) = datepart(dd, T_AssignedDateTime)
)) as 'new_tasks',
(select count(*) from dbo.v_HD_DW_ITSM_Tasks
WHERE incident.I_RecId = T_ParentLink_RecID
AND (
datepart(yy, GETDATE()) = datepart(yy, T_ResolvedDateTime)
and datepart(mm, GETDATE()) = datepart(mm, T_ResolvedDateTime)
and datepart(dd, GETDATE()) = datepart(dd, T_ResolvedDateTime)
)) as 'RESOLVED_TASKS',
(select count(*) from dbo.v_ITSM_Tasks
WHERE incident.I_RecId = T_ParentLink_RecID
AND datepart(yy, GETDATE()) >= datepart(yy, T_CreatedDateTime)
AND datepart(mm, GETDATE()) > = datepart(mm, T_CreatedDateTime)
AND datepart(dd, GETDATE()) > datepart(dd, T_CreatedDateTime)
AND T_ResolvedDateTime IS NULL
) as OPEN_tasks
FROM dbo.v_ITSM_Incidents incident, dbo.v_ITSM_Tasks Task
INNER JOIN (
select getdate() as curr_time
) z on 1=1
WHERE incident.I_RecId = Task.T_ParentLink_RecID
AND datepart(yy, GETDATE()) = datepart(yy, z.curr_time)
AND datepart(mm, GETDATE()) = datepart(mm, z.curr_time)
AND datepart(dd, GETDATE()) = datepart(dd, z.curr_time)
group by datepart(yy, z.curr_time), datepart(mm, z.curr_time), datepart(dd, z.curr_time)
, z.curr_time, incident.I_RecID, task.T_ParentLink_RecID
order by CURR_time
this is the table schema for the columns i require in this query
T_AssignedDateTimedatetimeno8 yes(n/a)
T_CreatedDateTimedatetimeno8 yes(n/a)(n/a)NULL
T_ResolvedDateTimedatetimeno8 yes(n/a)(n/a)NULL
January 20, 2009 at 2:38 pm
First, is curr_time date-only, or does it have times stored in it? You have that in the Group By clause, so if it has distinct times stored in it, you'll end up with one row for each combination of hours, minutes, second and milliseconds.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2009 at 3:42 pm
Hi and thanks for helping out a newbee... i have cleaned my query down to what i need however i am sorta new to sql so i dont know how i should go further with this query.
right now it brings me the following...
2009-01-20 00:00:002062051082459
what i need: one row for each date from current date.
2009-01-20 00:00:002062051082459
2009-01-19 00:00:002062051082459(with the corresponding values for that day)
2009-01-18 00:00:002062051082459(with the corresponding values for that day)
2009-01-17 00:00:002062051082459(with the corresponding values for that day)
2009-01-16 00:00:002062051082459(with the corresponding values for that day)
here is the query:
SELECT
cast(convert(varchar(20),getdate(),101) as smalldatetime) as curr_time,
SUM(CASE WHEN cast(convert(varchar(20),getdate(),101) as smalldatetime) = cast(convert(varchar(20),T_AssignedDateTime,101) as smalldatetime) THEN 1 ELSE 0 END) AS new_tasks,
SUM(CASE WHEN cast(convert(varchar(20),getdate(),101) as smalldatetime) = cast(convert(varchar(20),T_ResolvedDateTime,101) as smalldatetime) THEN 1 ELSE 0 END) AS RESOLVED_TASKS,
SUM(CASE WHEN T_ResolvedDateTime IS NULL AND cast(convert(varchar(20),getdate(),101) as smalldatetime) = cast(convert(varchar(20),T_CreatedDateTime,101) as smalldatetime) THEN 1 ELSE 0 END) as OPEN_tasks_Today,
SUM(CASE WHEN T_ResolvedDateTime IS NULL THEN 1 ELSE 0 END) as OPEN_tasks_Total
FROM dbo.v_HD_DW_ITSM_Incidents incident
INNER JOIN dbo.v_HD_DW_ITSM_Tasks Task
ON incident.I_RecId = Task.T_ParentLink_RecID
thanks in advance
January 21, 2009 at 10:52 am
You need to have a Group By clause on that query. What column of which table is the date supposed to come from?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply