SQL query against a table containing multiple entries of the same key, that i'm already consolidating some results in a singal cell

So given the following sql 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,
       Subscriber.MainPhone,
       MISC.Disposition,
       Siglog.faction as CellPhones,
       case when isnull(Stat.AccountID,0) > 0 then '1' else 'F' end as TE
FROM [History0218].[dbo].[SignalHistory] SignalHistory
cross apply (select stuff((select ','+isnull(faction,'') 
             from [History0218].[dbo].[SignalLog] siglog 
             where siglog.SignalID = SignalHistory.SignalID 
             and siglog.faction like '%-%-%'                                     
             for XML path('')),1,1,'')) siglog(faction)
LEFT JOIN [MISCELLANEOUS].[dbo].[Dispositions] MISC       ON SignalHistory.DispositionID = MISC.DispositionID
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(DAY, -3, GETUTCDATE())
ORDER BY SignalHistory.AlarmDate DESC

Open in new window


I have discovered that there is a more reliable place for me to check for my 'TE' field, however it involves checking to see if there is an entry for the SignalID in the dbo.SignalLog.faction that contains the text '* Zone/Code is in TEST Mode'
unfortunately the dbo.SignalLog has multiple instances of SignalID and im already using it to accumulate different entries into the result column "CellPhones'

siglog.faction = '* Zone/Code is in TEST Mode'

Open in new window

how do i incorperate the new criteria for finding 'TE' in my query without messing up the other function?
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.

PortletPaulEE Topic AdvisorCommented:
Use a second cross apply, e.g.
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
    , Subscriber.MainPhone
    , MISC.Disposition
    , Siglog.faction                              AS cellphones
    , case when te.test = 1 then '1' else 'F' end AS te
FROM [History0218].[dbo].[SignalHistory] signalhistory
cross apply (select stuff((select ','+isnull(faction,'') 
             from [History0218].[dbo].[SignalLog] siglog 
             where siglog.SignalID = SignalHistory.SignalID 
             and siglog.faction like '%-%-%'                                     
             for XML path('')),1,1,'')) siglog(faction)
cross apply (select 1 
             from [History0218].[dbo].[SignalLog] siglog 
             where siglog.SignalID = SignalHistory.SignalID 
             and siglog.faction = '* Zone/Code is in TEST Mode'                                   
             ) te(test)
LEFT JOIN [MISCELLANEOUS].[dbo].[Dispositions] MISC       ON SignalHistory.DispositionID = MISC.DispositionID
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(DAY, -3, GETUTCDATE())
ORDER BY SignalHistory.AlarmDate DESC

Open in new window

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:
The above parses ok, but returns no results? why would this be?
0
csePixelatedAuthor Commented:
ok it would seem that i figured out part of it
'* Zone/Code is in TEST Mode
'

Open in new window

is the full entry I am looking for, however it seems to only be including things that match 1 on the TE column.
0
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference ithrough the Colors of STEM program.

PortletPaulEE Topic AdvisorCommented:
For the apply that I added, use OUTER APPLY instead.  This will then allow unmatched rows into the result. Sorry I should have foreseen that.
0
csePixelatedAuthor Commented:
Now i get results however they are all F, is this checking to see if all the entries for that signal id say
'* Zone/Code is in TEST Mode
             '

Open in new window

or is it only the 1st entry or a single entry?
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
    , Subscriber.MainPhone
    , MISC.Disposition
    , Siglog.faction                              AS cellphones
    , case when te.test = 1 then '1' else 'F' end AS te
FROM [History0218].[dbo].[SignalHistory] signalhistory
cross apply (select stuff((select ','+isnull(faction,'') 
             from [History0218].[dbo].[SignalLog] siglog 
             where siglog.SignalID = SignalHistory.SignalID 
             and siglog.faction like '%-%-%'                                     
             for XML path('')),1,1,'')) siglog(faction)
outer apply (select 1 
             from [History0218].[dbo].[SignalLog] siglog 
             where siglog.SignalID = SignalHistory.SignalID 
             and siglog.faction = '* Zone/Code is in TEST Mode
             '                                   
             ) te(test)
LEFT JOIN [MISCELLANEOUS].[dbo].[Dispositions] MISC       ON SignalHistory.DispositionID = MISC.DispositionID
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(DAY, -2, GETUTCDATE())AND (Subscriber.AcctLineCode ='cs'OR Subscriber.AcctLineCode ='csa')
ORDER BY SignalHistory.AlarmDate DESC

Open in new window

0
csePixelatedAuthor Commented:
I got it now, i needed to look for more than one value, so that's on me thank you again PortletPaul!
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
    , Subscriber.MainPhone
    , MISC.Disposition
    , Siglog.faction                              AS cellphones
    , case when te.test = 1 then '1' else 'F' end AS te
FROM [History0218].[dbo].[SignalHistory] signalhistory
cross apply (select stuff((select ','+isnull(faction,'') 
             from [History0218].[dbo].[SignalLog] siglog 
             where siglog.SignalID = SignalHistory.SignalID 
             and siglog.faction like '%-%-%'                                     
             for XML path('')),1,1,'')) siglog(faction)
outer apply (select 1 
             from [History0218].[dbo].[SignalLog] siglog 
             where siglog.SignalID = SignalHistory.SignalID 
             and siglog.faction IN ('* Account is in TEST Mode
', '* Zone/Code is in TEST Mode
'                                   
            ) ) te(test)
LEFT JOIN [MISCELLANEOUS].[dbo].[Dispositions] MISC       ON SignalHistory.DispositionID = MISC.DispositionID
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(DAY, -2, GETUTCDATE())AND (Subscriber.AcctLineCode ='cs'OR Subscriber.AcctLineCode ='csa')
ORDER BY SignalHistory.AlarmDate DESC

Open in new window

0
PortletPaulEE Topic AdvisorCommented:
If there is a possibility that you could return more than 1 row, then you may need to do something like this:


    , case when te.test > 0 then '1' else 'F' end AS te

outer apply (select count(*)
             from [History0218].[dbo].[SignalLog] siglog
             where siglog.SignalID = SignalHistory.SignalID
             and siglog.faction IN ('* Account is in TEST Mode
                                                 ', '* Zone/Code is in TEST Mode
                                                  )
           ) te(test)
1
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.