SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 using DATEDIFF instead DATEPART
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 01/02/2014 :  16:53:30  Show Profile  Reply with Quote
Hi,

Am using sqlserver 2008 R2

Below is my current Query


select sum(Amount) from Coupon_Amount where IdCoupon in(select IdCoupon from All_Offers where getdate() between OfferStartDate and OfferEndDate
   and IsActive = 1 and DATEPART(MONTH,GETDATE()) = DATEPART(MONTH,OfferEndDate) or   
   (DATEPART(MONTH,GETDATE()) < DATEPART(MONTH,OfferEndDate) and 
    DATEPART(MONTH,GETDATE()) >= DATEPART(MONTH,OfferStartDate))
    
    and (DATEPART(YEAR,GETDATE()) = DATEPART(YEAR,OfferEndDate) or   
   (DATEPART(YEAR,GETDATE()) = DATEPART(YEAR,OfferEndDate) and 
    DATEPART(YEAR,GETDATE()) >= DATEPART(YEAR,OfferStartDate)))
   ) group by IsActive having IsActive = 1


This query works perfectly. Can this query be written using Datediff instead of DatePart? If yes please rewrite on my query

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 01/02/2014 :  18:35:39  Show Profile  Reply with Quote
Here is the test data and requirement


with All_Offers as (

select 101 as IdCoupon,'2014-01-14 13:30:00.000' as OfferStartDate,'2014-02-14 13:30:00.000' as OfferEndDate union all
select 102 as IdCoupon,'2013-12-19 13:30:00.000' as OfferStartDate,'2014-01-12 13:30:00.000' as OfferEndDate union all
select 103 as IdCoupon,'2013-12-14 13:30:00.000' as OfferStartDate,'2014-01-20 13:30:00.000' as OfferEndDate union all
select 104 as IdCoupon,'2014-01-01 13:30:00.000' as OfferStartDate,'2014-01-01 13:30:00.000' as OfferEndDate union all
select 105 as IdCoupon,'2013-06-14 13:30:00.000' as OfferStartDate,'2013-06-14 13:30:00.000' as OfferEndDate )


with Coupon_Amount as (
select 101 as IdCoupon, 2.00 as Amount union all
select 102 as IdCoupon, 20.00 as Amount union all
select 103 as IdCoupon, 100.00 as Amount union all
select 104 as IdCoupon, 29.00 as Amount union all
select 105 as IdCoupon, 40.00 as Amount)



if you run my query the expected output will be 120 (second and third row data)


Logic will be as follows,

I want to get the coupon based on the corresponding month dates and i should only consider the valid offer( not expired offer and futured)
the very first record in offer table talks about future offer which doesn't falls in future date. so need not to consider
the second and third row data offers valid till 12th and 20th corresponding so
the fourth row data offer ends yesterday which means expired. so need not to consider
the fifth row data offer was old and expired. so so need not to consider

i know without giving proper test data it is hard to provide solution.

Anyone can please provide me the alternate query for this.

Edited by - sqllover on 01/02/2014 18:39:52
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 01/02/2014 :  23:18:06  Show Profile  Reply with Quote
SELECT	SUM(Amount)
FROM	All_Offers o
	INNER JOIN Coupon_Amount c	ON	o.IdCoupon	= c.IdCoupon
WHERE	o.IsActive	= 1
AND	c.IsActive	= 1
AND	OfferStartDate	<= GETDATE()
AND	OfferEndDate	>= GETDATE()



KH
Time is always against us

Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 01/03/2014 :  08:17:04  Show Profile  Reply with Quote
Hi Khtan,

Perfect. your query works great. thank you.

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 01/03/2014 :  15:42:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
AND GETDATE() BETWEEN OfferStartDate AND OfferEndDate;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 01/03/2014 :  18:17:29  Show Profile  Reply with Quote
i did add the condition. Thanks everyone for this help
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/05/2014 :  06:35:55  Show Profile  Reply with Quote

AND	GETDATE() BETWEEN OfferStartDate AND OfferEndDate

I'm always nervous about the edge-condition of the value (GetDate()) matching the terminal value (OfferEndDate).

More often it seems better to have

GetDate() < OfferEndDate + "Some small offset"

e.g.

GetDate() < Tomorrow

Rather than

GetDate() <= Just-before-midnight
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 01/06/2014 :  15:52:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I agree with you, but in this case the original business logic says <= and >=.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000