Solved

SQL Syntax

Posted on 2013-10-27
25
208 Views
Last Modified: 2014-01-15
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;

Open in new window

I created the following views but can't get them incorporated in the first query.
------------------------------------------------------------------------------------------------------------------------------
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 ;

------------------------------------------------------------------------------------------------------------------------------

Open in new window

How do I incoprate the two views vHOURS and vPLACED in the original query?
0
Comment
Question by:hdcowboyaz
  • 10
  • 9
  • 5
  • +1
25 Comments
 

Author Comment

by:hdcowboyaz
Comment Utility
or shoudl I instead have thses as the views...

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);
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Is there a question in here somewhere?  'Maybe I should do this' doesn't really give us a lot to go on.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Wait. This would produce one number - for all jobs - regardless of when they were performed:
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);

Open in new window

I'm not sure that views are going to make this query substantially easier or more efficient. Nor do I really feel that this query is overly complex and with a few formatting tweaks it's much easier to read.

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
;

Open in new window

0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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)
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
;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
0
 

Author Comment

by:hdcowboyaz
Comment Utility
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
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
This helps. Some observations:

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_cost

Open in new window


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.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Nice work Kvwielink (and no triple posts :)
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;
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_cost

Open in new window

0
 

Author Comment

by:hdcowboyaz
Comment Utility
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
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"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?
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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...
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
0
 

Author Comment

by:hdcowboyaz
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.

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_cost

Open in new window

please consider carefully before sending us further error messages, and then waiting. We cannot run the query - only you can, and it will help if you identify what you have done to look for the problem. I presume you have had to clear similar problems before now.

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.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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:

        ,    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'

Open in new window

0
 

Author Comment

by:hdcowboyaz
Comment Utility
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;

Open in new window

I made changes to remove errors, however now I get no results?
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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.
0
 

Author Comment

by:hdcowboyaz
Comment Utility
Attached is an sql dump of the dummy database steel.
steel-10-28-13.sql
0
 

Author Comment

by:hdcowboyaz
Comment Utility
Should I change all inner joins to outer joins and see if I get data?
0
 

Author Comment

by:hdcowboyaz
Comment Utility
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
0
 

Author Comment

by:hdcowboyaz
Comment Utility
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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;
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
, 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
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
hdcowboyaz

I believe your existing query has logic errors.

I am recommending a substantial change to the join structure - I believe this is necessary

JOBS (as a general observation):
1. a job could exist with no labor and no material (yet)
2. a job could exist with no labor but some material cost
3. a job could exist with some labor cost but no material
4. a job could exist with labor cost and material cost

To allow for all those possibilities you should NOT be using inner joins!
(using all inner joins only permits number 4. above)

Because of conditions 1. and 2. the FROM table cannot be the labor table and it should be the job table.

I could not see the relevance of `release` and the joins that you are using look to be  wrong
        INNER JOIN `release` r ON r.RELEASE_ID = j.JOB_ID
this should be I believe:
        INNER JOIN `release` r ON r.JOB_ID = j.JOB_ID
So as the data in that table wasn't helping, and the join appears to be wrong, I'm simply ignoring `release` as you will see later.
So, you have - in my view - a number of problems in your existing query and I have severe reservations about its results.

Here is a modified query (with limitations noted in this comment) that would I believe provide the left side of your report, with one row per weeknumber, for as many weeks as you need. Note if there is no data in either the material table or the labor table for a week, no row would be produced however*.
set @job_ID     := 7398;
set @week_from  := 201330;
set @week_to    := 201342;

SELECT
          coalesce(l.weeknumber,o.weeknumber) as 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 job AS j
LEFT JOIN (
            SELECT
                      job_id
                    , ccode_id
                    , YEARWEEK( date_work) AS 'weeknumber'
                    , hours
            FROM labor 
           ) AS l ON j.JOB_ID = l.JOB_ID
LEFT JOIN ccode AS c   ON c.CCODE_ID = l.CCODE_ID
LEFT JOIN sccode s     ON s.SCCODE_ID = c.SCCODE_ID
LEFT JOIN sctype t     ON t.SCTYPE_ID = s.SCTYPE

  /* LEFT JOIN `release` r  ON j.JOB_ID = r.JOB_ID */

LEFT JOIN materials m  ON j.JOB_ID = m.JOB_ID
LEFT JOIN (
            SELECT
            job_ID
            , sccode
            , YEARWEEK( wedate ) AS 'weeknumber'
            , wjcost
            FROM jcost
          )  AS o  ON J.JOB_ID = O.JOB_ID
                  AND S.SCCODE = O.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
          coalesce(l.weeknumber,o.weeknumber)
;

Open in new window

The only data that is available in the sample data looks like this from that query:
| WEEKNUMBER | MAN_HOURS |     COST | PLACED | AVG WAGE | LBS/MHR | UNIT COST |
|------------|-----------|----------|--------|----------|---------|-----------|
|     201339 |        45 | 58991.88 | 180000 | 1,310.93 |   4,000 |    0.3277 |

Open in new window

the data and query are operating at: http://sqlfiddle.com/#!2/88bc2/7
The sample data isn't good. It does not have sufficient integrity to honestly test a query.

I have brutalized some data by forcing the job id to 7398 in: job, labor, & materials to proceed as you haven't responded.

There are other problems with the data supplied:
j.unitcost does not exist as a field, so I have ignored it

2 where clause conditions had to be ignored to get some data calculated.

*if no data exists in labor or materials for a week no row would be produced.
This can be overcome by having a table of weeknumbers (actually yearweek numbers)
but I don't know if that is required.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now