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

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/0开发者_StackOverflow社区2/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 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 ;

---

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;

---

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]