Solved

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

Posted on 2011-03-09
12
269 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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
 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 43
convert null in sql server 12 48
While in ##Table - Help 4 19
SSRS: Having a report run automatically 1 33
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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