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 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 ;