vendredi 19 juin 2015

SQL - replace returned data with other data

I am retrieving data using the SQL syntax below:

SELECT TOP 5 EventId, EventTime, DeviceName, Comment, Tenant, TenantName, Individual, 
                InetDb.dbo.Individuals.FirstName, InetDb.dbo.Individuals.LastName, InetDb.dbo.IndivImages.UserImage
                FROM taclogdata.dbo.Event
                LEFT JOIN InetDb.dbo.Tenants
                    ON taclogdata.dbo.Event.Tenant = InetDb.dbo.Tenants.TenantId
                LEFT JOIN InetDb.dbo.Individuals
                    ON taclogdata.dbo.Event.Individual = InetDb.dbo.Individuals.IndivId 
                    AND taclogdata.dbo.Event.Tenant = InetDb.dbo.Individuals.TenantNdx
                LEFT JOIN InetDb.dbo.IndivImages
                    ON InetDb.dbo.Individuals.IndivId = InetDb.dbo.IndivImages.IndivNdx
                    AND InetDb.dbo.Individuals.TenantNdx = InetDb.dbo.IndivImages.TenantNdx
                WHERE (taclogdata.dbo.Event.EventTime  > DATEADD(hh, -3, GETDATE())AND taclogdata.dbo.Event.EventTime < GETDATE())
                    AND (taclogdata.dbo.Event.Comment='Reader entry' OR taclogdata.dbo.Event.Comment='Reader exit')
                    AND (taclogdata.dbo.Event.DeviceName = 'L9 1/4/1' 
                        OR taclogdata.dbo.Event.DeviceName='L1 2/1/1-2 MainD'
                        OR taclogdata.dbo.Event.DeviceName='L1 2/1/3-4 MainD'
                        OR taclogdata.dbo.Event.DeviceName='L1 2/6/1-2 Stair'
                        OR taclogdata.dbo.Event.DeviceName='L1 2/2/1-2 FDT1')
                ORDER BY taclogdata.dbo.Event.EventTime DESC

This code works fine, however I'm trying to simplify the results.
I'm trying to simplify what the query returns, by replacing the DeviceName value from e.g. L1 2/1/3-4 MainD to Main Door when the results are shown (not replace the actual data in the database)

How may I achieve this please ?
Thanks in advance,
J

Aucun commentaire:

Enregistrer un commentaire