Solved

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

Posted on 2011-03-09
12
261 Views
Last Modified: 2012-05-11
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
Comment
Question by:rhservan
  • 7
  • 3
  • 2
12 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35086480
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
 

Author Comment

by:rhservan
ID: 35087578
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
 
LVL 40

Expert Comment

by:Sharath
ID: 35087607
Can you post the output of the query suggested and the expected result you are looking for?
0
 

Author Comment

by:rhservan
ID: 35087797
Sharath,
 
I have attached the requested query output as discussed for your review.


Graphic1.png
0
 

Author Comment

by:rhservan
ID: 35087849
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35088023
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 40

Expert Comment

by:Sharath
ID: 35088360
>> 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
 

Author Comment

by:rhservan
ID: 35089813
@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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35089889
doesn't 35088023 give you that?
0
 

Author Comment

by:rhservan
ID: 35094566
@ 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
 

Author Comment

by:rhservan
ID: 35095334
Another way of looking at it is:
1. I need count of distinct DimInvoiceDetail.Invoice_AK based on each of the six filters.
0
 

Author Comment

by:rhservan
ID: 35098673
Comment by Question owner - Rhservan - Please cancel delete.  
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Temporarily disable SQL Replication 7 21
SSRS set Parameter value 2 39
Word Template Mail merge with vb.net 4 40
sql query Help 12 33
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now