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

pallilu
pallilu used Ask the Experts™
on

Can any one of you guys help me with the datediff functions. I started out like this and for yesterdays functions Shall we use any called "yesterday" function please help me, Here is the request I need to put in one stored procedure With OR in the middle (Ie records should include all this requests in the results. ANd I really don't have an idea for last next 2 days and multi day request.

-All requests which were not sent within last 14 days (with“subscribers_email_sent=0”)
- All requests from prior day (with “subscribers_email_sent=0” OR with “subscribers_email_sent=1”)
- All requests from current day (with “subscribers_email_sent=0” OR with “subscribers_email_sent=1”)
- All requests for next 2 days (with “subscribers_email_sent=0” OR With “subscribers_email_sent=1”)
- 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”)

CREATE PROCEDURE ccsp_subscribers_emailer AS
select * from changecontrol cha
Where
(datediff(day,bdate, getdate())  > 14
and ((cha.subscribersemailsent is null) or (cha.subscribersemailsent =0)))
OR
(datediff(day,bdate, getdate())  = -1
and ((cha.subscribersemailsent is null) or (cha.subscribersemailsent =0)))
OR
(changecontrol.bdate = getdate()
and ((cha.subscribersemailsent is null) or (cha.subscribersemailsent =0) or(cha.subscribersemailsent is null)))

GO

I will really appreciate your help
Thanks pallilu

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:

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()) )




Author

Commented:
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”)

Author

Commented:
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

Author

Commented:
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?

Author

Commented:
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
I meant to ask you about these.  I figured that you would want these.  If you are sure that you don't want them, then we need to change things a bit...

-- Bdate is 2 days ago OR edate 1 day in future and email sent = 0 or 1
     (((DATEDIFF(DAY, bdate, GETDATE()) BETWEEN  0 AND 2)  OR
       (DATEDIFF(DAY, edate, GETDATE()) BETWEEN -1 AND 0)) AND
     ((cha.subscribersemailsent IN (0,1)))

Try this.  And check my parenthesis.  I want to OR the two date comparisons and then AND those with the email sent statement.

Paul

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial