SQL select experationdate field by userid where

smares32371
smares32371 used Ask the Experts™
on
I want to build a query that returns the experationdate by userid. Where it should only return a record once 31-40 days prior to the experationdate and 16-30 days prior to experationdate and 11-15 days and also daily during the 10 days prior to the experationdate
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
smares32371,

Please post some sample input, and the expected output based on that sample.

Patrick
dsackerContract ERP Admin/Consultant

Commented:
You can change the column and table names to whatever your real column and table names are.

select UserID,
       ExpirationDate,
       case
           when datediff(d, ExpirationDate, GetDate()) between 31 and 40 then 'Y'
       end as [31-40],
       case
           when datediff(d, ExpirationDate, GetDate()) between 16 and 30 then 'Y'
       end as [16-30],
       case
           when datediff(d, ExpirationDate, GetDate()) between 11 and 15 then 'Y'
       end as [11-15],
       case
           when datediff(d, ExpirationDate, GetDate()) < 11 datediff(d, ExpirationDate, GetDate())
       end as [Days Left]
from MyTable
where  datediff(d, ExpirationDate, GetDate()) <= 40

Open in new window

Author

Commented:
I just found out its for mysql and syntax written is for ms sql sorry.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

dsackerContract ERP Admin/Consultant

Commented:
No problem. Just change a few things out of my example.

Use CurDate() instead of GetDate()
Drop the "d" in DATEDIFF.

SQL Server:  datediff(d, ExpirationDate, GetDate())
MySQL:  datediff(ExpirationDate, CurDate())

The CASE statement should stay the same syntax (of course, you'll need to replace the datediff syntax inside it). Here's the new code.
 
select UserID,
       ExpirationDate,
       case
           when datediff(ExpirationDate, CurDate()) between 31 and 40 then 'Y'
       end as "31-40",
       case
           when datediff(ExpirationDate, CurDate()) between 16 and 30 then 'Y'
       end as "16-30",
       case
           when datediff(ExpirationDate, CurDate()) between 11 and 15 then 'Y'
       end as "11-15"],
       case
           when datediff(ExpirationDate, CurDate()) < 11 datediff(ExpirationDate, CurDate())
       end as "Days Left"
from MyTable
where  datediff(ExpirationDate, CurDate()) <= 40

Open in new window

Author

Commented:
there is an errror in the line below

      case
           when datediff(ExpirationDate, CurDate()) < 11 datediff(ExpirationDate, CurDate())
       end as "Days Left"
Contract ERP Admin/Consultant
Commented:
I left out the "then" part.
  case
           when datediff(ExpirationDate, CurDate()) < 11 THEN datediff(ExpirationDate, CurDate())
       end as "Days Left"

Open in new window

Top Expert 2011

Commented:
your requirement about once , during the date periods
needs to be expanded...

what is this for?
how are you going to control this once requirement?

you appear to be running daily (the in 10 days requirement) are you going to have separate processes scheduled to do this ?


Top Expert 2011

Commented:
please confirm mysql version
I am answering exactly what was asked - I am not modifying the request. It looks for me like e-mail system that should notify a client about debts rather than a report that shows debts in different columns.

1. "returns the experationdate by userid."

SELECT experationdate
FROM experationdates -- your table
WHERE userid = 25

2. "Where it should only return a record once 31-40 days"

a. "31-40 days" is easy:

SELECT experationdate FROM experationdates WHERE  userid = 25
AND DateDiff(d,GETDATE(),experationdate) BETWEEN 31 AND 40

b. "ONCE"

SELECT experationdate FROM experationdates -- your table WHERE userid = 25
UNION ALL
SELECT experationdate FROM experationdates -- your table WHERE userid = 25
UNION ALL
SELECT experationdate FROM experationdates -- your table WHERE userid = 25
UNION ALL

and it will return:

experationdate
2011-09-12 00:00:00.000
2011-09-12 00:00:00.000
2011-09-12 00:00:00.000

but you asked to return the record "ONCE".

How second query should know that it is second and not to return the record?
First query must flag the record that the record was already returned ONCE during "31-40" days.

We will use SELECT FROM PROCEDURE "pr_experationdate" instead of SELECT FROM TABLE.

One of possible ways:
SELECT * FROM OPENQUERY(local,'EXEC MedLab.dbo.pr_experationdate')

Why in this way? - I keep SELECT instead of going to "EXEC ...".
local is the LINKED SERVER:
Server_Objects / Linked_Servers / New / Name: local / Username: <user> / Password: <password> / <options if needed> / OK.

Now stored procedure:

CREATE PROCEDURE pr_experationdate AS
BEGIN
      SELECT experationdate FROM   experationdates WHERE  userid = 25
        AND DateDiff(d,GETDATE(),experationdate) BETWEEN 31 AND 40
END
GO

SELECT * FROM OPENQUERY(local,'EXEC MedLab.dbo.pr_experationdate')
UNION ALL
SELECT * FROM OPENQUERY(local,'EXEC MedLab.dbo.pr_experationdate')
UNION ALL
SELECT * FROM OPENQUERY(local,'EXEC MedLab.dbo.pr_experationdate')

Gives:
experationdate
2011-09-12 00:00:00.000
2011-09-12 00:00:00.000
2011-09-12 00:00:00.000

But we need ONCE.

Modifying the table:

CREATE TABLE experationdates (userid int, experationdate datetime, selected_31_40 bit DEFAULT 0)

Modifying the procedure:
ALTER PROCEDURE pr_experationdate AS
BEGIN
    SELECT experationdate FROM experationdates
    WHERE  userid = 25
    AND DateDiff(d,GETDATE(),experationdate) BETWEEN 31 AND 40
    AND selected_31_40 = 0

    UPDATE experationdates SET selected_31_40 = 1
    WHERE userid = 25
    AND DateDiff(d,GETDATE(),experationdate) BETWEEN 31 AND 40
    AND selected_31_40 = 0
   
END
GO

Now we have:
SELECT * FROM OPENQUERY(local,'EXEC MedLab.dbo.pr_experationdate')
UNION ALL
SELECT * FROM OPENQUERY(local,'EXEC MedLab.dbo.pr_experationdate')
UNION ALL
SELECT * FROM OPENQUERY(local,'EXEC MedLab.dbo.pr_experationdate')

Gives:
experationdate
2011-09-12 00:00:00.000

It is exactly what was requested.
--------------- other date intervals --------------
CREATE TABLE experationdates (userid int, experationdate datetime,
      selected_31_40 bit DEFAULT 0,
      selected_16_30 bit DEFAULT 0,
      selected_11_15 bit DEFAULT 0,
      selected_10 bit DEFAULT 0,
      selected_9 bit DEFAULT 0,
      selected_8 bit DEFAULT 0,
      selected_7 bit DEFAULT 0,
      selected_6 bit DEFAULT 0,
      selected_5 bit DEFAULT 0,
      selected_4 bit DEFAULT 0,
      selected_3 bit DEFAULT 0,
      selected_2 bit DEFAULT 0,
      selected_1 bit DEFAULT 0
)
GO
ALTER PROCEDURE pr_experationdate AS
BEGIN
      SELECT experationdate FROM experationdates
      WHERE  userid = 25
        AND DateDiff(d,GETDATE(),experationdate) BETWEEN 31 AND 40
        AND selected_31_40 = 0
    UNION ALL
      SELECT experationdate FROM experationdates WHERE  userid = 25
        AND DateDiff(d,GETDATE(),experationdate) BETWEEN 16 AND 30
        AND selected_16_30 = 0
    ...
    UPDATE experationdates SET selected_31_40 = 1
    WHERE  userid = 25
    AND DateDiff(d,GETDATE(),experationdate) BETWEEN 31 AND 40
    AND selected_31_40 = 0

    UPDATE experationdates SET selected_16_30 = 1
    WHERE  userid = 25
    AND DateDiff(d,GETDATE(),experationdate) BETWEEN 16 AND 30
    AND selected_16_30 = 0
   
    ...
END
GO
--------------------------------------------
What is next? - How to make userid as a parameter of stored procedure in OpenQuery?
A. Through the table
B. Dynamic SQL.

And it is a common technical topic.
---------------------- Test data ----------
CREATE TABLE experationdates (userid int, experationdate datetime, selected_31_40 bit DEFAULT 0)
GO
INSERT INTO experationdates (userid,experationdate) VALUES
(25,'2011-09-12')
GO
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
You want to report PRIOR to expiration date, so the MySQL function to use is DATE_SUB

e.g. SELECT DATE_SUB(CURDATE(), INTERVAL 31 DAY)


And you can use that in the case statement (either in the select, or even the where clause) :

select * from
(
   select UserID,
          ExpirationDate,
          case when ExpirationDate > DATE_SUB(CURDATE(), INTERVAL 40 DAY) then 'Y' else 'N' end as Expires_41_Plus,
          case when ExpirationDate between DATE_SUB(CURDATE(), INTERVAL 31 DAY) and DATE_SUB(CURDATE(), INTERVAL 40 DAY) then 'Y' else 'N' end as Expires_31_40,
          case when ExpirationDate between DATE_SUB(CURDATE(), INTERVAL 16 DAY) and DATE_SUB(CURDATE(), INTERVAL 30 DAY) then 'Y' else 'N' end as Expires_16_30,
          case when ExpirationDate between DATE_SUB(CURDATE(), INTERVAL 11 DAY) and DATE_SUB(CURDATE(), INTERVAL 15 DAY) then 'Y' else 'N' end as Expires_11_15,
          case when ExpirationDate < DATE_SUB(CURDATE(), INTERVAL 11 DAY) then 'Y' else 'N' end as Expires_10_Days

   from MyTable

) sq

where Expires_41_Plus = 'N'
and ExpirationDate >= CURDATE()

Open in new window

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