I have a temp table called #tmpFrames that gives the output as such:
| Frameid | Start_Day | End_Day |
|---|---|---|
| 1 | 2021-09-01 18:00:00.000 | 2021-09-02 06:00:00.000 |
| 2 | 2021-09-02 18:00:00.000 | 2021-09-03 06:00:00.000 |
| 3 | 2021-09-03 18:00:00.000 | 2021-09-04 06:00:00.000 |
| 4 | 2021-09-04 18:00:00.000 | 2021-09-05 06:00:00.000 |
| 5 | 2021-09-05 18:00:00.000 | 2021-09-06 06:00:00.000 |
| 6 | 2021-09-06 18:00:00.000 | 2021-09-07 06:00:00.000 |
| 7 | 2021-09-07 18:00:00.000 | 2021-09-08 06:00:00.000 |
| 8 | 2021-09-08 18:00:00.000 | 2021-09-09 06:00:00.000 |
| 9 | 2021-09-09 18:00:00.000 | 2021-09-10 06:00:00.000 |
Also, there is another table called Audit which is as follows:
| ID | Record | AccessedDTTM |
|---|---|---|
| 16 | Sign In | 2021-09-02 18:31:33.798 |
| 16 | Charge | 2021-09-02 21:41:33.770 |
| 16 | Session End - Sign Out | 2021-09-02 04:42:33.770 |
| 16 | Sign In | 2021-09-07 19:41:33.770 |
| 16 | Session End - Sign Out | 2021-09-07 04:42:33.770 |
| 17 | Sign In | 2021-09-01 00:03:04.070 |
| 17 | Session End - Sign Out | 2021-09-01 00:33:52.717 |
| 18 | Sign In | 2021-09-06 01:31:00.527 |
| 18 | Schedule | 2021-09-06 01:31:31.407 |
| 18 | Charge | 2021-09-06 01:43:27.427 |
| 18 | Session End - Sign Out | 2021-09-06 01:47:27.940 |
I need to create another temp table that would retrieve data from the Audit table based on every start_day and end_day time range that is mentioned in the temp table above and also add the start_day and end_day columns. for example :
I would like to have the data as follows for all the data from the Audit table and combine the data from Temp Table as well for every time frame:
| ID | AccessedDTTM | Start_day | End_Day | FrameId |
|---|---|---|---|---|
| 16 | 2021-09-02 18:31:33.798 | 2021-09-02 18:00:00.000 | 2021-09-03 06:00:00.000 | 2 |
| 16 | 2021-09-02 21:41:33.770 | 2021-09-02 18:00:00.000 | 2021-09-03 06:00:00.000 | 2 |
| 16 | 2021-09-02 04:42:33.770 | 2021-09-02 18:00:00.000 | 2021-09-03 06:00:00.000 | 2 |
Any suggestions how this can be achieved? I would really appreciate your help here. Thanks in advance!
CodePudding user response：
I hate this kind of join but itll get the job done.
SELECT
tf.ID
, tf.AccessedDTTM
, a.Start_Day
, a.End_Day
FROM
[Audit] a
INNER JOIN #tmpFrames tf ON a.AccessedDTTM>=tf.Start_Day
AND a.AccessedDTTM<=tf.End_Day