Link to home
Start Free TrialLog in
Avatar of polynominal
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
ASKER CERTIFIED SOLUTION
Avatar of ispaleny
ispaleny
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Add a comma after SELECT     contract.contractid,