SQL Query Running Total using dates and criteria

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
databarracksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
sum(case end_date when null then 0 else 1 end)
0
databarracksAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GanapathiFacets DeveloperCommented:
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
databarracksAuthor Commented:
Hi Ganapathi,

I get "end_date is not contained in either an aggregate function....etc"
0
GanapathiFacets DeveloperCommented:
Then try adding WHERE clause and include it there.
0
databarracksAuthor Commented:
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
databarracksAuthor Commented:
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
databarracksAuthor Commented:
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
GanapathiFacets DeveloperCommented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GanapathiFacets DeveloperCommented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
databarracksAuthor Commented:
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
GanapathiFacets DeveloperCommented:
Sure.

Make sure you are clear whether to include the Customers having end_date falling anywhere in the particular month.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Ganapathi, unfortunately your code would miss July and September 2014, which are not start date but only end dates.
0
GanapathiFacets DeveloperCommented:
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
databarracksAuthor Commented:
Would it be easier if we include all months for every year starting from a specific year perhaps? To alleviate Phillips point?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You can do. My solution includes only those months with start OR finish dates, as per your spreadsheet.
0
databarracksAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
GanapathiFacets DeveloperCommented:
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
databarracksAuthor Commented:
Hi Ganapathi,
Thanks for the proc, how would I apply this to my query though??
0
GanapathiFacets DeveloperCommented:
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
databarracksAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
Scott PletcherSenior DBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.