Solved

# Compare Data from different weeks

Posted on 2009-02-19
372 Views
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
0
Question by:tribalboy3000

LVL 14

Accepted Solution

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

0

Author Closing Comment

Exactly what I was looking for!
0

## Featured Post

This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…