query - help needed - 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)

query - help needed

Author

Topic

akas
Starting Member

42 Posts

Posted - 2009-03-27 : 12:05:12

i have query below. now, i want if vs.itemnmbr='kt-048' then Inventory = (same as vs.itemnmbr='COPDKT01')

so how can i add this query inside this below given query. in shot whatever inventory would be for itemnmbr='COPDKT01' should be same like 'kt-048'.

select

vs.campaign,
vs.itemnmbr,
vs.Title,
sum(vs.Qtyavail) as Inventory

From vwinventory vs

group by vs.itemnmbr,vs.campaign, vs.Title

any help would be appreciated.

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-27 : 12:09:31

Can you explain a li'l further...

What do you mean by "if vs.campaign='copd' and vs.itemnmbr='kt-048' then Inventory "...What do you want to add in the existing query?

akas
Starting Member

42 Posts

Posted - 2009-03-27 : 12:13:48

i edit my topic so it would help better way..

means, output would be like below:

itemnmbr itemtitle inventory
-----------------------------
COPDKIT01 Box 727
KT-048 Resource Kit 727

akas
Starting Member

42 Posts

Posted - 2009-03-27 : 13:02:46

i did like this

select

vs.campaign,
vs.itemnmbr,
vs.Title,
case when vs.itemnmbr = 'kt-048' then (select sum(vs.qtyavail) where vs.itemnmbr='copdkit01')
else sum(vs.Qtyavail) as Inventory

From vwinventory vs

group by vs.itemnmbr,vs.campaign, vs.Title

but i m getting null value:

itemnmbr itemtitle inventory
-----------------------------
COPDKIT01 Box 727
KT-048 Resource Kit null

instead of -

itemnmbr itemtitle inventory
-----------------------------
COPDKIT01 Box 727
KT-048 Resource Kit 727

akas
Starting Member

42 Posts

Posted - 2009-03-27 : 14:05:33

please some one help me!!!!!!!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-27 : 14:50:51

Try following the first link in my signature, and restating your question.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

akas
Starting Member

42 Posts

Posted - 2009-03-27 : 14:53:16

simple select statement - just selected two columns for example-

select distinct itemnmbr, qty , case when (itemnmbr = 'copdkit01' ) then (select qty where itemnmbr='copdkit01') end as inventory from iv102
where itemnmbr in ('kt-048','copdkit01')

itemnmbr qty inventory
--------------------------------------------------
COPDKIT01 727.00000 727.00000
KT-048 0.00000 NULL
so instead of NULL i want same value - 727 how should i get that??

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-27 : 15:13:21

you are valuing the inventory field only when itemnmbr = 'copdkit01'.

Then how will that be valued for itemnmbr = 'kt-048'

Try giving some sample data. What is 727?

akas
Starting Member

42 Posts

Posted - 2009-03-27 : 15:26:39

sample example:

itemnmbrs title inventory
-------------------------------------------------
56-292N acd 444
KT-048 kit 0
COPDKIT01 resource kit 727
06-5840 kiejr 1566
06-5841 erw 4548
06-5845 we 55985

so now where kt-048 - inventory =0 , i want the value 727 (of itemnmbr - copdkit01') instead of 0. the reason is that both are kit title and that's why whatever will be in copdkit01 it should display same value in kt-048. this is for feb month if in march the value of copdkit01 will 900 then in kt-048 will display 900 same value in both itemnmbrs.

itemnmbrs title inventory
-------------------------------------------------
56-292N acd 444
KT-048 kit 727
COPDKIT01 resource kit 727
06-5840 kiejr 1566
06-5841 erw 4548
06-5845 we 55985

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-27 : 15:41:22

Well this requirement sounds strange...

But can you try this

select itemnmbrs,qty , (select inventory from @t where itemnmbrs = 'COPDKIT01') from @t
where itemnmbrs in ('KT-048','COPDKIT01')
union
select itemnmbrs,qty, inventory from @t where itemnmbrs not in ('KT-048','COPDKIT01')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources