simple query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

simple query

Author

Topic

akas
Starting Member

42 Posts

Posted - 2010-06-10 : 13:15:01

Hello Freinds,

I have been given 3 columns items name and inventory in sales table.
if the inventory is 0 than it should be display previous item inventory.

example:
my query - select items,name,inventory from sales
gives results:-
items name inventory
105 ABC 1105.00
106 XXX 2115.00
201 XYZ 152.00
215 ACC 0.00

so in this case as last item 215 inventory is 0 than it should be display previous item inventory - like:
items name inventory
105 ABC 1105.00
106 XXX 2115.00
201 XYZ 152.00
215 ACC 152.00

how can i get that in my query -
select items,name,inventory from sales?

Thanks for your help.

Sachin.Nand

2937 Posts

Posted - 2010-06-10 : 13:42:57

[code]
declare @tbl as table(pid int identity,items int,name varchar(40),inventory decimal(18,2))
insert into @tbl
select 105, 'ABC', 1105.00 union all
select 106, 'XXX', 2115.00 union all
select 201, 'XYZ', 152.00 union all
select 215, 'ACC', 0.00

select * from @tbl

select pid,
items,
name,
case when inventory=0 then t2inventory else inventory end as inventory from @tbl t1
outer apply(select top 1 inventory as t2inventory from @tbl t2 where t1.pid=t2.pid+1 )t

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources