Solved

SQL Query Running Total using dates and criteria

Posted on 2014-09-18
27
206 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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
 

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:Scott Pletcher
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Log size 3 20
SQL Recursion schedule 13 19
interpreting data from function COLUMNS_UPDATED 2 18
SQL Database Restore 2008 R2 1 13
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

820 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