CODEWITHSUNDEEP
sql-serverdatetime
Vinay Sinha
Vinay Sinha

Reputation: 323

Select record over date range in SQL Server

I have a table which holds Rate of resource having UserId, Effective Date(datatype Date) & End Date(datatype Date)

RateId UserId Rate     EffectiveDate          EndDate
============================================================================
21  169 82  2015-01-01 00:00:00.000 2025-12-30 00:00:00.000
23  78  58  2015-07-01 00:00:00.000 2015-07-14 00:00:00.000
24  88  68  2015-07-01 00:00:00.000 2025-12-30 00:00:00.000
25  114 78  2015-07-01 00:00:00.000 2025-12-30 00:00:00.000
26  218 56  2015-07-01 00:00:00.000 2025-12-30 00:00:00.000
27  78  89  2015-07-15 00:00:00.000 2025-12-30 00:00:00.000
28  174 69  2015-01-01 00:00:00.000 2015-08-14 00:00:00.000
29  174 59  2015-08-15 00:00:00.000 2025-12-30 00:00:00.000
30  235 65  2015-01-01 00:00:00.000 2015-08-14 00:00:00.000
31  235 84  2015-08-15 00:00:00.000 2025-12-30 00:00:00.000
32  234 49  2015-08-01 00:00:00.000 2015-08-14 00:00:00.000
33  234 59  2015-08-15 00:00:00.000 2025-12-30 00:00:00.000
34  303 89  2014-10-01 00:00:00.000 2015-08-14 00:00:00.000
35  303 75  2015-08-15 00:00:00.000 2025-12-30 00:00:00.000
36  481 84  2015-01-01 00:00:00.000 2025-12-30 00:00:00.000

I need to select rates for those user only whose rate falls only range of the current month(between Start Date & End date of month).

This is what i tried up to now.

DECLARE @MONTH INT
DECLARE @YEAR INT
DECLARE @STARTDATEOFMONTH DATE
DECLARE @LASTDATEOFMONTH DATE
SET @MONTH = 8 /*Current Month*/
SET @YEAR = 2015 /*Current Year*/
SET @STARTDATEOFMONTH = DATEADD(MONTH,@MONTH-1,DATEADD(YEAR,@YEAR-1900,0)) /*FIRST*/
SET @LASTDATEOFMONTH = DATEADD(DAY,-1,DATEADD(MONTH,@MONTH,DATEADD(YEAR,@YEAR-1900,0))) /*LAST*/

SELECT * FROM TRNS_RATE_DETAILS
  WHERE  @STARTDATEOFMONTH >= EFFECTIVEDATE 
  AND @LASTDATEOFMONTH <= ENDDATE

I am calculating Start Date and End Date of every month (say in the above query i have passed Current month=8 & year=2015)

Query output is 

RateId  UserId  Rate       EffectiveDate          EndDate
============================================================================
21  169 82  2015-01-01 00:00:00.000 2025-12-30 00:00:00.000
24  88  68  2015-07-01 00:00:00.000 2025-12-30 00:00:00.000
25  114 78  2015-07-01 00:00:00.000 2025-12-30 00:00:00.000
26  218 56  2015-07-01 00:00:00.000 2025-12-30 00:00:00.000
27  78  89  2015-07-15 00:00:00.000 2025-12-30 00:00:00.000
36  481 84  2015-01-01 00:00:00.000 2025-12-30 00:00:00.000

As You can see i can get only those record which comes before Start Date of current Month & after Last date of current month.

I need to get those records whose Effective Date & End Date falls between Start Date & End Date of every months.

Say, I have Rate = 45 for Resource(UserId) for Date between 1-Jan-2015(Effective Date) to 14th Aug(End Date) and another Rate = 75 for Date between 15-Aug-2015(Effective Date) to 30th Dec 2025(End Date)

So i actually want both Rates(45 & 75) for the same resource for the Month of August, 2015?

Please help me out!

Thanks!

Upvotes: 1

Views: 549

Answers (3)

Sam CD
Sam CD

Reputation: 2097

If you just want to use the current month, you can use DATEPART and GETDATE:

SELECT * FROM TRNS_RATE_DETAILS
  WHERE  
  (
   (DATEPART(month,EFFECTIVEDATE) <= DATEPART(month,GETDATE())
   AND DATEPART(year,EFFECTIVEDATE) = DATEPART(year,GETDATE()))
   OR
   DATEPART(year,EFFECTIVEDATE) < DATEPART(year,GETDATE())
   )
  AND 
  (
   (DATEPART(month,ENDDATE) >= DATEPART(month,GETDATE())
   AND DATEPART(year,ENDDATE) = DATEPART(year,GETDATE()))
   OR
   DATEPART(year,ENDDATE) > DATEPART(year,GETDATE())
  )

Upvotes: 1

vipin
vipin

Reputation: 367

Maybe this will help you:

SELECT * 
FROM    TRNS_RATE_DETAILS
WHERE   (MONTH(EFFECTIVEDATE)   =   @MONTH  AND YEAR(EFFECTIVEDATE) =   @YEAR)
OR      (MONTH(ENDDATE)         =   @MONTH  AND YEAR(ENDDATE)       =   @YEAR)

Upvotes: 0

Jkike
Jkike

Reputation: 805

You want EFFECTIVEDATE between startdate and lastdate... but you are actually ask the db for record having startdate greater than effective date.... which is the opposite that you want.

try:

SELECT * FROM TRNS_RATE_DETAILS
  WHERE EFFECTIVEDATE >= @STARTDATEOFMONTH 
AND ENDDATE <=@LASTDATEOFMONTH

Btw, way don't you just check for the month using the extract function?

Upvotes: 0

Related Questions