[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

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
0
Stef Merlijn
Asked:
Stef Merlijn
1 Solution
 
Kobe_LenjouCommented:
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
 
Ephraim WangoyaCommented:

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
 
Stef MerlijnDeveloperAuthor Commented:
Thank you very much.
0
 
CoolBurn28Commented:
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
 
Stef MerlijnDeveloperAuthor Commented:
CoolBurn28:
Thanks for the addition.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now