0

I have the following table data.

CREATE TABLE [dbo].[Accounts](
    [AccountID] [int] IDENTITY(1,1) NOT NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL
)

INSERT INTO [dbo].[Accounts]
       ([StartDate]
       ,[EndDate])
 VALUES
      ('01/01/2012'  ,'02/01/2012'),
      ('01/06/2012' ,'07/01/2012'),
      ('01/08/2012'  ,'11/01/2012'),
      ('01/11/2012','01/01/2013'),
      ('02/07/2012' ,'01/01/2013'),
      ('04/01/2012' ,'01/01/2013'),
      ('06/01/2012'  ,'01/01/2013'),
      ('09/01/2012' ,'01/01/2013'),
      ('11/01/2012' ,'01/01/2013'),
      ('12/01/2012' ,'01/01/2014'),
      ('01/01/2013'  ,'02/01/2014'),
      ('01/06/2013' ,'07/01/2014'),
      ('01/08/2013'  ,'11/01/2014'),
      ('01/11/2013','01/01/2014'),
      ('02/07/2013' ,'01/01/2014'),
      ('04/01/2013' ,'01/01/2014'),
      ('06/01/2013'  ,'01/01/2014'),
      ('09/01/2013' ,'01/01/2014'),
      ('11/01/2013' ,'01/01/2014'),
      ('12/01/2013' ,'01/01/2015'),
      ('01/01/2014'  ,'02/01/2015'),
      ('01/06/2014' ,'07/01/2015'),
      ('01/08/2014'  ,'11/01/2015'),
      ('01/11/2014','01/01/2015'),
      ('02/07/2014' ,'01/01/2015'),
      ('04/01/2014' ,'01/01/2015'),
      ('06/01/2014'  ,'01/01/2015'),
      ('09/01/2014' ,'01/01/2015'),
      ('11/01/2014' ,'01/01/2015'),
      ('12/01/2014' ,'01/01/2015'),
      ('01/01/2014'  ,'02/01/2015'),
      ('01/06/2014' ,'07/01/2015'),
      ('01/08/2014'  ,'11/01/2015'),
      ('01/11/2014','01/01/2015'),
      ('02/07/2014' ,'01/01/2015'),
      ('04/01/2013' ,'01/01/2014'),
      ('06/01/2013'  ,'01/01/2014'),
      ('09/01/2013' ,'01/01/2014'),
      ('11/01/2013' ,'01/01/2014'),
      ('12/01/2013' ,'01/01/2015')


SELECT datename(month, [StartDate])+' '+datename(year, [StartDate]) AS 'Start Date',
       COUNT([AccountID]) AS 'Accounts by Month'
FROM [dbo].[Accounts]
GROUP BY datename(month, [StartDate])+' '+datename(year, [StartDate]);

enter image description here

I need two queries to get the running counts grouped by "Year-month". Result will have two columns "Month-Year" and "Running Count of Accounts".

  1. Running count of Accounts grouped by StartDate( Year,Month Level)
  2. Running count of Accounts taking EndDate into consideration, meaning any accounts that has EndDate past the StartDate should not be counted in the running count, again grouped by StartDate( Year,Month Level)

I would like the solution in T-SQL, HiveQL or even using PySpark DataFrames API.

Tronald Dump
  • 1,300
  • 3
  • 16
  • 27
  • What can I do if someone marked as duplicate and it is not even close to a duplicate? – Tronald Dump May 14 '17 at 14:18
  • You could start by clarifying the question: You provide sample data and a query, an image (frowned upon) with something else, and express a _need_ for two queries. What results do you expect for the two queries? What _needs_ does the alleged duplicate fail to meet? – HABO May 14 '17 at 14:30
  • @TronaldDump Your question is asking how to calculate a running total; the duplicate is asking how to calculate a running total. Yes it's not and _exact_ duplicate; but stackoverflow is not a free code writing service. There are 12 answers on the duplicate that you can study and try to adapt to your specific needs. If you apply yourself, you're likely to succeed. But you could get stuck on some other aspect of your problem; e.g. handling the dates. But that would be an entirely different question, which you would be able to ask with more specific information. – Disillusioned May 16 '17 at 07:26

0 Answers0