SQL Query Help

• dt293

  Default port

  Points: 1412

  More actions

  September 23, 2009 at 5:16 am

  #138998

  Hi,

  I need a little assistance with this query. Basically I need to select, UserID, ObjectID, the total number of attempts as well as the latest status.

  I have the total number of attempts but just need a bit of help with getting the latest status.

  Thanks

  CREATE TABLE #Status

  (

  [SessionID] uniqueidentifier,

  [UserID] uniqueidentifier,

  [ObjectID] uniqueidentifier,

  [SessionDate] datetime,

  [Status] varchar(20)

  )

  INSERT INTO #Status ([SessionID], [UserID], [ObjectID], [SessionDate], [Status])

  SELECT

  'A0084BE2-32E7-4FDE-A199-8637CAE35EB0','3430A7DF-6B8D-43C2-92D4-7932F234B6A6','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-09 09:32:53.000','Incomplete'

  UNION

  SELECT

  '26F3E5A5-45C0-4764-AAFB-57BFC25A3DF8','3430A7DF-6B8D-43C2-92D4-7932F234B6A6','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-14 16:26:51.000','Incomplete'

  UNION

  SELECT

  '3BF55C75-3695-4752-A120-7801F7EF6EA6','3430A7DF-6B8D-43C2-92D4-7932F234B6A6','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-15 09:10:38.000','Complete'

  UNION

  SELECT

  'B393D6D1-ED49-405F-9D87-E84B4DA3209D','3430A7DF-6B8D-43C2-92D4-7932F234B6A6','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-15 09:52:37.000','Complete'

  UNION

  SELECT

  'E4694456-AE90-459D-B4E4-5301B882637A','FE25EEB1-2293-4717-BC23-80B32EEA3551','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-15 11:03:55.000','Incomplete'

  UNION

  SELECT

  '04A316C8-82BF-4253-8ED5-E857908B0BB2','FE25EEB1-2293-4717-BC23-80B32EEA3551','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-16 14:14:28.000','Incomplete'

  UNION

  SELECT

  'A7ADE8B9-D378-48BE-965C-C70872F3B48D','EB35A6F2-7902-4CE5-9F97-8C566C5757F1','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-11 11:02:58.000','Complete'

  UNION

  SELECT

  '0AAD1CC0-AE82-4E7A-87EA-A7EC6DA4E7FE','F0407841-CD3A-4B9F-9218-A8671D6FDF12','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-16 08:48:17.000','Incomplete'

  UNION

  SELECT

  '3071A6D5-8A1D-44A7-8EED-EED1EE615F51','F0407841-CD3A-4B9F-9218-A8671D6FDF12','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-16 17:24:50.000','Complete'

  UNION

  SELECT

  '4F38D065-7964-4BB0-BFEA-606C7AC5FFA9','F0407841-CD3A-4B9F-9218-A8671D6FDF12','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-16 18:39:50.000','Complete'

  UNION

  SELECT

  'D67370CB-F8D4-4698-9916-B02FA38CAE18','344464A0-6775-4C61-A77B-A95DA67E7897','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-17 07:49:27.000','Incomplete'

  UNION

  SELECT

  '2FBF4B11-A9C6-4491-9416-85A11EF8C309','9B8B93D3-7317-4E9E-8CD0-AD13EC06610B','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-09-16 17:38:39.000','Incomplete'

  UNION

  SELECT

  '031168D9-BA91-4D3C-8B62-15F07074314A','6227A866-A1B3-44ED-BEF8-BE9108741BFC','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-08-19 13:45:05.000','Incomplete'

  UNION

  SELECT

  'AF9E1563-1C38-46EB-84CC-78DB475C96AD','6227A866-A1B3-44ED-BEF8-BE9108741BFC','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-08-19 15:04:33.720','Incomplete'

  UNION

  SELECT

  '371F5754-9A9D-4EB0-AA2A-78B0A8CF7274','6227A866-A1B3-44ED-BEF8-BE9108741BFC','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-08-19 15:07:12.673','Incomplete'

  UNION

  SELECT

  '5EB1AD06-97B5-4E3E-83D1-4306AA42B082','6227A866-A1B3-44ED-BEF8-BE9108741BFC','C505159A-BDA6-4A40-8B5F-327FC4130B4E','2009-08-27 12:00:35.000','Incomplete'

  SELECT

  UserID,

  ObjectID,

  COUNT(ObjectID) AS [Attempts]

  FROM

  #Status

  GROUP BY

  UserID,

  ObjectID

• HowardW

  One Orange Chip

  Points: 29109

  More actions

  September 23, 2009 at 5:50 am

  #1057421

  Assuming SessionID is your PK (which I hope it isn't in your real table):

  ;with CTE as (

  select ObjectID,UserID, status, row_number() over (partition by UserID,ObjectID order by SessionDate desc) as sequence from #status

  )

  SELECT S.UserID,S.ObjectID, S.Attempts,Status as Latest_Status FROM

  (SELECT

  UserID,

  ObjectID,

  COUNT(ObjectID) AS [Attempts]

  FROM

  #Status S

  GROUP BY

  UserID,

  ObjectID) S

  inner join CTE on CTE.UserID=S.UserID and CTE.ObjectID=S.ObjectID

  where CTE.sequence=1

  D'oh! Original post was incorrect. Now fixed!

• dt293

  Default port

  Points: 1412

  More actions

  September 23, 2009 at 5:51 am

  #1057422

  Please ignore, I have solved the issue. Will post when I have double checked the code.

  ------

  Thanks HowardW, your solution is great and works perfectly. Will use this instead of my own 🙂

• HowardW

  One Orange Chip

  Points: 29109

  More actions

  September 23, 2009 at 6:02 am

  #1057427

  No problem - make sure you use the code I just edited as the first code messed up your count for number of attempts.