Solved

SQL Query

Posted on 2011-09-21
7
177 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
Comment Utility
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 25

Accepted Solution

by:
Shaun Kline earned 250 total points
Comment Utility
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
Comment Utility
pratima_mcs

I am getting the following error.

Msg 102, Level 15, State 1, Procedure PXQ7070, Line 82
Incorrect syntax near '('.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Shaun_Kline

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

11 Experts available now in Live!

Get 1:1 Help Now