How to find GAP in date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to find GAP in date

Author

Topic

sudkool
Starting Member

1 Post

Posted - 2011-08-08 : 12:19:40

I am trying to build a SQL Script for following requirement:
Structure:

I have a table with 5 columns,
1. Customer Name
2. Activity Date
3. Month
4. Year
5. Revenue

Activity Date column will carry the day of month in which Customer had any activity in my system. Now, they will be some months when Customer is in active and then they come back after couple of months.
Example:
Customer Activity Date Month Year Revenue
A 4/1/2011 4 2011 $10
A 5/1/2011 5 2011 $56
A 8/1/2011 8 2011 $87
B 5/1/2011 5 2011 $124
B 7/1/2011 7 2011 $123
C 6/1/2011 6 2011 $13

As per the requirements, I want to find a gap in Activity Date (where customer is inactive) and the month they went inactive, I want to report that customer as LOST in next month and if they come back after two months count that customer in Returned Customer bucket.

So, from above examples

Customer A will be counted as NEW in Month 4, LOST in month 6 and returned in month 8.
Customer B will be counted in NEW in Month 5 and LOST in Month 6 and RETURNED in Month 7.
Customer C will be counted in NEW in Month 6.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 14:00:05

[code]
;With CTE( Customer,[Date],Cat,ActDate)
AS
(
SELECT t.Customer,f.[Date],
CASE WHEN f.[Date]=t.MinDate THEN 'NEW'
ELSE 'Unknown'
END,
t1.ActivityDate
FROM (SELECT Customer,MIN(ActivityDate) AS MinDate,MAX(ActvityDate) AS MaxDate
FROM Table
GROUP BY Customer)t
CROSS APPLY (SELECT [Date]
FROM dbo.CalendarDate(t.MinDate,t.MaxDate,0)
WHERE day([Date])=1)f
LEFT JOIN Table t1
ON t1.Customer=c.Customer
AND t.ActivityDate= c.[Date]
)

SELECT c.Customer,
c.Date,
CASE WHEN c.Cat='Unknown' AND c1.ActDate IS NULL THEN 'LOST'
WHEN c.Cat='Unknown' AND c2.ActDate IS NULL THEN 'RETURNED',
ELSE c.Cat
END
FROM CTE c
OUTER APPLY (SELECT TOP 1 ActDate
FROM CTE
WHERE Customer=c.Customer
AND [Date] > c.[Date]
ORDER BY [Date] ASC)c1
OUTER APPLY (SELECT TOP 1 ActDate
FROM CTE
WHERE Customer=c.Customer
AND [Date] < c.[Date]
ORDER BY [Date] DESC)c2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-09 : 00:39:46

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data (you did not), avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names (you do not). Please tell us if you can change the DDL.

>> I have a table with 5 columns, <<

NO. You have a table with three columns. Your vague narrative incorrectly split out the year and month (MONTH and YEAR are not only vague, they are reserved words in SQL). That creates redundancy. Your vague narrative incorrectly used a $ in a non-text column.

CREATE TABLE Cusortmer_Activity
(customer_name VARCHAR(25) NOT NULL
activity_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (customer_name, activity_date),
sales_revenue_amt DECIMAL (12,2) NOT NULL
CHECK (sales_revenue_amt >=0.00));

INSERT INTO Customer_Activity
VALUES
('A', '2011-04-01', 10.00),
('A', '2011-05-01', 56.00),
('A', '2011-08-01', 87.00),
('B', '2011-05-01', 124.00),
('B', '2011-07-01', 123.00),
('C', '2011-06-01', 1.00);

populate a table with the desired time slots, thus:

CREATE TABLE ReportPeriods
(report_period_name CHAR(10) NOT NULL PRIMARY KEY,
period_start_date DATE NOT NULL,
period_end_date DATE NOT NULL,
period_seq INTEGER NOT NULL,
CHECK (period_start_date <= period_end_date));

Then do your base query and put it in a VIEW that you can use for many reports. This is another part of an SQL mindset; build a tool kit instead of doing single tasks and re-inventing the wheel over and over.

CREATE VIEW Month_Activity_Summary
(customer_name, report_period_name, period_seq, activity_cnt)
AS
SELECT A.customer_name, R.report_period_name, R.period_seq, COUNT(*) AS activity_cnt
FROM ReportPeriods AS R
LEFT OUTER JOIN
Customer_Activity AS A
ON A.activity_date BETWEEN R.period_start_date AND R.period_end_date
GROUP BY A.customer_name, R.report_period_name;

The period_seq is a sequential number over the range of the whole table to Julianize the periods.

I like the MySQL trick for months and years that follows he ISO-8601 format. "yyyy-mm-00" has a double zero day, and "yyyy-00-00" is the year with the double zero month and double zero day.

>> Activity Date column will carry the day of month in which Customer had any activity in my system. Now, they will be some months when Customer is in active and then they come back after couple of months. <<

SELECT MAS1.customer_name, MAS1.report_period_name,
CASE WHEN MAS1.activity_cnt = 0
THEN 'LOST'
WHEN MAS1.activity_cnt > 0 AND MAS2.activity_cnt = 0
THEN 'RETURN'
ELSE 'NEW' END
AS month_activity_status
FROM Month_Activity_Summary AS MAS!,
Month_Activity_Summary AS MAS2
WHERE MAS1.customer_name = MAS2.customer_name
AND MAS1.period_seq = (MAS2.period_seq -1);

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources