Link to home
Start Free TrialLog in
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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

smares32371,

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

Patrick
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

Avatar of smares32371
smares32371

ASKER

I just found out its for mysql and syntax written is for ms sql sorry.
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

there is an errror in the line below

      case
           when datediff(ExpirationDate, CurDate()) < 11 datediff(ExpirationDate, CurDate())
       end as "Days Left"
ASKER CERTIFIED SOLUTION
Avatar of dsacker
dsacker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ?


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