Solved

Deteremine End Date of Promotion

Posted on 2011-02-22
5
345 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
  • 3
5 Comments
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

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 51

Expert Comment

by:HainKurt
Comment Utility
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 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
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 51

Accepted Solution

by:
HainKurt earned 500 total points
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now