polynominal
asked on
Adding a query to another using case and sum
I have a query below, which I received help on here, many thanks.
SELECT CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END AS contract,
SUM(CASE WHEN Weeks.WeekNumber = 1 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week1],
SUM(CASE WHEN Weeks.WeekNumber = 2 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week2],
SUM(CASE WHEN Weeks.WeekNumber = 3 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week3],
SUM(CASE WHEN Weeks.WeekNumber = 4 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week4],
SUM(CASE WHEN Weeks.WeekNumber = 5 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week5]
FROM job INNER JOIN
contract ON job.contractid = contract.contractid INNER JOIN
jobcat ON job.jobcatid = jobcat.jobcatid,
[dbo].[ufn_GetWeeks] ( @startdate, @enddate ) AS Weeks
WHERE (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (contract.contractid IN (2, 16)) AND (job.newsitesid IS NOT NULL) AND
(job.newsitesid <> '') AND complete.engcomplete between @startdate and @enddate
GROUP BY CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END
I want to add another query which uses sum into the same format as above can anybody help
SELECT contract.contractid, SUM(workitem.quantity) AS Expr1
FROM job INNER JOIN
jobcat ON job.jobcatid = jobcat.jobcatid INNER JOIN
contract ON job.contractid = contract.contractid INNER JOIN
workitem ON job.jobid = workitem.jobid INNER JOIN
template ON workitem.templateid = template.templateid
WHERE (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (contract.contractid IN (2, 16)) AND (job.newsitesid IS NOT NULL) AND (job.newsitesid <> '') and job.received is between @startdate and @enddate AND (workitem.accepted = 1) AND
(template.template IN ('N69', 'N65', 'N64', 'N307', 'N308'))
GROUP BY contract.contracted
Notice this now uses sum(workitem.quantity) and not count, or summing a count in the previous query
Many Thanks
P
SELECT CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END AS contract,
SUM(CASE WHEN Weeks.WeekNumber = 1 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week1],
SUM(CASE WHEN Weeks.WeekNumber = 2 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week2],
SUM(CASE WHEN Weeks.WeekNumber = 3 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week3],
SUM(CASE WHEN Weeks.WeekNumber = 4 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week4],
SUM(CASE WHEN Weeks.WeekNumber = 5 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN 1 ELSE 0 END) AS [Week5]
FROM job INNER JOIN
contract ON job.contractid = contract.contractid INNER JOIN
jobcat ON job.jobcatid = jobcat.jobcatid,
[dbo].[ufn_GetWeeks] ( @startdate, @enddate ) AS Weeks
WHERE (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (contract.contractid IN (2, 16)) AND (job.newsitesid IS NOT NULL) AND
(job.newsitesid <> '') AND complete.engcomplete between @startdate and @enddate
GROUP BY CASE WHEN contract.contractid IN (2, 16) THEN 'YH Cable' END
I want to add another query which uses sum into the same format as above can anybody help
SELECT contract.contractid, SUM(workitem.quantity) AS Expr1
FROM job INNER JOIN
jobcat ON job.jobcatid = jobcat.jobcatid INNER JOIN
contract ON job.contractid = contract.contractid INNER JOIN
workitem ON job.jobid = workitem.jobid INNER JOIN
template ON workitem.templateid = template.templateid
WHERE (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (contract.contractid IN (2, 16)) AND (job.newsitesid IS NOT NULL) AND (job.newsitesid <> '') and job.received is between @startdate and @enddate AND (workitem.accepted = 1) AND
(template.template IN ('N69', 'N65', 'N64', 'N307', 'N308'))
GROUP BY contract.contracted
Notice this now uses sum(workitem.quantity) and not count, or summing a count in the previous query
Many Thanks
P
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Add a comma after SELECT contract.contractid,