-2

---below query gives all the customers from fact irrespective of condition 

SELECT     count( dbo.Fact_Promotion.customerid) as Mailquantity
FROM         dbo.Fact_Promotion   
INNER JOIN dbo.Dim_Promotion  
    ON dbo.Fact_Promotion.PromotionID = dbo.Dim_Promotion.PromotionID

---below query gives customers with where condition

SELECT     count(distinct fact_loan.customerid) as [New loans] ,avg(Fact_Loan.Financeamount) as [Avg New Loan Amount]
FROM         dbo.Fact_Promotion 
 where <condition> 
      AND  dbo.Fact_Loan.LoanTypeID = 6 
      AND dbo.Fact_Loan.AccountStatusID = 1

----below query gives customers with different where condition

SELECT     count(distinct fact_loan.customerid) as [Total loans],avg(Fact_Loan.Financeamount) as [Avg Total Loan Amount]
FROM         dbo.Fact_Promotion 
 where <condition>
    AND dbo.Fact_Loan.AccountStatusID = 1
vamshi13
  • 1
  • 1
  • The queries have different numbers of columns. How exactly do you want them "combined"? Also, I am absolutely sure that all those joins are not necessary to demonstrate your problem. Please edit out all irrelevant SQL, eg changing each query to `select x, y from mytable1` and `select a, b from mytable2` would suffice – Bohemian Jun 19 '15 at 08:16
  • It's difficult to understand what exactly do you want? Could you explaine in more detail? – The Reason Jun 19 '15 at 08:23
  • Hi Bohemian. Need to merge above queries into a single query. I know that by using inner join we can do that but i do not want to repeat same code in the sub-query (expect where condition everything is same). is there anyway to write a query where there should not be repetition of code in the query? let me know if it makes sense – vamshi13 Jun 19 '15 at 10:14
  • @vamshi13: (i) Could you, please, `UNION` the output of the above queries and verify, the overall result set does take you somewhere? After all, your column aliases in query 2 and 3 won't be anywhere in your output (if you `UNION`ed them in the order listed above - and never at the row level anyway). (ii) The `WITH` clause might help to prevent code repetition. (iii) Is `dbo.Fact_Loan.AccountStatusID = 1` being in both the second and the third query a typo? (iv) BTW: What are the data warehouse, SSIS and MSBI aspects of your question, please? – Abecee Jun 19 '15 at 19:10

1 Answers1

0

I'm not sure from your question what you are trying to achieve.

The WHERE clause in the second query appears to deliver a subset of the data from the WHERE clause in the third query. Both WHERE statements look identical with the exception that the second query (New loans) includes an extra condition that the LoanTypeId (presumably the financial product they have taken) is 6. I guess this is the latest loan product or campaign.

Without knowing what you're trying to do it's difficult to give you an answer but if you want to show total number of customers by LoanTypeId you could aggregate a count by adding the LoanTypeId column to the SELECT statement and adding a GROUP BY dbo.Fact_Loan.LoanTypeId to the end of the statement.

This may not be a straight forward as that as you're doing some other stuff in your SELECT (such as the DISTINCT and the AVG) but without knowing what your end goal is, it's difficult to fully answer your question.

Moinkhan
  • 12,732
  • 5
  • 48
  • 65
Steve Matthews
  • 341
  • 4
  • 13