how do build a composite field for grouping (ie 2 fields)

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

XML

Need to <B>join </B> and  <B>group </B>on 2 fields in  a <B>ms sql server stored procedure</B>

ie

1. AIDNO<
2. ALOANUMBER<

from 2 tables #<B>VLOANDED </B> and #<B>VTRANS</B>

        INSERT INTO #VLOANDED
        SELECT A.IDNO      AS AIDNO ,
               A.LOANUMBER AS ALOANUMBER ,
               C.CUSTOMER  AS ACUSTOMER ,
               A.PERIOD    AS APERIOD ,
               A.YEAR1     AS AYEAR1  ,
               A.PRINR_PMT AS APRINR_PMT,
               A.INTR_PMT AS AINTR_PMT,
               A.TX_TYPE   AS ATX_TYPE ,
               A.PAY_AMT   AS APAY_AMT,
               A.TX_DATE   AS ATX_DATE,
               A.TY_DATE   AS ATY_DATE ,
               A.AC_BALANCE AS AAC_BALANCE,
               A.INT_NO    AS AINT_NO,
               C.BANKCD AS ABANKCD,
               C.BANKNAME AS ABANKNAME
        FROM LOANDED A JOIN LOANS C
        ON  A.IDNO = C.IDNO AND A.LOANUMBER = C.LOANUMBER
        WHERE A.YEAR1*1200+A.PERIOD<=@CYEAR*1200+@CMONTH


        INSERT INTO #VTRANS
        SELECT LOAN_TRANS.IDNO       AS BIDNO ,
               LOAN_TRANS.CUSTOMER   AS BCUSTOMER ,
               LOAN_TRANS.ID_CODE    AS BLOANUMBER,
               LOAN_TRANS.TRANS_NO   AS BTRANS_NO ,
               LOAN_TRANS.PERIOD     AS BPERIOD,
               LOAN_TRANS.YEAR1      AS BYEAR1,
               LOAN_TRANS.AMOUNT     AS BAMOUNT,
               LOAN_TRANS.TRANS_DATE AS BTRANS_DATE,
               LOAN_TRANS.TRANS_TYPE AS BTANS_TYPE,
               LOAN_TRANS.DESCPN     AS BDESCPN
         FROM LOAN_TRANS
         WHERE LOAN_TRANS.YEAR1*1200+PERIOD<=@CYEAR*1200+@CMONTH

Tried this

                SELECT #VLOANDED.AIDNO ,
               #VLOANDED.ALOANUMBER ,
               SUM(#VLOANDED.APRINR_PMT) AS SUM_PRIN,
                       SUM(#VLOANDED.AINTR_PMT)  AS SUM_INT ,
                       SUM(#VTRANS.BAMOUNT       AS SUM_AMT
                  FROM #VLOANDED JOIN #VTRANS
                  ON   #VLOANDED.AIDNO =#VTRANS.BIDNO AND  #VLOANDED.ALOANUMBER =#VTRANS.BLOANUMBER
                  GROUP ON (#VLOANDED.AIDNO+#VLOANDED.ALOANUMBER)



Please help correct the grouping GROUP ON (#VLOANDED.AIDNO+#VLOANDED.ALOANUMBER)


so that there is no space in between (#VLOANDED.AIDNO+#VLOANDED.ALOANUMBER)

in short how do you trim the group with 2 fields.

Thanks

Posted 17-Apr-15 2:17am

Member 10744248

Updated 17-Apr-15 2:21am

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2015-04-17T02:55:00

Solution 1

This appears to be a prepost of your other question.

Same answer:

You just need to trim both pieces.

SQL

SELECT AIDNO, RTRIM(LTRIM(ALOANNUMBER)) + RTRIM(LTRIM(AIDNO)) AS LOAM
FROM VLOANS

Posted 17-Apr-15 2:55am

ZurdoDev

Richard Deeming · Answer 2 · 2015-04-17T02:56:00

Try:

SQL

SELECT 
    #VLOANDED.AIDNO,
    #VLOANDED.ALOANUMBER,
    SUM(#VLOANDED.APRINR_PMT) AS SUM_PRIN,
    SUM(#VLOANDED.AINTR_PMT) AS SUM_INT,
    SUM(#VTRANS.BAMOUNT) AS SUM_AMT
FROM 
    #VLOANDED 
    JOIN #VTRANS
    ON #VLOANDED.AIDNO = #VTRANS.BIDNO 
    AND #VLOANDED.ALOANUMBER = #VTRANS.BLOANUMBER
GROUP BY 
    #VLOANDED.AIDNO,
    #VLOANDED.ALOANUMBER
;