Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Need help writing a query

Posted on 2015-01-13
4
58 Views
Last Modified: 2015-01-17
Experts,

Details have been attached

Thanks in advance.
ItemDetails.docx
0
Comment
Question by:Tpaul_10
  • 2
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40547980
To clarify:
need to get the list of distinct ItemIDs where an Item has an ItemCode stating with “G” and it’s previous ItemCode starting with “C”
You write HAS an itemCode starting wtih 'G' (implying that this is the CURRENT status), but, you expect to see ID2 in your results even though that doesn't have a current itemCode starting with G. It does have two sequential historic records with ItemCodes GP and CF (with historicIDs 3 and 2 respectively).

Can I just confirm that the chronological order of the ItemHistoryID values runs 1,2,3,4,5,n..0, where 1 is the oldest status, 2 is the next oldest status and n is the most recently superseded status?

Your item ID3 has 3 rows, all with ItemHistoryID =0. How can the same item have 3 different 'latest/current' statuses?
        ('IP3', 0, 'GP'),
      ('IP3', 0, 'N'),
      ('IP3', 0, 'CF'),
      ('IP3', 1, 'IP')

** In listings below, replace t1 with the name of your table  **

This code gets you CURRENT itemCode like G% with immediate predecessor like C%
with cteCurr as (
  select ItemID,ItemHistoryID,ItemCode from t1 where ItemHistoryID=0),
cteHist as (
   select ItemID,ItemHistoryID,ItemCode
  ,row_number() over (partition by itemID order by ItemHistoryID desc) as HistRank 
  from t1 where ItemHistoryID>0)

select cteCurr.ItemID,cteCurr.ItemCode as CurrCode,cteHist.itemCode as LastCode 
from cteCurr inner join ctehist on ctecurr.itemID=ctehist.itemID
where cteCurr.itemcode like 'G%' and cteHist.Itemcode like 'C%' and cteHist.HistRank = 1

Open in new window


This code gets you ANY itemCode  like G% with immediate predecessor like C%
with cteCurr as (
  select ItemID,ItemHistoryID,ItemCode,0 as HistRank from t1 where ItemHistoryID=0),
cteHist as (
   select ItemID,ItemHistoryID,ItemCode
  ,row_number() over (partition by itemID order by ItemHistoryID desc) as HistRank 
  from t1 where ItemHistoryID>0),
cteAll as (select * from cteCurr UNION ALL select * from cteHist)

select c.ItemID,c.ItemCode as CurrCode,p.itemCode as LastCode 
from cteAll c inner join cteAll p on c.itemID=p.itemID and p.histrank=c.histrank+1
where c.itemcode like 'G%' and p.Itemcode like 'C%' 

Open in new window


This is the test table for the above listings:
CREATE TABLE T1
	([ItemId] varchar(3), [ItemHistoryID] int, [ItemCode] varchar(2))
;
	
INSERT INTO T1
	([ItemId], [ItemHistoryID], [ItemCode])
VALUES
	('IP1', 0, 'GN'),
	('IP1', 1, 'N'),
	('IP1', 2, 'CF'),
	('IP2', 0, 'GN'),
	('IP2', 1, 'N'),
	('IP2', 2, 'CF'),
	('IP2', 3, 'GP'),
	('IP3', 0, 'GP'),
	('IP3', 0, 'N'),
	('IP3', 0, 'CF'),
	('IP3', 1, 'IP')
;

Open in new window

0
 

Author Comment

by:Tpaul_10
ID: 40548039
Thanks for the quick reply Simon and appreciate your questions. Here are the details and please excuse me for the confusion.

1. Not considering the historyID, I need to find out the items having itemcode starting with "C%" with the next row/transaction starting with  "G%".  (That was the reason I have had IP2 in my output.)
2. Yes, 1 is the oldest and 0 is the latest
3. on IP3 it should 0,1,2,3 and it was a type from me.

I have tried the above code and not returning rows but I will verify it again and hope I have clarified what I am looking for in #1

Thanks again.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40549318
Hi,

>"Not considering the historyID"
It seems you do need to consider (make use of) the ItemHistoryID column to order the data. If you don't order the data by ItemID then ItemHistoryID, there is no way of determining which the 'next row/transaction' will be.

Here's some revised code in a self-contained block, with the test data table included to make it easy to check the results.
declare @t1  table (ItemID  char(3),ItemHistoryID  int,ItemCode  varchar(5))

insert into @t1 values
('IP1',	0,	'GN'),
('IP1',	1,	'N'),
('IP1',	2,	'CF'),

('IP2',	0,	'GN'),
('IP2',	1,	'N'),
('IP2',	2,	'CF'),
('IP2',	3,	'GP'),

('IP3',	0,	'GP'),
('IP3',	1,	'N'),
('IP3',	2,	'CF'),
('IP3',	3,	'IP');

;with cteCurr as (
  select ItemID,ItemHistoryID,ItemCode,0 as HistRank from @t1 where ItemHistoryID=0),
cteHist as (
   select ItemID,ItemHistoryID,ItemCode
  ,row_number() over (partition by itemID order by ItemHistoryID desc) as HistRank 
  from @t1 where ItemHistoryID>0),
cteAll as (select * from cteCurr UNION ALL select * from cteHist)

select c.ItemID,c.ItemCode as CurrCode,p.itemCode as PrevCode ,c.ItemHistoryID as HistID,p.ItemHistoryID as PrevHistID
from cteAll c inner join cteAll p on c.itemID=p.itemID and p.histrank=c.histrank+1
where c.itemcode like 'G%' and p.Itemcode like 'C%' 

Open in new window


The result:
the results
0
 

Author Closing Comment

by:Tpaul_10
ID: 40555615
Very accurate and quick help, appreciate it.
Thank You Simon, appreciate all your help with the detailed explanation
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question