CODEWITHSUNDEEP
sql
sindhu Y
sindhu Y

Reputation: 15

How to get the records between two different times in one day while having other day data in the same table

How to get the records between two different times in one day while having other day data in the same table.

Table:

id reported_date         dept
1  2017-11-23 09:00:05   IT
2  2017-11-22 21:00:10   IT
3  2017-11-23 10:00:09   SC
4  2017-11-22 22:00:20   SC
5  2017-11-23 05:00:30   IT

The above data is captured based on below criteria: 8:00 pm (YESTERDAY)- 8:00 pm (TODAY) - WILL BE THERE IN today's table.

Now i want to flag the data if it is between 8:00 pm(previous day) - 6:00AM (today) as 'late'. as a new column or as a temp variable.

In the above table the records with 2,4,5 are to be marked as 'late', but you should not use time frame as 8-6 manually. Take the minimum time record after 8:00pm and maxtime before 6:00am. Please help me.

Upvotes: 0

Views: 138

Answers (2)

Egon Allison
Egon Allison

Reputation: 1388

Answer modified and written in mysql

/*SET @TODAY_TIMESTAMP = CURRENT_TIMESTAMP;*/ /*use this for live code*/
SET @TODAY_TIMESTAMP = '2017-11-23 14:27:10'; /*just for test scenary date of question was posed on */
SET @YesterdayEightPM = concat_ws(' ', DATE( DATE_ADD(@TODAY_TIMESTAMP, interval -1 day)),  ' 20:00:00'); 
SET @TodaySixAm  = concat_ws(' ', DATE(@TODAY_TIMESTAMP), '06:00:00');

CREATE temporary table  IF NOT EXISTS TBL_TEMP
( 
    id int,
    reported_date DATETIME,    
    dept varchar(40)
);

DELETE FROM TBL_TEMP where 1=1;

INSERT INTO TBL_TEMP (id,reported_date,dept)
VALUES (1,'2017-11-23 09:00:05', 'IT'),
       (2,'2017-11-22 21:00:10','IT'),
       (3,'2017-11-23 10:00:09','SC'),
       (4,'2017-11-22 22:00:20','SC'),
       (5,'2017-11-23 05:00:30','IT');   

SELECT ID, reported_date, dept,
       CASE WHEN reported_date BETWEEN CAST(@YesterdayEightPM AS DATETIME) AND CAST(@TodaySixAm AS DATETIME) THEN 'Late'                                       
       ELSE  'Normal' END AS 'Type',
       @TODAY_TIMESTAMP As TODAY_TIMESTAMP, 
       @YesterdayEightPM AS Yesterday8PM, 
       @TodaySixAm AS Today6AM
FROM TBL_TEMP;

Original Answer written in sql server

DECLARE @TODAY_TIMESTAMP DATE = CURRENT_TIMESTAMP
DECLARE @YesterdayEightPM DATETIME = CAST(DATEADD(DD, -1,@TODAY_TIMESTAMP) as varchar(50)) + ' 20:00:00.000'
DECLARE @TodaySixAm DATETIME = CAST(@TODAY_TIMESTAMP as varchar(50)) + ' 06:00:00.000'

SELECT @YesterdayEightPM AS Yesterday8PM, @TodaySixAm AS Today6AM

DECLARE @TBL_TEMP TABLE 
( 
    [id] [int] NOT NULL,
    [reported_date] [DATETIME] NOT NULL,    
    [dept] [varchar](40) NULL   
)

INSERT INTO @TBL_TEMP (ID, reported_date, dept)
VALUES  (1,'2017-11-23 09:00:05', 'IT'),
        (2,'2017-11-22 21:00:10','IT'),
        (3,'2017-11-23 10:00:09','SC'),
        (4,'2017-11-22 22:00:20','SC'),
        (5,'2017-11-23 05:00:30','IT')

SELECT  ID, reported_date, dept,
        CASE WHEN TT.reported_date BETWEEN     @YesterdayEightPM AND @TodaySixAm THEN 'Late'
        ELSE 'Normal'END
FROM @TBL_TEMP TT   WHERE 1=1

Upvotes: 0

rushifell
rushifell

Reputation: 11

SELECT *
FROM tablename
WHERE hour(reported_date)<6 or
      hour(reported_date)>20

Upvotes: 1

Related Questions