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
polynominalAsked:
Who is Participating?
 
ispalenyConnect With a Mentor Commented:
SELECT     contract.contractid
SUM(CASE WHEN Weeks.WeekNumber = 1 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN workitem.quantity ELSE 0 END) AS [Week1],
           SUM(CASE WHEN Weeks.WeekNumber = 2 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN workitem.quantity ELSE 0 END) AS [Week2],
           SUM(CASE WHEN Weeks.WeekNumber = 3 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN workitem.quantity ELSE 0 END) AS [Week3],
           SUM(CASE WHEN Weeks.WeekNumber = 4 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN workitem.quantity ELSE 0 END) AS [Week4],
           SUM(CASE WHEN Weeks.WeekNumber = 5 AND complete.engcomplete BETWEEN Weeks.WeekStart AND Weeks.WeekEnd THEN workitem.quantity ELSE 0 END) AS [Week5]
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
CROSS JOIN [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 job.received is between @startdate and @enddate AND (workitem.accepted = 1) AND
                      (template.template IN ('N69', 'N65', 'N64', 'N307', 'N308'))
GROUP BY contract.contractid
0
 
ispalenyCommented:
Add a comma after SELECT     contract.contractid,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.