Help with this complicated Time Stamp Calculation Script

Transact-SQL
1

Hello Experts,
I need your help in implementing a logic in my Sql Server Script:

I have a table :

CREATE TABLE dbo.Listener_State_Fact
(
START_DATETIME_KEY INTEGER
,END_DATETIME_KEY INTEGER
,MUSIC_TYPE_KEY INTEGER
,LISTENER_KEY INTEGER
,LISTENER_STATE VARCHAR(50)
,SECONDS_START INTEGER
,SECONDS_END INTEGER	
,DURATION INTEGER
)

Where START_DATETIME_KEY, END_DATETIME_KEY is foreign_key for DATETIME table, where each row are saved in 15min interval time.
Example for DATETIME Table with columns DATETIME_KEY, DATE
1360170000 2013-02-06 17:00:00.000
1360170900 2013-02-06 17:15:00.000
1360171800 2013-02-06 17:30:00.000

MUSIC_TYPE_KEY - I have 2 values as Music_type_Key, (1- Hindi, 2-English)
LISTENER_STATE - 4 States (Playing, Pause, Forward, Rewind)
SECONDS_START, SECONDS_END are exact timestamp in seconds,
Eg: 1360170059 means - 2013-02-06 17:00:59.000
DURATION - (SECONDS_END - SECONDS_START) in sec

I am not able to attach a dummy excel data, hence showing screen shot:

Data
Data.jpg885×465 178 KB

I want to calculate the neutral State for Listener for every DATETIME_KEY in DATETIME Table.
Output Columns would be
DATETIME_KEY, LISTENER_KEY, Duration_Playing, Duration_Pause, Duration_Forward, Duration_Rewind

Please note, If a row in fact table says, that Listener was playing song at 9:10am until 9:25am, Its
mean I have to break this single row into 2 rows, i.e 9:00 am - Duration_Playing = 300 sec, and 9:15am - Duration_Playing = 600 sec

If a listener is listening 2 Music_Types, Calculation is base on the following logic:

CASE
WHEN ISNULL(HINDI.LISTENER_STATE,'') IN ('','Forward') AND ISNULL(ENG.LISTENER_STATE,'') IN ('','Forward') THEN 'Forward'

WHEN ISNULL(HINDI.LISTENER_STATE,'') IN ('','Playing', 'Rewind','Forward','Pause') AND ISNULL(ENG.LISTENER_STATE,'') = 'Playing' THEN 'Playing'
WHEN ISNULL(HINDI.LISTENER_STATE,'') = 'Playing' AND ISNULL(ENG.LISTENER_STATE,'') IN ('','Playing', 'Rewind','Forward','Pause')THEN 'Playing'
WHEN ISNULL(HINDI.LISTENER_STATE,'') = 'Rewind' AND ISNULL(ENG.LISTENER_STATE,'') <> 'Playing' THEN 'Rewind'

WHEN ((ISNULL(HINDI.LISTENER_STATE,'') = 'Pause' AND ISNULL(ENG.LISTENER_STATE,'') = 'Pause') OR (ISNULL(HINDI.LISTENER_STATE,'') = 'Pause' AND ISNULL(ENG.LISTENER_STATE,'') = '') OR (ISNULL(HINDI.LISTENER_STATE,'') = '' AND ISNULL(ENG.LISTENER_STATE,'') = 'Pause'))   THEN 'Pause'
WHEN ISNULL(HINDI.LISTENER_STATE,'') = 'Pause' AND ISNULL(ENG.LISTENER_STATE,'') = 'Forward' THEN 'Pause'
WHEN ISNULL(HINDI.LISTENER_STATE,'') = 'Forward' AND ISNULL(ENG.LISTENER_STATE,'') = 'Pause' THEN 'Pause'

END

What I have done: I break DATETIME table into Second table and fetched details for every seconds.
This creates lots of rows, and the script is taking more than 1 hours to execute.

Let me know, if you have any question.

Thanks in advance.

2

This will "split" your rows into 15 minute timeslots and gives you 3 additional fields:

  • start date/time of timeslot
  • end date/time of timeslot
  • timeslot index (starting with 1)

I have not implemented your "case logic" as this has nothing to do with "splitting" the rows.

with cte_tally(n)
  as (select row_number() over(order by (select null)) as n
        from (            values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_tally1(n) /* 10 timeslots   = 150 minutes   = 2,5 hours */
/*****
 * expand 15 minute "timeslots" as needed, if 10 isn't enough
             cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_tally2(n) /* 100 timeslots  = 1500 minutes  = 25 hours */
             cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_tally3(n) /* 1000 timeslots = 15000 minutes = 10 days */
 *
 *****/
     )
select a.* /* here you should specify the fields you want. do not use * */
      ,a.seconds_start-(a.seconds_start%(15*60))+((c.n-1)*15*60) as slot_start
      ,a.seconds_start-(a.seconds_start%(15*60))+(c.n*15*60) as slot_end
      ,c.n as slot_idx
  from dbo.listener_state_fact as a
       cross apply (select ceiling((a.seconds_end-a.seconds_start+(a.seconds_start%(15*60)))/(15.*60)) as n) as b
       inner join cte_tally as c
               on c.n<=b.n
 order by a.seconds_start
         ,a.seconds_end
;
3

Thanks Bitsmed.
I am very new to SQL. And I did not understand your script.

4

Which part?