April 17, 2017 at 3:57 pm
I need a little help with a SQL query. I have a table that has a uniqueidentifier column, a descriptor, a reason, and a modified date. The table basically stores history information based on the uniqueidentifier, which is based on another table. What I need to do is get all uniqueidentfiers (GUID) that don’t have a specific reason. The data could look as follows:
[GUID1], NewItem, UnitAdded, 2017-01-01
[GUID1], UserUpdated, NameChanged, 2017-01-02
[GUID1], NewItem, UnitAdded, 2017-01-03
[GUID1], UserModified, NameUpdated, 2017-01-03
[GUID1], UserModified, NameUpdated, 2017-01-03
[GUID1], RecordClosed, Closed, 2017-01-03
[GUID2], NewItem, UnitAdded, 2017-01-01
.
.
[GUID100], RecordClosed, Closed, 2017-01-31
In the example of GUID1, there are no instances of a reason called ‘Reason.Reason’. Assume GUID2 thru GUID99 all have a Reason at least once of ‘Reason.Reason’, I want to be able to get the 2 distinct GUIDs (GUID1 and GUID100) that don’t have it, but can’t seem to get that info accurately.
Any help is greatly appreciated.
Regards,
R.
April 17, 2017 at 4:13 pm
WHERE NOT EXISTS? Or possibly just a not equals.
Note I am just guessing here since you didn't provide CREATE table statements with INSERTs of sample data and your exact expected output.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 17, 2017 at 4:43 pm
April 17, 2017 at 5:00 pm
Here's a sample data set... my result set would be the following GUIDs that don't have at least 1 instance of 'reason.reason':
5B82C628-2FA5-4A97-A665-001476DCBB9E
E1CF3C1C-7DDA-4006-820C-001748BEC17F
7BCD51C2-4494-401A-82BD-002A18AB3B07
O9DE7318-5739-4617-84DB-00A106DE3E3E
The other 2 GUID's have a reason.reason value, so they should be excluded from results.
INSERT INTO #temp_data VALUES
('E1CF3C1C-7DDA-4006-820C-001748BEC17F','Insert','Resolution','New Item',NULL,NULL,'2017-03-23 20:59:10'),
('E1CF3C1C-7DDA-4006-820C-001748BEC17F','Update','Resolution','Age','0','1','2017-03-24 07:22:19'),
('E1CF3C1C-7DDA-4006-820C-001748BEC17F','Update','Resolution','UserModified.ByUserId',NULL,'c2c21d87-cadd-452b-a98e-b72a42f9ebc5','2017-03-28 11:29:41'),
('E1CF3C1C-7DDA-4006-820C-001748BEC17F','Update','Resolution','UserModified.On',NULL,'3/28/17 11:29','2017-03-28 11:29:41'),
('E1CF3C1C-7DDA-4006-820C-001748BEC17F','Update','Resolution','Reason.Reason','Open','In Progress','2017-03-28 11:29:41'),
('E1CF3C1C-7DDA-4006-820C-001748BEC17F','Update','Resolution','UserModified.On','3/28/17 11:29','3/28/17 11:30','2017-03-28 11:30:33'),
('E1CF3C1C-7DDA-4006-820C-001748BEC17F','Update','Resolution','UserModified.On','4/14/17 19:52','4/14/17 19:58','2017-04-14 19:58:46'),
('5B82C628-2FA5-4A97-A665-001476DCBB9E','Insert','Resolution','New Item',NULL,NULL,'2017-03-31 18:51:50'),
('5B82C628-2FA5-4A97-A665-001476DCBB9E','Update','Resolution','Age','0','1','2017-04-03 09:02:41'),
('5B82C628-2FA5-4A97-A665-001476DCBB9E','Update','Resolution','UserModified.ByUserId',NULL,'4c53816b-1347-4894-8348-025491889a1a','2017-04-05 17:51:19'),
('5B82C628-2FA5-4A97-A665-001476DCBB9E','Update','Resolution','UserModified.On',NULL,'4/5/17 17:51','2017-04-05 17:51:19'),
('E1CF3C1C-7DDA-4006-820C-001748BEC17F','Insert','Resolution','New Item',NULL,NULL,'2017-03-23 20:59:10'),
('E1CF3C1C-7DDA-4006-820C-001748BEC17F','Update','Resolution','Age','0','1','2017-03-24 07:22:19'),
('E1CF3C1C-7DDA-4006-820C-001748BEC17F','Insert','Resolution','Payoff',NULL,'Report Generated','2017-03-24 19:10:13'),
('E1CF3C1C-7DDA-4006-820C-001748BEC17F','Insert','Resolution','Summary',NULL,'Report Generated','2017-03-24 19:10:27'),
('7BCD51C2-4494-401A-82BD-002A18AB3B07','Insert','Resolution','New Item',NULL,NULL,'2017-04-13 20:24:39'),
('7BCD51C2-4494-401A-82BD-002A18AB3B07','Update','Resolution','Age','0','1','2017-04-14 06:59:10'),
('7BCD51C2-4494-401A-82BD-002A18AB3B07','Update','Resolution','UserModified.On',NULL,'4/14/17 15:54','2017-04-14 15:54:55'),
('7BCD51C2-4494-401A-82BD-002A18AB3B07','Update','Resolution','UserModified.ByUserId',NULL,'cf1992a2-f068-48fc-b95c-31f5da883a1d','2017-04-14 15:54:55'),
('7BCD51C2-4494-401A-82BD-002A18AB3B07','Update','Resolution','Closed.ByUserId',NULL,'0f6aa46e-f2df-455e-9492-3447f003668f','2017-04-17 00:11:55'),
('7BCD51C2-4494-401A-82BD-002A18AB3B07','Update','Resolution','Closed.On',NULL,'4/17/17 0:11','2017-04-17 00:11:55'),
('09DE7318-5739-4617-84DB-00A106DE3E3E','Insert','Resolution','New Item',NULL,NULL,'2017-03-21 16:24:02'),
('09DE7318-5739-4617-84DB-00A106DE3E3E','Update','Resolution','Age','0','1','2017-03-22 00:01:14'),
('09DE7318-5739-4617-84DB-00A106DE3E3E','Update','Resolution','Age','1','2','2017-03-23 00:01:14'),
('O9DE7318-5739-4617-84DB-00A106DE3E3E','Update','Resolution','UserModified.ByUserId',NULL,'157ef044-01e4-462a-8bfc-012443d0cb15','2017-03-23 20:11:49'),
('09DE7318-5739-4617-84DB-00A106DE3E3E','Update','Resolution','UserModified.On',NULL,'3/23/17 20:11','2017-03-23 20:11:49'),
('09DE7318-5739-4617-84DB-00A106DE3E3E','Update','Resolution','UserModified.On','3/23/17 20:11','3/23/17 20:12','2017-03-23 20:12:36'),
('09DE7318-5739-4617-84DB-00A106DE3E3E','Update','Resolution','UserModified.On','3/23/17 20:12','3/23/17 20:14','2017-03-23 20:14:03'),
('09DE7318-5739-4617-84DB-00A106DE3E3E','Update','Resolution','UserModified.On','3/23/17 20:14','3/23/17 20:15','2017-03-23 20:15:39'),
('F3E992BD-44D9-4237-9C2E-00A495F2B935','Insert','Resolution','New Item',NULL,NULL,'2017-04-11 21:47:30'),
('F3E992BD-44D9-4237-9C2E-00A495F2B935','Update','Resolution','Age','0','1','2017-04-12 07:05:50'),
('F3E992BD-44D9-4237-9C2E-00A495F2B935','Update','Resolution','UserModified.ByUserId',NULL,'cf1992a2-f068-48fc-b95c-31f5da883a1d','2017-04-14 16:26:26'),
('F3E992BD-44D9-4237-9C2E-00A495F2B935','Update','Resolution','UserModified.On',NULL,'4/14/17 16:26','2017-04-14 16:26:26'),
('F3E992BD-44D9-4237-9C2E-00A495F2B935','Update','Resolution','Reason.Reason','Open','Close','2017-04-17 12:37:10'),
('F3E992BD-44D9-4237-9C2E-00A495F2B935','Update','Resolution','Closed.On',NULL,'4/17/17 12:37','2017-04-17 12:37:10')
April 17, 2017 at 5:58 pm
pseudo code:
select
from tablea a
where not exists (select * from tablea b where a.guid = b.guid and b.reason <> reason.reason)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 17, 2017 at 6:58 pm
Tried that with my sample data, but get 0 results:
April 17, 2017 at 7:28 pm
Try this...
SELECT DISTINCT
td1.Id
FROM
#temp_data td1
WHERE
NOT EXISTS (
SELECT 1
FROM
#temp_data td2
WHERE
td1.Id = td2.Id
AND td2.FieldName = 'Reason.Reason'
);
Results:
Id
--------------------------------------------------
09DE7318-5739-4617-84DB-00A106DE3E3E
5B82C628-2FA5-4A97-A665-001476DCBB9E
7BCD51C2-4494-401A-82BD-002A18AB3B07
O9DE7318-5739-4617-84DB-00A106DE3E3E
April 17, 2017 at 7:34 pm
Well, that brings back the 3 rows that have reason.reason. i want/need to return the ones that don't have FieldName = 'Reason.Reason'.
April 17, 2017 at 7:39 pm
woody_rd - Monday, April 17, 2017 7:34 PMWell, that brings back the 3 rows that have reason.reason. i want/need to return the ones that don't have FieldName = 'Reason.Reason'.
Check your test data... You only have 2 rows where FieldName = 'Reason.Reason'...
E1CF3C1C-7DDA-4006-820C-001748BEC17F
and
F3E992BD-44D9-4237-9C2E-00A495F2B935
April 17, 2017 at 7:43 pm
That's exactly right. THose are the 2 ID's I want to return. But when I change to != from = I get 0 results again.
April 17, 2017 at 7:57 pm
That's exactly right. THose are the 2 ID's I want to return. But when I change to != from = I get 0 results again. My apologies, I think I misstated in one of the posts. I need just the 2 GUIDS that DON'T have FieldName = 'reason.reason'.
April 17, 2017 at 7:59 pm
My apologies, I think I misstated what results I was looking for. I just want GUIDs that DON'T have a FieldName = 'Reason.Reason'
April 17, 2017 at 8:00 pm
woody_rd - Monday, April 17, 2017 7:43 PMThat's exactly right. THose are the 2 ID's I want to return. But when I change to != from = I get 0 results again.
You need to make up your mind... First you want IDs that DON't have a FieldName = 'Reason.Reason'... Now you want the ones that do...???
In your sample data, you have 6 distinct IDs... (two of them have a row of FieldName = 'Reason.Reason' and four that don't)
E1CF3C1C-7DDA-4006-820C-001748BEC17F
F3E992BD-44D9-4237-9C2E-00A495F2B935
09DE7318-5739-4617-84DB-00A106DE3E3E
5B82C628-2FA5-4A97-A665-001476DCBB9E
7BCD51C2-4494-401A-82BD-002A18AB3B07
O9DE7318-5739-4617-84DB-00A106DE3E3E
If you want the two that DO...
SELECT
td.Id
FROM
#temp_data td
WHERE
td.FieldName = 'Reason.Reason';
If you want the ones that DON'T...
SELECT DISTINCT
td1.Id
FROM
#temp_data td1
WHERE
NOT EXISTS (
SELECT 1
FROM
#temp_data td2
WHERE
td1.Id = td2.Id
AND td2.FieldName = 'Reason.Reason'
);
April 17, 2017 at 8:01 pm
woody_rd - Monday, April 17, 2017 7:59 PMMy apologies, I think I misstated what results I was looking for. I just want GUIDs that DON'T have a FieldName = 'Reason.Reason'
Then use the NOT EXISTS query...
April 18, 2017 at 10:13 am
Here is a solution that only requires a single scan of the table.
;
WITH guids_ordered AS
(
SELECT Id, FieldName,
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY CASE WHEN td.FieldName = 'Reason.Reason' THEN 0 ELSE 1 END, ModifiedOn) AS rn
FROM #temp_data td
)
SELECT Id
FROM guids_ordered g
WHERE rn = 1
AND FieldName <> 'Reason.Reason'
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply