Page 1 of 1
Query for total pans taken by month
Posted: Thu Mar 20, 2014 5:16 am
by Tom Zaccaria
Need some help with a query for the number of pans, D0330, taken for a year totaled by month.
Thanks
drtmz
Re: Query for total pans taken by month
Posted: Thu Mar 20, 2014 5:36 am
by 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
Re: Query for total pans taken by month
Posted: Thu Mar 20, 2014 6:14 am
by KevinRossen
Tom Zaccaria wrote: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;
Re: Query for total pans taken by month
Posted: Thu Mar 20, 2014 8:21 am
by Tom Zaccaria
Kevin,
That is exactly what I was looking for.
Thank you,
drtmz
Re: Query for total pans taken by month
Posted: Thu Mar 20, 2014 9:36 am
by KevinRossen
Tom Zaccaria wrote: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;
Re: Query for total pans taken by month
Posted: Thu Mar 20, 2014 4:12 pm
by Tom Zaccaria
That is even better. I'm gonna have fun with this new report.
Thanks again,
drtmz
Re: Query for total pans taken by month
Posted: Sat Mar 22, 2014 3:01 am
by 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
Re: Query for total pans taken by month
Posted: Mon Mar 24, 2014 7:50 am
by KevinRossen
Tom Zaccaria wrote: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?
Re: Query for total pans taken by month
Posted: Tue Mar 25, 2014 2:58 am
by Tom Zaccaria
Got it. I was changing both tables. Once I changed the field in t1 only it works likes a charm.
Thanks
drtmz
Re: Query for total pans taken by month
Posted: Thu Mar 27, 2014 2:56 am
by 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