Stef Merlijn
asked on
How to set up SQL with dateranges
Hi,
In my SQL Server 2008 DB I have two tables: Customers and Invoices
What I need retrieve is the following:
Based on two given dates (f.e. month or quarter) select:
- All Customers having DateCreated max one year before the given enddate
- All invoices for the selected Customers having InvoiceDate max one year after DateCreated of Customer and also InvoiceDate between the two given dates
In my SQL Server 2008 DB I have two tables: Customers and Invoices
What I need retrieve is the following:
Based on two given dates (f.e. month or quarter) select:
- All Customers having DateCreated max one year before the given enddate
- All invoices for the selected Customers having InvoiceDate max one year after DateCreated of Customer and also InvoiceDate between the two given dates
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much.
hey codes above are good..
then you can put it in function and procs.. something lke this.
then you can put it in function and procs.. something lke this.
create FUNCTION dbo.fn_vw_test(@FirstGivenDate datetime,@SecondGivenDate datetime)
RETURNS TABLE
AS
RETURN
(
SELECT inv.*,cust.*
FROM Invoices inv INNER JOIN Customers cust
WHERE cust.DateCreated > DATEADD(yy , 1, cust.given_enddate)
AND
(
( inv.InvoiceDate > DATEADD(yy , 1, cust.DateCreated))
or
(inv.InvoiceDate between @FirstGivenDate and @SecondGivenDate)
)
);
GO
create procedure sp_rptInvCustBy
@FirstGivenDate varchar(23),@SecondGivenDate varchar(23)
as
begin
declate @FirstDate datetime,@SecondDate datetime
set @FirstDate = convert(varchar(23), convert(datetime, @FirstGivenDate ), 25)
set @SecondDate = convert(varchar(23), convert(datetime, @SecondGivenDate ), 25)
SELECT * FROM dbo.fn_InvCustOnDate(@FirstDate,@SecondDate)
end
go
exec sp_rptInvCustBy '01/31/2009 12:00:01','01/31/2010 12:00:01'
go
cheers.. ;)
ASKER
CoolBurn28:
Thanks for the addition.
Thanks for the addition.
Open in new window