Solved

SQL Query

Posted on 2011-09-15
6
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
By default the complete memory dump option is disabled in windows . If we want to enable the complete memory dump for a diagnostic purpose, we have a solution for it. here we are using the registry method to enable this.
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.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

696 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