Avatar of smares32371
smares32371
 asked on

SQL select experationdate field by userid where

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
MySQL Server

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
Patrick Matthews

smares32371,

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

Patrick
dsacker

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

smares32371

ASKER
I just found out its for mysql and syntax written is for ms sql sorry.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
dsacker

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

smares32371

ASKER
there is an errror in the line below

      case
           when datediff(ExpirationDate, CurDate()) < 11 datediff(ExpirationDate, CurDate())
       end as "Days Left"
ASKER CERTIFIED SOLUTION
dsacker

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Lowfatspread

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 ?


Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Lowfatspread

please confirm mysql version
Andrei Fomitchev

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 Wills

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy