Solved

SQL Query

Posted on 2011-09-15
6
247 Views
Last Modified: 2012-06-27
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]
       ,Examiner
FROM    EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear, NULL, 0, 0, 0, 1,
                                         0, '99-')
        INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNo
WHERE   [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

Open in new window

0
Comment
Question by:mburk1968
  • 2
  • 2
6 Comments
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 36544201
Need to add parenthesis for order of operation.
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]
       ,Examiner
FROM    EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear, NULL, 0, 0, 0, 1,
                                         0, '99-')
        INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNo
WHERE   [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

Open in new window

0
 

Author Comment

by:mburk1968
ID: 36544490
Sorry to say this but I do notuns=derstand what you mean.
0
 
LVL 6

Accepted Solution

by:
dan_mason earned 500 total points
ID: 36548561
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]
       ,Examiner
FROM    EXPORTpahpixPROD.dbo.PXQ7000_UDF(@GetMonth, @GetYear, NULL, 0, 0, 0, 1,
                                         0, '99-')
        INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNo
WHERE   [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

Open in new window

0
 

Author Comment

by:mburk1968
ID: 36549137
I've requested that this question be deleted for the following reason:

I have the question in the wrong zone. Answer was not complete and have received no further replies.
0
 
LVL 6

Expert Comment

by:dan_mason
ID: 36549138
This cancellation notice must have been posted after my answer I think, and I don't think my answer was incomplete.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
On some Windows 7 (SP1) computers, Windows Update becomes super slow even the computer is reasonably fast.  There's one solution that seemed to have worked well for me (after trying a few other suggested solutions).
This Micro Tutorial will go in depth within Systems and Security in Windows 7 and will go into detail regarding Action Center, Windows Firewall, System, etc. This will be demonstrated using Windows 7 operating system.
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question