asked
SQL Syntax Assistance
Good day everyone. Thanks in advance for your assistance in the following matter. Below, please find a script that I am running to pull up only 2 entries in a DB. The first audit number returns values in the account number where the fist letters before the '/' value are all the same. The second audit number, there is the 'CIBM' amongst all the 'CSSA'. I need a query to return the full transaction (each transaction is the audit number, always unique to each transaction) where there is a different prefix (ie: CSSA vs CIBM). SELECT PostGL.TxDate AS [Transaction Date], PostGL.Reference, PostGL.Description, PostGL.Debit, PostGL.Credit, PostGL.cAuditNumber, Accounts.Master_Sub_Account AS [Account Number], Accounts.Description AS [Account Description] FROM PostGL INNER JOIN Accounts ON PostGL.AccountLink = Accounts.AccountLink WHERE PostGL.cAuditNumber IN ( '73.0006', '73.0005' ) ************************************************************************* Transaction Date Reference Description Debit Credit cAuditNumber Account Number Account Description 2015-08-03 00:00:00 INV000109 Sales Order 500 0 73.0005 CSSA/7000/001 CSSA/Customer Control Accounts/Customer Control Account 2015-08-03 00:00:00 INV000109 Sales Order 0 0 73.0005 CSSA/9500/511 CSSA/VAT Control/Provisional VAT Control Account 2015-08-03 00:00:00 INV000109 Sales Order 0 500 73.0005 CSSA/3107/000 CSSA/Seminars 2015-08-03 00:00:00 INV000109 Sales Order 0 0 73.0005 CSSA/7800/000 CSSA/Physical Stock 2015-08-03 00:00:00 INV000109 Sales Order 0 0 73.0005 CSSA/2000/000 CSSA/COS Physical Stock 2015-08-03 00:00:00 INV000110 Sales Order 590 0 73.0006 CSSA/7000/003 CSSA/Customer Control Accounts/Student Control Account 2015-08-03 00:00:00 INV000110 Sales Order 0 72.46 73.0006 CSSA/9500/511 CSSA/VAT Control/Provisional VAT Control Account 2015-08-03 00:00:00 INV000110 Sales Order 0 517.54 73.0006 CIBM/1100/138 CIBM/Membership Income/ACIBM Subs 2015-08-03 00:00:00 INV000110 Sales Order 0 0 73.0006 CSSA/7800/000 CSSA/Physical Stock 2015-08-03 00:00:00 INV000110 Sales Order 0 0 73.0006 CSSA/2000/000 CSSA/COS Physical Stock Thank you.
I see that your results appear to match your query. Can you please provide an example of what the final output should be from that data? Also, keeping in mind this is the first time we've seen this set of data, and this is the only knowledge we have of it, can you please explain the requirement in more detail? Thanks.
I would love the result to be as follows (in a simplified version) I want the transaction with audit Number 73.0005 not to appear at all as the entire transaction has the prefix of 'CSSA' in the account number, but I want the entire transaction of 73.0006 to appear, as there are 2 prefixes, namely 'CSSA' and 'CIBM'
You can determine which cAuditNumbers have more than one prefix by using a RANK() such as select PostGL.cAuditNumber, prefix = substring(Accounts.Master_Sub_Account,0, charindex('/',Accounts.Master_Sub_Account)), rank()over(partition by PostGL.cAuditNumber order by substring(Accounts.Master_Sub_Account,0, charindex('/',Accounts.Master_Sub_Account))) as [rank] from PostGL INNER JOIN Accounts on PostGL.AccountLink = Accounts.AccountLink and for any cAuditNumber that had a rank over 1, then they would be your target ones. So by combining this query (or similar) with another pass over the result set, using CTEs in this example, I can generate a set of cAuditNumbers, and then use that in your original query as a join to limit the output with cte_PrefixCountBycAuditNumber as ( select PostGL.cAuditNumber, prefix = substring(Accounts.Master_Sub_Account,0, charindex('/',Accounts.Master_Sub_Account)), rank()over(partition by PostGL.cAuditNumber order by substring(Accounts.Master_Sub_Account,0, charindex('/',Accounts.Master_Sub_Account))) as [rank] from PostGL INNER JOIN Accounts on PostGL.AccountLink = Accounts.AccountLink ) ,cte_AggregatedcAuditNumber as( select distinct cAuditNumber from cte_PrefixCountBycAuditNumber where cte_PrefixCountBycAuditNumber.[rank] > 1 ) SELECT PostGL. TxDate AS [Transaction Date], PostGL.Reference, PostGL.Description, PostGL.Debit, PostGL.Credit, PostGL.cAuditNumber, Accounts.Master_Sub_Account AS [Account Number], Accounts.Description AS [Account Description] FROM PostGL INNER JOIN Accounts on PostGL.AccountLink = Accounts.AccountLink join cte_AggregatedcAuditNumber on cte_AggregatedcAuditNumber.cAuditNumber = PostGL.cAuditNumber WHERE PostGL.cAuditNumber IN ( '73.0006', '73.0005' )
18 People are following this question.
