Run Different Months 2

[Stangleboy]

I am trying to get the same data retrieved for different months, but not sure how to code it. It works fine for 1 month (May in this case) but I need to know if the person made a payment in full for each month for April and June also in one query. Thank you for your time and patience:

SELECT Distinct
CC.C_CASE_EXTID As 'CaseNumber'
CA.CA_CRNT_SUPT As 'CurrentSupport'
--CAST.CAST_CHLD_SUPT_AMT As 'ChildSupport',
--CAST.CAST_EFF_TRANS_TS As 'PaymentDate'
FROM
CASE_CAS As CC INNER JOIN
CASE_ACCOUNT As CA ON CC.CAS_CASE_ACCT_SUM_ID = CA.CA_CASE_ACCT_SUM_ID INNER JOIN
CASE_ACCOUNT_SUMMARY_TRANSACTION As CAST ON CC.CAS_CASE_ACCT_SUM_ID = CAST.CAST_CASE_ACCT_SUM_ID
WHERE
CC.C_MNG_CNTY_FIPS_CD = '071'
And CC.C_PND_CLS_STAT_CD <> 'ACT'
And CC.C_CRNT_STAT_CD = 'OPN'
And CA.CA_CRNT_SUPT = '0'
And CAST.CAST_CHLD_SUPT_AMT <> '0'
And CAST.CAST_EFF_TRANS_TS Between '2010-05-01' And '2010-05-31'

 

[bborissov]

You could make this a SP and pass star and end date as parameters:

CREATE PROCEDURE MyTestProc(
   @StartDate datetime,
   @EndDate   datetime
   )
AS 
  BEGIN 
        SELECT  CC.C_CASE_EXTID              AS CaseNumber,
                CA.CA_CRNT_SUPT              AS CurrentSupport,
                MONTH(CST.CAST_EFF_TRANS_TS) AS Mnt,
                SUM(CAST_CHLD_SUPT_AMT)      AS ChildSupport    
        FROM CASE_CAS As CC
        INNER JOIN CASE_ACCOUNT                     AS CA  ON CC.CAS_CASE_ACCT_SUM_ID = CA.CA_CASE_ACCT_SUM_ID
        INNER JOIN CASE_ACCOUNT_SUMMARY_TRANSACTION AS CST ON CC.CAS_CASE_ACCT_SUM_ID = CST.CAST_CASE_ACCT_SUM_ID
        WHERE   CC.C_MNG_CNTY_FIPS_CD  = '071'
            AND CC.C_PND_CLS_STAT_CD  <> 'ACT'
            AND CC.C_CRNT_STAT_CD      = 'OPN'
            AND CA.CA_CRNT_SUPT        =  '0'
            AND CST.CAST_CHLD_SUPT_AMT <> '0'
            AND CST.CAST_EFF_TRANS_TS >= @StartDate
            AND CST.CAST_EFF_TRANS_TS <  @EndDate

        GROUP BY CC.C_CASE_EXTID,
                 CA.CA_CRNT_SUPT,
                 MONTH(CST.CAST_EFF_TRANS_TS)
  END

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Stangleboy]

I belive this will still require me to run three separate times for each month when I want it to run one time and give me the results for each month. Maybe a temporary table for each month inside the main query?

 

[bborissov]

Just try it:

EXEC MyTestProc 
     @StartDate = '20100401', -- April 1 2010
     @EndDate = '20100701'    -- July 1 2010

That should give you 3 months Apr, May and June.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Stangleboy]

Will do, give me a few I will let you know the results, thank you.

 

[markros]

Do you want consecutive months (e.g. date range) or some specific months (not sequential)?

PluralSight Learning Library