Can someone show me how to add these fields to this Stored Procedure

Question

I have a SP that was built for us that does a summary statement of tables in our DB. What I am trying to do is make it so the SP also pulls that last year/month of data as well based on the date entered. Below is the SQL code I am working with. What I am trying to get is a total and Volume field that is the sum based on the date parameter entered minus 1 month.

For example: If I put in 2013 10 01 start and 2013 10 31 end I would get the total and volume for 2013-10-01 to 2013-10-31 and in 2 separate columns the total and volume for 2013-09-01 to 1013-09-30

Code

      ( 
  @Start                      DATETIME,
  @End                        DATETIME
)
AS
DECLARE
   @reference int,
   @sSQL VARCHAR(2000)
BEGIN
select Convert(datetime,Cast(edi.Creation_dt as varchar(8)),103) as Date, ia.xref_no_tx, la.ldc_acct_no, la.serv_loc_nm
, a.acct_nm, c.company_last_nm
, Case RG.Rate_cd
    When 'DLS' then 'HEDGE'
    When 'STL' then 'STL'
    WHen 'SPOT BILLING' then 'SPOT'
    WHen 'SL SPOT' then 'STL SPOT'
    Else null
    End as Acct_type
      , Convert(datetime,Cast(ia.start_dt as varchar(8)),103)as Start_dt
      , Convert(datetime,Cast(ia.end_dt as varchar(8)),103) as End_dt
      , edi.trans_sub_ty as Inv_type
      , max( case       when la.class_ty_cd = 'COMM' and th.ppa_in = 'N' and th.trans_sub_ty_cd = 'FEES'  then th.trans_qty
                        when la.class_ty_cd = 'MUNI' and th.ppa_in = 'N' and th.trans_sub_ty_cd = 'EXCS' then th.trans_qty
                        when la.class_ty_cd <> 'COMM' and th.ppa_in = 'N' and th.trans_sub_ty_cd = 'BASE' then th.trans_qty
                        else 0 end) as Volume

      , sum(th.trans_am) as Total

from invoice_advise_relate iar, transaction_history th
      ,invoice_advise ia, ldc_account la, account a, customer c, edi_transaction edi
      , (select max(edi_trans_id) as m_edi_trans, relate_id from edi_transaction where class_nm = 'cInvoiceAdvise' group by relate_id) as edic
      , (Select max(rating_group_id) as m_rate, ldc_acct_id from rating_group group by ldc_acct_Id) as C_Rate
      , rating_group rg
where iar.trans_id = th.trans_id
and th.cancel_in = 'N'
and th.trans_ty_cd not in ('PAY', 'ANC')
and iar.inv_adv_id = ia.inv_adv_id
and ia.ldc_acct_id = la.ldc_acct_id
and la.acct_id = a.acct_id 
and a.cust_id = c.cust_id
and la.ldc_acct_no not like 'E%'
and edi.Creation_dt >= convert(varchar,@Start,112)
and edi.Creation_dt <= convert(varchar,@End,112)
and edi.relate_id = ia.inv_adv_id
and edic.m_edi_trans = edi.edi_trans_id
and edi.response_cd = ''
and rg.rating_group_id = C_Rate.M_Rate
and C_Rate.LDC_Acct_Id = la.ldc_Acct_Id
and edi.trans_sub_ty <> '00'
group by edi.Creation_dt, ia.xref_no_tx, la.ldc_acct_no,la.serv_loc_nm, a.acct_nm, c.company_last_nm, ia.start_dt, ia.end_dt,edi.trans_sub_ty, rg.rate_cd

Take a look at **[this post](http://stackoverflow.com/questions/3932947/sql-server-2005-how-to-subtract-6-month)** you'll likely need to use `DATEADD(month, -1, @date)` to subtract a month from your date values at some point. — Tanner, Nov 10 '14 at 16:43
yeah I pretty sure that is part of what I need to use I just not able to get the code and placement correct. — Taun Brown, Nov 10 '14 at 17:15
You also should look at using ANSI-92 style joins instead of the ANSI-89 style posted here. The style you are using is more difficult to read and much more prone to error. Also, is Creation_dt not a datetime? Not sure why you are converting your parameters to a datetime in order to compare against that value. — Sean Lange, Nov 10 '14 at 17:26
Sean Lang-Thanks for the tip the code posted is what the original coder left me to work with. I am not that great at re-writing code try so not confident in my abilities to re-write the joins. And that is a date_time field and a not sure why the original coder put that in. — Taun Brown, Nov 10 '14 at 17:29

DBNull · Accepted Answer · 2014-11-11T01:49:22.563

Start off by declaring and initializing a start date for the previous month.

DECLARE @PrevStart datetime 
SELECT @PrevStart = dateadd(month, -1, @Start)

In your WHERE clause substitute the previous start date for start date so that you include last month's data as well as this month's.

and edi.Creation_dt >= convert(varchar,@PrevStart,112)
and edi.Creation_dt <= convert(varchar,@End,112)

Then you filter last month's data from this month's using CASE statement logic.

  , max( case       when la.class_ty_cd = 'COMM' and th.ppa_in = 'N' and th.trans_sub_ty_cd = 'FEES'
                        AND edi.Creation_dt >= convert(varchar,@Start,112) then th.trans_qty 
                    when la.class_ty_cd = 'MUNI' and th.ppa_in = 'N' and th.trans_sub_ty_cd = 'EXCS'
                        AND edi.Creation_dt >= convert(varchar,@Start,112) then th.trans_qty
                    when la.class_ty_cd <> 'COMM' and th.ppa_in = 'N' and th.trans_sub_ty_cd = 'BASE'
                        AND edi.Creation_dt >= convert(varchar,@Start,112) then th.trans_qty
                    else 0 end) as Volume

  , sum(CASE WHEN edi.Creation_dt >= convert(varchar,@Start,112) THEN th.trans_am ELSE 0 END) as Total

  , max( case       when la.class_ty_cd = 'COMM' and th.ppa_in = 'N' and th.trans_sub_ty_cd = 'FEES'
                        AND edi.Creation_dt < convert(varchar,@Start,112) then th.trans_qty 
                    when la.class_ty_cd = 'MUNI' and th.ppa_in = 'N' and th.trans_sub_ty_cd = 'EXCS'
                        AND edi.Creation_dt < convert(varchar,@Start,112) then th.trans_qty
                    when la.class_ty_cd <> 'COMM' and th.ppa_in = 'N' and th.trans_sub_ty_cd = 'BASE'
                        AND edi.Creation_dt < convert(varchar,@Start,112) then th.trans_qty
                    else 0 end) as PrevVolume

  , sum(CASE WHEN edi.Creation_dt < convert(varchar,@Start,112) THEN th.trans_am ELSE 0 END) as PrevTotal

I Thank you very much for this. sadly it's not what I needed. what I was trying to get is the 2 fields you added but to show the values for both on 1 line. Right now it just add a new line for the other date range and does not give a side bu side compare. If you or anyone can help with that I would be most greatful — Taun Brown, Nov 11 '14 at 14:13
Did you try it? Pretty sure that would give you two additional columns on the same line. I do something like that all the time. — DBNull, Nov 11 '14 at 14:39
oh I did try it and it does give me the two additional columns but there is not data in them. It does give me additional lines of data for each account with and they have the values in the 2 additional columns — Taun Brown, Nov 11 '14 at 15:18
You're right. They will be on separate lines because they are separate transactions. You are GROUPing the transactions on fields that prevent you from summarizing them into one line. For instance, grouping on edi.Creation_dt ensures that every unique Creation_dt gets it's own line in your result set. Look at the two lines that you wish were one line and check the values for each field in your GROUP BY clause for differences. When you remove these differences from your grouping (and SELECT list) they should merge into one line. — DBNull, Nov 11 '14 at 17:36
Is there a way to edit this for -1 year if I just change the parameter I get all of the last years data. when I only want the data for the '@Start' month -1 year not all the last year up till the '@End'. — Taun Brown, Nov 11 '14 at 20:08

Can someone show me how to add these fields to this Stored Procedure

1 Answers1