Solved

Deteremine End Date of Promotion

Posted on 2011-02-22
5
354 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 52

Expert Comment

by:Huseyin KAHRAMAN
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 52

Expert Comment

by:Huseyin KAHRAMAN
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 52

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

730 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