Solved

How to set up SQL with dateranges

Posted on 2011-03-22
5
177 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Delphiwizard
5 Comments
 
LVL 8

Expert Comment

by:Kobe_Lenjou
ID: 35189680
Something like this?


select * from customers where DateCreated > dateadd(Y, -1, @GivenEndDate)

select * from Invoices Inv
inner join Customers Cus
On cus.id = inv.customerID
where Cus.DateCreated > dateadd(Y, -1, @GivenEndDate)
and Inv.Invoicedate < dateadd (Y, 1, Cus.CreateDate)

Open in new window

0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35189691

1.
select * from customers
where datecreated > (DATEADD(YY, -1, GivenDate)

2.
select *
from customers
inner join invoice on customers.customerid = invoice.customerid
where invoicedate > DATEADD(YY, 1, Customers.CreatedDate)
and invoicedate between FirstGivenDate and LastGivenDate
0
 

Author Closing Comment

by:Delphiwizard
ID: 35190497
Thank you very much.
0
 
LVL 2

Expert Comment

by:CoolBurn28
ID: 35190814
hey codes above are good..
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

Open in new window

cheers.. ;)
0
 

Author Comment

by:Delphiwizard
ID: 35191820
CoolBurn28:
Thanks for the addition.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question