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
LVL 1
csePixelatedAsked:
Who is Participating?
 
Máté FarkasDatabase Developer and AdministratorCommented:
Yes, you have to extend your query:
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


But you may need to adjust AlarmDate with DATEADD(HOUR, -6, SignalHistory.AlarmDate) before you join.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
0
 
csePixelatedAuthor Commented:
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'.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Máté FarkasDatabase Developer and AdministratorCommented:
Yes it depends on the name of the column in your table [SUBSCRIBER].[dbo].[Subscriber Status].
You can replace Stat.Status AS [Subscriber Status] in line 12 to Stat.AccountID AS [Subscriber Status]
0
 
csePixelatedAuthor Commented:
Ok, it works now, how would i get it to list a simple true or false?
0
 
csePixelatedAuthor Commented:
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...
0
 
Máté FarkasDatabase Developer and AdministratorCommented:
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).
0
 
csePixelatedAuthor Commented:
Ty Máté Farkas, that works great, any ideas as to renaming the column?
0
 
Mark WillsTopic AdvisorCommented:
IIF(ISNULL(Stat.AccountID, 0) > 0,'True','False') AS TE
0
 
csePixelatedAuthor Commented:
IIF(ISNULL(Stat.AccountID, 0) > 0,'True','False') AS TE

Open in new window

parse results " Incorrect syntax near '>' "
1
 
Máté FarkasDatabase Developer and AdministratorCommented:
That is correct. Check that previous column is terminated by a comma.
0
 
csePixelatedAuthor Commented:
it is terminated by a comma, still shows " Incorrect syntax near '>' " Im running sql server 2008r2 if that makes a difference?
0
 
csePixelatedAuthor Commented:
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 '>'.
0
 
Mark WillsTopic AdvisorCommented:
oops, sorry about that... IIF() is only available from SQL 2012

might need to use CASE e.g.
declare @id int

select IIF(isnull(@id,0) > 0, 'True','False') as TE

select case when isnull(@id,0) > 0 then 'True' else 'False' end as TE

Open in new window


So, you might need   :  case when isnull(Stat.AccountID,0) > 0 then 'True' else 'False' end as TE
0
 
csePixelatedAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.