Query for total pans taken by month

[Tom Zaccaria]

Need some help with a query for the number of pans, D0330, taken for a year totaled by month.

Thanks
drtmz

[JimZ]

In the reports menu under the daily box there is a report for PROCEDURES. We just put the range we desire and then place D0330 (or whatever code we are looking for) in the "Only for procedure codes similar to" box. If the date range is last year, all pans show up individually but not totaled by month. Not exactly what you asked, but workable.


Jim

[KevinRossen]

Need some help with a query for the number of pans, D0330, taken for a year totaled by month.

Thanks
drtmz

Here you go:

Code: Select all

/* Count Various KPI Stats */
SET @FromDate='2013-02-01' , @ToDate='2014-01-31';
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1(Month VARCHAR(10) NOT NULL, PanX DOUBLE NOT NULL);
/* Get Months */
INSERT INTO t1(Month)
SELECT DATE_FORMAT(pl.ProcDate, '%b %Y') AS 'Month'
FROM procedurelog pl
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate AND pl.ProcStatus=2
GROUP BY MONTH ORDER BY pl.ProcDate;
/* PanX Count */
CREATE TABLE t2
SELECT DATE_FORMAT(pl.ProcDate, '%b %Y') AS 'Month', COUNT(pl.ProcNum) AS 'PanX'
FROM procedurelog pl
LEFT JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
WHERE pl.ProcStatus='2' AND pl.ProcDate BETWEEN @FromDate AND @ToDate AND pc.ProcCode='D0330'
GROUP BY MONTH;
UPDATE t1,t2 SET t1.PanX=t2.PanX WHERE t1.Month=t2.Month;
SELECT * FROM t1;
DROP TABLE IF EXISTS t1,t2;

[Tom Zaccaria]

Kevin,
That is exactly what I was looking for.
Thank you,
drtmz

[KevinRossen]

Kevin,
That is exactly what I was looking for.
Thank you,
drtmz

You're welcome. You could also customize the report to search for other codes. Here's a modified version showing a few other recall type procedures:

Code: Select all

SET @FromDate='2013-03-01' , @ToDate='2014-02-28';
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1(Month VARCHAR(10) NOT NULL, PanX DOUBLE NOT NULL, 4BW DOUBLE NOT NULL, 2BW DOUBLE NOT NULL, PX DOUBLE NOT NULL, PXChild DOUBLE NOT NULL);
/* Get Months */
INSERT INTO t1(Month)
SELECT DATE_FORMAT(pl.ProcDate, '%b %Y') AS 'Month'
FROM procedurelog pl
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate AND pl.ProcStatus=2
GROUP BY MONTH ORDER BY pl.ProcDate;
/* PanX Count */
CREATE TABLE t2
SELECT DATE_FORMAT(pl.ProcDate, '%b %Y') AS 'Month', COUNT(pl.ProcNum) AS 'PanX'
FROM procedurelog pl
LEFT JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
WHERE pl.ProcStatus='2' AND pl.ProcDate BETWEEN @FromDate AND @ToDate AND pc.ProcCode='D0330'
GROUP BY MONTH;
UPDATE t1,t2 SET t1.PanX=t2.PanX WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
/* 4BW Count */
CREATE TABLE t2
SELECT DATE_FORMAT(pl.ProcDate, '%b %Y') AS 'Month', COUNT(pl.ProcNum) AS '4BW'
FROM procedurelog pl
LEFT JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
WHERE pl.ProcStatus='2' AND pl.ProcDate BETWEEN @FromDate AND @ToDate AND pc.ProcCode='D0274'
GROUP BY MONTH;
UPDATE t1,t2 SET t1.4BW=t2.4BW WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
/* 2BW Count */
CREATE TABLE t2
SELECT DATE_FORMAT(pl.ProcDate, '%b %Y') AS 'Month', COUNT(pl.ProcNum) AS '2BW'
FROM procedurelog pl
LEFT JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
WHERE pl.ProcStatus='2' AND pl.ProcDate BETWEEN @FromDate AND @ToDate AND pc.ProcCode='D0272'
GROUP BY MONTH;
UPDATE t1,t2 SET t1.2BW=t2.2BW WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
/* PX Count */
CREATE TABLE t2
SELECT DATE_FORMAT(pl.ProcDate, '%b %Y') AS 'Month', COUNT(pl.ProcNum) AS 'PX'
FROM procedurelog pl
LEFT JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
WHERE pl.ProcStatus='2' AND pl.ProcDate BETWEEN @FromDate AND @ToDate AND pc.ProcCode='D1110'
GROUP BY MONTH;
UPDATE t1,t2 SET t1.PX=t2.PX WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
/* PXChild Count */
CREATE TABLE t2
SELECT DATE_FORMAT(pl.ProcDate, '%b %Y') AS 'Month', COUNT(pl.ProcNum) AS 'PXChild'
FROM procedurelog pl
LEFT JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
WHERE pl.ProcStatus='2' AND pl.ProcDate BETWEEN @FromDate AND @ToDate AND pc.ProcCode='D1120'
GROUP BY MONTH;
UPDATE t1,t2 SET t1.PXChild=t2.PXChild WHERE t1.Month=t2.Month;
SELECT * FROM t1;
DROP TABLE IF EXISTS t1,t2;

[Tom Zaccaria]

That is even better. I'm gonna have fun with this new report.
Thanks again,
drtmz

[Tom Zaccaria]

I actually expanded the report to include a lot more procedures. But to make all this even better I would like to replace Pan with 3310 in the column heading and do this with all the ADA procedure codes in the report. It would be easer to read in it's expanded form. Changing the field declarations and the variables did not work.

Is this a situation where you need double quotes (or secret double quotes, sorry Animal House was just on again,) or how can I do this?

For example;
..............3310....2751....1110
Jan 2014.....0........10.......20
Feb 2014.....4........11.......15
Mar 2014.....6........8........12

I used the dots to keep things lined up.

drtmz

[KevinRossen]

I actually expanded the report to include a lot more procedures. But to make all this even better I would like to replace Pan with 3310 in the column heading and do this with all the ADA procedure codes in the report. It would be easer to read in it's expanded form. Changing the field declarations and the variables did not work.

Is this a situation where you need double quotes (or secret double quotes, sorry Animal House was just on again,) or how can I do this?

For example;
..............3310....2751....1110
Jan 2014.....0........10.......20
Feb 2014.....4........11.......15
Mar 2014.....6........8........12

I used the dots to keep things lined up.

drtmz

I think it's generally recommended to stick with single quotes in MySQL. When you say you expanded the report did you add the extra columns into the t1 table? Are you getting an error message?

[Tom Zaccaria]

Got it. I was changing both tables. Once I changed the field in t1 only it works likes a charm.

Thanks
drtmz

[Tom Zaccaria]

It would be really helpful if the report were reversed. Since there are many, many more procedures than months it would be better if the months were columns and the procedure codes rows.
Anyone out there with input on this?

Thanks,
drtmz