Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
eirikr_1
Starting Member
27 Posts |
Posted - 2013-02-10 : 01:38:26
|
Master tableComputerName. AuditId. AuditDate. A. 111. 1/1/13A. 222. 2/1/13A. 000. 2/1/13B. 111. 1/1/13C. 000. 1/1/13C. 111. 2/1/13AuditId equals to zero telling no vulnerability found on that day scan.I need to write a query to move computer A to different table because there is no vulnerable auditId found after the 2/1/13. Computer C won't be moved because there is another vulnerability in the next day. Please help. Thank you |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-10 : 06:27:41
|
Do you want to move all records for A (the 3 records in this example), or just the one's where there were no vulnerability scans? Also, do you want to move the 1/1/13 record for C? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-11 : 00:52:31
|
[code]INSERT INTO TableB (ComputerName, othercolumns...)SELECT ComputerName,othercolumns...FROM(SELECT MAX(CASE WHEN AuditId='000.' THEN AuditDate END) OVER (PARTITION BY ComputerName) AS ZeroDate,MAX(AuditDate) OVER (PARTITION BY ComputerName) AS MaxDate,*FROM Table)tWHERE MaxDate = ZeroDate[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
eirikr_1
Starting Member
27 Posts |
Posted - 2013-02-11 : 00:56:56
|
Hello JamesSince there is no vulnerability found after 2/1/13. I want to move all As to a historical table.With computer C, Although I found no Vulnerability on 1/1/13, however the scan shows another Vulnerability found on 2/1/13. So I don’t move the computer C record.Thank you for help |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-11 : 01:00:26
|
quote: Originally posted by eirikr_1 Hello JamesSince there is no vulnerability found after 2/1/13. I want to move all As to a historical table.With computer C, Although I found no Vulnerability on 1/1/13, however the scan shows another Vulnerability found on 2/1/13. So I don’t move the computer C record.Thank you for help
You missed the pointhe was asking whether you need to move all records related to Computer A or just the record with no vulnerability (ie latest alone)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
eirikr_1
Starting Member
27 Posts |
Posted - 2013-02-11 : 01:17:05
|
All three records related to A will be moved.Assuming on 2/2/13 scan show another vulnerability found with auditId 1234, then all 4 records related to A wont be moved. It means this computer need to be patched. Thank you with respecful |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-11 : 01:25:02
|
quote: Originally posted by eirikr_1 All three records related to A will be moved.Assuming on 2/2/13 scan show another vulnerability found with auditId 1234, then all 4 records related to A wont be moved. It means this computer need to be patched. Thank you with respecful
ok...then use my suggestion posted earlier------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|