April 27, 2009 at 5:20 am
Dear All,
I need a help to calculate Commercials durations, please.
I have three types of a Commercial:
Top and Tail Commercials:
1. Top Commercial, i.e. the one which opens the break.
2. Tail Commercial, i.e. the one which closes the break.
3. Commercial which is none of the above (a regular one)
Each Commercial has its own scheduled duration.
I need to do the follow:
1. If per a given break I have Top Commercial with its respective Tail one, I need to
display on the Top's record a scheduled duration of both and zero - on the Tail's record.
2. If there is a regular Commercial I need to display its scheduled duration, but if there is a
an even number of this commercial within the same break I need to display the scheduled duration * 2 on each uneven record and zero - on the even one.
3. A tricky one - if there're Tops without Tails or vs, treat those unpaired as usual ones
Thought I can do it using ROW_NUMBER and COUNT OVER, but seems I am getting to nowhere....
Thanks in advance!
WITH scheduled_commercials AS (
SELECT 1 AS commercial_break_id,
1 AS com_break_line,
100 AS commercial_id,
1 AS commercial_type, ---- 0 - regular, 1 - top, 2 - tail
100 AS top_commercial_id, --- if it is a Tail or Top - type in Top's commercial_id
15 AS scheduled_duration --- in seconds
UNION ALL
SELECT 1,
2,
119,
0,
0,
30
UNION ALL
SELECT 1,
3,
198,
2,
100,
20
UNION ALL
SELECT 1,
4,
119,
0,
0,
30
UNION ALL
SELECT 1,
5,
198,
2,
100,
20)
SELECT * FROM scheduled_commercials;
April 27, 2009 at 6:06 am
Thank you SO much for posting up sample data. 🙂
I'm still not sure I completely understand your requirements. Can you add to your post what you would expect to see as the final result, given the the input data you supplied?
Thanks again.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 27, 2009 at 6:42 am
Hi Bob,
Thanks for the quick reply of yours. The result set I expect to have is as follow:
commercial_break_id commercial_id duration
1 100 35 (15 + 20 )
1 119 60 (30 + 30 )
1 198 0 (has been calculated in the first row)
1 119 0 (has been calculated in the second row)
1 198 20 (has no pair, so origin duration displayed)
April 27, 2009 at 7:39 am
Hi Zeev,
I'm looking at this issue also, and there is one aspect of it that I don't understand:
SELECT
1 AS commercial_break_id,
1 AS com_break_line,
100 AS commercial_id,
1 AS commercial_type, ---- 0 - regular, 1 - top, 2 - tail
100 AS top_commercial_id, --- if it is a Tail or Top - type in Top's commercial_id
15 AS scheduled_duration --- in seconds
This is going to return all of the commercials for a given commercial break, where the order of the commercials is the com_break_line?
The set of commercials has a commercial that leads off the break (the top), a commercial that ends the break (the tail), and all commercials between. According to your test data, there are two commercials that are the tail... how can this be? (Rows #3, #5)
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 27, 2009 at 7:57 am
Wayne,
Thanks for the reply. There's an automatic booking process which works OK.
But the normal situation in TV business is that the Sales department can decide on the fly,
close to the broadcasting date, to make a change to the playlist, i.e. they have Top and Tail
scheduled commercials of Coca Cola. But close to the broadcasting date the Sales department or Coca Cola itself, have decided that they want to air Tail commercial twice. In this case, Coca Cola
will be charged twice: once for Top and Tail Commercial combination and once for the Tail Commercial only.
Let's make it uglier: there're Top and Tail Commercials booked, now as of the economic crisis there's a free time Sales department couldn't sell the commercials to book. It is possible that the department will decide to book the same Top and Tail commercials twice. In this case only the Top ones will have the scheduled time of Top+Tail and the Tails will have 0.
April 27, 2009 at 8:10 am
Okay....
So, based on the data in your sample code, what is the logic to determine that the third commercial gets combined with the top, and the last commercial doesn't?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 27, 2009 at 8:13 am
FIFO is fine - the first you get.In my example I have find the first Commercials by there sequence
ROW_NUMBER() OVER (PARTITION BY commercial_break_id, commercial_id ORDER BY commercial_break_id,com_break_line)
April 27, 2009 at 1:08 pm
I'm not sure if the following really does what you need, but it does reproduce your expected results.
;WITH scheduled_commercials AS (
SELECT 1 AS commercial_break_id,
1 AS com_break_line,
100 AS commercial_id,
1 AS commercial_type, ---- 0 - regular, 1 - top, 2 - tail
100 AS top_commercial_id, --- if it is a Tail or Top - type in Top's commercial_id
15 AS scheduled_duration --- in seconds
UNION ALL
SELECT 1, 2, 119, 0, 0, 30
UNION ALL
SELECT 1, 3, 198, 2, 100, 20
UNION ALL
SELECT 1, 4, 119, 0, 0, 30
UNION ALL
SELECT 1, 5, 198, 2, 100, 20
),
cteGrouping AS (
SELECT commercial_break_id,
com_break_line,
top_commercial_id AS commercial_id,
3 AS commercial_type,
scheduled_duration,
(ROW_NUMBER() OVER (PARTITION BY commercial_break_id, top_commercial_id ORDER BY commercial_break_id, top_commercial_id, com_break_line) + 1) / 2 AS group_id
FROM scheduled_commercials
WHERE (commercial_type IN (1, 2))
UNION ALL
SELECT commercial_break_id,
com_break_line,
commercial_id,
0 AS commercial_type,
scheduled_duration,
(ROW_NUMBER() OVER (PARTITION BY commercial_break_id, commercial_id ORDER BY commercial_break_id, commercial_id, com_break_line) + 1) / 2 AS group_id
FROM scheduled_commercials
WHERE (commercial_type = 0)
),
cteCombine AS (
SELECT
commercial_break_id,
MIN(com_break_line) AS com_break_line,
SUM(scheduled_duration) AS duration
FROM cteGrouping
GROUP BY commercial_break_id, commercial_id, commercial_type, group_id
)
SELECT
SC.commercial_break_id,
SC.commercial_id,
COALESCE(C.duration, 0) AS duration
FROM scheduled_commercials SC LEFT OUTER JOIN cteCombine C
ON (SC.commercial_break_id = C.commercial_break_id AND SC.com_break_line = C.com_break_line)
ORDER BY SC.commercial_break_id, SC.com_break_line
April 27, 2009 at 1:24 pm
Thanks, but adding of
UNION ALL
SELECT 1,6,100,1,100,15
my example will return zero duration for this Top record. This is a place to fix my explanation's mistake - in case of Top and Tail pair, their both duration should always be on the Top's record.
THX again
April 27, 2009 at 4:53 pm
Zeev Kazhdan (4/27/2009)
Thanks, but adding ofUNION ALL
SELECT 1,6,100,1,100,15
my example will return zero duration for this Top record. This is a place to fix my explanation's mistake - in case of Top and Tail pair, their both duration should always be on the Top's record.
What's the expected result with this extra 6th row?
commercial_break_id commercial_id duration
1 100 35
1 119 60
1 198 0
1 119 0
1 198 20
1 100 15
or
commercial_break_id commercial_id duration
1 100 35
1 119 60
1 198 0
1 119 0
1 198 0
1 100 35
April 28, 2009 at 1:26 am
Second one is the correct result.
I have just managed to do it using SUM OVER and ROW_NUMBER OVER and CASE on their result.
It is too ugly, so any aesthetic way will be more than welcome.
THX
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply