Update the initial log entry of an item 1

[tqeonline]

So I created an app and I need to go and update all of the very first log entries.

The app collects devices and when you add a device it writes a log entry. The status of the log entry is "in" and then the device can be checked in or out over and over. It is basically an inventory application. However, I'm trying to figure out how to count how many phones are checked out and be able to do it historically.

So I want to count all the IN's then get a count of all the OUT's subtract the two and you have how many are out. HOWEVER - since I have the initial entry as "IN" then it shows like 1600 devices checked in and only 200 out so it says -1400 are checked out. If I can update all the initial entries to "Added" or "Initial" then i believe it will work.

SELECT [ID]
  ,[USER_ID]
  ,[DEVICE_ID]
  ,[ACTION]
  ,[DATE_TIME]
FROM [SQA_bsd_qa_db].[dbo].[AMA_Device_History]

This is the select query... What I need to do is something along the lines of:

update [SQA_bsd_qa_db].[dbo].[AMA_Device_History]
where min(date_time]
group by [device_id]

I just can't wrap my head around it. I only want to update the very first entry for each device id.

any ideas?

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

this gets all the min entries:

SELECT [DEVICE_ID]
  ,min([DATE_TIME])
FROM [SQA_bsd_qa_db].[dbo].[AMA_Device_History]
where [ACTION] = 'IN'
and [DEVICE_ID] like 'D%'
group by [device_id]
order by device_id

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[gmmastros]

Use your query as a derived table that you join back to the original table. If you inner join on the device id and the date/time, it will only update the rows that match.

UPDATE Device
Set    Device.ACTION = 'Initial'
From   [SQA_bsd_qa_db].[dbo].[AMA_Device_History]  As Device
       Inner Join ([blue]
         SELECT [DEVICE_ID]
           ,min([DATE_TIME])
         FROM [SQA_bsd_qa_db].[dbo].[AMA_Device_History]
         where [ACTION] = 'IN'
         and [DEVICE_ID] like 'D%'
         group by [device_id][/blue]
         ) As Initial
         On Device.DEVICE_ID = Initial.DEVICE_ID
         And Device.DATE_TIME = Initial.DATE_TIME

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tqeonline]

A few minor tweaks (naming the second column, and join reference) and we have a working product!

Thanks George!

UPDATE Device
SET Device.ACTION = 'Initial'
FROM   [SQA_bsd_qa_db].[dbo].[AMA_Device_History]  AS Device       
INNER JOIN (         
	SELECT [DEVICE_ID]           
	,MIN([DATE_TIME]) AS MIN_DATE_TIME         
	FROM [SQA_bsd_qa_db].[dbo].[AMA_Device_History]         
	WHERE [ACTION] = 'IN'         
	AND [DEVICE_ID] LIKE 'D%'         
	GROUP BY [DEVICE_ID]) AS Initial         
ON Device.DEVICE_ID = Initial.DEVICE_ID         
AND Device.DATE_TIME = Initial.MIN_DATE_TIME

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[gmmastros]

Cool. Do you understand how this works? Using a derived table to inner join back to itself is a powerful technique that you may find other uses for.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tqeonline]

yes the derived table gets the list of the items and then the inner join limits the list because an inner join says it has to exist on both table sto show up.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008