Count number of trails and live based on a month in SQL

Question

I have the following table in my database where [start date] and [End date] data type is in datetime

CompanyID ServiceStartDat BillingStartDate

    1      03/01/2010     03/02/2010
    2      12/01/2010     12/02/2010
    3      15/02/2010     15/03/2010
    4      28/02/2010     28/03/2010
    5      04/03/2010     04/04/2010
    6      04/04/2010     04/05/2010
    7      29/04/2010     29/05/2010
    8      07/05/2010     07/06/2010
   15      30/08/2010     30/09/2010
   16      31/08/2010     31/09/2010
---------- ----------     ----------
  155      01/09/2010     01/10/2010

From the above table I want to produce the table below. I have managed to get the Trail column correctly but I am having few problems with the Live Column and after being stuck for few days I have all but given up.

The Live Column should contain the values of the Trail column in the previous month. So the Live column in February should be 2 which is the Trail Values in January. Sorry for any ambiguity

use testDB
Go
SELECT month(StartDate) as [MonthName],
Trail = COUNT(month(StartDate))
FROM mytable 
Group by month(StartDate)

I want the query to produce the following table

 MonthName   Trail    Live
 Jan          2        0
 Feb          2        2
 Mar          1        4
 Apr          2        5
 May          1        7
 Jun          0        7
 Jul          0        7
 Aug          2        9
 Sep          1       10

The live counts appear to go off track after May - shouldn't they be 8 from Jun to Aug? — martin clayton, Sep 03 '10 at 10:02

score 0 · Answer 1 · answered Oct 23 '13 at 06:05

SELECT A.[MonthName],Trail,lIVE FROM

(SELECT month(StartDate) as [MonthName], Trail = COUNT(month(StartDate)) FROM SalesInvoice Group by month(StartDate)) A INNER JOIN

(SELECT month(ENDDATE) as [MonthName], lIVE = COUNT(month(ENDDATE)) FROM SalesInvoice Group by month(ENDDATE)) B ON A.[MonthName] = B.[MonthName]

score 0 · Accepted Answer · answered Sep 03 '10 at 12:00

the best thing I have to propose is a Stored Procedure (if you MySQL engine allows them)

DELIMITER $$

DROP PROCEDURE IF EXISTS `getTrailAndLive` $$
CREATE PROCEDURE `getTrailAndLive` ()
BEGIN

  DECLARE i INTEGER;

  CREATE TEMPORARY TABLE `liveTable` (
    `liveMonthId` INTEGER UNSIGNED NOT NULL,
    `liveValue` INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY (`liveMonthId`)
  );

  SET i = 1;
  labelLoop: LOOP
    INSERT INTO `liveTable` SELECT i, COUNT(*) FROM `myTable` WHERE MONTH(startdate) < i;
    SET i = i + 1;
    IF(i < 13) THEN ITERATE labelLoop;
    END IF;
    LEAVE labelLoop;
  END LOOP labelLoop;

  SELECT MONTH(startdate) AS MonthId, COUNT(*) AS Trail, liveValue AS Live
  FROM `myTable`, `liveTable`
  WHERE liveMonthId=MONTH(startdate)
  GROUP BY MONTH(startdate);

  DROP TABLE IF EXISTS `liveTable`;

END $$

DELIMITER ;

score 0 · Answer 3 · answered Sep 03 '10 at 14:03

PierrOZ Thank you ever so much!
I have modified your code slightly and it worked a treat. Just what I was looking for

here is the final code

    use testDB
Go
CREATE PROCEDURE [dbo].[getTrailAndLive] 
AS
SET nocount ON
IF EXISTS(
SELECT name FROM [testDB]..sysobjects
WHERE name = '#liveTable' AND xtype='U') DROP TABLE [dbo].[#liveTable];

CREATE table #liveTable(
liveMonthId  int, 
liveValue  int 
 PRIMARY KEY (liveMonthId)
);   

  DECLARE @i int;
  SET @i = 1;
  WHILE @i <13 
    BEGIN
        INSERT INTO #liveTable SELECT @i, COUNT(*) FROM myTable WHERE MONTH(startDate) < @i;
        SET @i = @i + 1;
     END

  SELECT MONTH(startDate) AS MonthId, COUNT(*) AS Trail, liveValue AS Live
  FROM myTable, #liveTable
  WHERE liveMonthId=MONTH(startdate)
  GROUP BY MONTH(startDate), liveValue;

  DROP TABLE #liveTable;

you're welcome!... The WHILE is clearly more "readable" for the loop :) I just wanted to test LOOP in the same time... — pierroz, Sep 03 '10 at 16:49
pierrOz... I was able to use your solution without any problems (thank you) but I have just came across a minor issue. For example all the dates in the StartDate column might not necessarly contain all the 12 months.. i.e it might not have january, April, or August.. So, how can I best modify the above code reflect this requirements?? — ADALINA, Sep 13 '10 at 15:20

Count number of trails and live based on a month in SQL

3 Answers3