• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

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?
0
csePixelated
Asked:
csePixelated
  • 4
  • 3
2 Solutions
 
PortletPaulfreelancerCommented:
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
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now