July 11, 2014 at 11:50 am
Hi All
I have a physical table that is similar to the following
DECLARE @Temperature TABLE (CalendarDate datetime, Temperature decimal(10,2),UOM char(1))
INSERT INTO @Temperature
SELECT GETDATE(), 55, 'F'
UNION ALL
SELECT GETDATE(), 15, 'C'
UNION ALL
SELECT GETDATE() + 1, 58, 'F'
UNION ALL
SELECT GETDATE() + 2, 60, 'F'
UNION ALL
SELECT GETDATE() + 3, 14, 'C'
The result that I should get would be
CalendarDate Fahrenheit
----------------------- ---------------------------------------
2014-07-12 01:26:41.647 55.00
2014-07-13 01:26:41.647 58.00
2014-07-14 01:26:41.647 60.00
2014-07-15 01:26:41.647 57.20
Effectively the temp for a particular day could be captured in Fahrenheit, Celsius or Both
If for a particular day the Temperature is only captured in Fahrenheit then the output will be in Fahrenheit. If it is only Captured in Celsius then the value will be Celsius
converted to Fahrenheit.
If on that day the temperature is captured in both Celsius and Fahrenheit then Fahrenheit should be the value.
A view already exists in the DB is a cross tab statement (Pivot without Pivot) and is wrong.
SELECT CalendarDate
,MAX(CAST(CASE WHEN UOM = 'F' THEN Temperature
WHEN UOM = 'C' THEN (Temperature * 1.8000) + 32.00
END AS DECIMAL(10,2))) AS Fahrenheit
FROM @Temperature
GROUP BY CalendarDate
The result should be
CalendarDate Fahrenheit
----------------------- ---------------------------------------
2014-07-12 01:26:41.647 59.00
2014-07-13 01:26:41.647 58.00
2014-07-14 01:26:41.647 60.00
2014-07-15 01:26:41.647 57.20
Can anyone tell me why the case statement is not short circuiting when it finds a value for Fahrenheit. I was under the impression that case returns the first value that is evaluated to true.
Here it Should find Fahrenheit on 2014-07-12 and return that instead it returns Celsius converted.
July 11, 2014 at 12:24 pm
To see what is happening, run the following. If you look at the second query I have removed the MAX and GROUP BY. The result set you see will show you why you aren't getting the results you expect.
Your query is returning exactly what it is asked to return, the MAX temperature for the given day.
DECLARE @Temperature TABLE (CalendarDate datetime, Temperature decimal(10,2),UOM char(1))
INSERT INTO @Temperature
SELECT GETDATE(), 55, 'F'
UNION ALL
SELECT GETDATE(), 15, 'C'
UNION ALL
SELECT GETDATE() + 1, 58, 'F'
UNION ALL
SELECT GETDATE() + 2, 60, 'F'
UNION ALL
SELECT GETDATE() + 3, 14, 'C'
SELECT
CalendarDate,
MAX(CAST(CASE WHEN UOM = 'F'
THEN Temperature
WHEN UOM = 'C' THEN (Temperature * 1.8000) + 32.00
END AS DECIMAL(10,2))) AS Fahrenheit
FROM
@Temperature
GROUP BY
CalendarDate
SELECT
CalendarDate,
CAST(CASE WHEN UOM = 'F'
THEN Temperature
WHEN UOM = 'C' THEN (Temperature * 1.8000) + 32.00
END AS DECIMAL(10,2)) AS Fahrenheit
FROM
@Temperature
July 11, 2014 at 12:26 pm
Second question, since you posted this in a SQL Server 7/2000 forum, are you using SQL Server 2000 or SQL Server 7? It matters in helping you develop a solution to meets your requirements.
July 11, 2014 at 12:33 pm
I made an assumption you are using at least SQL Server 2000.
DECLARE @Temperature TABLE (CalendarDate datetime, Temperature decimal(10,2),UOM char(1))
INSERT INTO @Temperature
SELECT GETDATE(), 55, 'F'
UNION ALL
SELECT GETDATE(), 15, 'C'
UNION ALL
SELECT GETDATE() + 1, 58, 'F'
UNION ALL
SELECT GETDATE() + 2, 60, 'F'
UNION ALL
SELECT GETDATE() + 3, 14, 'C'
SELECT
CalendarDate,
MAX(CAST(CASE WHEN UOM = 'F'
THEN Temperature
WHEN UOM = 'C' THEN (Temperature * 1.8000) + 32.00
END AS DECIMAL(10,2))) AS Fahrenheit
FROM
@Temperature
GROUP BY
CalendarDate
SELECT
CalendarDate,
CAST(CASE WHEN UOM = 'F'
THEN Temperature
WHEN UOM = 'C' THEN (Temperature * 1.8000) + 32.00
END AS DECIMAL(10,2)) AS Fahrenheit
FROM
@Temperature
-- I think this will give you what you are looking for:
SELECT
t.CalendarDate,
CAST(CASE WHEN t.UOM = 'F'
THEN t.Temperature
WHEN t.UOM = 'C' THEN (t.Temperature * 1.8000) + 32.00
END AS DECIMAL(10,2)) AS Fahrenheit
FROM
@Temperature t
inner join (select t1.CalendarDate, max(t1.UOM) UOM from @Temperature t1 group by t1.CalendarDate)dt
on (t.CalendarDate = dt.CalendarDate and t.UOM = dt.UOM)
ORDER BY
t.CalendarDate;
July 14, 2014 at 10:43 am
Thanks so much for that. If this were extended for future development to include say Kelvin would I have to rank the conversions somehow rather than select the min.
July 14, 2014 at 11:01 am
I'm posting a different option that will read the table just once. It might be faster but I can't assure that. I included an option with ISNULL which will work for 2 options and an option with COALESCE which will work with multiple options but need to be casted to retain the datatype.
SELECT t.CalendarDate,
ISNULL(MAX( CASE WHEN t.UOM = 'F' THEN t.Temperature END), MAX( CASE WHEN t.UOM = 'C' THEN (t.Temperature * 1.8000) + 32.00 END)) AS Fahrenheit,
CAST( COALESCE(MAX( CASE WHEN t.UOM = 'F' THEN t.Temperature END), MAX( CASE WHEN t.UOM = 'C' THEN (t.Temperature * 1.8000) + 32.00 END)) AS decimal(10,2)) AS Fahrenheit2
FROM @Temperature t
GROUP BY t.CalendarDate
ORDER BY t.CalendarDate
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply