Solved

SQL Query Running Total using dates and criteria

Posted on 2014-09-18
27
201 Views
Last Modified: 2014-10-02
Hi there,

I am trying to create a running total of existing customers by month and year. Each customer record has a start_date and an end_date.

I have managed to do this using the below query:

select 
  CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd')) as StartDate,
  count(ID) as TotalCustomers,
  sum(count(ID)) over (
    order by CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd'))
  ) as RunningTotal
from tblCustomers
group by CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd'))
order by CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd'));

Open in new window


This is fine however I would like to not count the customers who have an end_date value associated with them from the TotalCustomers monthly count as illustrated in the attached excel sample.

Could someone please kindly show me how I can achieve this using a query or stored proc?

I hope this makes sense and believe some kind of join would be appropriate but not sure how to do it?

Hope this makes sense and appreciate the help as always.
Result.xls
0
Comment
Question by:databarracks
  • 10
  • 8
  • 8
  • +1
27 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40329937
sum(case end_date when null then 0 else 1 end)
0
 

Author Comment

by:databarracks
ID: 40329940
Hi Phillip,

Thank you for your response, however your method will eliminate the count of customers as per this part of my query:

  sum(count(ID)) over (
    order by CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd'))
  ) as RunningTotal

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40329944
Doesn't this work?

select
  CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd')) as StartDate,
  count(ID) as TotalCustomers,
  sum(count(ID)) over (
    order by CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd'))
  ) as RunningTotal,
  sum(case end_date when null then 0 else 1 end) as TotalCustomersWithNoEndDate
from tblCustomers
group by CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd'))
order by CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd'));
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40329945
Include HAVING clause right after the GROUP BY clause.
HAVING ISNULL(end_date,'') = ''

Open in new window

OR
HAVING end_date IS NULL

Open in new window

0
 

Author Comment

by:databarracks
ID: 40329946
Hi Ganapathi,

I get "end_date is not contained in either an aggregate function....etc"
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40329948
Then try adding WHERE clause and include it there.
0
 

Author Comment

by:databarracks
ID: 40329951
Hi Phillip,

Your second approach is fine however it doesn't retain the running total if that makes sense. I need to retain the running total of customers. IF a customer has an end_date they need to be taking off the totalcustomers running total.

If you have a look at my spreadsheet you will understand the workings. Again I appreciate your help on this very much
0
 

Author Comment

by:databarracks
ID: 40329958
So all customers will always have a start date but not all customers will have an end date. So all customers would need to be accounted for somewhere in the count as they did exist as a customer. But those that fall away in their specific month need to be deducted from the total customers count at that point?

Hope that makes sense??
0
 

Author Comment

by:databarracks
ID: 40329959
Hi Ganapathi,

If I add the where clause then it won't count the customer when they started. I still need to count them at the point the start and deduct them when they end
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40329968
Makes sense.

I have included a CASE statement inside SUM function. Check if it works.
select 
  CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd')) as StartDate,
  count(ID) as TotalCustomers,
  sum(count(CASE
               WHEN CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [end_date]), 'yyyy-MM-dd')) = CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd'))
                  THEN 0
               ELSE 1
            END)) over (
    order by CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd'))
  ) as RunningTotal
from tblCustomers
group by CONVERT(datetime, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd'))

Open in new window

0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40329979
Assuming the answers in your spreadsheet are incorrect (e.g. you appear to have only one new customer according to the results in December 2013, but there are two - ID 2 and ID 9), here is the code:

with C as
(
select ID, Name,
CONVERT(date, FORMAT(DATEADD(dd, -(DAY([start_date]) - 1), [start_date]), 'yyyy-MM-dd')) as Start_month,
CONVERT(date, FORMAT(DATEADD(dd, -(DAY([end_date]) - 1), [end_date]), 'yyyy-MM-dd')) as End_month
from tblCustomers
group by id, name, start_date, end_date),
M as
(
select Start_month as myMonth
from C
where Start_month is not null
union
select End_month
from C
where End_month is not null)
, T as
(select myMonth, (select count(ID) from C where C.Start_month = M.myMonth) as NewCustomersStarting,
  (select count(ID) from C where C.End_month = M.myMonth) as NewCustomersEnding
from M
group by M.myMonth)
select myMonth, NewCustomersStarting, NewCustomersEnding,
SUM(NewCustomersStarting) over(order by myMonth rows between unbounded preceding and current row) as CustomersStarting,
SUM(NewCustomersEnding) over(order by myMonth rows between unbounded preceding and current row) as CustomersEnding,
SUM(NewCustomersStarting) over(order by myMonth rows between unbounded preceding and current row) -
SUM(NewCustomersEnding) over(order by myMonth rows between unbounded preceding and current row) as CurrentCustomers
from T
group by myMonth, NewCustomersStarting, NewCustomersEnding
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40329981
Darabarracks correct as per his statement. It is like YearToDate cumulative. Count it from the beginning till Dec 13, you will get it.

Result excel is correct.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40329984
March 12 = 1 - that's ID 4
October 12 = 2 - that's also ID 6
Mar 13 = 3 - that's also ID 3
Dec 13 = 4 - that's also one other ID. But both ID 2 and ID 9 start in December 2013. Therefore, it should be 5.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:databarracks
ID: 40329994
Hi guys,
Will test both approaches in a second and let you know of the results. If there is a error in the spreadsheet then it's simply my manual calculations gone wrong. Importantly it's the concept of the running total that matters. Add the customer to count and remove from count when end date is available for month. Again please bear with me while I test this
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40329995
Sure.

Make sure you are clear whether to include the Customers having end_date falling anywhere in the particular month.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40329996
Ganapathi, unfortunately your code would miss July and September 2014, which are not start date but only end dates.
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40330008
This statement "But those that fall away in their specific month need to be deducted from the total customers count at that point" mentioned by Databarracks. That's what i wrote to exclude them.

Let's see what he comes up with.
0
 

Author Comment

by:databarracks
ID: 40330009
Would it be easier if we include all months for every year starting from a specific year perhaps? To alleviate Phillips point?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40330015
You can do. My solution includes only those months with start OR finish dates, as per your spreadsheet.
0
 

Author Comment

by:databarracks
ID: 40330024
Ok Phillip

The spreadsheet was solely a copy of my initial results from my initial query. If I could show all months elapsed say from 01/01/2003 and have my running totals then it would be better.  Sorry to be a pain but could you amend your sample code to show this
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40330028
Having seen your initial code, I'm sure you can do it. Just change the following bit:

M as
(
select Start_month as myMonth
from C
where Start_month is not null
union
select End_month
from C
where End_month is not null)
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40330062
See if this works. Might have syntax errors. Please correct it.
CREATE PROCEDURE dbo.SP_YTD_CUST_COUNT
AS
BEGIN
   CREATE TABLE #TMP_YEAR
   (
      YEAR DATE,
      RUNNNING_COUNT INT
   )

   DECLARE @COUNTER INT,
           @START INT
   SET @COUNTER = DATEDIFF(MM,'01/01/2003',GETDATE()
   SET @START = 1   

   WHILE(@COUNTER > @START)
   BEGIN
      INSERT INTO #TMP_YEAR SELECT DATEADD(MM,@START,'01/01/2003')
      SELECT @START = @START + 1
   END   
   

   SELECT
      YEAR,
      COUNT(1) AS RUNNING_COUNT
   FROM
      #TMP_YEAR    TMP,
      tblCustomers CUS
   WHERE
      YEAR BETWEEN start_date and end_date
   GROUP BY
      YEAR
END
GO

Open in new window

0
 

Author Comment

by:databarracks
ID: 40330080
Hi Ganapathi,
Thanks for the proc, how would I apply this to my query though??
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40330093
Execute this as an onetime activity in your database. Without this, you may not be able to get the months that do not have any customers.
CREATE TABLE TMP_YEAR
   (
      YEAR DATE,
      RUNNNING_COUNT INT
   )

   DECLARE @COUNTER INT,
           @START INT
   SET @COUNTER = DATEDIFF(MM,'01/01/2003',GETDATE()
   SET @START = 1   

   WHILE(@COUNTER > @START)
   BEGIN
      INSERT INTO #MP_YEAR SELECT DATEADD(MM,@START,'01/01/2003')
      SELECT @START = @START + 1
   END

Open in new window

Use the below instead of the one you have.
SELECT
      YEAR,
      COUNT(1) AS RUNNING_COUNT
   FROM
      #TMP_YEAR    TMP,
      tblCustomers CUS
   WHERE
      YEAR BETWEEN start_date and end_date
   GROUP BY
      YEAR

Open in new window

0
 

Author Comment

by:databarracks
ID: 40330114
Hi Phillip,

I have sampled your approach and it is very good indeed, it provides me with a lot of detail of which I was planning to use later. This is definitely the answer to my problems, although I would like to show all months as originally requested. You gave me some sample code:

M as
(
select Start_month as myMonth
from C
where Start_month is not null
union
select End_month
from C
where End_month is not null) 

Open in new window


But I don't know how to apply it?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40330120
That's the current code. You just need to replace the bit between the brackets to reference a table (temporary or otherwise) which has all of the months between min(start_month) and max(end_month).
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40330674
I don't think all of that complexity is required to do this.  Please try the code below.


-- controlling variable/parameter values
DECLARE @start_date datetime
DECLARE @end_date datetime

SET @start_date = '20120131' --<<-- set to desired starting month (day doesn't matter, will be forced to 1st of month)
SET @end_date = '20140901' --<<-- set to desired ending month (day doesn't matter, will be forced to be entire month)


------------------------------------------------------------------------------------------------------------------------

--main code:
--  cte handles 100 months = ~8 yrs: if you need a longer time frame, increase # of cte rows.
;WITH
cteTally10 AS (
    SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
)
SELECT
    CONVERT(varchar(10), Start_Month, 101) AS Start_Month,
    New_Customers,
    Total_Customers,
    Expiring_Customers
FROM (
    SELECT
        month_ranges.Start_Month,
        SUM(CASE WHEN c.START_DATE >= month_ranges.Start_Month AND c.START_DATE < month_ranges.End_Month
            THEN 1 ELSE 0 END) AS New_Customers,
        COUNT(c.ID) AS Total_Customers,
        SUM(CASE WHEN c.END_DATE >= month_ranges.Start_Month AND c.END_DATE < month_ranges.End_Month
            THEN 1 ELSE 0 END) AS Expiring_Customers
    FROM cteTally100 t
    CROSS APPLY (
        SELECT
            DATEADD(MONTH, t.tally, DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date), 0)) AS Start_Month,
            DATEADD(MONTH, t.tally + 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date), 0)) AS End_Month
    ) AS month_ranges
    LEFT OUTER JOIN tblCustomers c ON
        c.START_DATE < month_ranges.End_Month AND
        ISNULL(c.END_DATE, '99991231') >= month_ranges.Start_Month
    WHERE
        t.tally BETWEEN 0 AND DATEDIFF(MONTH, @start_date, @end_date)
    GROUP BY
        month_ranges.Start_Month
) AS base_query
ORDER BY
    base_query.Start_Month
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 40
separate column 24 20
Numeric sequence in SQL 14 37
Updating ms sql with special characters 8 23
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 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

19 Experts available now in Live!

Get 1:1 Help Now