Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Deteremine End Date of Promotion

Posted on 2011-02-22
5
Medium Priority
?
360 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 60

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 60

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 60

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

618 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