what is wrong wth this script - 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

General SQL Server Forums

New to SQL Server Programming

what is wrong wth this script

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-01 : 23:18:29

Guys,

My objective is to get all itemID that like (SMA,GLO,SAM) and DataareaID ('tbc'). running this script, there are some dataareaid which should not be included in the result. it display also tkt, car and tlc. i need only dataareaid is 'tbc'.

Select
inv.itemid as ItemNumber,
tab.ITEMNAME,
cust.EXTERNALITEMID,
SUM(inv.physicalinvent) as OnHandQty,
SUM(inv.availphysical) as Availablephysical,
SUM(inv.onOrder) as SOQty,
SUM(inv.ORDERED) as POQty,
inv.DATAAREAID
from TABLE1 as inv
Left Outer Join TABLE2 as tab
On inv.ITEMID = tab.ITEMID
Left Outer Join TABLE3 as cust
On inv.ITEMID= cust.ITEMID
where inv.ITEMID like ('SMA%')
or inv.ITEMID like ('GLO%')
or inv.ITEMID like ('SAM%')
and inv.DATAAREAID in ('tbc')
group by inv.itemid,
tab.ITEMNAME,
inv.DATAAREAID,
cust.EXTERNALITEMID
order by inv.itemid

Thank you in advance.

JOV

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-01 : 23:23:08

[code]
where (inv.ITEMID like ('SMA%')
or inv.ITEMID like ('GLO%')
or inv.ITEMID like ('SAM%'))
and inv.DATAAREAID in ('tbc')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-01 : 23:34:30

Thank you Kthan. its running.
btw, i have another query.
I have to pullout supplier based on the latest shipdate.
here is my script and sample data. thanks.

Select
t1.ItemNumber,
Parsename(replace(t1.itemNumber,'-','.'),2) AS Model,
t1.ESN,
t1.COO,
t2.supplierId
From #VPData2 as t1
Cross Apply (Select MAX(Shipdate) as shipdate, SupplierId
from chrisMartin.dbo.ESNShip
where ESN = t1.esn COLLATE Chinese_Taiwan_Stroke_CI_AS
group by supplierid ) t2

TABLE1
ItemNumber|Model|ESN|COO
P300-1710-DROIDX-U|DROIDX|123|PHI

TABLE2
ESN|ModelCode|Supplier|Shipdate
123|P300-1710-DROIDX-U|45|2010-12-30
123|P300-1710-DROIDX-U|43|2011-07-16
123|P300-1710-DROIDX-U|43|2010-10-04

jov

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-01 : 23:38:34

[code]
Cross Apply
(
Select TOP 1 SupplierId
from chrisMartin.dbo.ESNShip
where ESN = t1.esn COLLATE Chinese_Taiwan_Stroke_CI_AS
order by Shipdate desc
) t2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources