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

Answer 1

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 ;

Answer 2

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;