May 21, 2014 at 1:05 pm
Is there a way to add a count of the occurances of my concatenated field "ConCatCNLN"
that have a count of 1 or 2 and just return a set with just those (rare if any)?
SELECT DISTINCT
L.[ResourceID]
,T.[ClassName]
,L.[LookupName]
,T.[SystemName]
,L.[LongValue]
,L.[ShortValue]
,L.[Value]
,T.[ClassName] + ' ' + L.[LookupName] AS ConCatCNLN
FROM [tblLogJam] L WITH(NOLOCK)
LEFT JOIN
[tblTolerance] T WITH(NOLOCK)
ON L.[LookupName] = T.[LookupName]
AND L.[ResourceID] = T.[ResourceID]
ORDER BY
L.[LookupName]
,T.[ClassName]
,T.[SystemName]
,L.[LongValue]
May 21, 2014 at 2:29 pm
SQLalchemy (5/21/2014)
Is there a way to add a count of the occurances of my concatenated field "ConCatCNLN"that have a count of 1 or 2 and just return a set with just those (rare if any)?
SELECT DISTINCT
L.[ResourceID]
,T.[ClassName]
,L.[LookupName]
,T.[SystemName]
,L.[LongValue]
,L.[ShortValue]
,L.[Value]
,T.[ClassName] + ' ' + L.[LookupName] AS ConCatCNLN
FROM [tblLogJam] L WITH(NOLOCK)
LEFT JOIN
[tblTolerance] T WITH(NOLOCK)
ON L.[LookupName] = T.[LookupName]
AND L.[ResourceID] = T.[ResourceID]
ORDER BY
L.[LookupName]
,T.[ClassName]
,T.[SystemName]
,L.[LongValue]
Not quite sure what you mean. You could add a count easily enough.
Something Like this:
SELECT DISTINCT
L.[ResourceID]
,T.[ClassName]
,L.[LookupName]
,T.[SystemName]
,L.[LongValue]
,L.[ShortValue]
,L.[Value]
,T.[ClassName] + ' ' + L.[LookupName] AS ConCatCNLN
, COUNT(T.[ClassName] + ' ' + L.[LookupName]) AS ConCatCNLN_Count
from FROM [tblLogJam] L WITH(NOLOCK)
LEFT JOIN
[tblTolerance] T WITH(NOLOCK)
ON L.[LookupName] = T.[LookupName]
AND L.[ResourceID] = T.[ResourceID]
GROUP BY L.[ResourceID]
,T.[ClassName]
,L.[LookupName]
,T.[SystemName]
,L.[LongValue]
,L.[ShortValue]
,L.[Value]
,T.[ClassName] + ' ' + L.[LookupName]
HAVING COUNT(T.[ClassName] + ' ' + L.[LookupName]) BETWEEN 1 AND 2 --This would get only those with 1 or 2 occurences
ORDER BY
L.[LookupName]
,T.[ClassName]
,T.[SystemName]
,L.[LongValue]
Notice I did a strike through on your NOLOCK hints. Do you understand what that hint does? Are you ok with inaccurate results because of missing and/or duplicate data?
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2014 at 6:40 am
Actually I'm trying to have a count of specific values resulting in the concatenated field... typical data in this fiild looks like this and I've added the totals and quotes manually here:
"C 564"
"C 564"
"C 564"
"C 564"
"C 564"
_______
Total 5
"C 563"
"C 563"
"C 563"
_______
Total 3
"J 561"
"J 561"
_______
Total 2
and ultimately I'm looking for totals that are 1 or 2
my first thought was to use PARTITION BY, but this may not work, at least I've tried and couldn't get it to work
May 22, 2014 at 7:02 am
I finally got it to work with PARTITION BY:
SELECT DISTINCT
L.[ResourceID]
,T.[ClassName]
,T.[SystemName] AS IncomingFieldName
,L.[LookupName]
,L.[LongValue]
,L.[ShortValue]
,L.[Value]
,T.[SystemName] AS MatrixSelectName
,L.[LongValue] AS MatrixSelectValue
,L.[Value] AS MatrixSelectRVALUE
,COUNT(T.[ClassName] + ' ' + L.[LookupName]) OVER (PARTITION BY(T.[ClassName] + ' ' + L.[LookupName])) AS CountConcat
FROM tblLogJam L
LEFT JOIN
tblTolerance T
ON L.[LookupName] = T.[LookupName]
AND L.[ResourceID] = T.[ResourceID]
WHERE
T.ResourceID = 'Property'
AND T.ClassName <> 'XPROP'
ORDER BY
L.[LookupName]
,T.[ClassName]
,T.[SystemName]
,L.[LongValue]
July 8, 2014 at 6:53 pm
How did you only pick those items with a count of 1 or 2 in the batch? Did you use a CTE /inner view ?
Thanks
----------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply