SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Next Value Null
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chennaraaj
Starting Member

India
15 Posts

Posted - 04/25/2014 :  06:33:17  Show Profile  Reply with Quote
Hi All..

PONbr POValue DCNum InvoiceNbr
14/90549 1661359.9 2069 769
14/90549 1661359.9 2118 1152
14/90549 1661359.9 1152 1152
14/90549 1661359.9 2148 808
14/90549 1661359.9 2276 1265
14/90549 1661359.9 2254 836
14/90549 1661359.9 9 5

from the above i need to write the query for to display like below..

PONbr POValue DCNum InvoiceNbr
14/90549 1661359.9 2069 769
14/90549 0.00 2118 1152
14/90549 0.00 1152 1152
14/90549 0.00 2148 808
14/90549 0.00 2276 1265
14/90549 0.00 2254 836
14/90549 0.00 9 5

regards,
RK


rk

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 04/25/2014 :  08:17:32  Show Profile  Reply with Quote
The following query won't do exactly what you are looking for, it will put the last row with the 9 and 5 for DCNum and INvoicNbr at the top. But that is only because I don't know the rule that you used to make the row with DCNum = 2069 the special row that has the correct value of PONum. Is there some other column you are using for determining that?
;WITH cte AS
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY PONbr, POValue ORDER BY InvoiceNbr) AS RN
	FROM
		YourTable
)
SELECT
	PONbr,
	CASE WHEN RN = 1 THEN POValue ELSE 0.0 END AS POValue,
	DCNum,
	InvoiceNbr
FROM
	cte
ORDER BY
	RN;
Go to Top of Page

gbritton
Constraint Violating Yak Guru

405 Posts

Posted - 04/25/2014 :  08:25:23  Show Profile  Reply with Quote
This query produces your desired output. Dunno if this is what you really want:


declare @t table (PONbr	varchar(20), POValue decimal(18,2),	 DCNum	int, InvoiceNbr int)

insert into @t values
('14/90549',	1661359.9,	2069,	769),
('14/90549',	1661359.9,	2118,	1152),
('14/90549',	1661359.9,	1152,	1152),
('14/90549',	1661359.9,	2148,	808),
('14/90549',	1661359.9,	2276,	1265),
('14/90549',	1661359.9,	2254,	836),
('14/90549',	1661359.9,	9,	5)

select PONbr
    , case InvoiceNbr when 769 then POValue else 0.0 end as POValue
    , DCNum
    , InvoiceNbr
from @t

Edited by - gbritton on 04/25/2014 08:25:41
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 04/25/2014 :  11:00:01  Show Profile  Reply with Quote
"next value" is not something you can do based on the physical order of the rows. You need something in the data to establish an order. And I don't see anything special about the row with InvoiceNbr=769 to make that the row with a non-zero POValue. it is not the min or max value and same with other columns. So we can't know that this the "first row" so of course we can't know the "subsequent" rows either. So unless you want to hard-code the magic invoice number like gbritton's solution did you can't do this.

Is there a column or columns you can use to know the logical order of the rows?

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000