[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

Compare Data from different weeks

I need to compare data exactly like below in a SQL query. The table is being output in coldfusion, but mainly need help writing the correct SQL code.

There is only one " Week" column in the db with numbers so the week will not be dynamically output but pulled from the database itself. I have attached some code so you have an idea of what variables are needed but my code is not producing the desired effect.


Course   |   Week1   |   Week2   |   Week3
---------------------------------------------
acctng   |    12     |     20    |    3

writng   |    9      |     7     |    2

math     |    7      |     8     |    6
SELECT COALESCE(WCRSE.FTE, 0) AS FTE, WCRSE.Number, WCRSE.Name, WCRSE.Yr, WCRSE.Week
FROM WCRSE
WHERE
WCRSE.Week Between '29' and '32'
AND 
WCRSE.Yr= '2008-9'
ORDER by WCRSE.Week, WCRSE.Name, WCRSE.Number

Open in new window

0
tribalboy3000
Asked:
tribalboy3000
1 Solution
 
psadacCommented:
you will have to generate with your programming language the lines with weeks in a loop.
it should look like this (sorry, i don't know coldfusion programming language)

sql = "SELECT ..."
for week = weekStart to weekEnd  

sql += ", SUM(CASE WHEN WCRSE.Week = '"+ week + "' THEN WCRSE.FTE ELSE 0 END) AS Week" + week

next
SELECT
    WCRSE.Number
    , WCRSE.Name
    , WCRSE.Yr
    , WCRSE.Week
    , SUM(CASE WHEN WCRSE.Week = '29' THEN WCRSE.FTE ELSE 0 END) AS Week29
    , SUM(CASE WHEN WCRSE.Week = '30' THEN WCRSE.FTE ELSE 0 END) AS Week30
    , SUM(CASE WHEN WCRSE.Week = '31' THEN WCRSE.FTE ELSE 0 END) AS Week31
    , SUM(CASE WHEN WCRSE.Week = '32' THEN WCRSE.FTE ELSE 0 END) AS Week32
FROM
    WCRSE
WHERE
    WCRSE.Week BETWEEN '29' AND '32'
    AND WCRSE.Yr= '2008-9'
GROUP BY
    WCRSE.Week
    , WCRSE.Name
    , WCRSE.Number
ORDER BY
    WCRSE.Week
    , WCRSE.Name
    , WCRSE.Number

Open in new window

0
 
tribalboy3000Author Commented:
Exactly what I was looking for!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now