Calculating 2 weeks for every week SQL

Question

I'm still new to SQL so this question I am asking may be easy for you. So I am creating a report that for every week generates the prior 14 days (Or 2 weeks) of the funded contracts. I know this has to be Hardcoded to a specific company. The ID for that specific company is '55' So can someone help me with this function? My query I know is not yet finished I am just stuck on how to enter the Date function for this.

Create PROC [dbo].[spAdminFundedDateee]

   Begin

        SELECT  c.program_id, d.dealer_code,b.last_name As DealerName, a.account_no, a.vin, 
                ((e.last_name)+','+(e.first_name)) As Name, a.funded_date, a.cancel_refund_date,
                  a.purchase_date,a.miles, a.duration,a.sale_price,a.number_of_payments,
                  a.sales_tax, a.downpayment

        from tDealer d 
    Join tContact b ON d.contact_id = b.contact_id 
    Join tContract a On d.dealer_id = a.dealer_id 
    Join tCompany c ON d.company_id= c.company_id
    Join tContact E On e.contact_id = a.contact_id

        Where c.program_id = 55 And a.funded_date between 

      End

    exec spAdminFundedDateee '05/1/2014','05/30/2014','55'

Read the SQL Server documentation on stored procedures, particularly the section on passing parameters. In this case, you could pass the parameters `@ProgramID` and `@FundedDate`, and calculate the starting period date from the funded date parameter. — Ken White, Jun 18 '14 at 18:52
First off all you would need to specify your parameters to the SP. and then you could just do a between [@BeginDate] and [@EndDate]. — TMNT2014, Jun 18 '14 at 18:53
I was going to do this but my boss wants it without parameters. I have to have it Hardcoded. Also its going to become a subscription — user3753188, Jun 18 '14 at 18:58

score 0 · Answer 1 · answered Jun 18 '14 at 19:08

0

if a.funded_date is a DATETIME then

a.funded_date between dateadd(day,-14,getdate()) and getdate()

if a.funded_date is a DATE then

 a.funded_date between cast(dateadd(day,-14,getdate()) as date) and cast(getdate() as date)

answered Jun 18 '14 at 19:08

Will

104
3

This is with the assumption, that the prior 14 days of the funded contracts implies: 14 days prior to today. – Will Jun 18 '14 at 19:12

score 0 · Accepted Answer · answered Jun 18 '14 at 19:13

In order to check if a.funded_date is between today's date and two weeks ago, you are going to need several sql server functions. The first one is GetDate(). This returns the current date and time as a datetime value.

Now, you want to check only for the date parameter (not the time). If someone runs your stored procedure at 1pm, you don't want to eliminate all of the data from before 1pm from the day 14 days ago. You want all the data, no matter the time, beginning from 14 days ago. To solve this issue, we want to change getDate() to a date only. so, cast(getDate() as date). Today, this would return 6-18-14.

Lastly, you want to check for the date two weeks ago. dateAdd allows you to add any amount of time that you specify to a date or a time. in this case, you want the information from 14 days ago. this is going to look like dateadd(dd, -14, cast(getDate() as date)).

Since between is inclusive, all you need to do now is put it together!

between dateadd(dd, -14, cast(getDate() as date)) and cast(getDate() as date)

So I tried your codec. I put all of it together but I get no result when I execute. Is there something wrong in my sp? — user3753188, Jun 18 '14 at 19:46
@user3753188 do you get an empty table or do you get an error? if you are getting an empty table, you should try to look at the information stored in your tables and find data points that this query should pull back. — Jenn, Jun 18 '14 at 19:50

score 0 · Answer 3 · answered Jun 19 '14 at 14:10

Hey Guys so I updated my Where clause but when I run the query I get the column header but with an empty table. When I entered the hardcode of the dates is it correct? I am still new to the Date function so I know it must be common sense for most of you than for me. I am suppose to Hardcode the dates prior 14 days and suppose to get the funded contracts every Monday prior the 14 days every 2 weeks. This is my first report so I apologize for asking some questions that some of you may think I should know. I am still learning.

      Alter PROC [dbo].[spAdminFundedDateee]
      As
         Begin
             SELECT  c.program_id, d.dealer_code,b.last_name As DealerName, a.account_no, 
                     a.vin,  ((e.last_name)+','+(e.first_name)) As Name, 
                     a.funded_date, a.cancel_refund_date, a.purchase_date,a.miles, 
                     a.duration,a.sale_price,a.number_of_payments,  a.sales_tax, a.downpayment


      from tDealer d 
       Join tContact b ON d.contact_id = b.contact_id 
       Join tContract a On d.dealer_id = a.dealer_id 
       Join tCompany c ON d.company_id= c.company_id
       Join tContact E On e.contact_id = a.contact_id


     Where c.program_id = 55 And  a.funded_date between '05/19/2014' and '06/02/2014'And 
           a.funded_date between  dateadd(dd, -14, cast(getDate() as date)) and cast(getDate()
           as date)


     END
     GO

    EXEC spAdminFundedDateee

Calculating 2 weeks for every week SQL

3 Answers3