-1

I need to list all the dates between a given (prescription) start date and end date - (as a part of a much bigger query).

Table name: patientprescription
Start date field: prescriptionstartdate
End date field: prescriptionenddate

I'm trying to use session variables. But I find it difficult to set the initial value in the variable. First time execution doesn't result in any value but the subsequent execution gives me the correct result (because the first execution sets the variables).

To simplify my need, here is the gist of my query:

SELECT
    @gg := DATE_ADD ( @gg, INTERVAL 1 DAY ) AS rxDate
FROM patientprescription AS rx
    JOIN ( SELECT @gg := @hh FROM t1 ) AS v1 ON @hh := rx.prescriptionstartdate
WHERE
    rx.id = 8
    AND @gg <= rx.prescriptionenddate;

t1 is some table with a few records.

To reset the variables back after execution:

SELECT @gg := NULL, @hh := NULL;
Ramanathan Subbiah
  • 37
  • 1
  • 3
  • Possible duplicate of [How to get list of dates between two dates in mysql select query](https://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query) – Gurwinder Singh Oct 18 '17 at 06:29
  • Thanks for pointing me to the link. I'm looking at it. Besides that, is it possible to have the same result with the above query using variables? – Ramanathan Subbiah Oct 18 '17 at 06:41
  • Still struggling? See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Oct 18 '17 at 07:16
  • Don't understand why my question is down voted? I wanted to have a solution using a variable which I felt would be efficient than iterating through a big list of number as suggested in the above posts. I don't think its a duplicate question. – Ramanathan Subbiah Oct 18 '17 at 09:10

1 Answers1

0

I managed to get this with variable.

SELECT
    @gg := DATE( DATE_ADD (rx.prescriptionstartdate, INTERVAL @hh DAY) ) rxDateNext,
    @hh := @hh + 1 AS adderStmt
FROM patientprescription rx
    JOIN (SELECT @hh := 0 FROM t1) AS v1
WHERE 
    rx.id = 8
    AND @hh <= DATEDIFF(rx.prescriptionenddate, rx.prescriptionstartdate);
Ramanathan Subbiah
  • 37
  • 1
  • 3