PIVOT QUERY and capture specific records in string

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

PIVOT QUERY and capture specific records in string

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-16 : 04:10:28

Hi Forumer's

Need a query to to capture or find a certain records in a string.
I made already a script but im no so sure specially with the jan-feb records which
should be the jan is the reference.
need your additional input please. also the result would be like a pivot format based on ETAMonth and the value is the OpenQty.

--SAMPLE DATA
[code]
Create table #Temp
(Itemid nvarchar(35), OpenQty numeric (28,12),VendorRef nvarchar(100))
Insert into #Temp(Itemid, OpenQty,VendorRef) values ('HTC1112',100,'LG LS670 May FC')
Insert into #Temp(Itemid, OpenQty,VendorRef) values ('HTC1114',50,'HTC A7373 APR FC')
Insert into #Temp(Itemid, OpenQty,VendorRef) values ('HTC1113',300,'SAM D700 Jan-Feb SB')
Insert into #Temp(Itemid, OpenQty,VendorRef) values ('HTC1119',25,'LG ALLY Apr SB')
Insert into #Temp(Itemid, OpenQty,VendorRef) values ('HTC1120',60,'HTC A7373 FEB SB (5,5,5,5,3,5,3,5,5)')
Insert into #Temp(Itemid, OpenQty,VendorRef) values ('HTC1123',5,'Jun FC')
Insert into #Temp(Itemid, OpenQty,VendorRef) values ('HTC1121',2,'HTC THUNDERBOLT JAN SB (3)')
Insert into #Temp(Itemid, OpenQty,VendorRef) values ('HTC1122',2,'HTC EVO MAR SB (3,5,5,3,7)')
--QUERY
;WITH OpenPO
AS
(
Select
Itemid,
OpenQty,
Case When r.VendorRef like '%May%' Then 'May'
When r.VendorRef like '%APR%' Then 'Apr'
When r.VendorRef like '%Jan-Feb%' or r.vendorref like '%JAN%' Then 'Jan'
When r.VendorRef like '%Mar%' Then 'Mar'
When r.VendorRef like '%Apr%' Then 'Apr'
When r.VendorRef like '%Feb%' or r.vendorref like '%Feb SB' Then 'Feb'
When r.VendorRef like '%Jun%' Then 'Jun'
else ' ' End As ETAMonth
From #Temp r
)
SELECT Itemid,
[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec],
[Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec] as Total
FROM OpenPO
PIVOT
(
count(ETAMonth) FOR (ETAMonth)) IN ([Jan], [Feb], [Mar], [Apr],[May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])
) AS PivotTable;

Derive Result:

ITEMID---JAN-FEB--MAR--APR--MAY--JUN--JUL--AUG--SEP--OCT--NOV--DEC
------------------------------------------------------------------
HTC1112---------------------100
HTC1114-----------------50
HTC1113--300
HTC1119----------------25
HTC1120------60
HTC1121--2
HTC1122-----------2
HTC1123---------------------------5
[\code]

THANK YOU in Advance..

JOV

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-16 : 04:19:40

change to


PIVOT
(
        count(ETAMonth) 
	sum(OpenQty)

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-16 : 04:28:32

Thank you kthan.

Btw, kindly please take a look this on..
I have concern specially this portion..

When r.VendorRef like '%Jan-Feb%' or r.vendorref like '%JAN%' Then 'Jan'

Select
Itemid,
OpenQty,
Case When r.VendorRef like '%May%' Then 'May'
When r.VendorRef like '%APR%' Then 'Apr'
[bold]When r.VendorRef like '%Jan-Feb%' or r.vendorref like '%JAN%' Then 'Jan'[\bold]
When r.VendorRef like '%Mar%' Then 'Mar'
When r.VendorRef like '%Apr%' Then 'Apr'
When r.VendorRef like '%Feb%' or r.vendorref like '%Feb SB' Then 'Feb'
When r.VendorRef like '%Jun%' Then 'Jun'
else ' ' End As ETAMonth
From #Temp r

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-16 : 04:42:09

what is your concern ? Can you also explain what is the logic here ?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources