[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Deteremine End Date of Promotion

Posted on 2011-02-22
5
Medium Priority
?
361 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: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 61

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 61

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 61

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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

873 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