[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

How do I create multiple output on the same columns based on filter?

Current Query - See Attached - Using the 'Where' statement I created a query parameter which will filter on Report parameter item, 1-6.  When I run the query it prompts for input and runs the query fine.

Desired Query - I need to maintain the 1-6 separate Report filters  and Instead of using the Where for a parameter I need my output to provide all six filters at once with their individual outputs:
Count Inv_  AS inv # 1 (Filter 1)
Count Inv_  AS inv # 2 (Filter 2)
Count Inv_  AS inv # 3 (Filter 3)
etc...thru 6

Let me know if I can provide any additional clarity.
EE1.txt
0
rhservan
Asked:
rhservan
  • 7
  • 3
  • 2
1 Solution
 
SharathData EngineerCommented:
Are you looking for something like this.
SELECT SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (SalesData.GPPercentage <= 0.05)) THEN 1 
             ELSE 0 
           END) Filter1_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (SalesData.GPPercentage >= 0.4)) THEN 1 
             ELSE 0 
           END) Filter2_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (DimInvoiceDetail.NetPrice = 0.01)) THEN 1 
             ELSE 0 
           END) Filter3_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI')) 
                  AND ((DimInvoiceDetail.CostPrice = .01) 
                        OR (DimInvoiceDetail.CostPrice = 888.88) 
                        OR (DimInvoiceDetail.CostPrice = 999.99)) THEN 1 
             ELSE 0 
           END) Filter4_Count, 
       SUM(CASE 
             WHEN ((DimInvoiceDetail.OrderType = 'WA') 
                    OR (DimInvoiceDetail.OrderType = 'WS')) THEN 1 
             ELSE 0 
           END) Filter5_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI')) THEN 1 
             ELSE 0 
           END) Filter6_Count 
  FROM FadRoc.DimEmployee AS DimEmployee 
       INNER JOIN FadRoc.DimProduct AS DimProduct 
                  INNER JOIN FadRoc.DimInvoiceDetail AS DimInvoiceDetail 
                    ON DimProduct.Product_SK = DimInvoiceDetail.Product_SK 
                  INNER JOIN FadRoc.DimCustomer AS DimCustomer 
                    ON DimInvoiceDetail.Customer_SK = DimCustomer.Customer_SK 
                  INNER JOIN FadRoc.DimCompany AS DimCompany 
                    ON DimInvoiceDetail.Company_SK = DimCompany.Company_SK 
         ON DimEmployee.Employee_SK = DimCustomer.Salesman_SK 
       INNER JOIN FadRoc.SalesData AS SalesData 
         ON DimInvoiceDetail.InvoiceDetail_SK = SalesData.InvoiceDetail_SK 
       INNER JOIN FadRoc.DimDate 
         ON DimInvoiceDetail.InvoiceDate_SK = FadRoc.DimDate.Date_SK 
 WHERE FadRoc.DimDate.Date_AK BETWEEN @StartDate AND @EnRRate

Open in new window

0
 
rhservanAuthor Commented:
Sharath - Thank you for our efforts.
Please read below to incorporate the count for the invoice number.
I need the total count on this column, DimInvoiceDetail.Invoice_AK per filter.  Doing so will give me the count of invoices.  Is there some way to incorporate this by each filter?
0
 
SharathData EngineerCommented:
Can you post the output of the query suggested and the expected result you are looking for?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
rhservanAuthor Commented:
Sharath,
 
I have attached the requested query output as discussed for your review.


Graphic1.png
0
 
rhservanAuthor Commented:
I am not sure what the actual counts are from your query.  What do the counts represent?
The filters are correct and I will later make some minor changes to those.

I need the total count on this column, DimInvoiceDetail.Invoice_AK per filter.

Filter1 count = Count of DimInvoiceDetail.Invoice_AK based on filter1
Filter2 count = Count of DimInvoiceDetail.Invoice_AK  based on filter2
etc.....

Let me know if you need additionally clarity.  Thanks again for your efforts.
0
 
LowfatspreadCommented:
and your expected result is?


do you want to sum DimInvoiceDetail.Invoice_AK   or group by it  or ?
SELECT DimInvoiceDetail.Invoice_AK
       ,SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (SalesData.GPPercentage <= 0.05)) THEN 1 
             ELSE 0 
           END) Filter1_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (SalesData.GPPercentage >= 0.4)) THEN 1 
             ELSE 0 
           END) Filter2_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (DimInvoiceDetail.NetPrice = 0.01)) THEN 1 
             ELSE 0 
           END) Filter3_Count, 
       SUM(CASE 
             WHEN DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
              AND DimInvoiceDetail.CostPrice in ( .01, 888.88, 999.99) THEN 1 
             ELSE 0 
           END) Filter4_Count, 
       SUM(CASE 
             WHEN DimInvoiceDetail.OrderType in ( 'WA', 'WS') THEN 1 
             ELSE 0 
           END) Filter5_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI')) THEN 1 
             ELSE 0 
           END) Filter6_Count 
  FROM  FadRoc.DimProduct AS DimProduct 
  INNER JOIN FadRoc.DimInvoiceDetail AS DimInvoiceDetail 
     ON DimProduct.Product_SK = DimInvoiceDetail.Product_SK 
  INNER JOIN FadRoc.DimCustomer AS DimCustomer 
     ON DimInvoiceDetail.Customer_SK = DimCustomer.Customer_SK 
  INNER JOIN FadRoc.DimCompany AS DimCompany 
     ON DimInvoiceDetail.Company_SK = DimCompany.Company_SK 
  Inner Join FadRoc.DimEmployee AS DimEmployee    
     ON DimEmployee.Employee_SK = DimCustomer.Salesman_SK 
  INNER JOIN FadRoc.SalesData AS SalesData 
     ON DimInvoiceDetail.InvoiceDetail_SK = SalesData.InvoiceDetail_SK 
  INNER JOIN FadRoc.DimDate 
     ON DimInvoiceDetail.InvoiceDate_SK = FadRoc.DimDate.Date_SK 
 WHERE FadRoc.DimDate.Date_AK BETWEEN @StartDate AND @EnRRate
 group by DimInvoiceDetail.Invoice_AK
 order by 1

Open in new window

0
 
SharathData EngineerCommented:
>> I am not sure what the actual counts are from your query.  What do the counts represent?

Those are the counts based on each filter condition.  Run your original query by passing filter 1 to 6 and compare the counts to the counts from my query. Is that what you are looking for?
0
 
rhservanAuthor Commented:
@Sharath - The query filters are on the OrderType and it is returning the query is retruning the total number of orders.
Although the numbers match up correctly based on your query,  they are not the numbers equal to  count of the DimInvoiceDetail.Invoice_AK. column, which would be a smaller number since there are more orders than invoices.
@All
Example:
Invoice #1
Order #1
Order #2
Order #3
Invoice #2
Order #1
Order #2
Order #3
Total Invoice Count is 2 & Total Order Count is 6.
I need the Invoice Count


0
 
LowfatspreadCommented:
doesn't 35088023 give you that?
0
 
rhservanAuthor Commented:
@ Lowfatspread - I am not sure what that number is but It is not what I am looking for.  Also, in your query it is retruning all invoice rows not the sum or count of all invoice rows.

@All - Everything Sarath has done is correct accept the sum is on orders not on invoice.. Be sure to view the origional query in my first comment where you will find DimInvoiceDetail.Invoice_AK which is the  invoice column  which is the column count or sum I need based on the six filters.. This is what that query output looks like on the 2  columns looks like:

Example:
Invoice                          Order
Invoice #1                   Order #1
                                    Order #2
                                    Order #3

Invoice #2
                                    Order #1
                                    Order #2
                                     Order #3
I need Total Invoice SUM is 2
I dont need  Total Order SUM is 6.

In this format:.
Filter1            Filter2                Filter3              Filter4               Filter5               Filter6
InvSum          InvSum              InvSum            InvSum             InvSum              InvSum


The original query producess all invoice rows plus all order(type) rows based on the filters in the where command. I need the sum  on
0
 
rhservanAuthor Commented:
Another way of looking at it is:
1. I need count of distinct DimInvoiceDetail.Invoice_AK based on each of the six filters.
0
 
rhservanAuthor Commented:
Comment by Question owner - Rhservan - Please cancel delete.  
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now