?
Solved

Deteremine End Date of Promotion

Posted on 2011-02-22
5
Medium Priority
?
359 Views
Last Modified: 2012-06-27
I have a report I have to create on how many calls we recieved for each promotion. We have a Promotion table that stores the Promotion Name, Promotion Start Date, and Phone Number.  (No End Date)

We need to link this to the 'Call Log' table that only has the Date of the Call, and the Phone Number.

Normally, we could join on the phone number as that is unique for the Promotion when the promotion is active and get the count of how many calls were made. However, once the promotion is over the phone number will be used for another Promotion and this phone number could be used multiple times in a year.

Without an End Date in the Promotion table, I can't do a join using a date range between start and end date to get the number of calls from the 'Call Log' table and I am looking to see if anyone can tell me if there is a way to determine the End date of the promotion.  The key of the Promotion table is the Start Date and Phone number for each Promotion. What I need to do is use the Start date of the next promotion that uses that phone number as the End date for the previous promotion. The problem I can't overcome is if this phone number is used multiple times, how do I get the end date?

Hope this is clear...
0
Comment
Question by:JS56
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34953852

I'm not sure this is possible with your current design.
You need a PromotionID field in both tables to link the correct data

You can not use the Start Date as the key and the start date in itself will not march the correct record in the call log anyway so that does not help much.

Your best bet is to redesign your tables by adding the appropriate key fields

But wait and see if someone has other ideas
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 34953887
use this query, or better save it as a view

--create view v_promotions as
with p as (
select StartDate, PhoneNumber, row_number() over (partition by PhoneNumber order by StartDate) rn
from Promotion
)
select p1.PhoneNumber, p1.StartDate, p2.StartDate as EndDate
from p pa left join on p p2 on p1.rn+1=p2.rn and p1.PhoneNumber=p2.PhoneNumber

then use this in your other query (this query will give you start & enddate)
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 34953937
typo above, fix is below:

here is a sample

with p as (
select StartDate, PhoneNumber, row_number() over (partition by PhoneNumber order by StartDate) rn
from (
select '123456' PhoneNumber, GETDATE()-30 StartDate
union
select '123456' PhoneNumber, GETDATE()-10 StartDate
union
select '123457' PhoneNumber, GETDATE()-45 StartDate
) as Promotion
)
select p1.PhoneNumber, p1.StartDate, p2.StartDate as EndDate
from p p1 left join p p2 on p1.rn+1=p2.rn and p1.PhoneNumber=p2.PhoneNumber

PhoneNumber      StartDate      EndDate
123456      2011-01-23 12:57:26.303      2011-02-12 12:57:26.303
123456      2011-02-12 12:57:26.303      NULL
123457      2011-01-08 12:57:26.303      NULL
--create view v_promotions as
with p as (
select StartDate, PhoneNumber, row_number() over (partition by PhoneNumber order by StartDate) rn
from Promotion
)
select p1.PhoneNumber, p1.StartDate, p2.StartDate as EndDate
from p p1 left join p p2 on p1.rn+1=p2.rn and p1.PhoneNumber=p2.PhoneNumber

Open in new window

0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34954007
Try the following. The End Date is the day prior to the next promotion start date, also If it is the latest promotion the query returns 'Today' as the end date, which should make it easier to join with your calls table later.

SELECT     p1.Phone, p1.StartDate, dateadd(day, -1, ISNULL(MIN(p2.StartDate), GETDATE())) AS EndDate
FROM         Promotion AS p1 LEFT OUTER JOIN
                      Promotion AS p2 ON p2.Phone = p1.Phone AND p2.StartDate > p1.StartDate
GROUP BY p1.Phone, p1.StartDate

Open in new window

0
 
LVL 57

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 34954110
if you wish, we can use currentdate for last record instead of null


--create view v_promotions as
with p as (
select StartDate, PhoneNumber, row_number() over (partition by PhoneNumber order by StartDate) rn
from Promotion
)
select p1.PhoneNumber, p1.StartDate, isnull(p2.StartDate,GETDATE()) as EndDate
from p p1 left join p p2 on p1.rn+1=p2.rn and p1.PhoneNumber=p2.PhoneNumber

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question