Filling missing data into an array

[knox203]

Hello all, I'm not sure if this is better to solve with PHP or my MSSQL query, but here's what I'm working with:

 

I'm pulling my data out of my database with this query:

SELECT		SUM(O.AmountCharged) AS Amount, 
			right('00'+convert(varchar(4),datepart(year,O.OrderDate)),2) As Year, 
			right('00'+convert(varchar(2),datepart(month,O.OrderDate)),2) as Month
FROM		  dbo.OrderMain O INNER JOIN
	     dbo.Customer C ON O.CustomerID = C.CustomerID
WHERE	 ( C.CustomerCode LIKE 'XXXXXX' ) AND
		( O.OrderStatus <> 'x' ) AND
		( O.OrderDate >= '04/01/08' AND 
		O.OrderDate <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'04/01/09')+1,0)) )
GROUP BY 	right('00'+convert(varchar(4),datepart(year,O.OrderDate)),2), right('00'+convert(varchar(2),datepart(month,O.OrderDate)),2)
ORDER BY 	right('00'+convert(varchar(4),datepart(year,O.OrderDate)),2), right('00'+convert(varchar(2),datepart(month,O.OrderDate)),2)

 

This returns my data that looks like:

 

Amount  Year  Month
92.40	08	04
32.34	08	05
140.82	08	06
66.08	08	07
173.58	08	08
168.95	08	10
31.89	08	11
57.68	08	12
58.03	09	03

 

As you can see in the month column in the results, it's missing september (09). I'd like to fill that gap inside my PHP array with the missing month and a $0 amount, so the data would look like:

 

Array
(
    [0] => Array
        (
            [Amount] => 32.34
            [Month] => 05
            [Year] => 08
        )

    [1] => Array
        (
            [Amount] => 140.82
            [Month] => 06
            [Year] => 08
        )

    [2] => Array
        (
            [Amount] => 66.08
            [Month] => 07
            [Year] => 08
        )

    [3] => Array
        (
            [Amount] => 173.58
            [Month] => 08
            [Year] => 08
        )
    [4] => Array
        (
            [Amount] => 0
            [Month] => 09
            [Year] => 08
        )
    [5] => Array
        (
            [Amount] => 168.95
            [Month] => 10
            [Year] => 08
        )

    [6] => Array
        (
            [Amount] => 31.89
            [Month] => 11
            [Year] => 08
        )

    [7] => Array
        (
            [Amount] => 57.68
            [Month] => 12
            [Year] => 08
        )

    [8] => Array
        (
            [Amount] => 58.03
            [Month] => 03
            [Year] => 09
        )

)

 

These result vary greatly, sometimes an account will be missing multiple months in a row... so I've been trying to figure out a solution that will recognize this dynamically. Anybody got any ideas?

 

Thanks!!

- Adam