Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Query Problem

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
FROM
(

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?
0
halifaxman
Asked:
halifaxman
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
FROM
(

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'
AND (case when a.status = 'Closed' and a.closedate is null then a.modified else a.closedate end ) >= CONVERT(varchar(6),GETDATE(), 112 )

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now