SQL Sub-Query Help

Hi,
Please try this-

SELECT
DISTINCT
incidentidentifier,
CAST(whencreated as datetime) whencreated,
COUNT(*) OVER (PARTITION BY b.clientID) Counts
FROM ticketCategory as a
inner join im_incident as b
on a.ticketcategoryid = b.ticketcategoryid
where a.name = 'ROW DOWN'	
FROM im_incident C

Select allOpen in new window

Hope it helps!

Something like:

SELECT

incidentidentifier,
(CAST(whencreated as datetime)) as Created,
COUNT(b.clientID) as TimesOccured
		FROM ticketCategory as a
				inner join im_incident as b
				on a.ticketcategoryid = b.ticketcategoryid
				where a.name = 'ROW DOWN'	
				group by incidentidentifier, whencreated

								

Select allOpen in new window

Also can you please provide some sample rows from both the table. I think there is still some catch.

Pawan your solution was 80% correct. It still does not duplicate identifiers but that is because in the identifiers.

How would you like to send you the data? In what format?

Any format, Paste directly here or you can also use Excel file. paste the data in excel and attach.

Here you go, Pawan.
results_ee_query.xlsx

Hi,
Thanks for the data.

Can you please explain this more -

Pawan your solution was 80% correct. It still does not duplicate identifiers but that is because in the identifiers.

What you need here?

       354989      TX008 Round Rock 009-354989      ROW Down      1/28/2015
983      354990      TX008 Round Rock 009-354990      ROW Down      1/28/2015

You need 1 row from these 2 rows?

Well 80% is inaccurate, more like 95%. I apologize. :)

You are correct. I just want a count for events like TX008 and to display the identifier. I understand that the two events are different by 009-354989 and 009-354990. Maybe stripping out those contents and just leaving TX008 Round Rock and showing the count of times the event ROW Down occurred.

Does that make sense?

select b.ClientID, count(b.IncidentID)
FROM ticketCategory as a
inner join im_incident as b
on a.ticketcategoryid = b.ticketcategoryid
where a.name = 'ROW DOWN'
group by b.ClientID
having count(b.IncidentID) > 1

awking00, that is 98% right. Is there a way to make the identifier appear but not duplicate to appear?

You could add max or min IncidentID.

Hi sj77,
Please try this..

SELECT
DISTINCT
MAX(incidentidentifier) OVER (PARTITION BY b.clientID,CAST(whencreated as datetime)) incidentidentifier ,
CAST(whencreated as datetime) whencreated,
COUNT(*) OVER (PARTITION BY b.clientID) Counts
FROM ticketCategory as a
inner join im_incident as b
on a.ticketcategoryid = b.ticketcategoryid
where a.name = 'ROW DOWN'	
FROM im_incident C

Select allOpen in new window

so replace count(incidentid) with max or min(incidentid)?

Please use the solution I gave. Either Max or MIN.

I did and look at the results. You see how the identifier is duplicated? I understand that there are unique pieces in each identifier. Is there a way to maybe just strip everything and leave the AL002 Montgomery as opposed to have two lines(as an example)?
ee_queryresult.jpg

Your sample file is only for ticketCategory table, right? How do you relate ticketCategory and im_incident tables? Is only by the CategoryID? What about the IncidentID? I'm leaving you 2 options and would like to know what they seems to you.
Link only on Category:

SELECT incidentidentifier,
	(CAST(whencreated as datetime)),
	TimesOccurred = (SELECT COUNT(C.clientID)
					FROM ticketCategory as a
					where a.ticketcategoryid = C.ticketcategoryid AND a.name = 'ROW DOWN')
FROM im_incident C

Select allOpen in new window

Link only on Category and IncidentID:

SELECT incidentidentifier,
	(CAST(whencreated as datetime)),
	TimesOccurred = (SELECT COUNT(C.clientID)
					FROM ticketCategory as a
					where a.IncidentID = C.incidentidentifier
					 AND a.ticketcategoryid = C.ticketcategoryid
					 AND a.name = 'ROW DOWN')
FROM im_incident C

Select allOpen in new window

Hi sj77,
Please find the code for you last requirement-

--

;WITH CTE AS
(
	SELECT
	DISTINCT
	MAX(incidentidentifier) OVER (PARTITION BY b.clientID,CAST(whencreated as datetime)) incidentidentifier ,
	CAST(whencreated as datetime) whencreated,
	COUNT(*) OVER (PARTITION BY b.clientID) Counts
	FROM ticketCategory as a
	inner join im_incident as b
	on a.ticketcategoryid = b.ticketcategoryid
	where a.name = 'ROW DOWN'	
	FROM im_incident C
)
,CTE1 AS 
(
	SELECT CASE WHEN RIGHT(LTRIM(RTRIM(SUBSTRING(incidentidentifier,0,PATINDEX('%[0-9]%',incidentidentifier)))),1) = '-' THEN 
	SUBSTRING(LTRIM(RTRIM(SUBSTRING(incidentidentifier,0,PATINDEX('%[0-9]%',incidentidentifier)))),0
	,LEN(LTRIM(RTRIM(SUBSTRING(incidentidentifier,0,PATINDEX('%[0-9]%',incidentidentifier)))))-1)
	ELSE LTRIM(RTRIM(SUBSTRING(incidentidentifier,0,PATINDEX('%[0-9]%',incidentidentifier)))) END incidentidentifier
	,whencreated,Counts
	FROM CTE
)
SELECT incidentidentifier,MAX(whencreated) whencreated , SUM(DISTINCT [Counts]) Counts
FROM CTE1
GROUP BY incidentidentifier


--

Select allOpen in new window

Hope it helps!

I am unable to test (not sure which table storename comes from) but the following might do what you want.
select b.ClientIDcount(b.IncidentID)
,left(storename,len(storename) - charindex(' ',reverse(storename))) as storename
,count(b.IncidentID)
FROM ticketCategory as a
inner join im_incident as b
on a.ticketcategoryid = b.ticketcategoryid
where a.name = 'ROW DOWN'
group by b.ClientID,left(storename,len(storename) - charindex(' ',reverse(storename)))
having count(b.IncidentID) > 1;

For example. this should produce for ClientID -
1164 | CO004 Highlands Ranch | 6

Pawan you almost have it. The results are so close. However, its trimming the substring way too much.

For example, for AL002 it trims to AL. You have the right idea here and we are so close. Could you have it trim to just AL002 and show that only? Why is this important? Well because our stores are identified by state identifiers ex: AL000 or TX000, etc. So we would have TX001 or TX080 as an example. Or AL001 or AL010.

Does that make sense?

If you fix the trim to the 5 characters that would show our answer.

BINGO!

There it is!

Thank you very much, Pawan!

Glad to help sj77 !