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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.