SQL conditional query

Question

SQL conditional query

Question

With all and all of the examples and data below, is there any other way to do this to get the expected results done UNION

for different tables?

My problem

Essentially I need to have conditional logic:

if there are records found within a certain time period , then get all records (multiple) from the table
if there are no records for a certain period of time , then get the newest 1 record from the table since up to a certain period of time

Steps

Just create tables and insert the data listed below with this logic and then run every query I provided in every example and you can easily emulate exactly what I mean.

Context and clarification

Perhaps using temp / memory table instead of variables @Now~

and conditions TRUE

there UNION

in a correlated temporary table or perhaps some detailed (or easy for some) data table JOIN

methods?
I mean " Specific time period " below or words that exactly mean the same thing, and with these words I am talking about a specific start time and a specific end time and all the time between these two points is what I am talking about when I use this term.
- There is a reporting system that generates these start and end time frames and I got the logic in the called stored procedure and the date time.
  - I have provided only a portion of the stored procedure and have made some custom queries out of it to provide an example of the problem and the expected or desired result.

Data creation

With the help below you can create three tables in the MySQL database and this will also fill in the data that I used in the queries I run to show the results and such below each so you can follow and have data to play with, maybe even help me with a solution or a pointer or two.

Note. Before be sure to change <DBName>

to the actual name or DB schema on your MySQL instance.

USE <DBName>; 
CREATE TABLE `ponumber` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

CREATE TABLE `batch_number` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

CREATE TABLE `batchweight` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 10:47:55',0);
INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',1217911);
INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 05:24:18',1217906);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:18',5522);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:25:33',5521);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 11:44:45',5520);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:05',5519);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 05:22:58',5518);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:19',38985);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',38985);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-28 05:23:03',31002);

Query One

USE <DBName>; 

SET @bStartTime   = '2017-09-29 11:10:00';
SET @bEndTime     = '2017-09-29 12:48:00';

SELECT TimeStr, CONCAT('Set Load Number: ',Value) AS Value
FROM ponumber 
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

UNION 

SELECT TimeStr, CONCAT('Set Batch Number: ',Value) AS Value
FROM batch_number 
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

UNION 

SELECT TimeStr, 
    CONCAT('Batch Weight: ',Value) AS Value
FROM batchweight
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

ORDER BY TimeStr;

Query result

As you can see, no Set Load Number

, because PONumber

there are no entries in the table from the times defined by the variables @bStartTime

and @bEndTime

- by the given time.

If PONumber

there are no data records in the table for the specified time period, then the result should display the most recent records from that table, even those that go out for a specific time period, so I built Query Two .

Query Two

USE <DBName>; 
SET @bStartTime   = '2017-09-29 11:10:00';
SET @bEndTime     = '2017-09-29 12:48:00';

SET @LastPONumber = (SELECT Value FROM PONumber ORDER BY TimeStr DESC LIMIT 1); 
SET @NowPONumber  = (SELECT Value FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);
SET @LastPONumTimeStr  = (SELECT TimeStr FROM PONumber ORDER BY TimeStr DESC LIMIT 1); 
SET @NowPONumTimeStr   = (SELECT TimeStr FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);

SELECT DISTINCT TimeStr, Value FROM  
    (SELECT 
        CASE 
        WHEN TimeStr = 1 THEN 
            (SELECT @NowPONumTimeStr AS TimeStr) 
                ELSE 
            (SELECT @LastPONumTimeStr AS TimeStr)
                END AS TimeStr,
        CASE 
        WHEN Value = 1 THEN 
            (SELECT (CONCAT('Set Load Number: ',@NowPONumber)) AS Value) 
                ELSE 
            (SELECT (CONCAT('Set Load Number: ',@LastPONumber)) AS Value)
                END AS Value
    FROM PONumber) AS X

UNION 

SELECT TimeStr, CONCAT('Set Batch Number: ',Value) AS Value
FROM batch_number 
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

UNION 

SELECT TimeStr, 
    CONCAT('Batch Weight: ',Value) AS Value
FROM batchweight
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

ORDER BY TimeStr;

Query Result 2

For So the Query of Two - this is where I created the variables @LastPONumber

, @NowPONumber

, @LastPONumTimeStr

and @NowPONumTimeStr

and set these values as a result of the query SELECT

, using ORDER BY DESC

and LIMIT 1

depending on conditions using expressions CASE WHEN

.

The results now show the most recent table entries PONumber

as none were found between a specific time period, so this allowed it to go back beyond a specific time period.

There is now a requirement, if Batch_Number

there are no data records in the table for a certain period of time, to display the most recent record, as was done with the table PONumber

in Query Two , so I built Query Three using the same CASE WHEN

logic conditions , etc. like Query Two used for a table PONumber

.

Query triangle

USE <DBName>; 
SET @bStartTime   = '2017-09-29 11:10:00';
SET @bEndTime     = '2017-09-29 12:48:00';

## - PONumber Table Variables
SET @LastPONumber = (SELECT Value FROM PONumber ORDER BY TimeStr DESC LIMIT 1); 
SET @NowPONumber  = (SELECT Value FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);
SET @LastPONumTimeStr  = (SELECT TimeStr FROM PONumber ORDER BY TimeStr DESC LIMIT 1); 
SET @NowPONumTimeStr   = (SELECT TimeStr FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);

## - Batch_Number Table Variables
SET @LastBatNumber = (SELECT Value FROM Batch_Number ORDER BY TimeStr DESC LIMIT 1); 
SET @NowBatNumber  = (SELECT Value FROM Batch_Number WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);
SET @LastBatNumTimeStr  = (SELECT TimeStr FROM Batch_Number ORDER BY TimeStr DESC LIMIT 1); 
SET @NowBatNumTimeStr   = (SELECT TimeStr FROM Batch_Number WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);

SELECT DISTINCT TimeStr, Value FROM  
    (SELECT 
        CASE 
        WHEN TimeStr = 1 THEN 
            (SELECT @NowPONumTimeStr AS TimeStr) 
                ELSE 
            (SELECT @LastPONumTimeStr AS TimeStr)
                END AS TimeStr,
        CASE 
        WHEN Value = 1 THEN 
            (SELECT (CONCAT('Set Load Number: ',@NowPONumber)) AS Value) 
                ELSE 
            (SELECT (CONCAT('Set Load Number: ',@LastPONumber)) AS Value)
                END AS Value
    FROM PONumber) AS X

UNION 

SELECT DISTINCT TimeStr, Value FROM  
    (SELECT 
        CASE 
        WHEN TimeStr = 1 THEN 
            (SELECT @NowBatNumTimeStr AS TimeStr) 
                ELSE 
            (SELECT @LastBatNumTimeStr AS TimeStr)
                END AS TimeStr,
        CASE 
        WHEN Value = 1 THEN 
            (SELECT (CONCAT('Set Batch Number: ',@NowBatNumber)) AS Value) 
                ELSE 
            (SELECT (CONCAT('Set Batch Number: ',@LastBatNumber)) AS Value)
                END AS Value
    FROM Batch_Number) AS X

UNION 

SELECT TimeStr, 
    CONCAT('Batch Weight: ',Value) AS Value
FROM batchweight
WHERE TimeStr BETWEEN @bStartTime AND @bEndTime

ORDER BY TimeStr;

Query three results

When the Query Three result detects that the table Batch_Number

has records between a specific time period, it only displays the most recent one record and does not list the other two batch number ( 5521, and 5520

) values , as shown in Query Two Result . This is obviously due to the query variable @NowBatNumber

containing only the most recent 1 record.

Problem

I need to check records Batch_Number

between a certain period of time and do one of two things:
- If records exist between this time period, then list all records, not just the most recent one record
- If no entries are found between the specified time period, look again for that time period and list the most recent one entry

Essentially for a table, Batch_Number

and as I tried it in Query Three , I expected the results to look exactly like Query Two Result , and still use conditionals to indicate what is needed otherwise.

Go back and read the Question again , please, as it might make sense now.

+1

sql mysql

Bitcoin murderous maniac 29 Sep 17 at 18:49

source to share

1 answer

Used_By_Already · Accepted Answer · 2017-10-02T03:53:44+0000

Short and simple questions tend to get more attention than long / complex ones. This is not because we cannot answer, but with so many questions and so little time for volunteers that it is difficult to explain that the big questions need to be read.

However, I think your basic requirement is not that hard. You need a way to extract rows that fall within a span of time, or if there are no closest rows in that range in that range.

In databases that support ROW_NUMBER () OVER () this is pretty straightforward (and MySQL 8.x plans to support it), but until then, you can use variables and an ordered subquery to emulate row_number ().

You can try this solution here, SQL Fiddle

MySQL 5.6 Schema setup :

CREATE TABLE `ponumber` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 10:47:55',0);
INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',1217911);
INSERT INTO `PONumber`     (`TimeStr`,`Value`) VALUES ('2017-09-28 05:24:18',1217906);

CREATE TABLE `batch_number` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:18',5522);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:25:33',5521);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 11:44:45',5520);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:05',5519);
INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 05:22:58',5518);

CREATE TABLE `batchweight` (
  `TimeStr` datetime NOT NULL,
  `Value` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeStr`));

INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:19',38985);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',38985);
INSERT INTO `batchweight`  (`TimeStr`,`Value`) VALUES ('2017-09-28 05:23:03',31002);

Query

SET @bStartTime  := '2017-09-29 11:10:00'   
SET @bEndTime    := '2017-09-29 12:48:00'

SELECT 
      SrcTable, TimeStr, Value
FROM (
      SELECT
            @row_num :=IF( @prev_value=u.SrcTable, @row_num + 1 ,1) AS RowNumber
          , u.*
          , @prev_value := u.SrcTable
      FROM (

          select 'ponumber' SrcTable , TimeStr, `Value`
          from ponumber
          union all
          select 'batch_number' SrcTable , TimeStr, `Value`
          from batch_number
          union all
          select 'batchweight' SrcTable , TimeStr, `Value`
          from batchweight
          ) u
      CROSS JOIN (SELECT @row_num := 1,  @prev_value :='') vars
      ORDER BY SrcTable, TimeStr DESC
      ) d
WHERE (d.TimeStr between @bStartTime and @bEndTime)
   OR (TimeStr < @bStartTime AND RowNumber = 1)

So what it is, the "RowNumber" is calculated, which starts at 1 for the very last row for each source table. This view is then filtered by either a time range or a row number, if not within a time range.

Also note that I have NOT used UNION

but used instead UNION ALL

. There is a big difference in performance and one must learn to use each one according to needs. If the use UNION

also does not use select distinct

, because you are just wasting effort.

Results :

|     SrcTable |              TimeStr | Value |
|--------------|----------------------|-------|
|  batchweight | 2017-09-29T12:46:19Z | 38985 |
| batch_number | 2017-09-29T12:46:18Z |  5522 |
| batch_number | 2017-09-29T12:25:33Z |  5521 |
| batch_number | 2017-09-29T11:44:45Z |  5520 |
|     ponumber | 2017-09-28T10:47:55Z |     0 |

SQL conditional query

Question

Data creation

Query One

Query Two

Query triangle

More articles: