Solved

How to set up SQL with dateranges

Posted on 2011-03-22
5
178 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql how to count case when 4 26
sql 2016 Integration Service connecting to 2012 3 32
Stored Proc - Rewrite 42 60
SQL Recursion 6 20
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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

828 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