Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlbug
Posting Yak Master
201 Posts |
 Posted - 2009-01-05 : 12:37:54
|
| We have two tables – lets call them SAMPLE_DATA and REAL_DATA tables containing basic information about the data and details data, respectively. Sample_num from the sample_data is the foreign key in real_data table.In the real_data table, data for each hour of the day are stored in separate rows. Before, we had one sample number for the whole day, for a plot. Now, we have 24 sample numbers for the 24 hours of the day for a plot. As a result – we need to change the query that shows output on a webpage as a table of all 24 values for a day in each row. Because (reminder) – before, we had one sample number for a day for a plot, now we have 24 sample number for 24 hourly values.Here’s what we have now in short:SELECT RDATA_SAMPLE_NUM, METHOD_NAME Method, UNIT_ABBREV Unit, SUM(Hour1) AS Hour1, SUM(Hour2) AS Hour2,......SUM(Hour24) AS Hour24 FROM (SELECT DISTINCT RDATA_SAMPLE_NUM, METHOD_NAME, UNIT_ABBREV, (CASE RDATA_HOUR WHEN 1 THEN ISNULL(CONVERT(decimal(4,4),RDATA_VALUE/10000.0), 0) END) AS Hour1, (CASE RDATA_HOUR WHEN 2 THEN ISNULL(CONVERT(decimal(4,4),RDATA_VALUE/10000.0), 0) END) AS Hour2,...... (CASE RDATA_HOUR WHEN 24 THEN ISNULL(CONVERT(decimal(4,4),RDATA_VALUE/10000.0), 0) END) AS Hour24,FROM REAL_DATA,SAMPLE_DATA,......WHERE RDATA_SAMPLE_NUM = SAMPLE_NUMAND DATEPART("MM", SAMPLE_START_DATE) = ...AND DATEPART("YYYY", SAMPLE_START_DATE) = ... GROUP BY ...) SGROUP BY ...Will highly appreciate your help, thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-05 : 12:41:52
|
| so whats will be output format? |
 |
|
|
sqlbug
Posting Yak Master
201 Posts |
Posted - 2009-01-05 : 14:34:12
|
| Output was tied to a gridview to display like:Method Unit Hour1 Hour2 ... Hour24------- ---- ----- ------ ...-------Method Unit 0.011 0.923 ... 0.008 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-06 : 02:56:55
|
| Are you using sql 2005? |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-06 : 05:43:53
|
If you are on sql2000, you could use something like this-SELECT RDATA_SAMPLE_NUM, METHOD_NAME as Method, UNIT_ABBREV as Unit, Sum(CASE WHEN RDATA_HOUR = 1 THEN RDATA_VALUE/10000.0 END) AS Hour1, Sum(CASE WHEN RDATA_HOUR = 2 THEN RDATA_VALUE/10000.0 END) AS Hour2, ... ... Sum(CASE WHEN RDATA_HOUR = 24 THEN RDATA_VALUE/10000.0 END) AS Hour24,From REAL_DATA,SAMPLE_DATA,......WHERE RDATA_SAMPLE_NUM = SAMPLE_NUM ..GROUP BY RDATA_SAMPLE_NUM, METHOD_NAME , UNIT_ABBREV Above query will give you hourly reports for all RDATA_SAMPLE_NUM, METHOD_NAME , UNIT_ABBREV combinations. Include the time/date column in your group by to split it by days/months/years etc. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-06 : 08:28:47
|
| and if you're using sql 2005 use PIVOT operator. |
|
|
|
|
|
|
|
|
Modifying the query