Pablo Gûereca
Reputation: 725
Take next date value SQL Server
I have a table (A) and another table(B) with this information:
What I am trying to do is get something like FINAL DATA in the image........ if the Receipt value doesn't match with the ExecutionDate+ExecutionTime in the same day ... the query should take the first next ExecutionDate+ExecutionTime
Let me explain you what I am trying to get with 2 retailers:
COSTWH - 2017-12-26 11:15:09:000
This one should take the value
SellThru Refresh - 2017-12-26 23:30:00 in the table B
because the receipt date was before the execution time ( 11:15:09:000 vs 23:30:00) so the final data should look like
COSTWH - 2017-12-26 11:15:09:000 - 2017-12-26 23:30:00
for AMAZONCA - 2017-12-26 23:59:08:000 -----
the value from table B should be
SellThru Refresh - 2017-12-27- 04:30:00
WHY? --- because the receipt date was after the last execution on that day ('2017-12-26 23:59:08:000' VS '2017-12-26 23:30:00') so the query should take the next closest execution and in this case is '2017-12-27','04:30:00'
so the data for AMAZONCA should look like
AMAZONCA - 2017-12-26 23:59:08:000 - 2017-12-27 04:30:00'
SAMPLEDATA:
Table A
CREATE TABLE TableA(
Retailer VARCHAR(24) NOT NULL
,ReceiptDate DATETIME NOT NULL
);
INSERT INTO TableA VALUES ('AMAZON','2017-12-26 07:00:12:000');
INSERT INTO TableA VALUES ('MEIJER','2017-12-26 07:00:13:000');
INSERT INTO TableA VALUES ('SAMC01','2017-12-26 07:00:13:000');
INSERT INTO TableA VALUES ('SAMC01:COM','2017-12-26 07:00:13:000');
INSERT INTO TableA VALUES ('SAMSPR','2017-12-26 07:00:13:000');
INSERT INTO TableA VALUES ('WMT:COMCA','2017-12-26 07:00:14:000');
INSERT INTO TableA VALUES ('WMT001:COM','2017-12-26 07:00:14:000');
INSERT INTO TableA VALUES ('WMTCAN','2017-12-26 07:00:14:000');
INSERT INTO TableA VALUES ('TARGET','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARGET','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARGET','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARGET','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARGET','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARGET','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARGET','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARGET','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARGET','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARSTO','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARSTO','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARSTO','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARSTO','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARSTO','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARSTO','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARSTO','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARSTO','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('TARSTO','2017-12-26 09:30:11:000');
INSERT INTO TableA VALUES ('COSTCM','2017-12-26 11:15:09:000');
INSERT INTO TableA VALUES ('COSTCN','2017-12-26 11:15:09:000');
INSERT INTO TableA VALUES ('COSTCN','2017-12-26 11:15:09:000');
INSERT INTO TableA VALUES ('COSTCN:COM','2017-12-26 11:15:09:000');
INSERT INTO TableA VALUES ('COSTWH','2017-12-26 11:15:09:000');
INSERT INTO TableA VALUES ('AMAZONCA','2017-12-26 23:59:08:000');
INSERT INTO TableA VALUES ('AMAZON','2017-12-26 23:59:08:000');
INSERT INTO TableA VALUES ('SAMSMX','2017-12-26 23:59:08:000');
INSERT INTO TableA VALUES ('WMT001:COM','2017-12-26 23:59:08:000');
INSERT INTO TableA VALUES ('BBYCAN','2017-12-27 07:00:08:000');
INSERT INTO TableA VALUES ('MEIJER','2017-12-27 07:00:08:000');
INSERT INTO TableA VALUES ('SYNNEX','2017-12-27 07:00:08:000');
INSERT INTO TableA VALUES ('TECD01','2017-12-27 07:00:08:000');
INSERT INTO TableA VALUES ('SAMC01','2017-12-27 07:00:08:000');
INSERT INTO TableA VALUES ('SAMC01:COM','2017-12-27 07:00:08:000');
INSERT INTO TableA VALUES ('SAMSPR','2017-12-27 07:00:08:000');
INSERT INTO TableA VALUES ('WMT:COMCA','2017-12-27 07:00:08:000');
INSERT INTO TableA VALUES ('WMT001','2017-12-27 07:00:08:000');
INSERT INTO TableA VALUES ('WMTCAN','2017-12-27 07:00:08:000');
INSERT INTO TableA VALUES ('WMT001','2017-12-27 10:15:08:000');
INSERT INTO TableA VALUES ('COSTCM','2017-12-27 11:15:08:000');
INSERT INTO TableA VALUES ('COSTCN','2017-12-27 11:15:08:000');
INSERT INTO TableA VALUES ('COSTCN:COM','2017-12-27 11:15:08:000');
INSERT INTO TableA VALUES ('COSTWH','2017-12-27 11:15:08:000');
INSERT INTO TableA VALUES ('SAMSMX','2017-12-27 23:59:10:000');
Table B
CREATE TABLE TableB(
jobname VARCHAR(30) NOT NULL
,ExecutionDate Date NOT NULL
,StartTime Time NOT NULL
,EndTime Time NOT NULL);
INSERT INTO TableB VALUES ('SellThru Refresh','2017-12-26','23:30:00','23:33:30');
INSERT INTO TableB VALUES ('SellThru Refresh','2017-12-27','04:30:00','04:48:29');
INSERT INTO TableB VALUES ('SellThru Refresh','2017-12-27','07:15:00','07:19:29');
INSERT INTO TableB VALUES ('SellThru Refresh','2017-12-27','11:30:00','11:36:15');
INSERT INTO TableB VALUES ('SellThru Refresh','2017-12-27','15:00:00','15:08:50');
INSERT INTO TableB VALUES ('SellThru Refresh','2017-12-27','23:30:00','23:34:52');
INSERT INTO TableB VALUES ('SellThru Refresh','2017-12-28','04:30:00','05:05:02');
INSERT INTO TableB VALUES ('SellThru Refresh','2017-12-28','07:15:00','07:27:38');
INSERT INTO TableB VALUES ('SellThru Refresh','2017-12-28','11:30:00','11:37:51');
INSERT INTO TableB VALUES ('SellThru Refresh','2017-12-28','15:00:00','15:13:38');
INSERT INTO TableB VALUES ('SellThru Refresh','2017-12-28','23:30:00','23:33:16');
Upvotes: 3
Views: 154
Answers (1)
Dejan Dozet
Reputation: 1009
Then it should be like this:
select distinct Retailer
, ReceiptDate
, (select min(CAST(ExecutionDate AS DATETIME) + CAST(StartTime AS DATETIME)) -- first appearance
from TableB
where CAST(ExecutionDate AS DATETIME) + CAST(StartTime AS DATETIME) >= TableA.ReceiptDate) DataRefresh
from TableA
order by Retailer
, ReceiptDate
And the result is like this:
+------------+-------------------------+-------------------------+
| Retailer | ReceiptDate | DataRefresh |
+------------+-------------------------+-------------------------+
| AMAZON | 2017-12-26 07:00:12.000 | 2017-12-26 23:30:00.000 |
| AMAZON | 2017-12-26 23:59:08.000 | 2017-12-27 04:30:00.000 |
| AMAZONCA | 2017-12-26 23:59:08.000 | 2017-12-27 04:30:00.000 |
| BBYCAN | 2017-12-27 07:00:08.000 | 2017-12-27 07:15:00.000 |
| COSTCM | 2017-12-26 11:15:09.000 | 2017-12-26 23:30:00.000 |
| COSTCM | 2017-12-27 11:15:08.000 | 2017-12-27 11:30:00.000 |
| COSTCN | 2017-12-26 11:15:09.000 | 2017-12-26 23:30:00.000 |
| COSTCN | 2017-12-27 11:15:08.000 | 2017-12-27 11:30:00.000 |
| COSTCN:COM | 2017-12-26 11:15:09.000 | 2017-12-26 23:30:00.000 |
| COSTCN:COM | 2017-12-27 11:15:08.000 | 2017-12-27 11:30:00.000 |
| COSTWH | 2017-12-26 11:15:09.000 | 2017-12-26 23:30:00.000 |
| COSTWH | 2017-12-27 11:15:08.000 | 2017-12-27 11:30:00.000 |
| MEIJER | 2017-12-26 07:00:13.000 | 2017-12-26 23:30:00.000 |
| MEIJER | 2017-12-27 07:00:08.000 | 2017-12-27 07:15:00.000 |
| SAMC01 | 2017-12-26 07:00:13.000 | 2017-12-26 23:30:00.000 |
| SAMC01 | 2017-12-27 07:00:08.000 | 2017-12-27 07:15:00.000 |
| SAMC01:COM | 2017-12-26 07:00:13.000 | 2017-12-26 23:30:00.000 |
| SAMC01:COM | 2017-12-27 07:00:08.000 | 2017-12-27 07:15:00.000 |
| SAMSMX | 2017-12-26 23:59:08.000 | 2017-12-27 04:30:00.000 |
| SAMSMX | 2017-12-27 23:59:10.000 | 2017-12-28 04:30:00.000 |
| SAMSPR | 2017-12-26 07:00:13.000 | 2017-12-26 23:30:00.000 |
| SAMSPR | 2017-12-27 07:00:08.000 | 2017-12-27 07:15:00.000 |
| SYNNEX | 2017-12-27 07:00:08.000 | 2017-12-27 07:15:00.000 |
| TARGET | 2017-12-26 09:30:11.000 | 2017-12-26 23:30:00.000 |
| TARSTO | 2017-12-26 09:30:11.000 | 2017-12-26 23:30:00.000 |
| TECD01 | 2017-12-27 07:00:08.000 | 2017-12-27 07:15:00.000 |
| WMT:COMCA | 2017-12-26 07:00:14.000 | 2017-12-26 23:30:00.000 |
| WMT:COMCA | 2017-12-27 07:00:08.000 | 2017-12-27 07:15:00.000 |
| WMT001 | 2017-12-27 07:00:08.000 | 2017-12-27 07:15:00.000 |
| WMT001 | 2017-12-27 10:15:08.000 | 2017-12-27 11:30:00.000 |
| WMT001:COM | 2017-12-26 07:00:14.000 | 2017-12-26 23:30:00.000 |
| WMT001:COM | 2017-12-26 23:59:08.000 | 2017-12-27 04:30:00.000 |
| WMTCAN | 2017-12-26 07:00:14.000 | 2017-12-26 23:30:00.000 |
| WMTCAN | 2017-12-27 07:00:08.000 | 2017-12-27 07:15:00.000 |
+------------+-------------------------+-------------------------+
Upvotes: 2
Related Questions
- SQL Server 2008 R2: Updating past dates to the 'next' date
- SQL - Select next date query
- If Date exists then show next date
- Find next available date
- Fetch next date from based on a date parameter
- SQL Query to Show next date
- SQL get next date time
- Select value on next date to be calculated on current date SQL
- select the next date based on a specified date SQL 2008
- Find next date for certain record in SQL Server 2008