I have the the following query "See Code" In that query I want to pull all records that fall between the dates entered for Disposition Date. MY issue is that I am getting dates that fall outside of the entered dates. I think the issue is how I have my where clause written.

DECLARE @GetMonth VARCHAR(2)DECLARE @GetYear VARCHAR(4)SET @GetMonth = MONTH(GETDATE())SET @GetYear = YEAR(GETDATE())SELECT DISTINCT Claim ,Claimant ,Insured ,Risk ,[Loss Date] ,CSC.NoticeDate ,[Company Position] ,[Disposition Date] ,CSC.DispositionCode ,[Category] = CASE WHEN CSC.DispositionCode = '5' THEN 'Trail' WHEN CSC.DispositionCode = '6' THEN 'Trail' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Trial' WHEN CSC.DispositionCode = '11' THEN 'Trial' WHEN CSC.DispositionCode = '12' THEN 'Trial' WHEN CSC.DispositionCode = '13' THEN 'Arbitration' WHEN CSC.DispositionCode = '14' THEN 'Arbitration' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,[Type] = CASE WHEN CSC.DispositionCode = '5' THEN 'Judgment for Plaintiff' WHEN CSC.DispositionCode = '6' THEN 'Judgment for Defendant' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Pending Appeal' WHEN CSC.DispositionCode = '11' THEN 'For Plaintiff After Appeal' WHEN CSC.DispositionCode = '12' THEN 'For Defendant After Appeal' WHEN CSC.DispositionCode = '13' THEN 'Award for Plaintiff' WHEN CSC.DispositionCode = '14' THEN 'Award for Defense' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,[Plaintiff Counsel] ,[Defense Counsel] ,CSC.Venue ,[Indem Paid] ,[Exp Paid] ,ExaminerFROM EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear, NULL, 0, 0, 0, 1, 0, '99-') INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNoWHERE [Disposition Date] BETWEEN '08/01/2010' AND '08/30/2011' AND CSC.DispositionCode IN ( '4', '5', '6', '11', '12', '13', '14', '15', '18' ) OR CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' OR CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' OR CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' OR CSC.DispositionCode = ' ' AND AppealFlag = 'Y'ORDER BY CSC.DispositionCode

DECLARE @GetMonth VARCHAR(2)DECLARE @GetYear VARCHAR(4)SET @GetMonth = MONTH(GETDATE())SET @GetYear = YEAR(GETDATE())SELECT DISTINCT Claim ,Claimant ,Insured ,Risk ,[Loss Date] ,CSC.NoticeDate ,[Company Position] ,[Disposition Date] ,CSC.DispositionCode ,[Category] = CASE WHEN CSC.DispositionCode = '5' THEN 'Trail' WHEN CSC.DispositionCode = '6' THEN 'Trail' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Trial' WHEN CSC.DispositionCode = '11' THEN 'Trial' WHEN CSC.DispositionCode = '12' THEN 'Trial' WHEN CSC.DispositionCode = '13' THEN 'Arbitration' WHEN CSC.DispositionCode = '14' THEN 'Arbitration' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,[Type] = CASE WHEN CSC.DispositionCode = '5' THEN 'Judgment for Plaintiff' WHEN CSC.DispositionCode = '6' THEN 'Judgment for Defendant' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Pending Appeal' WHEN CSC.DispositionCode = '11' THEN 'For Plaintiff After Appeal' WHEN CSC.DispositionCode = '12' THEN 'For Defendant After Appeal' WHEN CSC.DispositionCode = '13' THEN 'Award for Plaintiff' WHEN CSC.DispositionCode = '14' THEN 'Award for Defense' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,[Plaintiff Counsel] ,[Defense Counsel] ,CSC.Venue ,[Indem Paid] ,[Exp Paid] ,ExaminerFROM EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear, NULL, 0, 0, 0, 1, 0, '99-') INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNoWHERE [Disposition Date] BETWEEN '08/01/2010' AND '08/30/2011' AND (CSC.DispositionCode IN ( '4', '5', '6', '11', '12', '13', '14', '15', '18' ) OR CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' OR CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' OR CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' OR CSC.DispositionCode = ' ' AND AppealFlag = 'Y')ORDER BY CSC.DispositionCode

See how a bracket was added before CSC.DispositionCode in the WHERE clause? And closed at the end?

I think you may need a few more brackets in there though to get exactly the result you want, as per the following.

DECLARE @GetMonth VARCHAR(2)DECLARE @GetYear VARCHAR(4)SET @GetMonth = MONTH(GETDATE())SET @GetYear = YEAR(GETDATE())SELECT DISTINCT Claim ,Claimant ,Insured ,Risk ,[Loss Date] ,CSC.NoticeDate ,[Company Position] ,[Disposition Date] ,CSC.DispositionCode ,[Category] = CASE WHEN CSC.DispositionCode = '5' THEN 'Trail' WHEN CSC.DispositionCode = '6' THEN 'Trail' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Trial' WHEN CSC.DispositionCode = '11' THEN 'Trial' WHEN CSC.DispositionCode = '12' THEN 'Trial' WHEN CSC.DispositionCode = '13' THEN 'Arbitration' WHEN CSC.DispositionCode = '14' THEN 'Arbitration' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,[Type] = CASE WHEN CSC.DispositionCode = '5' THEN 'Judgment for Plaintiff' WHEN CSC.DispositionCode = '6' THEN 'Judgment for Defendant' WHEN CSC.DispositionCode = ' ' AND AppealFlag = 'Y' THEN 'Pending Appeal' WHEN CSC.DispositionCode = '11' THEN 'For Plaintiff After Appeal' WHEN CSC.DispositionCode = '12' THEN 'For Defendant After Appeal' WHEN CSC.DispositionCode = '13' THEN 'Award for Plaintiff' WHEN CSC.DispositionCode = '14' THEN 'Award for Defense' WHEN CSC.DispositionCode = '15' THEN 'Mediation' WHEN CSC.DispositionCode = '18' THEN 'Early Offer Claims' WHEN CSC.DispositionCode = '4' THEN 'Involuntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] = '0.00' THEN 'Voluntary Dismissal' WHEN CSC.DispositionCode = '3' AND [Indem Paid] > '0.00' THEN 'Settlements' WHEN CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00' THEN 'Incident Only Payment' END ,[Plaintiff Counsel] ,[Defense Counsel] ,CSC.Venue ,[Indem Paid] ,[Exp Paid] ,ExaminerFROM EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear, NULL, 0, 0, 0, 1, 0, '99-') INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNoWHERE [Disposition Date] BETWEEN '08/01/2010' AND '08/30/2011' AND (CSC.DispositionCode IN ( '4', '5', '6', '11', '12', '13', '14', '15', '18' ) OR (CSC.DispositionCode = '3' AND [Indem Paid] = '0.00') OR (CSC.DispositionCode = '3' AND [Indem Paid] > '0.00') OR (CSC.DispositionCode = 'E' AND [Indem Paid] > '0.00') OR (CSC.DispositionCode = ' ' AND AppealFlag = 'Y'))ORDER BY CSC.DispositionCode

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.

Open in new window