June 11, 2009 at 4:08 am
Hi, Can anyone help me do the following. I have six fields and data as follows:
create table #temptable
(
Rangevarchar(20),
YearofValueint,
Baseprice1int,
Baseprice3int,
Baseprice8int,
Baseprice9 int
)
insert into #temptable
select 'fiesta','2005','0','1000','1100','1200'union all
select 'fiesta','2006','1300','1400','1500','1600'union all
select 'fiesta','2007','1700','0','0','0'union all
select 'focus','2007','0','0','0','2000'union all
select 'focus','2008','2100','2200','2300','2400'union all
select 'focus','2009','2500','2600','0','0'
What I'm trying to achieve is
A) find the MIN yearofValue by Range, and then the 1st baseprice that is > 0.
B) And then find the MAX yearofvalue by Range, and then the last baseprice that is > 0. (the number suffixes at the end of the baseprice field names represent months, i.e. 1 = Jan. 3 = Mar etc.)
And then I just want to return the month no (so if it's baseprice3, just return 3). The correct output would be (the A and B are just for reference):
A) fiesta 2005 3
B) fiesta 2007 1
A) focus 2007 9
B) focus 2007 3
I have managed to do it but my solution is so ugly & convoluted I'm just after a simpler way.
Many thanks,
Jason
June 11, 2009 at 4:19 am
Oops...forgot to say, I'm using SQLServer 2000.
Thanks,
Jason
___
June 11, 2009 at 4:43 am
if you are saying that baseprice1 is Jan and baseprice3 is march then the below query will work :
Your question is still not very clear my friend .Try to write a clear question if you want others to reply as they spend important time for you (for free 🙂 )
[font="Verdana"]select min(yearofvalue),range,1 as month from temptable where baseprice1 > 0 group by range
union all
select max(yearofvalue),range,3 as month from temptable where baseprice3 > 0 group by range[/font]
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 11, 2009 at 4:45 am
since there is no datetype column in the table , there is no other way ...
you need to add that column to your table ...
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 11, 2009 at 4:46 am
hi,
try this
A) find the MIN yearofValue by Range, and then the 1st baseprice that is > 0.
select Range,min(yearofValue)
from #temptable
where Baseprice1 >0
group by Range
B) And then find the MAX yearofvalue by Range, and then the last baseprice that is > 0. (the number suffixes at the end of the baseprice field names represent months, i.e. 1 = Jan. 3 = Mar etc.)
select Range,MAX(yearofValue)
from #temptable
where Baseprice9 >0
group by Range
A) fiesta 2005 3
B) fiesta 2007 1
A) focus 2007 9
B) focus 2007 3
this i can't understand what actually you need?
ARUN SAS
June 11, 2009 at 4:56 am
Sorry if I wasn't clear.
The Range and Yearofvalue fields should be self-explanatory.
The other fields present prices for January, March, August & September (baseprice1, baseprice3, baseprice8, baseprice9).
What I'm trying to find is the earliest Yearofvalue, and the earliest Month priced > 0 in that year, and the latest Yearofvalue, and the latest Month priced >0 in that year.
So if you look at the 'fiesta' records, the earliet year is 2005, and the earliest baseprice valued > 0 (for that year) is baseprice3 March. So, the output would be- fiesta 2005 3.
The latest year for fiesta is 2007 and the latest baseprive valued > 0 (for that year) is baseprice1. So, the output would be- fiesta 2007 1.
Any help greatly appreciated. Sorry for any confusion.
regards,
Jason
June 11, 2009 at 5:05 am
Here are two solutions, both of which are complicated because:
A) the table is denormalized as the BasePrices are an array with the column name indicating the month of the BasePrice.
B) the BasePrice columns use a zero to indicate no value instead of nulls.
This solution changes the BasePrice of 0 to NULL and then uses the COALESCE function to get the first or last non-null value.
SELECT RangeYears.RANGE
,RangeYears.YearofValue
,CASE RangeYearType
WHEN 'Min' THEN
CASE WHEN Baseprice1 = BasepriceFirst THEN 1
WHEN Baseprice3 = BasepriceFirst THEN 3
WHEN Baseprice8 = BasepriceFirst THEN 8
WHEN Baseprice9 = BasepriceFirst THEN 9
ELSE 0
END
WHEN 'Max' THEN
CASE WHEN Baseprice9 = BasepriceLast THEN 9
WHEN Baseprice8 = BasepriceLast THEN 8
WHEN Baseprice3 = BasepriceLast THEN 3
WHEN Baseprice1 = BasepriceLast THEN 1
ELSE 0
END
END AS BasepriceMonth
,RangeYearType, Baseprice1, Baseprice3, Baseprice8, Baseprice9
FROM(SELECT RANGE
,'Min'AS RangeYearType
,MIN(YearofValue)AS YearofValue
FROM#temptable
GROUP BY RANGE
UNION ALL
SELECT RANGE
,'Max' AS RangeYearType
,MAX(YearofValue)
FROM#temptable
GROUP BY RANGE
) AS RangeYears
JOIN(SELECT RANGE
,YearofValue
,COALESCE(NULLIF(Baseprice1,0),NULLIF(Baseprice3,0),NULLIF(Baseprice8,0),NULLIF(Baseprice9,0))
as BasepriceFirst
,COALESCE(NULLIF(Baseprice9,0),NULLIF(Baseprice8,0),NULLIF(Baseprice3,0),NULLIF(Baseprice1,0))
as BasepriceLast
,NULLIF(Baseprice1,0) ASBaseprice1
,NULLIF(Baseprice3,0) ASBaseprice3
,NULLIF(Baseprice8,0) ASBaseprice8
,NULLIF(Baseprice9,0) ASBaseprice9
FROM#temptable
)AS temptable
ON temptable.RANGE= RangeYears.RANGE
AND temptable.YearofValue = RangeYears.YearofValue
ORDER BY RangeYears.RANGE
,RangeYears.YearofValue
,RangeYearType
-- Normalized Table Solution
SELECT RangeYears.RANGE
,RangeYears.YearofValue
,RangeYears.RangeYearType
,CASE RangeYearType
WHEN 'Min' THEN MIN(MonthOfValue)
ELSE MAX(MonthOfValue)
END AS BasepriceMonth
FROM(SELECT RANGE
,'Min'AS RangeYearType
,MIN(YearofValue)AS YearofValue
FROM#temptable
GROUP BY RANGE
UNION ALL
SELECTRANGE
,'Max' AS RangeYearType
,MAX(YearofValue)
FROM#temptable
GROUP BY RANGE
) AS RangeYears (RANGE , RangeYearType , YearofValue )
-- Normalize the BasePrices table to have one row for each base price with month as a seperate column
JOIN(SELECT RANGE
,YearofValue
,1AS MonthOfValue
FROM#temptable
WHEREBaseprice1 0
UNION ALL
SELECT RANGE
,YearofValue
,3AS MonthOfValue
FROM#temptable
WHEREBaseprice3 0
UNION ALL
SELECT RANGE
,YearofValue
,8AS MonthOfValue
FROM#temptable
WHEREBaseprice8 0
UNION ALL
SELECT RANGE
,YearofValue
,9AS MonthOfValue
FROM#temptable
WHEREBaseprice9 0
)AS temptable
ON temptable.RANGE= RangeYears.RANGE
AND temptable.YearofValue = RangeYears.YearofValue
GROUP BY RangeYears.RANGE
,RangeYears.YearofValue
,RangeYears.RangeYearType
ORDER BY RangeYears.RANGE
,RangeYears.YearofValue
,RangeYearType
SQL = Scarcely Qualifies as a Language
June 11, 2009 at 5:06 am
Hi,
just try this,
select Range,YearofValue,
(case when Baseprice1 > 0 then '1'
when Baseprice3 > 0 then '3'
when Baseprice8 > 0 then '8'
when Baseprice9 > 0 then '9' end )months
from #temptable
ARUN SAS
June 11, 2009 at 5:20 am
That's great...I really appreciate your help. Works a treat.
I realised half of the problem was the non-normalized table structure, but that's our IT team's responsibility - I'm just one of the risk analysts, so have no real control over base table form.
Thanks again for your time. I'll take some time to understand the queries, and am sure I'll learn something.
Best regards,
Jason
___
June 11, 2009 at 5:29 am
Hi,
I am not sure about the best way it can be done, or the way you had tried this .
You can use the following query to get the required stats:
select A.range ,A.yearofvalue,(case when baseprice1 >0 then 1 when baseprice3 >0 then 3 when baseprice8 >0 then 8 when baseprice9 >0 then 9 else 0 end) as monthNo
from #temptable as A
join
(select [range],min(YearOFValue) as YOV from #temptable group by range
union all
select [range],max(YearOFValue) as YOV from #temptable group by range
) as B
on (A.range = B.range and A.yearOfvalue = b.YOV)
Hopefully this would help 🙂
Prashant Bhatt
Sr Engineer - Application Programming
June 11, 2009 at 6:07 am
You could also combine the Year and Month either as a float, as below, or as a datetime:
SELECT D.Range
,CASE N.N
WHEN 1
THEN FLOOR(MinYearMonthofValue)
ELSE FLOOR(MaxYearMonthofValue)
END AS YearofValue
,CASE N.N
WHEN 1
THEN CAST((MinYearMonthofValue * 100) AS int) % 100
ELSE CAST((MaxYearMonthofValue * 100) AS int) % 100
END AS MonthofValue
FROM
(
SELECT Range
,MIN(YearMonthofValue) AS MinYearMonthofValue
,MAX(YearMonthofValue) AS MaxYearMonthofValue
FROM
(
SELECT T.Range
,T.YearofValue + M.M AS YearMonthofValue
,CASE M.M
WHEN 0.01 THEN BasePrice1
WHEN 0.03 THEN BasePrice3
WHEN 0.08 THEN BasePrice8
WHEN 0.09 THEN BasePrice9
END AS BasePrice
FROM #temptable T
CROSS JOIN
(
SELECT 0.01 UNION ALL
SELECT 0.03 UNION ALL
SELECT 0.08 UNION ALL
SELECT 0.09
) M (M)
) D1
WHERE BasePrice 0
GROUP BY Range
) D
CROSS JOIN
(
SELECT 1 UNION ALL
SELECT 2
) N (N)
June 11, 2009 at 6:43 am
Brain dead today - it can, of course, all be done with integers. Still only one read of the table:
SELECT D.Range
,CASE N.N
WHEN 1
THEN MinYearMonthofValue / 100
ELSE MaxYearMonthofValue / 100
END AS YearofValue
,CASE N.N
WHEN 1
THEN MinYearMonthofValue % 100
ELSE MaxYearMonthofValue % 100
END AS MonthofValue
FROM
(
SELECT Range
,MIN(YearMonthofValue) AS MinYearMonthofValue
,MAX(YearMonthofValue) AS MaxYearMonthofValue
FROM
(
SELECT T.Range
,T.YearofValue * 100 + M.M AS YearMonthofValue
,CASE M.M
WHEN 1 THEN BasePrice1
WHEN 3 THEN BasePrice3
WHEN 8 THEN BasePrice8
WHEN 9 THEN BasePrice9
END AS BasePrice
FROM #temptable T
CROSS JOIN
(
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 8 UNION ALL
SELECT 9
) M (M)
) D1
WHERE BasePrice 0
GROUP BY Range
) D
CROSS JOIN
(
SELECT 1 UNION ALL
SELECT 2
) N (N)
June 11, 2009 at 7:58 am
Ken McKelvey's solution is much more elegant than mine. Ken's solution accesses the table once, compared to my solution, which accesses the table 3 times.
Very nice technique on normalizing the rows and I will need to remember that trick.
SQL = Scarcely Qualifies as a Language
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply