June 18, 2014 at 12:55 pm
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. Is there a way without using parameters?
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'
June 18, 2014 at 1:13 pm
Take a look at the DATEADD function.
After you understand how does it work, you could go further by using this date routines: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
June 18, 2014 at 1:31 pm
Thank you Luis, I managed to update it but is there a way for me to not put a parameter in the Stored procedure. Because by "Begin" SQL is stating I should declare something for the sp can be created
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 dateadd(dd, -14, cast(getDate() as date)) and cast(getDate() as date)
END
exec spAdminFundedDateee
June 18, 2014 at 1:43 pm
swaseem345 (6/18/2014)
Thank you Luis, I managed to update it but is there a way for me to not put a parameter in the Stored procedure. Because by "Begin" SQL is stating I should declare something for the sp can be created
You are missing the as
Create PROC [dbo].[spAdminFundedDateee]
AS --needed
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 dateadd(dd, -14, cast(getDate() as date)) and cast(getDate() as date)
END
exec spAdminFundedDateee
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 18, 2014 at 1:48 pm
Also make sure you have a GO after your procedure code or the procedure will end up trying to call itself.
Create PROC [dbo].[spAdminFundedDateee]
AS --needed
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 dateadd(dd, -14, cast(getDate() as date)) and cast(getDate() as date)
END
GO
exec spAdminFundedDateee
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2014 at 1:50 pm
So I am running this query, Thanks Sqlrnnr.
But I am getting no result. Is there something wrong in my updated query?
Create 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 dateadd(dd, -14, cast(getDate() as date)) and cast(getDate() as date)
END
GO
exec spAdminFundedDateee
June 18, 2014 at 2:02 pm
Sean Lange (6/18/2014)
Also make sure you have a GO after your procedure code or the procedure will end up trying to call itself.
Create PROC [dbo].[spAdminFundedDateee]
AS --needed
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 dateadd(dd, -14, cast(getDate() as date)) and cast(getDate() as date)
END
GO
exec spAdminFundedDateee
baha - good catch
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply