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

This cancellation notice must have been posted after my answer I think, and I don't think my answer was incomplete.

0

Featured Post

The purpose of this paper is to provide you background on SQL Server. Itâ€™s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Open in new window