Be seen. Boost your question’s priority for more expert views and faster solutions
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