September 27, 2013 at 9:44 am
Hi
I have written a query and the output for the query is
Run DateFiscal Year Posting PeriodPortFuel Oil PriceGas Oil Price
20061031200610FOSNULL578.5
20061031200610FOS275NULL
20061031200610FUJNULL592.5
20061031200610FUJ276NULL
20061031200610GEN273NULL
20061031200610GENNULL568.5
20061031200610HOU262NULL
20061031200610HOUNULL537.5
20061031200610NYK306NULL
20061031200610ROTNULL472.5
Query is
select top 10 CONVERT( varchar(28),observation_dt,112) AS [Run Date],
cast(year(applicable_dt)as varchar(28))as [Fiscal Year],
cast(month(applicable_dt)as varchar(28)) as [Posting Period],
cast(price_reference_cd as varchar(28)) as Port,
(case r.price_type_cd
when'FUEL OIL'then cast(quote_val as varchar(28)) END)as [Fuel Oil Price],
(case r.price_type_cd
when'GAS OIL'then cast(quote_val as varchar(28)) END)as [Gas Oil Price]
from OSS_MARKET_PRICE_DAILY o inner join rd_market_price r
on o.market_price_seq=r.market_price_seq
where r.price_type_cd in ('FUEL OIL','GAS OIL')
group by observation_dt,applicable_dt,price_reference_cd,quote_val,price_type_cd
order by 1,2,3,4
I want to get the values as
Run DateFiscal Year Posting PeriodPortFuel Oil PriceGas Oil Price
2013060520139HOU599.985574.19
20130605201310HLS597.975573.5199
20130605201311SIN596.3572.85
20130605201312STE594.625572.18
2013060520141TRI592.615597.305
2013060520142YOS590.605597.305
2013060520143FOS590.605597.305
Can any one pls let me know what to modify to get both prices values i one line
Regards
Naveen
September 27, 2013 at 10:02 am
You need to add a MAX (or MIN) to your price columns and group by the others.
September 28, 2013 at 10:45 pm
I need to get the last 2 columns which are price in a single row. currently it is getting as null and value, value and null as values but i need to it as value and value
eg: current:
col1 col2
null 10
20 null
expected output is
col1 col2
20 10
Thanks
Naveen
September 29, 2013 at 9:46 am
This gets your original data into the desired format.
Consumable DDL & Insert Statements:
declare @t1 table
(
RunDate date,
FiscalYear int,
PP int,
Port char(3),
OilPrice decimal(11,2),
GasPrice decimal(11,2)
)
insert @t1 (RunDate, FiscalYear, PP, Port, OilPrice, GasPrice) values
('20061031', 2006, 10, 'FOS', NULL, 578.5)
,('20061031', 2006, 10, 'FOS', 275, NULL)
,('20061031', 2006, 10, 'FUJ', NULL, 592.5)
,('20061031', 2006, 10, 'FUJ', 276, NULL)
,('20061031', 2006, 10, 'GEN', 273, NULL)
,('20061031', 2006, 10, 'GEN', NULL, 568.5)
,('20061031', 2006, 10, 'HOU', 262, NULL)
,('20061031', 2006, 10, 'HOU', NULL, 537.5)
,('20061031', 2006, 10, 'NYK', 306, NULL)
,('20061031', 2006, 10, 'ROT', NULL, 472.5)
Query that returns output formatted like you posted.
;with cte as
(
select rundate, fiscalyear, pp, port, oilprice, gasprice,
ROW_NUMBER() over (partition by rundate, fiscalyear, pp, port order by port) as RowNum
from @t1
)
select c.rundate, c.fiscalyear, c.pp, c.port,
case when n.oilprice is null then c.OilPrice else n.OilPrice end OilPrice,
case when c.gasprice is null then n.GasPrice else c.GasPrice end GasPrice
from cte as c
left outer join cte as n
on c.Port = n.Port and c.RowNum = N.RowNum - 1
where c.RowNum = 1
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 29, 2013 at 6:38 pm
Like Luis suggested, can't you just do a GROUP BY?
SELECT RunDate, FiscalYear, PP, Port
,OilPrice=MAX(OilPrice)
,GasPrice=MAX(GasPrice)
FROM @t1
GROUP BY RunDate, FiscalYear, PP, Port
Based on LinksUp's set up data.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply