Question about Select query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question about Select query

Author

Topic

alanhuro
Starting Member

34 Posts

Posted - 2008-04-09 : 11:48:18

Hi! Can you help me to write an Oracle query to return the result as I will explain below?

I try to write a query to return a result with a database as follow

Day Area Variable Value
04/01/08 A 1 50
04/02/08 B 2 100
04/03/08 A 3 10
04/04/08 C 4
04/05/08
.....
.....
04/31/08

I only want to return day and Value. Value has a 2 columns Good and Bad. The requirement are

1. If the Variable = 3 is consider Bad it will go to bad column
2. Should display all the day event the data is null
3. If the value > 100 then maximum should be 60 in the good column. So the result of the query should be looked like this

Day Value Good Value Bad
04/01/08 50
04/02/08 60
04/03/08 10
04/04/08
04/05/08
.....
.....
04/31/08

Return result for whole month

readysetstop
Posting Yak Master

123 Posts

Posted - 2008-04-09 : 11:51:50

alanhuro,

This is a Microsoft SQL Server forum. I'd go here for Oracle help: http://www.dbforums.com/forumdisplay.php?f=4

-D.

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

alanhuro
Starting Member

34 Posts

Posted - 2008-04-09 : 11:59:25

Assume the DB is SQL. How I write the query for that?

Thanks
Alan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 12:09:31

[code]SELECT Day,
CASE WHEN Variable<> 3 THEN CASE WHEN Value >100 THEN 60 ELSE Value END END AS GoodValue,
CASE WHEN Variable= 3 THEN Value END AS BadValue
FROM YourTable
Where Day BETWEEN @Start AND DATEADD(mm,1,@Start)[/code]

@Start is start date of month for which you want result

alanhuro
Starting Member

34 Posts

Posted - 2008-04-09 : 16:32:35

Thanks Visakh16 you are a genius. So far it works great. But now I run into another problem. How can I group the data together. Assume there are 2 similar results from two different areas. I don't need the location so I won't include it in the query. When I ran the query there are duplicate information on the day of 04/02/08 which I want it only show up one. By using case how can I group information together?

Day_______Area_____Variable______Value
04/01/08___A_________ 1___________50
04/02/08___B_________ 2___________50
04/02/08___C_________ 2___________50
04/02/08___D_________ 3___________50
04/03/08___A_________ 1___________50
.....
.....
04/31/08

Thanks again.

Alan

alanhuro
Starting Member

34 Posts

Posted - 2008-04-10 : 09:07:23

Another question I have is cascading the column. The Union function only work for cascading the row. Is there any function work the same as Union but for Column. Here is the situation. I have a table which has the data structure like this

DateTime___Characteristic___Variable____________Te st_Result
04-05-08-------Soft------------A--------------------5
04-05-08-------Medium---------B--------------------10
04-06-08-------Soft -----------C--------------------30
04-07-08-------Hard------------A--------------------12
04-07-08-------Hard------------A--------------------17

How can I write a query with the condition are.
1. If the variable is A return the test result with a hard charac.
2. If the variable is B return with all test result
3. If the variable is C return the test result with a soft charac.
4. Need to give an average result for the same day for each variable or charateristic
5. Sorted by Most recent day on the top.
6. Do not return any result for any others variable exect A, B,C. if there isn't any test result for a specific variable the query should return null. The average of test result should show at the bottom of the result. Here is the format of the report

DateTime______Result A____Result B____Result C
04-05-08-------Null-----------10---------Null
04-06-08-------Null-----------Null--------30
04-07-08-------avg(12,17)---Null--------Null
Average-------Avg(ResultA)--Avg(ResultB)--Avg(ResultC)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-11 : 12:16:27

Something like this maybe?

-- Setup
DECLARE @Yak TABLE (DATE DateTime, Characteristic VARCHAR(20), Variable CHAR(1), Test_Result INT)

INSERT @Yak
SELECT '04-05-08', 'Soft', 'A', 5
UNION ALL SELECT '04-05-08', 'Medium', 'B', 10
UNION ALL SELECT '04-06-08', 'Soft', 'C', 30
UNION ALL SELECT '04-07-08', 'Hard', 'A', 12
UNION ALL SELECT '04-07-08', 'Hard', 'A', 17

-- Query
SELECT
	CAST(Date AS VARCHAR(12)) AS [DateTime],
	AVG(CASE WHEN Variable = 'A' AND Characteristic = 'Hard' THEN Test_Result ELSE NULL END) AS Result_A,
	AVG(CASE WHEN Variable = 'B' THEN Test_Result ELSE NULL END) AS Result_B,
	AVG(CASE WHEN Variable = 'C' AND Characteristic = 'Soft' THEN Test_Result ELSE NULL END) AS Result_C
FROM
	@Yak
GROUP BY
	Date


UNION ALL

SELECT
		'Average',
		AVG(Result_A),
		AVG(Result_B),
		AVG(Result_C)
FROM
	(
		SELECT
			AVG(CASE WHEN Variable = 'A' AND Characteristic = 'Hard' THEN Test_Result ELSE NULL END) AS Result_A,
			AVG(CASE WHEN Variable = 'B' THEN Test_Result ELSE NULL END) AS Result_B,
			AVG(CASE WHEN Variable = 'C' AND Characteristic = 'Soft' THEN Test_Result ELSE NULL END) AS Result_C
		FROM
			@Yak
		GROUP BY
			Date
	) AS T

EDIT: Changed QUery to use AVG rather than SUM(x) / COUNT(x)

alanhuro
Starting Member

34 Posts

Posted - 2008-04-14 : 13:11:56

Lamprey you are a truely Yak Warrior. I have been pulling hair for this problem and now you are making it so simple. By the way let say if I want to list all the results for all 3 the result columns is this the way how to do it?

DECLARE @Yak TABLE (DATE DateTime, Characteristic VARCHAR(20), Variable CHAR(1), Test_Result INT)

INSERT @Yak
SELECT '04-05-08', 'Soft', 'A', 5
UNION ALL SELECT '04-05-08', 'Medium', 'B', 10
UNION ALL SELECT '04-06-08', 'Soft', 'C', 30
UNION ALL SELECT '04-07-08', 'Hard', 'A', 12
UNION ALL SELECT '04-07-08', 'Hard', 'A', 17
UNION ALL SELECT '04-09-08', 'Hard', 'F', 17

-- Query

select *
from
(SELECT
CAST(Date AS VARCHAR(12)) AS [DateTime],
(CASE WHEN Variable = 'A' AND Characteristic = 'Hard' THEN Test_Result END) AS Result_A,
(CASE WHEN Variable = 'B' THEN Test_Result ELSE NULL END) AS Result_B,
(CASE WHEN Variable = 'C' AND Characteristic = 'Soft' THEN Test_Result END) AS Result_C
FROM @yak) yak2
where (Yak2.Result_A is not null) or (Yak2.Result_B is not null) or (Yak2.Result_C is not null)

Thanks
Alan I feel like hitting a jackpot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources