Home > OS >  Creating a temp table based on a regular table and a few columns from another temp table and looping
Creating a temp table based on a regular table and a few columns from another temp table and looping

Time:09-16

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
  • Related