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
 Next Value Null

Author  Topic 

chennaraaj
Starting Member

17 Posts

Posted - 2014-04-25 : 06:33:17
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-25 : 08:17:32
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-25 : 08:25:23
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
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-25 : 11:00:01
"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
   

- Advertisement -