November 8, 2017 at 11:53 pm
Hi guys,
I've been tasked with creating a job which will charged a fee for debits, but if there is a credit they won't be charged
After all the conditions are applied I'm left with the following dataset.
(Sorry for the formatting - first post, and not sure how I post tables)
LoanNo DebitAmt CreditAmt
20201 1500 0
20201 150 0
20222 200 0
20222 0 100
30110 150 0
30001 150 0
30110 0 1500
What I want returned is a distinct row row each LoanNo with a count of the number of debits and credit for that loanNo.
Something like:
LoanNo cntDr cntCr
20201 2 0
20222 1 1
30110 1 1
30001 1 0
Any assistance from you guru's with the required SQL would be greatly appreciated.
November 9, 2017 at 12:11 am
Derek Paul - Wednesday, November 8, 2017 11:53 PMHi guys,
I've been tasked with creating a job which will charged a fee for debits, but if there is a credit they won't be charged
After all the conditions are applied I'm left with the following dataset.
(Sorry for the formatting - first post, and not sure how I post tables)
LoanNo DebitAmt CreditAmt
20201 1500 0
20201 150 0
20222 200 0
20222 0 100
30110 150 0
30001 150 0
30110 0 1500
What I want returned is a distinct row row each LoanNo with a count of the number of debits and credit for that loanNo.
Something like:
LoanNo cntDr cntCr
20201 2 0
20222 1 1
30110 1 1
30001 1 0
Any assistance from you guru's with the required SQL would be greatly appreciated.
Quick suggestion, sum up the sign of the values, here is an example
😎
USE [TEEST]
GO
SET NOCOUNT ON;
WITH SAMPLE_DATA (LoanNo,DebitAmt,CreditAmt) AS
(SELECT LoanNo,DebitAmt,CreditAmt FROM
(
VALUES
(20201,1500, 0)
,(20201, 150, 0)
,(20222, 200, 0)
,(20222, 0, 100)
,(30110, 150, 0)
,(30001, 150, 0)
,(30110, 0, 1500)
) X(LoanNo,DebitAmt,CreditAmt)
)
SELECT
SD.LoanNo
,SUM(ABS(SIGN(SD.DebitAmt))) AS DEBIT_CNT
,SUM(ABS(SIGN(SD.CreditAmt))) AS CREDIT_CNT
FROM SAMPLE_DATA SD
GROUP BY SD.LoanNo;
OutputLoanNo DEBIT_CNT CREDIT_CNT
----------- ----------- -----------
20201 2 0
20222 1 1
30001 1 0
30110 1 1
='font-size:9.0pt;mso-bidi-font-size:11.0pt;line-height:107%;font-family:"courier>
November 9, 2017 at 12:24 am
Thanx Eirikur, I think I can use that 🙂
November 9, 2017 at 12:56 am
Derek Paul - Thursday, November 9, 2017 12:24 AMThanx Eirikur, I think I can use that 🙂
You are welcome
😎
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply