Posted on 2007-07-24
I have the following query below
SELECT a.ID,a.modified,a.status ,a.Progress,a.dept,a.OpenDate,case when a.status = 'Closed' and a.closedate is null then a.modified else a.closedate end ClosedDate
SELECT UserData.tp_ID AS ID, max(UserData.datetime5) as CloseDate,max(UserData.tp_modified) as modified,min(UserData.nvarchar18) as Status,max(UserData.nvarchar17) as Progress,
case when UserData.sql_variant1 = 'Personal Injury' then 'Personal Injury' when UserData.sql_variant1 = 'Insurance Services' then 'Insurance Services' when UserData.sql_variant1 in ('IM Business','Private Client') then 'IMB & PC' else 'Others' end as Dept,UserData.tp_Created as OpenDate
FROM Operations.dbo.AllLists Lists INNER JOIN
Operations.dbo.AllUserData UserData ON Lists.tp_ID = UserData.tp_ListId
WHERE (Lists.tp_Title = 'CR') and nvarchar16 = 'BAU CR'
group by UserData.tp_ID,UserData.sql_variant1,UserData.tp_Created) a
where a.dept = 'Operations'
I want to add the following line after where a.dept = 'Operations'
and year(ClosedDate) = year(getdate()) and month(ClosedDate) <= getdate()
where closeddate is the following in the select line
case when a.status = 'Closed' and a.closedate is null then a.modified else a.closedate end ClosedDate
How can I do this?