asked on

date range from table to show weather timestamp falls within date range.

I have the following query

SELECT DATEADD(HOUR, -6, SignalHistory.AlarmDate) 'Alarm Date', 
       IsNull(Subscriber.AcctLineCode, 'XX') AS LC, Subscriber.AcctNum, Subscriber.Name, Codes.Description, SignalHistory.AlarmZones as 'zone #', IsNull(Zones.Description, '') AS 'Zone Description', Subscriber.Address, Subscriber.City, Subscriber.State, SignalHistory.RawData
FROM [History0218].[dbo].[SignalHistory] SignalHistory
   LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones ON SignalHistory.AccountID = Zones.AccountID AND LTRIM(SignalHistory.AlarmZones) = LTRIM (Zones.Number)
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -1, GETUTCDATE())AND (Subscriber.AcctLineCode ='DS'OR Subscriber.AcctLineCode ='DSA')
ORDER BY AlarmDate DESC

Open in new window

I now have one more table I need to reference, to add one more column to my results, and i'm not sure how to get the result i am looking for....
the new table is in [subscriber] and it is dbo.Subscriber status. this of course has the standard key 'AccountID' in it
what i need to do is check this table for 2 columns, A 'TestStart' column and a 'TestEnd' column, if the 'AlarmDate' fits within the test window for the 'AccountID' i need to have a column that states weather each line matches.

Im assuming I will have to add another left join
(AlarmDate >= [TestStart] AND AlarmDate <= [TestEnd]) = yes

AndyAinscow

More or less correct, this should be what you wanted
AND (AlarmDate >= [TestStart] AND AlarmDate <= [TestEnd])
in the WHERE clause

no need for the = yes that you had

ASKER CERTIFIED SOLUTION

Máté Farkas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csePixelated

ASKER

SELECT DATEADD(HOUR, -6, SignalHistory.AlarmDate) AS [Alarm Date], 
       ISNULL(Subscriber.AcctLineCode, 'XX') AS LC, 
       Subscriber.AcctNum, 
       Subscriber.Name, 
       Codes.Description, 
       SignalHistory.AlarmZones AS [Zone #], 
       ISNULL(Zones.Description, '') AS [Zone Description], 
       Subscriber.Address, 
       Subscriber.City, 
       Subscriber.State, 
       SignalHistory.RawData,
       Stat.Status AS [Subscriber Status]
FROM [History0218].[dbo].[SignalHistory] SignalHistory
LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones           ON SignalHistory.AccountID = Zones.AccountID 
                                                          AND LTRIM(SignalHistory.AlarmZones) = LTRIM (Zones.Number)
LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes      ON SignalHistory.AlarmCode = Codes.Code
LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Status] Stat     ON SignalHistory.AccountID = Stat.AccountID
                                                          AND SignalHistory.AlarmDate BETWEEN Stat.TestStart AND Stat.TestEnd 
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -1, GETUTCDATE()) 
AND Subscriber.AcctLineCode IN ('DS', 'DSA')
ORDER BY SignalHistory.AlarmDate DESC

Open in new window

Parses ok, but when I run the sql
Msg 207, Level 16, State 1, Line 12
Invalid column name 'Status'.

SOLUTION

Máté Farkas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csePixelated

ASKER

Ok, it works now, how would i get it to list a simple true or false?

csePixelated

ASKER

is it possible to shorten the column name, I tried

(Stat.AccountID AS [Subscriber Status]) AS 'TE'

Open in new window

of course that gave an incorrect syntax error...

Máté Farkas

Change that column to:

SIGN(ISNULL(Stat.AccountID, 0)) AS [Subscriber Status]

Open in new window

This will give you a 0 (false) or 1 (true).

csePixelated

ASKER

Ty Máté Farkas, that works great, any ideas as to renaming the column?

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csePixelated

ASKER

IIF(ISNULL(Stat.AccountID, 0) > 0,'True','False') AS TE

Open in new window

parse results " Incorrect syntax near '>' "

Máté Farkas

That is correct. Check that previous column is terminated by a comma.

csePixelated

ASKER

it is terminated by a comma, still shows " Incorrect syntax near '>' " Im running sql server 2008r2 if that makes a difference?

csePixelated

ASKER

SELECT DATEADD(HOUR, -6, SignalHistory.AlarmDate) AS [Alarm Date], 
       ISNULL(Subscriber.AcctLineCode, 'XX') AS LC, 
       Subscriber.AcctNum, 
       Subscriber.Name, 
       Codes.Description, 
       SignalHistory.AlarmZones AS [Zone #], 
       ISNULL(Zones.Description, '') AS [Zone Description], 
       Subscriber.Address, 
       Subscriber.City, 
       Subscriber.State, 
       SignalHistory.RawData,
       IIF(ISNULL(Stat.AccountID, 0) > 0,'True','False') AS TE
FROM [History0218].[dbo].[SignalHistory] SignalHistory
LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones           ON SignalHistory.AccountID = Zones.AccountID 
                                                          AND LTRIM(SignalHistory.AlarmZones) = LTRIM (Zones.Number)
LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes      ON SignalHistory.AlarmCode = Codes.Code
LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Status] Stat     ON SignalHistory.AccountID = Stat.AccountID
                                                          AND SignalHistory.AlarmDate BETWEEN Stat.TestStart AND Stat.TestEnd
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -8, GETUTCDATE()) 
AND Subscriber.AcctLineCode IN ('rc', 'rcd')
ORDER BY SignalHistory.AlarmDate DESC

Open in new window

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '>'.

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csePixelated

ASKER

works like a charm Mark, thanks

SELECT DATEADD(HOUR, -6, SignalHistory.AlarmDate) AS [Alarm Date], 
       ISNULL(Subscriber.AcctLineCode, 'XX') AS LC, 
       Subscriber.AcctNum, 
       Subscriber.Name, 
       Codes.Description, 
       SignalHistory.AlarmZones AS [Zone #], 
       ISNULL(Zones.Description, '') AS [Zone Description], 
       Subscriber.Address, 
       Subscriber.City, 
       Subscriber.State, 
       SignalHistory.RawData,
       case when isnull(Stat.AccountID,0) > 0 then '1' else 'F' end as TE
FROM [History0218].[dbo].[SignalHistory] SignalHistory
LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones           ON SignalHistory.AccountID = Zones.AccountID 
                                                          AND LTRIM(SignalHistory.AlarmZones) = LTRIM (Zones.Number)
LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes      ON SignalHistory.AlarmCode = Codes.Code
LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Status] Stat     ON SignalHistory.AccountID = Stat.AccountID
                                                          AND SignalHistory.AlarmDate BETWEEN Stat.TestStart AND Stat.TestEnd
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -8, GETUTCDATE()) 
AND Subscriber.AcctLineCode IN ('rc', 'rcd')
ORDER BY SignalHistory.AlarmDate DESC

Open in new window

I shortened the answers as i need them as low profile as possible