SQL Syntax
This statement works but is confusing. To simply, it was suggested to use views.
SELECT
(
SELECT MAX(WEDATE)
FROM jcost o
WHERE o.JOB_ID = j.JOB_ID
AND o.SCCODE = s.SCCODE
AND o.WEDATE = '2013-10-20'
) AS 'WEEK ENDING',
SUM(l.HOURS) AS MAN_HOURS,
FORMAT(@cost / SUM(l.HOURS), 2) 'AVG WAGE',
FORMAT(@place / SUM(l.HOURS), 0) 'LBS/MHR',
@place := (
[b] SELECT SUM(m.PLACEMENT)
FROM materials m
INNER JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
INNER JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
INNER JOIN job j ON m.JOB_ID = j.JOB_ID
WHERE m.JOB_ID = 7398
AND (t.SCTYPE_ID = 1 OR t.SCTYPE_ID = 7)
AND m.DATE_PLACE BETWEEN '2013-10-14' AND '2013-10-20'
AND c.TM = 'N'
AND (j.DELETED != 'Y'OR j.DELETED IS NULL)
AND (c.DELETED != 'Y'OR c.DELETED IS NULL)
AND (s.DELETED != 'Y'OR s.DELETED IS NULL)
AND (t.DELETED != 'Y'OR t.DELETED IS NULL)
AND (m.DELETED != 'Y'OR m.DELETED IS NULL)
) 'PLACED',
[/b] @cost := (
SELECT SUM(WJCOST)
FROM jcost o, job j, sccode s
WHERE o.JOB_ID = j.JOB_ID
AND o.SCCODE = s.SCCODE
AND o.WEDATE = '2013-10-20'
AND (j.DELETED != 'Y'OR j.DELETED IS NULL)
AND (c.DELETED != 'Y'OR c.DELETED IS NULL)
AND (s.DELETED != 'Y'OR s.DELETED IS NULL)
AND (o.DELETED != 'Y'OR o.DELETED IS NULL)
) 'COST',
FORMAT(@place * j.UNITCOST, 0) 'EST COST',
FORMAT((@place * j.UNITCOST) - @cost, 0) 'GAIN (LOSS)',
FORMAT(@cost / @place, 4) 'UNIT COST'
[b]FROM labor l
INNER JOIN job j ON j.JOB_ID = l.JOB_ID
INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
INNER JOIN `release` r ON r.RELEASE_ID = j.JOB_ID
INNER JOIN materials m ON m.RELEASE_ID = r.RELEASE_ID
WHERE j.JOB_ID = 7398
AND l.DATE_WORK BETWEEN '2013-10-14' AND '2013-10-20'
AND t.SCTYPE_ID IN (1, 7)
AND (l.DELETED != 'Y'OR l.DELETED IS NULL)
AND (j.DELETED != 'Y'OR j.DELETED IS NULL)
AND (c.DELETED != 'Y'OR c.DELETED IS NULL)
AND (s.DELETED != 'Y'OR s.DELETED IS NULL)
AND (t.DELETED != 'Y'OR t.DELETED IS NULL)
AND (m.DELETED != 'Y'OR m.DELETED IS NULL)
[/b]GROUP BY j.JOB_ID;------------------------------------------------------------------------------------------------------------------------------
DELIMITER $$
USE `jds`$$
DROP VIEW IF EXISTS `vHOURS`$$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vHOURS` AS
SELECT
SUM(`l`.`HOURS`) AS `SUM(l.HOURS)`
FROM ((((((`labor` `l`
JOIN `job` `j`
ON ((`j`.`JOB_ID` = `l`.`JOB_ID`)))
JOIN `ccode` `c`
ON ((`c`.`CCODE_ID` = `l`.`CCODE_ID`)))
JOIN `sccode` `s`
ON ((`s`.`SCCODE_ID` = `c`.`SCCODE_ID`)))
JOIN `sctype` `t`
ON ((`t`.`SCTYPE_ID` = `s`.`SCTYPE`)))
JOIN `release` `r`
ON ((`r`.`RELEASE_ID` = `j`.`JOB_ID`)))
JOIN `materials` `m`
ON ((`m`.`RELEASE_ID` = `r`.`RELEASE_ID`)))
WHERE ((`j`.`JOB_ID` = 7398)
AND (`t`.`SCTYPE_ID` IN(1,7))
AND (`l`.`DATE_WORK` <= _utf8'2013-10-20')
AND ((`c`.`DELETED` <> _latin1'Y')
OR ISNULL(`c`.`DELETED`))
AND ((`j`.`DELETED` <> _latin1'Y')
OR ISNULL(`j`.`DELETED`))
AND ((`l`.`DELETED` <> _latin1'Y')
OR ISNULL(`l`.`DELETED`))
AND ((`m`.`DELETED` <> _latin1'Y')
OR ISNULL(`m`.`DELETED`))
AND ((`r`.`DELETED` <> _latin1'Y')
OR ISNULL(`r`.`DELETED`))
AND ((`s`.`DELETED` <> _latin1'Y')
OR ISNULL(`s`.`DELETED`))
AND ((`t`.`DELETED` <> _latin1'Y')
OR ISNULL(`t`.`DELETED`)))
GROUP BY `j`.`JOB_ID`$$
DELIMITER ;
------------------------------------------------------------------------------------------------------------------------------
DELIMITER $$
USE `jds`$$
DROP VIEW IF EXISTS `vPLACED`$$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vPLACED` AS
SELECT
SUM(`m`.`PLACEMENT`) AS `SUM(m.PLACEMENT)`
FROM (((((`materials` `m`
JOIN `release` `r`
ON ((`m`.`RELEASE_ID` = `r`.`RELEASE_ID`)))
JOIN `ccode` `c`
ON ((`r`.`CCODE_ID` = `c`.`CCODE_ID`)))
JOIN `sccode` `s`
ON ((`s`.`SCCODE_ID` = `c`.`SCCODE_ID`)))
JOIN `sctype` `t`
ON ((`s`.`SCTYPE` = `t`.`SCTYPE_ID`)))
JOIN `job` `j`
ON ((`m`.`JOB_ID` = `j`.`JOB_ID`)))
WHERE ((`m`.`JOB_ID` = 7398)
AND (`t`.`SCTYPE_ID` IN(1,7))
AND (`m`.`DATE_PLACE` <= _utf8'2013-10-20')
AND ((`c`.`DELETED` <> _latin1'Y')
OR ISNULL(`c`.`DELETED`))
AND ((`j`.`DELETED` <> _latin1'Y')
OR ISNULL(`j`.`DELETED`))
AND ((`m`.`DELETED` <> _latin1'Y')
OR ISNULL(`m`.`DELETED`))
AND ((`r`.`DELETED` <> _latin1'Y')
OR ISNULL(`r`.`DELETED`))
AND ((`s`.`DELETED` <> _latin1'Y')
OR ISNULL(`s`.`DELETED`))
AND ((`t`.`DELETED` <> _latin1'Y')
OR ISNULL(`t`.`DELETED`)))
GROUP BY `j`.`JOB_ID`$$
DELIMITER ;
------------------------------------------------------------------------------------------------------------------------------
Is there a question in here somewhere? 'Maybe I should do this' doesn't really give us a lot to go on.
Wait. This would produce one number - for all jobs - regardless of when they were performed:
I do have these questions though:
Q1: the first column always equals '2013-10-20' doesn't it? So why use a subquery?
Q2: @cost subquery, how does this get limited to job 7398?
Q3: in that @cost subquery there is a join that does not look right, is it right?
If we had the tables and sample data then perhaps it might be simplified, but as the intent of the query is to produce a single row of output I'd stick with pretty much what you have. Some suggested changes and re-formatted:
CREATE VIEW v2HOURS AS SELECT SUM(l.HOURS)
FROM labor l
INNER JOIN job j ON j.JOB_ID = l.JOB_ID
INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
INNER JOIN `release` r ON r.RELEASE_ID = j.JOB_ID
INNER JOIN materials m ON m.RELEASE_ID = r.RELEASE_ID
WHERE (l.DELETED != 'Y'OR l.DELETED IS NULL)
AND (j.DELETED != 'Y'OR j.DELETED IS NULL)
AND (c.DELETED != 'Y'OR c.DELETED IS NULL)
AND (s.DELETED != 'Y'OR s.DELETED IS NULL)
AND (t.DELETED != 'Y'OR t.DELETED IS NULL)
AND (m.DELETED != 'Y'OR m.DELETED IS NULL);I do have these questions though:
Q1: the first column always equals '2013-10-20' doesn't it? So why use a subquery?
Q2: @cost subquery, how does this get limited to job 7398?
Q3: in that @cost subquery there is a join that does not look right, is it right?
If we had the tables and sample data then perhaps it might be simplified, but as the intent of the query is to produce a single row of output I'd stick with pretty much what you have. Some suggested changes and re-formatted:
@Job_ID := 7398
@StartDate := '2013-10-14'
@EndDate := '2013-10-20'
SELECT
@EndDate AS 'WEEK ENDING'
, SUM(l.HOURS) AS MAN_HOURS
, FORMAT(@cost / SUM(l.HOURS), 2) 'AVG WAGE'
, FORMAT(@place / SUM(l.HOURS), 0) 'LBS/MHR'
, @place := (
SELECT SUM(m1.PLACEMENT)
FROM materials m1
INNER JOIN `release` r1 ON m1.RELEASE_ID = r1.RELEASE_ID /* change; unique aliases, not the same as outer query */
INNER JOIN ccode c1 ON r1.CCODE_ID = c1.CCODE_ID
INNER JOIN sccode s1 ON c1.SCCODE_ID = s1.SCCODE_ID
INNER JOIN sctype t1 ON s1.SCTYPE = t1.SCTYPE_ID
INNER JOIN job j ON m1.JOB_ID = j1.JOB_ID
WHERE m1.JOB_ID = j.JOB_ID /* change; note j.job_id is from the outer query */
AND m1.DATE_PLACE BETWEEN @StartDate AND @EndDate
AND t1.SCTYPE_ID IN (1, 7)
AND c1.TM = 'N'
AND (j1.DELETED != 'Y' OR j1.DELETED IS NULL)
AND (c1.DELETED != 'Y' OR c1.DELETED IS NULL)
AND (s1.DELETED != 'Y' OR s1.DELETED IS NULL)
AND (t1.DELETED != 'Y' OR t1.DELETED IS NULL)
AND (m1.DELETED != 'Y' OR m1.DELETED IS NULL)
) 'PLACED'
, @cost := (
SELECT SUM(WJCOST)
FROM jcost o2
INNER JOIN job j2 ON o2.JOB_ID = j2.JOB_ID /* change; unique aliases different to outer join */
INNER JOIN sccode s2 ON o2.SCCODE = s2.SCCODE
WHERE o2.WEDATE = @EndDate
AND j2.JOB_ID = j.JOB_ID /* change; note j.job_id is from the outer query */
AND (j2.DELETED != 'Y' OR j2.DELETED IS NULL)
AND (c.DELETED != 'Y' OR c.DELETED IS NULL) /* ?? are you sure aboout this join (to outer query) ?? */
AND (s2.DELETED != 'Y' OR s2.DELETED IS NULL)
AND (o2.DELETED != 'Y' OR o2.DELETED IS NULL)
) 'COST'
, FORMAT(@place * j.UNITCOST, 0) 'EST COST'
, FORMAT((@place * j.UNITCOST) - @cost, 0) 'GAIN (LOSS)'
, FORMAT(@cost / @place, 4) 'UNIT COST'
FROM labor l
INNER JOIN job j ON j.JOB_ID = l.JOB_ID
INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
INNER JOIN `release` r ON r.RELEASE_ID = j.JOB_ID
INNER JOIN materials m ON m.RELEASE_ID = r.RELEASE_ID
WHERE j.JOB_ID = @Job_ID
AND l.DATE_WORK BETWEEN @StartDate AND @EndDate
AND t.SCTYPE_ID IN (1, 7)
AND (l.DELETED != 'Y' OR l.DELETED IS NULL)
AND (j.DELETED != 'Y' OR j.DELETED IS NULL)
AND (c.DELETED != 'Y' OR c.DELETED IS NULL)
AND (s.DELETED != 'Y' OR s.DELETED IS NULL)
AND (t.DELETED != 'Y' OR t.DELETED IS NULL)
AND (m.DELETED != 'Y' OR m.DELETED IS NULL)
GROUP BY j.JOB_ID
;
I think the problematic parts are with the @place and @cost nested statements. You want to change those select statements to views. You're on the right track, with your placement view, but it's missing fields to join the view to the rest of your query. If you only select SUM(m.Placement) it will just sum whatever is in the underlying tables, basically a total of everything.
Instead, you need to add some fields to the select statement so you can have it behave in the same way as a table. So if you want to know sum(placement) per job ID, add m.Job_ID to your select clause and add it to a group by clause at the bottom. This way the view will sum the placement for all your Job_ID's and you can link it to other tables based on that.
Now I don't know what your primary keys are on your data and how exactly you want to sum it, but you have to add a few fields to your views to be able to create joins on them.
See a view as a summary of your data. If you have to use the same summary over and over again, put it in a view instead and link to it as you would a table.
Hope this puts you in the right direction.
Instead, you need to add some fields to the select statement so you can have it behave in the same way as a table. So if you want to know sum(placement) per job ID, add m.Job_ID to your select clause and add it to a group by clause at the bottom. This way the view will sum the placement for all your Job_ID's and you can link it to other tables based on that.
Now I don't know what your primary keys are on your data and how exactly you want to sum it, but you have to add a few fields to your views to be able to create joins on them.
See a view as a summary of your data. If you have to use the same summary over and over again, put it in a view instead and link to it as you would a table.
Hope this puts you in the right direction.
by the way, 2 further points:
I'm surprised jimhorn hasn't mentioned something about horsewhipping and absence of commentary (i.e. signposting to explain what you are doing - and why)
You are looking at views "top down" instead of "bottom up" I think. Instead of expecting views to produce the single summary numbers, look for opportunities for views to consistently present the details needed - and then derive the wanted summary numbers from them.
PURELY as an example (not a full suggestion as I don't have the full facts)
I'm surprised jimhorn hasn't mentioned something about horsewhipping and absence of commentary (i.e. signposting to explain what you are doing - and why)
You are looking at views "top down" instead of "bottom up" I think. Instead of expecting views to produce the single summary numbers, look for opportunities for views to consistently present the details needed - and then derive the wanted summary numbers from them.
PURELY as an example (not a full suggestion as I don't have the full facts)
CREATE VIEW example_only_v AS
SELECT
j.* /* don't actually use "select *" this should be detailed, I don't have all the facts */
FROM job j ON j.JOB_ID = l.JOB_ID
INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE t.SCTYPE_ID IN (1, 7)
AND (j.DELETED != 'Y' OR j.DELETED IS NULL)
AND (c.DELETED != 'Y' OR c.DELETED IS NULL)
AND (s.DELETED != 'Y' OR s.DELETED IS NULL)
AND (t.DELETED != 'Y' OR t.DELETED IS NULL)
;
@Job_ID := 7398
@StartDate := '2013-10-14'
@EndDate := '2013-10-20'
SELECT
@EndDate AS 'WEEK ENDING'
, SUM(l.HOURS) AS MAN_HOURS
, FORMAT(@cost / SUM(l.HOURS), 2) 'AVG WAGE'
, FORMAT(@place / SUM(l.HOURS), 0) 'LBS/MHR'
, @place := (
SELECT SUM(m1.PLACEMENT)
FROM materials m1
INNER JOIN example_only_v j1 ON j1.JOB_ID = m1.JOB_ID
WHERE m1.JOB_ID = j.JOB_ID /* change; note j.job_id is from the outer query */
AND m1.DATE_PLACE BETWEEN @StartDate AND @EndDate
) 'PLACED'
, @cost := (
SELECT SUM(WJCOST)
FROM jcost o2
INNER JOIN example_only_v j2 ON j2.JOB_ID = o2.JOB_ID
WHERE o2.WEDATE = @EndDate
AND j2.JOB_ID = j.JOB_ID /* change; note j.job_id is from the outer query */
) 'COST'
, FORMAT(@place * j.UNITCOST, 0) 'EST COST'
, FORMAT((@place * j.UNITCOST) - @cost, 0) 'GAIN (LOSS)'
, FORMAT(@cost / @place, 4) 'UNIT COST'
FROM labor l
INNER JOIN example_only_v j ON j.JOB_ID = l.JOB_ID
WHERE j.JOB_ID = @Job_ID
AND l.DATE_WORK BETWEEN @StartDate AND @EndDate
AND (l.DELETED != 'Y' OR l.DELETED IS NULL)
GROUP BY j.JOB_ID
;
mmm, and as I think about that example view, and the final where clause, I wonder if this wouldn't be sufficient for that anyway:
FROM labor l
WHERE l.JOB_ID = @Job_ID
AND l.DATE_WORK BETWEEN @StartDate AND @EndDate
AND (l.DELETED != 'Y' OR l.DELETED IS NULL)
GROUP BY l.JOB_ID
just an idea
FROM labor l
WHERE l.JOB_ID = @Job_ID
AND l.DATE_WORK BETWEEN @StartDate AND @EndDate
AND (l.DELETED != 'Y' OR l.DELETED IS NULL)
GROUP BY l.JOB_ID
just an idea
ASKER
Since you wanted more info, here it goes. There are 8 tables involved in this report. I’ve attached the ERD (ERD_FR) with the involved tables/fields.
ccode c, job j, jcost o, labor l, materials m, release r, sccode s, sctype t
Actually the query is much more complicated. I was sharing only the initial part of it. See attached report (FieldReport). The query I've shared only is the first row of data on the CURRENT (left side of the report). That is why I only display the week ending 10/20/13. The entire report will have a many weeks descending as there are for that particular job. I assume I will do the succeeding weeks with some sort of loop.
The formulas are as follows for the first eight columns of numbers (after week ending) are:
MAN HOUR = SUM(l.HOURS)
AVG WAGE = SUM(o.WJCOST) / SUM(l.HOURS)
LBS/MHR = SUM(m.PLACEMENT) / SUM(l.HOURS)
PLACED = SUM(m.PLACEMENT)
COST = SUM(o.WJCOST)
EST COST = ((c.POPU / c.POUEST) * c.POEST / c.POPU) * SUM(m.PLACEMENT)
GAIN (LOSS) = ((c.POPU / c.POUEST) * c.POEST / c.POPU) * SUM(m.PLACEMENT) - SUM(o.WJCOST)
UNIT COST = SUM(o.WJCOST) / SUM(m.PLACEMENT)
All of this is for the first half of row 1 on the FieldReport
ERD-FR.jpg
FieldReport.jpg
ccode c, job j, jcost o, labor l, materials m, release r, sccode s, sctype t
Actually the query is much more complicated. I was sharing only the initial part of it. See attached report (FieldReport). The query I've shared only is the first row of data on the CURRENT (left side of the report). That is why I only display the week ending 10/20/13. The entire report will have a many weeks descending as there are for that particular job. I assume I will do the succeeding weeks with some sort of loop.
The formulas are as follows for the first eight columns of numbers (after week ending) are:
MAN HOUR = SUM(l.HOURS)
AVG WAGE = SUM(o.WJCOST) / SUM(l.HOURS)
LBS/MHR = SUM(m.PLACEMENT) / SUM(l.HOURS)
PLACED = SUM(m.PLACEMENT)
COST = SUM(o.WJCOST)
EST COST = ((c.POPU / c.POUEST) * c.POEST / c.POPU) * SUM(m.PLACEMENT)
GAIN (LOSS) = ((c.POPU / c.POUEST) * c.POEST / c.POPU) * SUM(m.PLACEMENT) - SUM(o.WJCOST)
UNIT COST = SUM(o.WJCOST) / SUM(m.PLACEMENT)
All of this is for the first half of row 1 on the FieldReport
ERD-FR.jpg
FieldReport.jpg
This helps. Some observations:
This is always a bit tricky without knowing the data in depth, but I think with the following query you should be able to get the left hand side of your report data for all the lines within the selected week numbers. Note that I'm not familiar with MySQL syntax so I hope I got my syntax correct:
Again, based on your data it seems that inner joins are appropriate, but if you are missing records you may have to transform some of them to outer joins.
It's probably possible to get rid of the select statements in the from clause altogether but this was easier for me to work out.
Give it a whirl and let us know if it works.
In your query you're using a field from jcost called WEDATE, but I don't see it in the schema. Did you accidentally leave this out? It seems a rather important field based on the query you provided.
You appear to be grouping all your information by week, but there is no week number available in your dataset. This could be a strong reason to actually use a view, because it would allow you to add week numbers to all the data rows containing dates, which in turn will allow you to group your data by week.
For your report, what kind of reporting tool are you using? Since the right hand side of the report is basically a running total of the left hand side, you should be able to only extract the left hand part using SQL from the database, and use the report tool and some formulas to calculate the running totals directly in the report. This would not require additional complex SQL queries
This is always a bit tricky without knowing the data in depth, but I think with the following query you should be able to get the left hand side of your report data for all the lines within the selected week numbers. Note that I'm not familiar with MySQL syntax so I hope I got my syntax correct:
@job_ID := 7398
@week_from := 42
@week_to := 42
select o.weeknumber
, sum(l.hours) as 'Man_hours'
, sum(o.wjcost) as 'cost'
, sum(m.placement) as 'placed'
, FORMAT(sum(o.wjcost) / SUM(l.HOURS), 2) 'AVG WAGE',
, FORMAT(sum(m.placement) / SUM(l.HOURS), 0) 'LBS/MHR',
, FORMAT(sum(m.placement) * j.UNITCOST, 0) 'EST COST',
, FORMAT(sum(m.placement) * j.UNITCOST) - sum(o.wjcost), 0) 'GAIN (LOSS)',
, FORMAT(sum(o.wjcost) / sum(m.placement), 4) 'UNIT COST'
FROM (select labor_id
, job_id
, ccode_id
, hours
, deleted
, datepart(week, date_work) as 'weeknumber'
from labor
) as l
INNER JOIN job as j
ON j.JOB_ID = l.JOB_ID
INNER JOIN ccode as c
ON c.CCODE_ID = l.CCODE_ID
INNER JOIN sccode s
ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t
ON t.SCTYPE_ID = s.SCTYPE
INNER JOIN `release` r
ON r.RELEASE_ID = j.JOB_ID
INNER JOIN materials m
ON m.RELEASE_ID = r.RELEASE_ID
INNER JOIN (Select job_ID
, sccode
, wjcost
, datepart(Week, wedate) as 'weeknumber'
, deleted
from jcost) as o
ON o.JOB_ID = j.JOB_ID
AND o.SCCODE = s.SCCODE
Where j.job_id = @job_ID
and l.week_number between @week_from and @week_to
and o.week_number between @week_from and @week_to
and t.sctype in (1,7)
and c.TM = 'N'
and (l.DELETED != 'Y' OR l.DELETED IS NULL)
AND (j.DELETED != 'Y' OR j.DELETED IS NULL)
AND (c.DELETED != 'Y' OR c.DELETED IS NULL)
AND (s.DELETED != 'Y' OR s.DELETED IS NULL)
AND (t.DELETED != 'Y' OR t.DELETED IS NULL)
AND (m.DELETED != 'Y' OR m.DELETED IS NULL)
and (o.deleted != 'Y' OR o.DELETED IS NULL)
group by o.weeknumber
, j.Unit_costAgain, based on your data it seems that inner joins are appropriate, but if you are missing records you may have to transform some of them to outer joins.
It's probably possible to get rid of the select statements in the from clause altogether but this was easier for me to work out.
Give it a whirl and let us know if it works.
Nice work Kvwielink (and no triple posts :)
I would recommend using YEARWEEK()
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_yearweek
the equivalent to
datepart(week, date_work) as 'weeknumber'
would be
week( date_work )
BUT! take care if using weeknumbers if the overall date range is (say) 60 weeks then 8 of those weeks will share a weeknumber- and after grouping you will have 52 weeks again (but now 8 weeks of numbers are wrong).
Note the week() function will also accept a second parameter for day of the week.
see: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week
So;datepart(week, date_work) as 'weeknumber'
would be
week( date_work )
BUT! take care if using weeknumbers if the overall date range is (say) 60 weeks then 8 of those weeks will share a weeknumber- and after grouping you will have 52 weeks again (but now 8 weeks of numbers are wrong).
Note the week() function will also accept a second parameter for day of the week.
see: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week
I would recommend using YEARWEEK()
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_yearweek
@job_ID := 7398
@week_from := 201342 /* NB! contains year and weeknumber */
@week_to := 201342 /* NB! contains year and weeknumber */
select o.weeknumber
, sum(l.hours) as 'Man_hours'
, sum(o.wjcost) as 'cost'
, sum(m.placement) as 'placed'
, FORMAT(sum(o.wjcost) / SUM(l.HOURS), 2) 'AVG WAGE',
, FORMAT(sum(m.placement) / SUM(l.HOURS), 0) 'LBS/MHR',
, FORMAT(sum(m.placement) * j.UNITCOST, 0) 'EST COST',
, FORMAT(sum(m.placement) * j.UNITCOST) - sum(o.wjcost), 0) 'GAIN (LOSS)',
, FORMAT(sum(o.wjcost) / sum(m.placement), 4) 'UNIT COST'
FROM (
select labor_id
, job_id
, ccode_id
, hours
, deleted
, yearweek( date_work) as 'weeknumber'
from labor
) as l
INNER JOIN job as j ON j.JOB_ID = l.JOB_ID
INNER JOIN ccode as c ON c.CCODE_ID = l.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
INNER JOIN `release` r ON r.RELEASE_ID = j.JOB_ID
INNER JOIN materials m ON m.RELEASE_ID = r.RELEASE_ID
INNER JOIN (
Select job_ID
, sccode
, wjcost
, yearweek( wedate ) as 'weeknumber'
, deleted
from jcost
) as o ON o.JOB_ID = j.JOB_ID
AND o.SCCODE = s.SCCODE
WHERE j.job_id = @job_ID
AND l.week_number between @week_from and @week_to
AND o.week_number between @week_from and @week_to
AND t.sctype in (1,7)
AND c.TM = 'N'
AND (l.DELETED != 'Y' OR l.DELETED IS NULL)
AND (j.DELETED != 'Y' OR j.DELETED IS NULL)
AND (c.DELETED != 'Y' OR c.DELETED IS NULL)
AND (s.DELETED != 'Y' OR s.DELETED IS NULL)
AND (t.DELETED != 'Y' OR t.DELETED IS NULL)
AND (m.DELETED != 'Y' OR m.DELETED IS NULL)
AND (o.deleted != 'Y' OR o.DELETED IS NULL)
GROUP BY
o.weeknumber
, j.Unit_costASKER
Yes, I accidently left off WEDATE which is on the jcost table. It is not that important because it can be easily created. Its just the last week ending. Weeks run Monday thru Sunday. I added it it and posted another ERD (ERD_FP2).
The data is gather by week. This will be done via a PHP loop
10/14 to 10/20
10/07 to 10/13
09/30 to 10/06
The report is done using PHP/MySQL (i.e., LAMP platform). You are correct, the right side is a Job-to-Date total or runing total. This can be figured out once the first query is resolved.
I assumed you wanted a ';' after j.Unit_cost
Query Error:
Query : @job_ID := 7398 @week_from := 42 @week_to := 42 select o.weeknumber , sum(l.hours) as 'Man_hours' , sum(o.wjcost) as 'cost...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@job_ID := 7398
@week_from := 42
@week_to := 42
select o.weeknumber
, sum(l.' at line 1
ERD-FR2.jpg
The data is gather by week. This will be done via a PHP loop
10/14 to 10/20
10/07 to 10/13
09/30 to 10/06
The report is done using PHP/MySQL (i.e., LAMP platform). You are correct, the right side is a Job-to-Date total or runing total. This can be figured out once the first query is resolved.
I assumed you wanted a ';' after j.Unit_cost
Query Error:
Query : @job_ID := 7398 @week_from := 42 @week_to := 42 select o.weeknumber , sum(l.hours) as 'Man_hours' , sum(o.wjcost) as 'cost...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@job_ID := 7398
@week_from := 42
@week_to := 42
select o.weeknumber
, sum(l.' at line 1
ERD-FR2.jpg
>>"Since you wanted more info, here it goes. "
thank goodness, this is vastly different to "one row of output"
plus: >>"but as the intent of the query is to produce a single row of output I'd stick with pretty much what you have. "
this is totally rescinded :)
>>"The entire report will have a many weeks descending as there are for that particular job. I assume I will do the succeeding weeks with some sort of loop."
try to avoid loops and use "set operations" (queries) wherever possible - it will be faster.
and Kvwielink's third dot point/question about the "reporting tool" is important
what is it you are using to format that output?
thank goodness, this is vastly different to "one row of output"
plus: >>"but as the intent of the query is to produce a single row of output I'd stick with pretty much what you have. "
this is totally rescinded :)
>>"The entire report will have a many weeks descending as there are for that particular job. I assume I will do the succeeding weeks with some sort of loop."
try to avoid loops and use "set operations" (queries) wherever possible - it will be faster.
and Kvwielink's third dot point/question about the "reporting tool" is important
what is it you are using to format that output?
Ok, sorry about that. Like I said, I'm not too familiar with MySQL syntax and in MSSQL the ";" would not be required.
Paul, can you double check the code? You know MySQL better than I do. By the way, very valid point on adding the year to the week number parameter.
Oh, and triple posts don't seem to occur if I first preview the post. Very strange...
Paul, can you double check the code? You know MySQL better than I do. By the way, very valid point on adding the year to the week number parameter.
Oh, and triple posts don't seem to occur if I first preview the post. Very strange...
MY FAULT...
syntax for using those variables is this:
SET @job_ID := 7398;
SET @week_from := 201342;
SET @week_to := 201342;
(if forgot the set and the semi-colons)
please note datepart(...) will also bomb, please read recent posts
syntax for using those variables is this:
SET @job_ID := 7398;
SET @week_from := 201342;
SET @week_to := 201342;
(if forgot the set and the semi-colons)
please note datepart(...) will also bomb, please read recent posts
ASKER
Error...
(0 row(s) affected)
Execution Time : 00:00:00:031
Transfer Time : 00:00:00:000
Total Time : 00:00:00:031
(0 row(s) affected)
Execution Time : 00:00:00:032
Transfer Time : 00:00:00:000
Total Time : 00:00:00:032
(0 row(s) affected)
Execution Time : 00:00:00:015
Transfer Time : 00:00:00:000
Total Time : 00:00:00:015
Query : select o.weeknumber , sum(l.hours) as 'Man_hours' , sum(o.wjcost) as 'cost' , sum(m.placement) as 'placed' , FORMAT(sum...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' FORMAT(sum(m.placement) / SUM(l.HOURS), 0) 'LBS/MHR',
, FORMAT(sum(m.placemen' at line 6
(0 row(s) affected)
Execution Time : 00:00:00:031
Transfer Time : 00:00:00:000
Total Time : 00:00:00:031
(0 row(s) affected)
Execution Time : 00:00:00:032
Transfer Time : 00:00:00:000
Total Time : 00:00:00:032
(0 row(s) affected)
Execution Time : 00:00:00:015
Transfer Time : 00:00:00:000
Total Time : 00:00:00:015
Query : select o.weeknumber , sum(l.hours) as 'Man_hours' , sum(o.wjcost) as 'cost' , sum(m.placement) as 'placed' , FORMAT(sum...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' FORMAT(sum(m.placement) / SUM(l.HOURS), 0) 'LBS/MHR',
, FORMAT(sum(m.placemen' at line 6
Please look at all the rows that start with a comma, they should not also end with a comma I've had another look and cannot see any other glaring syntax errors - the balance, if any, will probably require access to the tables/fields.
It is also impossible for us to know what variant of the several queries on this page you have actually used (and perhaps also edited) - this can lead to misinformation and I've seen that get frustrating for all parties.
set @job_ID := 7398;
set @week_from := 201342;
set @week_to := 201342;
select
o.weeknumber
, sum(l.hours) as 'Man_hours'
, sum(o.wjcost) as 'cost'
, sum(m.placement) as 'placed'
, FORMAT(sum(o.wjcost) / SUM(l.HOURS), 2) 'AVG WAGE'
, FORMAT(sum(m.placement) / SUM(l.HOURS), 0) 'LBS/MHR'
, FORMAT(sum(m.placement) * j.UNITCOST, 0) 'EST COST'
, FORMAT(sum(m.placement) * j.UNITCOST) - sum(o.wjcost), 0) 'GAIN (LOSS)'
, FORMAT(sum(o.wjcost) / sum(m.placement), 4) 'UNIT COST'
FROM (
select
labor_id
, job_id
, ccode_id
, hours
, deleted
, yearweek( date_work) as 'weeknumber'
from labor
) as l
INNER JOIN job as j ON j.JOB_ID = l.JOB_ID
INNER JOIN ccode as c ON c.CCODE_ID = l.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
INNER JOIN `release` r ON r.RELEASE_ID = j.JOB_ID
INNER JOIN materials m ON m.RELEASE_ID = r.RELEASE_ID
INNER JOIN (
Select job_ID
, sccode
, wjcost
, yearweek( wedate ) as 'weeknumber'
, deleted
from jcost
) as o ON o.JOB_ID = j.JOB_ID
AND o.SCCODE = s.SCCODE
WHERE j.job_id = @job_ID
AND l.week_number between @week_from and @week_to
AND o.week_number between @week_from and @week_to
AND t.sctype in (1,7)
AND c.TM = 'N'
AND (l.DELETED != 'Y' OR l.DELETED IS NULL)
AND (j.DELETED != 'Y' OR j.DELETED IS NULL)
AND (c.DELETED != 'Y' OR c.DELETED IS NULL)
AND (s.DELETED != 'Y' OR s.DELETED IS NULL)
AND (t.DELETED != 'Y' OR t.DELETED IS NULL)
AND (m.DELETED != 'Y' OR m.DELETED IS NULL)
AND (o.deleted != 'Y' OR o.DELETED IS NULL)
GROUP BY
o.weeknumber
, j.Unit_costIt is also impossible for us to know what variant of the several queries on this page you have actually used (and perhaps also edited) - this can lead to misinformation and I've seen that get frustrating for all parties.
That would be my fault.
Remove the commas at the end of each line of code. I moved them to the front for better readability but I forgot to remove some of the existing ones when I copy/pasted parts of the original code:
Remove the commas at the end of each line of code. I moved them to the front for better readability but I forgot to remove some of the existing ones when I copy/pasted parts of the original code:
, FORMAT(sum(o.wjcost) / SUM(l.HOURS), 2) 'AVG WAGE'
, FORMAT(sum(m.placement) / SUM(l.HOURS), 0) 'LBS/MHR'
, FORMAT(sum(m.placement) * j.UNITCOST, 0) 'EST COST'
, FORMAT(sum(m.placement) * j.UNITCOST) - sum(o.wjcost), 0) 'GAIN (LOSS)'
, FORMAT(sum(o.wjcost) / sum(m.placement), 4) 'UNIT COST'ASKER
SET @job_ID := 7398;
SET @week_from := 201342;
SET @week_to := 201342;
SELECT
o.weeknumber
, SUM(l.hours) AS 'Man_hours'
, SUM(o.wjcost) AS 'cost'
, SUM(m.placement) AS 'placed'
, FORMAT(SUM(o.wjcost) / SUM(l.HOURS), 2) 'AVG WAGE'
, FORMAT(SUM(m.placement) / SUM(l.HOURS), 0) 'LBS/MHR'
, FORMAT(SUM(m.placement) * j.UNITCOST, 0) 'EST COST'
, FORMAT((SUM(m.placement) * j.UNITCOST) - SUM(o.wjcost), 0) 'GAIN (LOSS)'
, FORMAT(SUM(o.wjcost) / SUM(m.placement), 4) 'UNIT COST'
FROM (
SELECT
labor_id
, job_id
, ccode_id
, hours
, YEARWEEK( date_work) AS 'weeknumber'
FROM labor
) AS l
INNER JOIN job AS j ON j.JOB_ID = l.JOB_ID
INNER JOIN ccode AS c ON c.CCODE_ID = l.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
INNER JOIN `release` r ON r.RELEASE_ID = j.JOB_ID
INNER JOIN materials m ON m.RELEASE_ID = r.RELEASE_ID
INNER JOIN (
SELECT job_ID
, sccode
, wjcost
, YEARWEEK( wedate ) AS 'weeknumber'
FROM jcost
) AS o ON o.JOB_ID = j.JOB_ID
AND o.SCCODE = s.SCCODE
WHERE j.job_id = @job_ID
AND l.weeknumber BETWEEN @week_from AND @week_to
AND o.weeknumber BETWEEN @week_from AND @week_to
AND t.sctype IN (1,7)
AND c.TM = 'N'
AND (j.DELETED != 'Y' OR j.DELETED IS NULL)
AND (c.DELETED != 'Y' OR c.DELETED IS NULL)
AND (s.DELETED != 'Y' OR s.DELETED IS NULL)
AND (t.DELETED != 'Y' OR t.DELETED IS NULL)
AND (m.DELETED != 'Y' OR m.DELETED IS NULL)
GROUP BY
o.weeknumber;
That would usually indicate an issue with the joins. If you force an inner join, and one of the tables is missing a record, you get no result.
What I usually do in such a case is trace the data for a single result line through all the tables and check whether the joins make sense. If you're missing a record in even one of the tables, because all the joins are inner joins no result will be returned. In that case you may have to use outer joins.
It's very hard to pinpoint as we don't have your data. If you can post some sample data to play with we can probably assist you better.
What I usually do in such a case is trace the data for a single result line through all the tables and check whether the joins make sense. If you're missing a record in even one of the tables, because all the joins are inner joins no result will be returned. In that case you may have to use outer joins.
It's very hard to pinpoint as we don't have your data. If you can post some sample data to play with we can probably assist you better.
ASKER
Attached is an sql dump of the dummy database steel.
steel-10-28-13.sql
steel-10-28-13.sql
ASKER
Should I change all inner joins to outer joins and see if I get data?
ASKER
When I change to LEFT joins I get the same thing, no errors and no data. When I change to OUTER joins i get this error?
Query : select o.weeknumber , sum(l.hours) as 'Man_hours' , sum(o.wjcost) as 'cost' , sum(m.placement)...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer JOIN job j ON j.JOB_ID = l.JOB_ID
OUTER JOIN ccode c ON c.CC' at line 20
Query : select o.weeknumber , sum(l.hours) as 'Man_hours' , sum(o.wjcost) as 'cost' , sum(m.placement)...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer JOIN job j ON j.JOB_ID = l.JOB_ID
OUTER JOIN ccode c ON c.CC' at line 20
ASKER
I have NULLS in the jcost table for JOB_ID. These are not rows that are on the JOB that we are doing the search on.
Before amending joins - I'd look at relaxing or changing the where clause.
In the sample I don't see a job 7398 in the job table, and/or those yearweek parameters may stop any records from being returned
SET @job_ID := 7398; -- << verify
SET @week_from := 201322; --<< decrease by some number?
SET @week_to := 201342;
In the sample I don't see a job 7398 in the job table, and/or those yearweek parameters may stop any records from being returned
SET @job_ID := 7398; -- << verify
SET @week_from := 201322; --<< decrease by some number?
SET @week_to := 201342;
regret to say that the sample data isn't cohesive:
jcost is for job 7398,
job has no 7398
nor does labor have that job, and the dates are not for the yearweek 201342
Your queries contain reference to a job.unitcost
but that field does not exist in the DDL.
And hence it isn't surprising (from that data) that nothing is returned
jcost is for job 7398,
job has no 7398
nor does labor have that job, and the dates are not for the yearweek 201342
Your queries contain reference to a job.unitcost
, FORMAT(SUM(m.placement) * j.UNITCOST, 0) 'EST COST'
but that field does not exist in the DDL.
And hence it isn't surprising (from that data) that nothing is returned
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
CREATE VIEW v2PLACED AS SELECT SUM(m.PLACEMENT)
FROM materials m
INNER JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
INNER JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
INNER JOIN job j ON m.JOB_ID = j.JOB_ID
WHERE (j.DELETED != 'Y'OR j.DELETED IS NULL)
AND (c.DELETED != 'Y'OR c.DELETED IS NULL)
AND (s.DELETED != 'Y'OR s.DELETED IS NULL)
AND (t.DELETED != 'Y'OR t.DELETED IS NULL)
AND (m.DELETED != 'Y'OR m.DELETED IS NULL)
AND (r.DELETED != 'Y'OR r.DELETED IS NULL);
--------------------------
CREATE VIEW v2HOURS AS SELECT SUM(l.HOURS)
FROM labor l
INNER JOIN job j ON j.JOB_ID = l.JOB_ID
INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
INNER JOIN `release` r ON r.RELEASE_ID = j.JOB_ID
INNER JOIN materials m ON m.RELEASE_ID = r.RELEASE_ID
WHERE (l.DELETED != 'Y'OR l.DELETED IS NULL)
AND (j.DELETED != 'Y'OR j.DELETED IS NULL)
AND (c.DELETED != 'Y'OR c.DELETED IS NULL)
AND (s.DELETED != 'Y'OR s.DELETED IS NULL)
AND (t.DELETED != 'Y'OR t.DELETED IS NULL)
AND (m.DELETED != 'Y'OR m.DELETED IS NULL);