Help with simple SQL query

either <> or != or not (xx= yy)

 EntryDescription <> 'Successfully changed workstation domain membership.'

Select allOpen in new window

If there can be only one 'Start' and only one 'Successful' message, then:

SELECT migration_id
FROM table_name
WHERE EntryDescription LIKE 'Start Auto Migration%' OR
    EntryDescription LIKE 'Successfully changed workstation domain membership%'
GROUP BY migration_id
HAVING COUNT(*) = 2

If there can be more than one of either:

SELECT migration_id
FROM table_name
WHERE EntryDescription LIKE 'Start Auto Migration%' OR
    EntryDescription LIKE 'Successfully changed workstation domain membership%'
GROUP BY migration_id
HAVING MAX(CASE WHEN EntryDescription LIKE 'Start Auto Migration%' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN EntryDescription LIKE 'Successfully changed workstation domain membership%' THEN 1 ELSE 0 END) = 1

so like this?

where EntryDescription = 'Start Auto Migration' and TransactionTimeStamp between '2016-06-17' and '2016-06-20' order by TransactionTimeStamp desc;
EntryDescription <> 'Successfully changed workstation domain membership.'

or does that mean that your table could contain 2 rows for the same migration ID, one for start and one for successfully?

select *
from YourTableName as AliasStart
left join YourTableName as AliasSuccess
on AliasSuccess.MigrationID = AliasStart.MigrationID 
and AliasSuccess.EntryDescription  = 'Successfully changed workstation domain membership.'
where AliasStart.EntryDescription = 'Start Auto Migration' 
and AliasStart.TransactionTimeStamp between '2016-06-17' and '2016-06-20' 
AND AliasSuccess.MigrationID is null

Select allOpen in new window

this doesn't work together with AND statement....

which query is not working ?

This works!!!! Thank you so much Eric! Excellent answer

oh. Sorry. I have been told this is incorrect even though it works.

"The entry with EntryDescription = 'Start Auto Migration' obviously will not match the string with 'Successfully changed workstation domain membership.’!"


where EntryDescription = 'Start Auto Migration'
and TransactionTimeStamp between '2016-06-17' and '2016-06-20'
and EntryDescription <> 'Successfully changed workstation domain membership.'
order by TransactionTimeStamp desc

Something like this I have been told but trying this and it failing to run. Could you please let me know what is wrong here.



select  distinct BatchId from migrationActivity


where EntryDescription = 'Start Auto Migration' AND


TransactionTimeStamp >= '2016-06-16' AND TransactionTimeStamp < '2016-06-18' AND


SubStepName NOT IN ( 'GetConfigSetting', 'SQLROLLBACK' ) AND


BatchId NOT IN (


select  distinct BatchId from migrationActivity


where


EntryDescription = 'Start Auto Migration' AND  


TransactionTimeStamp >= '2016-06-16' AND TransactionTimeStamp < '2016-06-18' AND


EntryDescription = 'Successfully changed workstation domain membership.'


)

as I already told you:

if EntryDescription = 'Start Auto Migration' that means that it is automatically <> 'Successfully changed workstation domain membership.'. That means that you don't need both condition!

For a single record, the field cannot have both descriptions!

have you tried my query of https:#41664827

again, this condition does not make sense:

where 
EntryDescription = 'Start Auto Migration' AND  
TransactionTimeStamp >= '2016-06-16' AND TransactionTimeStamp < '2016-06-18' AND 
EntryDescription = 'Successfully changed workstation domain membership.' 

Select allOpen in new window

oh it works!!! I was executing it wrong. I selected script table as select and New window and it works now!!

Thank you so much for your help!!

Hello Eric,

You are correct. Below doesn't make sense. EntryDescription has many rows in the column. How would I select more than one value from EntryDescriptioin?

where
EntryDescription = 'Start Auto Migration' AND  
TransactionTimeStamp >= '2016-06-16' AND TransactionTimeStamp < '2016-06-18' AND
EntryDescription = 'Successfully changed workstation domain membership.'



EntryDescription
Completed Auto Migration
FinalizeReporting completed.
FinalizeReporting called
RunWorkstationMigrattionTool Completed
WaitForExit for msiexec.exe completed, exit code = 0x0: The operation completed successfully

have you tried my query of https:#41664827

can you re-word what you are trying to achieve? can you provide a sample of data and the expected result to illustrate your problem?

I am trying to locate all migration IDs that ran on around those dates that have EntryDescription = “Start Auto Migration”  but do NOT have an EntryDescription = “Successfully changed workstation domain membership.”

Also, I want to locate all migration IDs that ran on around those dates that have EntryDescription = “Start Auto Migration”  AND have an EntryDescription = “Successfully changed workstation domain membership.”


There are hundreds of EntryDescription rows. So now I just keep scrolling and looking for particular ones. Would be nice to filter those and to see what I need.

Also, I want to locate all migration IDs that ran on around those dates that have EntryDescription = “Start Auto Migration”  AND have an EntryDescription = “Successfully changed workstation domain membership.”

This is still not making sense. A single row of data cannot have to different value for the EntryDescription.

For the 3rd time, have you tried my query of https://www.experts-exchange.com/questions/28953165/Help-with-simple-SQL-query.html?anchorAnswerId=41664827#a41664827

Éric:
There are multiple rows in the table for each MigrationID, reflecting different actions/states of that id.


select BatchId --as BatchIdWithoutSuccessMsg, ...
from (
    select *, ROW_NUMBER() over(PARTITION BY BatchId ORDER BY TransactionTimeStamp DESC) AS row_num
    from migrationActivity
    where TransactionTimeStamp >= '2016-06-16' AND TransactionTimeStamp < '2016-06-18' AND
        EntryDescription in ( 'Start Auto Migration', 'Successfully changed workstation domain membership.' )
) as derived
where row_num = 1 AND
    --can be true ONLY if the 'Successfully changed ...' message is NOT present for this Batchid.
    EntryDescription = 'Start Auto Migration'

once again: have you tried my query of https://www.experts-exchange.com/questions/28953165/Help-with-simple-SQL-query.html?anchorAnswerId=41664827#a41664827