Date diff function in SQL Server stored procedure with multiple requests included in the stored procedure.

Couple of points,
First, be careful with datediff, this may be what you want, but just be sure.  The difference between 8-27-2003 23:59:59.997 and 8-28-2003 00:00:00.003 is 1 day.  
This probably doesn't matter, but you should be aware of it.

Second, when you are looking for the records where bdate is yesterday, this won't work:

     (datediff(day, bdate, getdate())  = -1 ,  
you should use:
     (datediff(day, bdate, getdate())  = 1

For next two days, you can use:

     (datediff(day, bdate, getdate())  between -2 and 0

For multi days, something like this:

(bdate <> edate  AND getdate between dateadd(d, -2 bdate) and dateadd(d, 1, edate))


Paul

Thanks for your response, Paul I will really apprecite it.
I am getting error for the last Multi day functionality
When Try to put this line getdate between dateadd(d, -2 bdate) and dateadd(d, 1, edate))
Getting AS - Error-207 Invalid Cloumn name 'getdate', Invalid Coulmn name 'getdate'

Did you meant bdate or edate, I don't know I am getting this error,
Please advice, I will really appreciate your help, all others worked fine

And I wanted to ask for the  requests (within last 14 days) and (current day) is this syntax right you didn't comment on that
-For last 14 days
(datediff(day,bdate, getdate())  > 14
and ((cha.subscribersemailsent is null) or (cha.subscribersemailsent =0)))
-For from current day
(changecontrol.bdate = getdate()
and ((cha.subscribersemailsent is null) or (cha.subscribersemailsent =0) or(cha.subscribersemailsent =1))
Please let me know

For the error, add the parenthesis to getdate()
Last 14 days should be (datediff(day,bdate, getdate())  between 0 and 14
Today should be (datediff(day,bdate, getdate())  = 0

Paul for Multi day functionality
I am getting record only which is 1 day after after edate, please correct the statement , if you can...........
May be I am wrong, But I checked multiple times changing the data, Can you please verify one more time, I think it is not adding dates properly.

Plase help me
I appreciate your help.
Thanks
Pallilu

-- Ok, lets step back a bit, that code's not right.
-- I am going to give you examples of dates and you have to tell me which ones
-- you want and which you don't (and why)

1. bdate = 8-10-2003  edate = 8-11-2003
2. bdate = 8-10-2003  edate = 8-20-2003
3. bdate = 8-20-2003  edate = 8-21-2003
4. bdate = 8-28-2003  edate = 8-29-2003
5. bdate = 8-28-2003  edate = 8-30-2003
6. bdate = 8-29-2003  edate = 8-30-2003


See if you can formulate in english which dates should be included and which should not.  Thats the hard part.

Paul

1. bdate = 8-10-2003  edate = 8-11-2003
2. bdate = 8-10-2003  edate = 8-20-2003
3. bdate = 8-20-2003  edate = 8-21-2003
4. bdate = 8-28-2003  edate = 8-29-2003
5. bdate = 8-28-2003  edate = 8-30-2003
6. bdate = 8-29-2003  edate = 8-30-2003
7. bdate = 8-30-2003  edate = 9-10-2003

-- I think you want 2, 3, 4, 5, and 6.  If so, then try this:
DROP TABLE testdate
CREATE TABLE testdate (bdate datetime, edate datetime)

INSERT INTO testdate VALUES ('08-10-2003', '08-11-2003')
INSERT INTO testdate VALUES ('08-10-2003', '08-20-2003')
INSERT INTO testdate VALUES ('08-20-2003', '09-21-2003')
INSERT INTO testdate VALUES ('08-28-2003', '08-29-2003')
INSERT INTO testdate VALUES ('08-28-2003', '08-30-2003')
INSERT INTO testdate VALUES ('08-29-2003', '08-30-2003')
INSERT INTO testdate VALUES ('08-30-2003', '09-10-2003')

SELECT       *
FROM       testdate
WHERE       bdate <> edate AND
            ((dateadd(d, -14, getdate()) BETWEEN bdate AND edate)  OR
             (bdate BETWEEN dateadd(d, -14, getdate()) AND dateadd(d, 1, getdate())  OR
             (edate BETWEEN dateadd(d, -14, getdate()) AND dateadd(d, 1, getdate()) )

Paul,
If the query run on 8/28/03 here is the what would be included

1. badate=8-10-2003 edate =8-11-2003  No  Edate<1 Day Before
2. badate=8-10-2003 edate =8-20-2003  No  Edate<1 Day Before
3. badate=8-20-2003 edate =8-21-2003  No  Edate<1 Day Before
4. badate=8-28-2003 edate =8-29-2003  Yes  Bdate= ToDay
5. badate=8-28-2003 edate =8-30-2003  Yes  Bdate= ToDay
6. badate=8-29-2003 edate =8-30-2003  Yes  Bdate 1 Day >Today

Please let me know, What do you think
Thanks for being so pateint and working with me, Today I am taking off. I will work with you defnitley When I come back on tuesday.
I really appreciate your help paul

Actaully The logic is cosidered like this
Multi-day Events(Multi-Day is determined when bdate and edate are not equal) - All requests will be included “2” days before beginning date through  “1” day after the ending date (with “subscribers_email_sent=0” OR With “subscribers_email_sent=1”)

Paul,
 I can work with you today,
 Please provide me the example so that I can execute and tell exactly which ones I want and which ones I Don't . That will help me understand better for both of us.
The multi-day logic, I should include records which are
All requests will be included “2” days before beginning date through  “1” day after the ending date (with “subscribers_email_sent=0” OR With “subscribers_email_sent=1”)

I did not understand this statement you provided on last posted message, why are you comparing with 14 days
SELECT      *
FROM      testdate
WHERE      bdate <> edate AND
          ((dateadd(d, -14, getdate()) BETWEEN bdate AND edate)  OR
           (bdate BETWEEN dateadd(d, -14, getdate()) AND dateadd(d, 1, getdate())  OR
          (edate BETWEEN dateadd(d, -14, getdate()) AND dateadd(d, 1, getdate()) )


Please help me understand better.we can wrap this up and I can give points for your help.
Thanks for your help
Pallilu

Ok, sorry about the mixup on the days, lets go with this and see where we get:

-- Requirements
-- 1. All requests which were not sent within last 14 days (with“subscribers_email_sent=0”)
-- 2. All requests from prior day (with “subscribers_email_sent=0” OR with “subscribers_email_sent=1”)
-- 3. All requests from current day (with “subscribers_email_sent=0” OR with “subscribers_email_sent=1”)
-- 4. All requests for next 2 days (with “subscribers_email_sent=0” OR With “subscribers_email_sent=1”)
-- 5. Events when bdate and edate are not equal - and its 2 days before beginning date through 1 day after the end date (with “subscribers_email_sent=0” OR With “subscribers_email_sent=1”)


SELECT       *
FROM       changecontrol cha
WHERE
      -- Last 14 days, email sent null or 0
      (DATEDIFF(d, bdate, GETDATE()) BETWEEN  0 AND 14  AND
      ((cha.subscribersemailsent IS NULL) OR (cha.subscribersemailsent = 0)))
      OR
      -- Yesterday and email sent = 0 or 1
      (DATEDIFF(d, bdate, GETDATE())  = 1 AND
      (cha.subscribersemailsent IN (0,1)))
      OR
      -- Today and email sent = 0 or 1
      (DATEDIFF(d, bdate, GETDATE())  = 0 AND
      ((cha.subscribersemailsent IN (0,1)))
      OR
      -- Next 2 days and email sent = 0 or 1
      (DATEDIFF(DAY, bdate, GETDATE()) BETWEEN -2 AND 0 AND
      ((cha.subscribersemailsent IN (0,1)))
      OR
      -- Bdate is 2 days ago thru edate 1 day in future and email sent = 0 or 1
      (GETDATE() BETWEEN DATEADD(d, -2, bdate) AND DATEADD(d, 1, edate) AND
      ((cha.subscribersemailsent IN (0,1)))

Paul

Paul, For last one multiday I am confused, I am getting records which are not 2 days old, please investigate
Everything else worked fine, Give me some example so that we can try and confirm we are on the same page.

Thanks for tour help
Can I write update statement in the same stored procedure to set "subscribersemailsent = 1" after email has sent to subscribers.Please Advice

what is the end date on the ones that you are getting back?

1.bdate 08/31/23303 edate - 09/21/2003
2.bdate - 08/01/2003 edate - 09/13/2003
3.bdate - 07/29/2003 edate - 09/04/2003
 I am getting these records, please advice, I thought i should not get 2 and 3

Please help me paul
Thanks Pallilu