Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Posts
    4

    Unanswered: How to find Last Month

    I have a report that uses a date parameter field and I want to change this to pull the previous months records instead of using the date parameter. I know I have to declare and set this somewhere somehow. Any ideas??? The last line of code contains the field I need to change(apply_dte). I am using sql server 2005.

    I am using the following SQL:

    declare @BillingAfterTerm table (
    account_no varchar(12)
    ,bill_amount money
    ,billing_sales_tax money
    ,billing_enter_date datetime
    ,account_term_date datetime
    ,billing_note varchar(255)
    ,account_term_type_code smallint
    ,term_type_name varchar(50)
    ,term_type_code smallint
    ,billing_pmtsub_dist_code tinyint
    ,billing_adj_ind tinyint
    ,billing_post_date datetime
    ,portfolio_id tinyint
    )

    insert into @BillingAfterTerm
    SELECT "billing"."account_no", "billing"."bill_amt", "billing"."sales_tax_amt", "billing"."enter_dte",
    "account_termination"."term_dte", "billing_note"."note", "account_termination"."term_type_cde", "termination_type"."term_type_nme",
    "termination_type"."term_type_cde", "billing"."pmtsub_dist_cde", "billing"."adj_ind", "billing"."post_dte", "account"."portfolio_id"
    FROM ((("BMWInfoStore"."dbo"."account" "account" INNER JOIN
    "BMWInfoStore"."dbo"."billing" "billing" ON "account"."account_no"="billing"."account_no") INNER JOIN
    "BMWInfoStore"."dbo"."account_termination" "account_termination" ON "account"."account_no"="account_termination"."acco unt_no") INNER JOIN
    "BMWInfoStore"."dbo"."billing_note" "billing_note" ON "billing"."bill_tran_id"="billing_note"."bill_tran _id") INNER JOIN
    "BMWInfoStore"."dbo"."termination_type" "termination_type" ON "account_termination"."term_type_cde"="termination _type"."term_type_cde"
    WHERE ("billing"."pmtsub_dist_cde"=67 or "billing"."pmtsub_dist_cde"=222) AND "billing"."adj_ind"=0 AND "billing"."post_dte">"account_termination"."term_d te"


    SELECT "payment"."account_no", "payment"."apply_dte", "payment"."pmt_amt", "payment"."pmt_type_cde", "payment"."adj_ind",
    BAT.bill_amount, BAT.billing_sales_tax, BAT.billing_enter_date, BAT.account_term_date, BAT.billing_note, BAT.account_term_type_code,
    BAT.term_type_name, BAT.term_type_code, BAT.billing_pmtsub_dist_code, BAT.billing_adj_ind, BAT.billing_post_date, BAT.portfolio_id
    FROM "BMWInfoStore"."dbo"."payment" "payment" inner join
    @BillingAfterTerm BAT on BAT.account_no = payment.account_no
    WHERE "payment"."pmt_type_cde" in (17,102,103,104) AND "payment"."adj_ind"=0 and
    apply_dte >= {?EnterDate} AND apply_dte <= {?EndDate}+1

    Thanks to all!!
    Last edited by Bruce DiPaola; 04-12-10 at 16:41.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --
    Code:
    AND apply_dte >= {?EnterDate} 
    AND apply_dte <= {?EndDate}+1
    to this --
    Code:
    AND apply_dte >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) 
    AND apply_dte  < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    4
    Yes that works and much easier.
    I will have to check out your book too!!

  4. #4
    Join Date
    Apr 2010
    Posts
    4
    Can this code be modified to pull the entire year of 2010 instead of just the last full month?

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you could look at the syntax diagram for the DATEADD function.
    Dave

  6. #6
    Join Date
    Apr 2010
    Posts
    4
    Yes,

    I think I have it now. Just wanted to compare.

    Cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •