SQL genius required! A different missing months issue between two years

kiwimediapro · #1 (**permalink**) 07-14-11, 20:34

Hello all,

I have tried various queries to many to list here but currently have this as the start point:

Code:

SELECT
						Sum(crm_sales.totalprice) AS totalprice,
						MONTHNAME(crm_sales.date) AS mnth,
						YEAR(crm_sales.date) AS `year`,
						Count(crm_sales.totalprice) AS sales,
						FROM `crm_sales`
						WHERE `date` BETWEEN '2009-08-01' AND '2010-04-08'
						GROUP BY crm_sales.date

Firstly NOTE it runs between 2 years. I get this:
TOTALPRICE MONTH YEAR SALES
11248.54 August 2009 48
3989.14 August 2009 20
4701.97 August 2009 25
4940.00 August 2009 24
2208.00 August 2009 10
8090.53 August 2009 39
3532.54 August 2009 17
4193.81 August 2009 20
4072.98 August 2009 20
4658.39 August 2009 24
13256.39 August 2009 64
4568.32 August 2009 23
4806.71 August 2009 23
6049.95 August 2009 30
4037.00 August 2009 19
8255.88 August 2009 39
3420.26 August 2009 19
4770.00 August 2009 24
4798.26 August 2009 23
2976.74 August 2009 15
210.00 August 2009 1
12440.91 August 2009 61
4200.00 September 2009 20
4000.00 September 2009 20
6031.92 September 2009 30
4541.00 September 2009 23
6450.00 September 2009 30
6497.00 September 2009 31
4063.00 September 2009 22
3840.00 September 2009 19
3265.00 September 2009 16
8651.00 September 2009 42
5040.00 September 2009 24
5650.55 September 2009 26
5148.00 September 2009 24
4465.00 September 2009 21
5921.00 September 2009 28
5239.00 September 2009 25
6962.00 September 2009 34
6561.00 September 2009 32
5670.00 September 2009 28
9565.00 September 2009 46
3205.00 September 2009 15
6617.00 September 2009 32
7074.00 October 2009 33
3521.00 October 2009 19
12372.00 October 2009 62
6025.97 October 2009 27
6744.00 October 2009 31
6009.32 October 2009 30
4320.00 October 2009 22
9891.00 October 2009 50
5178.30 October 2009 27
7390.98 October 2009 35
4520.00 October 2009 22
4163.22 October 2009 22
7267.16 October 2009 35
5210.61 October 2009 27
5923.72 October 2009 29
4267.56 October 2009 19
4630.00 October 2009 23
12820.32 October 2009 62
5298.24 October 2009 26
5060.00 October 2009 26
6949.00 October 2009 33
8952.23 November 2009 42
4644.70 November 2009 23
3289.00 November 2009 17
4706.29 November 2009 23
1434.99 November 2009 7
7347.80 November 2009 36
3270.00 November 2009 17
3717.95 November 2009 17
4113.00 November 2009 20
2875.77 November 2009 14
7285.15 November 2009 35
4893.00 November 2009 23
6479.43 November 2009 31
3463.36 November 2009 17
5949.45 November 2009 29
8079.16 November 2009 39
5627.33 November 2009 28
3588.18 November 2009 17
4420.00 November 2009 22
3401.00 November 2009 16
10277.26 November 2009 51
5171.00 December 2009 25
2983.00 December 2009 16
2980.23 December 2009 17
5459.54 December 2009 26
7914.96 December 2009 38
5440.50 December 2009 28
4139.29 December 2009 20
4200.00 December 2009 20
3959.31 December 2009 18
6788.26 December 2009 32
4506.51 December 2009 23
6200.00 December 2009 30
4706.00 December 2009 24
3054.12 December 2009 17
10928.04 December 2009 52
6683.16 December 2009 31
4903.89 December 2009 23
2420.00 December 2009 12
6620.00 December 2009 32
5139.00 December 2009 25
2520.00 December 2009 12
6220.00 January 2010 32
10699.07 January 2010 57
5900.00 January 2010 30
5493.16 January 2010 26
4743.98 January 2010 22
10140.22 January 2010 50
4826.45 January 2010 24
5628.02 January 2010 27
3717.03 January 2010 17
6229.24 January 2010 31
9024.16 January 2010 44
6829.00 January 2010 34
4995.85 January 2010 24
5501.28 January 2010 26
4320.78 January 2010 21
7981.03 January 2010 38
5929.62 January 2010 30
6025.64 January 2010 29
4473.70 January 2010 23
5173.37 January 2010 24
16836.90 February 2010 82
4626.70 February 2010 22
4591.04 February 2010 22
3912.27 February 2010 18
12642.76 February 2010 58
5605.51 February 2010 29
6336.37 February 2010 32
4838.82 February 2010 25
4218.46 February 2010 22
9566.09 February 2010 49
5359.00 February 2010 26
4217.90 February 2010 22
5371.23 February 2010 28
4620.00 February 2010 22
9634.88 February 2010 47
5410.31 February 2010 27
5312.55 February 2010 25
6184.82 February 2010 28
7467.11 February 2010 35
12713.32 March 2010 63
4771.00 March 2010 23
3799.68 March 2010 20
6100.63 March 2010 29
5664.83 March 2010 28
9287.40 March 2010 45
5709.90 March 2010 27
6300.00 March 2010 30
4526.26 March 2010 23
5491.54 March 2010 26
7812.89 March 2010 37
6336.75 March 2010 32
5805.78 March 2010 25
6379.38 March 2010 30
4533.04 March 2010 21
14003.88 March 2010 66
5702.65 March 2010 27
4373.87 March 2010 22
5333.38 March 2010 27
4730.00 March 2010 22
11504.55 March 2010 59
6125.00 March 2010 29
4356.59 March 2010 21
3866.23 April 2010 19
149.00 April 2010 1
10460.37 April 2010 54
6237.52 April 2010 31
2580.10 April 2010 13

Note that
for 2009 figures I am missing months January - July
for 2010 figures I am missing months May - December

I know that that means there is no data and I know how to fill in the missing databy using a Month table in a left outer join.

What i dont know i how to do this for each year....

Thanks

r937 · #2 (**permalink**) 07-14-11, 22:51

Quote:

Originally Posted by kiwimediapro

I know how to fill in the missing databy using a Month table in a left outer join.

could you show this query please

then i'll show you how to modify it so that it works for the years you want

kiwimediapro · #3 (**permalink**) 07-14-11, 23:27

As you requested:

Code:

DROP TABLE IF EXISTS datatbl;
CREATE TEMPORARY TABLE IF NOT EXISTS datatbl
						SELECT SUM(t1.totalprice) as totalprice,
						SUM(t1.sales) as salessubtotals, t1.`year` as `year`,
						t1.mnth as `month`
						from 
						(SELECT
						Sum(crm_sales.totalprice) AS totalprice,
						MONTHNAME(crm_sales.date) AS mnth,
						YEAR(crm_sales.date) AS `year`,
						Count(crm_sales.totalprice) AS sales,
						MONTH(crm_sales.date) AS `ordering`
						FROM `crm_sales`
						WHERE `date` BETWEEN '2009-08-01' AND '2010-04-08'
						GROUP BY crm_sales.date
						) as t1
						GROUP BY t1.`year`,t1.mnth
						ORDER BY  t1.ordering;


						SELECT m.monthname as `month` 
									,COALESCE(s.salessubtotals, 0) sales
									,COALESCE (s.`year`,2009) `year`
									,COALESCE(s.totalprice, 0) totalprice
									,CASE m.monthname
										WHEN 'January' then '1'
										WHEN 'February' then '2'
										WHEN 'March' then '3'
										WHEN 'April' then '4'
										WHEN 'May' then '5'
										WHEN 'June' then '6'
										WHEN 'July' then '7'
										WHEN 'August' then '8'
										WHEN 'September' then '9'
										WHEN 'October' then '10'
										WHEN 'November' then '11'
										WHEN 'December' then '12'
									END AS ordering
						FROM months m
						LEFT OUTER JOIN datatbl s
						on m.monthname = s.`month`
						GROUP BY m.monthname
						ORDER BY ABS(ordering) ASC;

r937 · #4 (**permalink**) 07-15-11, 06:48

this looks like it'll be pretty straightforward to fix for you

two questions first, though

1. why are you using a temporary table?

2. doesn't the month table have a month number column? could you do a SHOW CREATE TABLE for it please

kiwimediapro · #5 (**permalink**) 07-15-11, 06:53

Here is the show create table for the months:

Code:

CREATE TABLE `months` (
  `monthname` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

r937 · #6 (**permalink**) 07-15-11, 07:17

Quote:

Originally Posted by kiwimediapro

Here is the show create table for the months:

could you change it please...

Code:

DROP TABLE months;

CREATE TABLE months
( m SMALLINT NOT NULL PRIMARY KEY 
, monthname VARCHAR(9) NOT NULL
);
INSERT INTO months (monthname,m) VALUES 
 ( 'January'   , 1 )
,( 'February'  , 2 )
,( 'March'     , 3 )
,( 'April'     , 4 )
,( 'May'       , 5 )
,( 'June'      , 6 )
,( 'July'      , 7 )
,( 'August'    , 8 )
,( 'September' , 9 )
,( 'October'   , 10 )
,( 'November'  , 11 )
,( 'December'  , 12 )
;

the important part here is not only does the m column save you from that horrible CASE expression you had, but it also indexes the table

also, why the temporary table? you don't need it for anything else, do you?

the reason i ask is because you can do everything in one query, and creating a table, even a temporary one, has overhead

kiwimediapro · #7 (**permalink**) 07-15-11, 07:50

Well as you can see I am a noob with SQL and could not find a way to do it all in one query so created the temp table.... :-(

r937 · #8 (**permalink**) 07-15-11, 08:23

Quote:

Originally Posted by kiwimediapro

... could not find a way to do it all in one query so created the temp table.... :-(

not a problem, i was just axin'

Code:

SELECT years.y AS year
     , months.monthname AS month 
     , COALESCE(s.sales,0) AS sales
     , COALESCE(s.totalprice,0) AS totalprice
  FROM ( SELECT 2009 AS y
         UNION ALL
         SELECT 2010
         UNION ALL
         SELECT 2011 ) AS years
CROSS
  JOIN months 
LEFT OUTER 
  JOIN ( SELECT YEAR(crm_sales.date) AS y
              , MONTH(crm_sales.date) AS m
              , SUM(crm_sales.totalprice) AS totalprice
              , COUNT(crm_sales.totalprice) AS sales
           FROM crm_sales
          WHERE crm_sales.date BETWEEN '2009-08-01' 
                                   AND '2010-04-08'
         GROUP 
             BY y
              , m ) AS s
    ON s.y = years.y
   AND s.m = months.m
ORDER 
    BY years.y
     , months.m

kiwimediapro · #9 (**permalink**) 07-15-11, 22:37

WOw so simple.

Thank you very much!

Only thing is i may be wrong as usual it seems but this seems to fix the years does it not?

SELECT 2009 AS y
UNION ALL
SELECT 2010
UNION ALL
SELECT 2011

SO from PHP drop downs i could have dates BETWEEN '2002-08-01'
AND '2010-04-08'

r937 · #10 (**permalink**) 07-15-11, 22:46

yeah, that was just one way to approach the generation of the year numbers

replace that with this --

Code:

FROM ( SELECT DISTINCT YEAR(crm_sales.date) AS y
           FROM crm_sales
          WHERE crm_sales.date BETWEEN '2002-08-01' 
                                   AND '2010-04-08' ) AS years

you could actually tweak it further to eliminate the months not in your date range (2002 january through july, 2010 from may to december) but i wouldn't bother

kiwimediapro · #11 (**permalink**) 07-16-11, 23:53

THank you so much.

I shall endeavor to study this query so as to learn more.

Appreciate it.

IF you had a paypal account i would send you a beer.

:-)

Internet Services

Web Development

Digital Marketing

Consumer Tech