Link to home
Create AccountLog in
Avatar of cstraim
cstraim

asked on

SQL HELP

I need to be able to get this query to show the data as below.  Currently I dont have a "FoundIt" column, which I would like to create

here is my current query

SELECT      ACCOUNT_NUMBER, COUNT(*)
FROM      USCHIFFAC03.trx.dbo.tbl_trx_main_ar
WHERE      DATEDIFF(DAY, Trade_Date, GETDATE()) < 180
AND            MSG_ID = 'TRX'
AND            Action_Code      = 'A'
AND            APS_GUS_Ind <> 'G'
AND            Msg_Length <> 299
AND         (Account_Number LIKE 'NBP%'
            Account_Number LIKE '%KBK%' OR
            Account_Number LIKE '%666%' OR
            Account_Number LIKE '%1527%') Group by Account_number

Currently my data looks like this:

ACCOUNT_NUMBER      (No column name)
4666            4
666403             3
666952             2
84666              1035
G284666              8
NBP-C              1079
NBP-U               510
184666             30
184666             30

i need it to look like this:

Found IT      ACCOUNT_NUMBER      (No column name)
666            4666            4
666            666403             3
666            666952             2
666            84666              1035
666            G284666              8
NBP            NBP-C              1079
NBP            NBP-U               510      
NBP            184666             30
KBK            Null            0
1527            Null            0

Out of the four criteria listed in the where clause only 2 were in the data NBP and 666.  This query brought up all account numbers that have an NBP or 666 in them.  It should also bring up the other criteria, even if there isnt any data returned.  So for example, KBK is not found in the account number data anywhere, however, i still need to see it as a null and or zero to account for it as not having anything.

I aslo need the Found IT column created so that i can isolate the criteria I am trying to find with the accounts that are returned.

Thanks
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

here we go:
SELECT      ACCOUNT_NUMBER
       , COUNT(*) found_instances
       , CASE WHEN Account_Number LIKE 'NBP%' THEN 'NBP'
              WHEN Account_Number LIKE '%KBK%' THEN 'KBK'
              WHEN Account_Number LIKE '%666%' THEN '666'
              WHEN Account_Number LIKE '%1527%' THEN '1527'
              ELSE 'N/A' END FoundThis 
FROM      USCHIFFAC03.trx.dbo.tbl_trx_main_ar 
WHERE      DATEDIFF(DAY, Trade_Date, GETDATE()) < 180
AND            MSG_ID = 'TRX'
AND            Action_Code      = 'A'
AND            APS_GUS_Ind <> 'G'
AND            Msg_Length <> 299
AND         (Account_Number LIKE 'NBP%'
            Account_Number LIKE '%KBK%' OR
            Account_Number LIKE '%666%' OR
            Account_Number LIKE '%1527%') 
Group by Account_number

Open in new window

hi, try this
SELECT    CASE WHEN Account_Number LIKE 'NBP%' THEN 'NBP'
               WHEN Account_Number LIKE '%KBK%' THEN 'KBK'
               WHEN Account_Number LIKE '%666%' THEN '666'
               WHEN Account_Number LIKE '%1527%' THEN '1527'END AS FoundIT,
          ACCOUNT_NUMBER, COUNT(*)
FROM      USCHIFFAC03.trx.dbo.tbl_trx_main_ar
WHERE      DATEDIFF(DAY, Trade_Date, GETDATE()) < 180
AND            MSG_ID = 'TRX'
AND            Action_Code      = 'A'
AND            APS_GUS_Ind <> 'G'
AND            Msg_Length <> 299
AND         (Account_Number LIKE 'NBP%'
            Account_Number LIKE '%KBK%' OR
            Account_Number LIKE '%666%' OR
            Account_Number LIKE '%1527%') Group by Account_number

Open in new window

Avatar of cstraim
cstraim

ASKER

they both worked well for the first portion.  I am seeing the criteria with account numbers listed (NBP and 666) but I am not seeing anything for KBK or 1527, which both do not have accounts in my data.  my goal is to still identify the creiteria in the FoundIT column but ist the account number as Null or N/A or whatever and the count as 0.  I think Angelll was thinking about that with the else N/A statement, but for some reason nothing came up for KBK or 1527 still.

Thanks
If there is nothing in "Account_Number" then "LIKE '%KBK%' " will not return it.
Sorry, now I think I see what you want! Perhaps you can union together some queries to force a display for items that naturally would return no results?
Avatar of cstraim

ASKER

i am working on creating a view and a table with all of the "like" criteria in it.  I will look for any record in the criteria table that is not in the view (the view you gave me above) and see if that will produce what im looking for
ok, try this
SELECT A.*, B.RECCOUNT
(SELECT DISTINCT ACCOUNT_NUMBER,
                CASE WHEN Account_Number LIKE 'NBP%' THEN 'NBP'
                     WHEN Account_Number LIKE '%KBK%' THEN 'KBK'
                     WHEN Account_Number LIKE '%666%' THEN '666'
                     WHEN Account_Number LIKE '%1527%' THEN '1527'END AS FoundIT,
                ACCOUNT_NUMBER
FROM USCHIFFAC03.trx.dbo.tbl_trx_main_ar) A
LEFT JOIN
(SELECT    ACCOUNT_NUMBER, COUNT(*) AS RECCOUNT
FROM      USCHIFFAC03.trx.dbo.tbl_trx_main_ar
WHERE      DATEDIFF(DAY, Trade_Date, GETDATE()) < 180
AND            MSG_ID = 'TRX'
AND            Action_Code      = 'A'
AND            APS_GUS_Ind <> 'G'
AND            Msg_Length <> 299
AND         (Account_Number LIKE 'NBP%'
            Account_Number LIKE '%KBK%' OR
            Account_Number LIKE '%666%' OR
            Account_Number LIKE '%1527%') Group by Account_number) B ON (A.ACCOUNT_NUMBER=B.ACCOUNT_NUMBER)

Open in new window

Avatar of cstraim

ASKER

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'B'.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account