Is there a better way - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Is there a better way

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-09-06 : 05:43:49

Declare @InvoiceNumber varchar(6)
TRUNCATE TABLE TB1.dbo.tblCBM_InvoicePrep

INSERT INTO TB1.dbo.tblCBM_InvoicePrep
(LedgerKey, OAccountID, SubmittedDate, AccountCode, AccountName, Country, TransactionCount, FileValue)
Select LedgerKey, OAccountID, SubmittedDate,
(Select OAccountClientID FROM TB1.dbo.tbCBMOAccount
WHERE tbCBMOAccount.OAccountID = tbCBMTranSet.OAccountID) As 'AccountCode',
(Select LEFT(AccountName, 18) FROM TB1.dbo.tbCBMOAccount
WHERE tbCBMOAccount.OAccountID = tbCBMTranSet.OAccountID) As 'AccountName',
(Select Country FROM TB1.dbo.tbCBMOAccount
WHERE tbCBMOAccount.OAccountID = tbCBMTranSet.OAccountID) As 'Country',
(Select COUNT(TransValue) FROM TB1.dbo.tbCBMTransaction
WHERE tbCBMTransaction.LedgerKey = tbCBMTranSet.LedgerKey) As 'TransactionCount',
(Select SUM(TransValue) FROM TB1.dbo.tbCBMTransaction
WHERE tbCBMTransaction.LedgerKey = tbCBMTranSet.LedgerKey) As 'FileValue'
FROM TB1.dbo.tbCBMTranSet
WHERE SubmissionStatus= 'Submitted'
AND SubmissionType LIKE '%LIVE%'
AND Licence = (Select CustomerRef_FullName From TB2.dbo.invoice where RefNumber = @InvoiceNumber)
AND MONTH(convert(smalldatetime, (Select TxnDate From TB2.dbo.invoice where RefNumber = @InvoiceNumber), 103)) = MONTH(Submitteddate)
AND YEAR(convert(smalldatetime, (Select TxnDate From TB2.dbo.invoice where RefNumber = @InvoiceNumber), 103)) = YEAR(Submitteddate)

SELECT AccountCode, AccountName,
COALESCE(COUNT(CASE Country WHEN 'BACS' THEN FileValue END),0) AS FCOUNT,
COUNT(CASE WHEN FileValue > 0 THEN 1 END) as 'TOTALBACSFILES',
COALESCE(SUM(CASE Country WHEN 'BACS' THEN FileValue END),0) AS FVALUE,
Sum(CASE WHEN FileValue > 0 THEN TransactionCount END) as 'TOTALBACSTrans',
Sum(CASE WHEN ISNULL(FileValue, 0) = 0 THEN TransactionCount ELSE 0 END) as 'TOTALAUDDISTrans',
COUNT(CASE WHEN FileValue = 0 THEN 1 END) as 'TOTALAUDDIS',
COALESCE(Count(CASE Country WHEN 'IRISH' THEN TransactionCount END),0) AS IRISHFILS,
COALESCE(SUM(CASE Country WHEN 'IRISH' THEN TransactionCount END),0) AS IRISHTRNS,
COALESCE(SUM(CASE Country WHEN 'IRISH' THEN FileValue END),0) AS IVALUE
FROM TB1.dbo.tblCBM_InvoicePrep
GROUP BY AccountCode, AccountName
ORDER BY AccountCode

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-06 : 05:58:11

can you please explain what are you trying to do in this.?

Karthik
http://karthik4identity.blogspot.com/

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-09-06 : 06:08:58

quote:
Originally posted by karthik_padbanaban

can you please explain what are you trying to do in this.?

Karthik
http://karthik4identity.blogspot.com/

I am trying to get a summary of activity from tb1 based on an invoice number in tb2.

Tb1 can contain 100's of records for a client, but with different country codes and different OAccountID

What i currently get is returned is a summary list of activity on each account code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 06:10:45

why do you nested so many subqueries? you can reduce them to derived tables and join to them from main table.
something like


INSERT INTO TB1.dbo.tblCBM_InvoicePrep 
(LedgerKey, OAccountID, SubmittedDate, AccountCode, AccountName, Country, TransactionCount, FileValue)
Select LedgerKey, OAccountID, SubmittedDate,
u.OAccountClientID As 'AccountCode',
u.AccountName,
u.Country,
t.TransactionCount,
t.FileValue
FROM TB1.dbo.tbCBMTranSet
outer apply (Select SUM(TransValue) AS 'FileValue',
COUNT(TransValue) AS 'TransactionCount'
FROM TB1.dbo.tbCBMTransaction tr
WHERE tbCBMTransaction.LedgerKey = tbCBMTranSet.LedgerKey)t
outer apply (Select Country,LEFT(AccountName, 18) AS AccountName,OAccountClientID 
FROM TB1.dbo.tbCBMOAccount 
WHERE tbCBMOAccount.OAccountID = tbCBMTranSet.OAccountID)u
inner join (Select CustomerRef_FullName,MONTH(TxnDate) AS Mnth,YEAR(TxnDate) AS Yr  From TB2.dbo.invoice where RefNumber = @InvoiceNumber) v
on v.CustomerRef_FullName = tr.Licence
AND v.Mnth= MONTH(tr.Submitteddate)
AND v.Yr = YEAR(tr.Submitteddate)
WHERE tr.SubmissionStatus= 'Submitted' 
AND tr.SubmissionType LIKE '%LIVE%' 
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-09-06 : 06:34:32

hi thank you for that suggestion, I was hoping that I could get away from using the invoiceprep table and just run a straight query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 07:01:30

quote:
Originally posted by Pete_N

hi thank you for that suggestion, I was hoping that I could get away from using the invoiceprep table and just run a straight query

thats also possible
but for that we need some sample data from tables and also what output you expect out of them. otherwise we wont know how are tables related ie. is it one to one, one to many etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-09-06 : 07:07:00

Hi,
I generate table defs and dummy data

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 07:08:16

ok..please post once done

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources