Solved

SQL Query

Posted on 2011-09-21
7
178 Views
Last Modified: 2012-05-12
I am using the following query as my backend for an Access Datasheet. I would like to set the @AsOfMonth and @AsOfYear to be the current month/year so that I do not need to create a parm entry on the Access side since this will always be the current month/year. When I run this query ad-Hoc I use the following.
DECLARE @GetMonth VARCHAR(2)
DECLARE @GetYear VARCHAR(4)

SET @GetMonth = MONTH(GETDATE())
SET @GetYear = YEAR(GETDATE())

USE [EXPORTpahpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[PXQ7070]    Script Date: 09/21/2011 07:27:31 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/* ***************************************************************************************************/
/*  		                       Closed Claims Report    	                                     */
/* ***************************************************************************************************/
 
ALTER PROCEDURE [dbo].[PXQ7070]
(     
      @AsOfMonth DATETIME ,
      @AsOfYear DATETIME ,
      @FromDate DATETIME ,
      @ToDate DATETIME,
      @GblOptState VARCHAR(4)

)
AS 
    SET NOCOUNT ON

SELECT DISTINCT
        [Category] = CASE WHEN CSC.DispositionCode = '5' THEN 'Trial'
                          WHEN CSC.DispositionCode = '6' THEN 'Trial'
                          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
       ,Claim
       ,Claimant
       ,Insured
       ,Risk
       ,[Loss Date]
       ,CSC.NoticeDate
       ,[Company Position]
       ,[Disposition Date]
       ,CSC.DispositionCode
       ,[Plaintiff Counsel]
       ,[Defense Counsel]k
       ,CSC.Venue
       ,[Indem Paid]
       ,[Exp Paid]
       ,Examiner
FROM    EXPORTpahpixPROD.dbo.PXQ7000_UDF(@AsOfMonth, @AsOfYear, NULL, 0, 0, 0, 1,
                                         0, @GblOptState)
        INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNo
WHERE   [Disposition Date] BETWEEN @FromDate
                           AND     @ToDate
        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 = 'E'
                   AND AppealFlag = 'Y'
                 )
            )
         AND (CurrentFlag = 'Y')
ORDER BY CSC.DispositionCode

Open in new window

0
Comment
Question by:mburk1968
  • 3
  • 3
7 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36573347
USE [EXPORTpahpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[PXQ7070]    Script Date: 09/21/2011 07:27:31 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/* ***************************************************************************************************/
/*  		                       Closed Claims Report    	                                     */
/* ***************************************************************************************************/
 
ALTER PROCEDURE [dbo].[PXQ7070]
(     
      @AsOfMonth DATETIME ,
      @AsOfYear DATETIME ,
      @FromDate DATETIME ,
      @ToDate DATETIME,
      @GblOptState VARCHAR(4)

)
AS 
    SET NOCOUNT ON

SELECT DISTINCT
        [Category] = CASE WHEN CSC.DispositionCode = '5' THEN 'Trial'
                          WHEN CSC.DispositionCode = '6' THEN 'Trial'
                          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
       ,Claim
       ,Claimant
       ,Insured
       ,Risk
       ,[Loss Date]
       ,CSC.NoticeDate
       ,[Company Position]
       ,[Disposition Date]
       ,CSC.DispositionCode
       ,[Plaintiff Counsel]
       ,[Defense Counsel]k
       ,CSC.Venue
       ,[Indem Paid]
       ,[Exp Paid]
       ,Examiner
FROM    EXPORTpahpixPROD.dbo.PXQ7000_UDF(cast (MONTH(GETDATE()) as varchar(2)),cast (YEAR(GETDATE()) as varchar(4)), NULL, 0, 0, 0, 1,
                                         0, @GblOptState)
        INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNo
WHERE   [Disposition Date] BETWEEN @FromDate
                           AND     @ToDate
        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 = 'E'
                   AND AppealFlag = 'Y'
                 )
            )
         AND (CurrentFlag = 'Y')
ORDER BY CSC.DispositionCode

Open in new window

0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 250 total points
ID: 36573362
If you provide a default value, you can test for that value in your stored procedure to determine if a value has been passed in. For DATETIME data types, use NULL:

ALTER PROCEDURE [dbo].[PXQ7070]
(    
      @AsOfMonth DATETIME = NULL,
      @AsOfYear DATETIME = NULL,

And in the body of the SP, perform a check for that value:

IF @AsOfMonth IS NULL
   SET @AsOfMonth = MONTH(GETDATE())
IF @AsOfYear IS NULL
   SET @AsOfYear = YEAR(GETDATE())
0
 

Author Comment

by:mburk1968
ID: 36573375
pratima_mcs

I am getting the following error.

Msg 102, Level 15, State 1, Procedure PXQ7070, Line 82
Incorrect syntax near '('.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36573407
USE [EXPORTpahpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[PXQ7070]    Script Date: 09/21/2011 07:27:31 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/* ***************************************************************************************************/
/*                                     Closed Claims Report                                               */
/* ***************************************************************************************************/
 
ALTER PROCEDURE [dbo].[PXQ7070]
(    
      @AsOfMonth DATETIME ,
      @AsOfYear DATETIME ,
      @FromDate DATETIME ,
      @ToDate DATETIME,
      @GblOptState VARCHAR(4)

)
AS
    SET NOCOUNT ON

SELECT DISTINCT
        [Category] = CASE WHEN CSC.DispositionCode = '5' THEN 'Trial'
                          WHEN CSC.DispositionCode = '6' THEN 'Trial'
                          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
       ,Claim
       ,Claimant
       ,Insured
       ,Risk
       ,[Loss Date]
       ,CSC.NoticeDate
       ,[Company Position]
       ,[Disposition Date]
       ,CSC.DispositionCode
       ,[Plaintiff Counsel]
       ,[Defense Counsel]k
       ,CSC.Venue
       ,[Indem Paid]
       ,[Exp Paid]
       ,Examiner
FROM    EXPORTpahpixPROD.dbo.PXQ7000_UDF(select cast (MONTH(GETDATE()) as varchar(2)), select cast (Year(GETDATE()) as varchar(2)), NULL, 0, 0, 0, 1,
                                         0, @GblOptState)
        INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNo
WHERE   [Disposition Date] BETWEEN @FromDate
                           AND     @ToDate
        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 = 'E'
                   AND AppealFlag = 'Y'
                 )
            )
         AND (CurrentFlag = 'Y')
ORDER BY CSC.DispositionCode
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36573415
USE [EXPORTpahpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[PXQ7070]    Script Date: 09/21/2011 07:27:31 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/* ***************************************************************************************************/
/*                                     Closed Claims Report                                               */
/* ***************************************************************************************************/
 
ALTER PROCEDURE [dbo].[PXQ7070]
(    
      @AsOfMonth DATETIME ,
      @AsOfYear DATETIME ,
      @FromDate DATETIME ,
      @ToDate DATETIME,
      @GblOptState VARCHAR(4)

)
AS
    SET NOCOUNT ON

SELECT DISTINCT
        [Category] = CASE WHEN CSC.DispositionCode = '5' THEN 'Trial'
                          WHEN CSC.DispositionCode = '6' THEN 'Trial'
                          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
       ,Claim
       ,Claimant
       ,Insured
       ,Risk
       ,[Loss Date]
       ,CSC.NoticeDate
       ,[Company Position]
       ,[Disposition Date]
       ,CSC.DispositionCode
       ,[Plaintiff Counsel]
       ,[Defense Counsel]k
       ,CSC.Venue
       ,[Indem Paid]
       ,[Exp Paid]
       ,Examiner
FROM    EXPORTpahpixPROD.dbo.PXQ7000_UDF(cast (MONTH(GETDATE()) as varchar(2)), cast (Year(GETDATE()) as varchar(2)), NULL, 0, 0, 0, 1,
                                         0, @GblOptState)
        INNER JOIN wellandp.ClaimSubclaim CSC ON dbo.PXQ7000_UDF.Claim = CSC.ClaimNo
WHERE   [Disposition Date] BETWEEN @FromDate
                           AND     @ToDate
        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 = 'E'
                   AND AppealFlag = 'Y'
                 )
            )
         AND (CurrentFlag = 'Y')
ORDER BY CSC.DispositionCode
0
 

Author Comment

by:mburk1968
ID: 36573431
Now this... I'm certain it is something simple.

Msg 156, Level 15, State 1, Procedure PXQ7070, Line 82
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Procedure PXQ7070, Line 82
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Procedure PXQ7070, Line 83
Incorrect syntax near ')'.
0
 

Author Comment

by:mburk1968
ID: 36573566
Shaun_Kline

Where would I place the if in the body exactly?
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to SUM hours for the same record 1 33
format nvarchar field as mm/dd/yyyy 4 61
Sql Permission 6 52
Not selecting duplicate data 6 46
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore 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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now