December 14, 2017 at 2:31 am
Thanks Chris, I understand how this is confusing for everyone here after re-reading what I have posted. I work on creating better sample data showing exactly what I am trying to achieve and post back. Thanks again.
December 14, 2017 at 5:47 am
Hi Chris, I'm hoping the below will now make a bit more sense. The objective of this is in text at the bottom of the query. Thanks in Advance
-- Sample data
IF OBJECT_ID('tempdb..#CSIQUESTIONLOG') IS NOT NULL DROP TABLE #CSIQUESTIONLOG
SELECT * INTO #CSIQUESTIONLOG FROM (VALUES
('BA', '2017-01-01','123451', '185', 2),
('BA', '2017-01-01','123452', '185', 4),
('BA', '2017-01-01','123453', '184', 1),
('BA', '2017-01-01','123454', '183', 3),
('BA', '2017-01-01','123455', '182', 5),
('BA', '2017-01-01','123456', '181', 0),
('BA', '2017-01-01','123457', '182', 1),
('BA', '2017-01-01','7684417', '180', 2)
) d (Dealer, Created, Logseqno, CSIseqno, Answer)
IF OBJECT_ID('tempdb..#LOGFILE') IS NOT NULL DROP TABLE #LOGFILE
SELECT * INTO #LOGFILE FROM (VALUES
(7684417, 'BA', 498, 'WCM', 1261723),
(7669984, 'BA', 38, 'CSI', 1261723),
(7685141, 'BA', 400, 'WCM', 1261750),
(7686369, 'BA', 193, 'CSI', 1261750),
(7692571, 'BA', 401, 'WCM', 1262289),
(7700336, 'BA', 38, 'CSI', 1262289)
) d (Seq, Dealer, OpNum, Contcode, ContSeqNo)
SELECT a.*, x.Seq AS [SEQ2]
FROM #LOGFILE a
OUTER APPLY (
SELECT Seq
FROM #LOGFILE b
WHERE b.ContSeqNo = a.ContSeqNo AND b.ContCode = 'CSI'
) x
--FINAL QUERY
SELECT #CSIQUESTIONLOG.Created, #CSIQUESTIONLOG.CSIseqno, #LOGFILE.OpNum
,COUNT (*) AS TOTAL
FROM #CSIQUESTIONLOG
JOIN #LOGFILE ON #CSIQUESTIONLOG.LogSeqNo=#LOGFILE.Seq AND #LOGFILE.Contcode = 'WCM'
GROUP BY #CSIQUESTIONLOG.Created, #CSIQUESTIONLOG.CSIseqno, #LOGFILE.OpNum
--IS IT POSSIBLE TO CHANGE
--JOIN #LOGFILE ON #CSIQUESTIONLOG.LogSeqNo=#LOGFILE.Seq AND #LOGFILE.Contcode = 'WCM'
--TO
--JOIN #LOGFILE ON #CSIQUESTIONLOG.LogSeqNo=#LOGFILE.[SEQ2] AND #LOGFILE.Contcode = 'WCM'
--SO IT JOINS TO THE [SEQ2] COLUMN NOT THE SEQ COLUMN
--AND SO I ONLY HAVE TO RUN ONE QUERY IE THE FINAL QUERY
December 14, 2017 at 7:59 am
Please post expected results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 15, 2017 at 12:59 am
Hi Drew,
thanks for coming back to me. I can't really post expected results as I dont know how to join to [SEQ2]. This is what I am trying to achieve as per the query
--IS IT POSSIBLE TO CHANGE
--JOIN #LOGFILE ON #CSIQUESTIONLOG.LogSeqNo=#LOGFILE.Seq AND #LOGFILE.Contcode = 'WCM'
--TO
--JOIN #LOGFILE ON #CSIQUESTIONLOG.LogSeqNo=#LOGFILE.[SEQ2] AND #LOGFILE.Contcode = 'WCM'
--SO IT JOINS TO THE [SEQ2] COLUMN NOT THE SEQ COLUMN
--AND SO I ONLY HAVE TO RUN ONE QUERY IE THE FINAL QUERY
December 15, 2017 at 7:36 am
craig.jenkins - Friday, December 15, 2017 12:59 AMHi Drew,thanks for coming back to me. I can't really post expected results as I dont know how to join to [SEQ2]. This is what I am trying to achieve as per the query
--IS IT POSSIBLE TO CHANGE
--JOIN #LOGFILE ON #CSIQUESTIONLOG.LogSeqNo=#LOGFILE.Seq AND #LOGFILE.Contcode = 'WCM'
--TO
--JOIN #LOGFILE ON #CSIQUESTIONLOG.LogSeqNo=#LOGFILE.[SEQ2] AND #LOGFILE.Contcode = 'WCM'
--SO IT JOINS TO THE [SEQ2] COLUMN NOT THE SEQ COLUMN
--AND SO I ONLY HAVE TO RUN ONE QUERY IE THE FINAL QUERY
The issue I see here is that you are trying to take a specific approach and see where it takes you rather than take a specific destination and see what the best way to get there is. SQL Server doesn't have an INDEX MATCH, because SQL Server is not a spreadsheet application. Stop trying to force it into one.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 15, 2017 at 7:51 am
HI Drew, whilst I appreciate what you are saying; as a newbee to sql all I'm trying to do is find a solution to a problem, The solution ChrisM gave me to the initial index match query is perfect and exactly what I need I'm just trying to build on that.
December 15, 2017 at 8:08 am
craig.jenkins - Friday, December 15, 2017 7:51 AMHI Drew, whilst I appreciate what you are saying; as a newbee to sql all I'm trying to do is find a solution to a problem, The solution ChrisM gave me to the initial index match query is perfect and exactly what I need I'm just trying to build on that.
If you had a problem, you would be able to tell us what results you expect. What you have is an approach (INDEX MATCH) looking for a problem, which is why you're unable to specify expected results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 15, 2017 at 9:39 am
Let me put this another way.
MATCH returns the position of a value within a range. This requires that the value has a defined position within the range. That is, it requires an ordered set. SQL Server is based on set theory, and sets do not have order, so records (or fields) cannot have a specified position within the set.
INDEX returns the value from a specific position within a range. Again, this requires an ordered set. Again, SQL Server does not have ordered sets.
To put it another way, it's not clear what the relation between the WCM record and the CSI record is. Based on your question and the data provided, I'm guessing that you have an EAV, and what you really need to do is unpivot your data, but since you are focused on the approach, rather than the problem, you are having trouble conveying whether that really is the case.
If you do indeed have an EAV, then this approach is probably better.
SELECT ContSeqNo, MIN(CASE WHEN ContCode = 'CSI' THEN Seq END) AS CSI, MIN(CASE WHEN ContCode = 'WCM' THEN Seq END) AS WCM
FROM #SampleData
GROUP BY ContSeqNo
Of course, you haven't explained what to do when there are multiple values that MATCH. I've used the MIN value.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply