SQL, How to combine queries by a common column?

Also note that a string column like name can not take advantage of clustered indexes.  Well, technically you can create a clustered index on a string, but most of the time that is not what you really want to do.  Clustered indexes are best for values that are increasing, such as an integer Id.

What this means is that your query is missing out on a performance gain.

Thanks for your help.

Thanks for your comments on join column and I will keep it in mind in the future.

In my case,  'Store Full Name' from all sets of queries should return the same 'Store Full Name' as each query joined #StoreFullName. Maybe I wrote it in a stupid way. So, I do not know if join the name column will cause any problems in my case from your knowledge?

Thank you

hi zephyr_hex,

Do you mind to show me any example for me to learn? Thanks.

>So, I do not know if join the name column will cause any problems in my case from your knowledge?
Maybe not now, but you're just setting yourself up for trouble.  

Thanks for the grade.  Good luck with your project.

Don't use the store name until you actually need it.

Throughout your queries you have access to storeid ... use that for the "guts" of the query, then - at the last moment - use the name. This is a general principle: put off using names/labels/concatenations/date formatting/any "pretty stuff" until the last moment.

;
WITH CTELoans AS
        (
                SELECT
                        *
                FROM tblLoans L
                WHERE L.LoanDate >= '20150101'
                AND L.LoanDate < '20160101' --<< always use the alias and use: less than "the next day"
                AND L.AssetTypeID = 1
        )
SELECT
        S.storefullname
      , COUNT(loanid)           AS numberofloans            ---verified 54486----
      , SUM(loanamountfinanced) AS dolloaramountofloans
      , MAX(LoanAmountFinanced) AS highestamountofloans
      , MIN(LoanAmountFinanced) AS lowestamountofloans
      , AVG(Loanamountfinanced) AS avgamountofloans
      , MAX(FN.TILAAPR)         AS highestAPR
      , MIN(FN.TILAAPR)         AS lowestAPR
      , AVG(FN.TILAAPR)
      , c.numberofcustomers
      , p.numberoflatepayment
      , rc.repossessed
      , rc.soldcars
FROM #GroupByStores S
        INNER JOIN CTELoans L ON S.STOREID = L.LoanStoreID
        CROSS APPLY GetTILAInfo(L.LoanID) AS FN --<< always use the alias
        LEFT JOIN
        ( /* # of customers */
                SELECT
                        L1.LoanStoreID
                      , COUNT(DISTINCT tblCustomers.customerssn) AS numberofcustomers
                FROM CTELoans L1
                        INNER JOIN tblCustomers ON L1.CustomerID = tblCustomers.customerID
                GROUP BY L1.LoanStoreID
        ) c ON S.STOREID = C.LoanStoreID
        LEFT JOIN
        (  /* get # of late payments */
                SELECT
                        L2.LoanStoreID
                      , COUNT(DISTINCT L2.LOANId) AS numberoflatepayment
                FROM tblLoans L2
                        INNER JOIN tblReportingTransactions R ON L2.loanID = R.LoanID
                WHERE R.TransactionDate >= '20150101'
                AND R.TransactionDate < '20160101' --<< use: less than "the next day"
                AND R.DaysLate >= 60
                AND L2.LoanDate >= '20101001'
                GROUP BY L2.LoanStoreID
        ) p ON S.STOREID = p.LoanStoreID
        LEFT JOIN
        ( /* repossessedCars & Sold Cars */
                SELECT
                        L3.LoanID
                      , COUNT(DISTINCT L3.loanid) AS repossessed --<< isn't DISTINCT NEEDED HERE????
                      , SUM(CASE
                                WHEN L3.AuctionCheckDate >= '20150101' AND
                                        L3.AUCTIONCHECKDATE < '20160101' AND --<< use: less than "the next day"
                                        L3.AuctionCheckAmount > 0 THEN 1
                                ELSE 0
                        END)                      AS soldcars
                FROM tblStatusTransactions ST
                        INNER JOIN CTELoans L3 ON ST.StatusTransactionLoanID = L3.LoanID
                WHERE ST.StatusTransactiontypeID = 3
                AND ST.StatusTransactionDate >= '20150101'
                AND ST.StatusTransactionDate < '20160101' --<< use: less than "the next day"
                AND L3.LoanDate >= '20101001'
                GROUP BY L3.LoanID
        ) rc ON S.STOREID = rc.LoanStoreID
GROUP BY
        S.storefullname
      , c.numberofcustomers
      , p.numberoflatepayment
      , rc.repossessed
      , rc.soldcars

;

Select allOpen in new window

Note I have no idea how you generate that temp table. Maybe it is needed, maybe not.

I have introduced a CTE because I can get some performance savings (by reuse of that CTE for the count of customers)

PLEASE be careful to ALWAYS use table aliases. While you did that for most, sometimes you didn't and that can get you into trouble - particularly when you try to combine multiple queries.

and, I STRONGLY recommend you start thinking about date ranges using a combination of "greater then or equal" with "less than" (the wanted day)+1

{+edit} and I used LEFT JOINS for those "derived tables", I don't know if that is required but it might be (e.g. if there are no repossessed cars), for final output you might need ISNULL() or COALESCE()

and, while I was producing this answer, a number of other comments occurred - oh well.

{+edit 2}
oh and I think you need a DISTINCT in one of the counts

and... don't embed comments using --, instead use
/* comment here */

where I have used --<< some comment
I would expect you to remove that