I am using Query Writer (should be SQL 2005) and have included the following code. The end result:
-should retrieve columns and display the count for two different data sources that were added by personnel in a specific department. Problem: results are returned but not accurate. The code below works just fine and returns the results for all spot buy orders added by personnel in the sales department. However, I want to add a column in the same report that also counts blanket orders from the exact same table added by personnel in the sales department. The database uses views (v) in lieu of dbo.

Parameters:
@Add_Date_From SMALLDATETIME='',
@Add_Date_To SMALLDATETIME='',
@Last_name VARCHAR(50)='',
@First_Name VARCHAR(50)=''

Query:
SELECT
T1.Last_name,
T1.First_name,
T2.Position,
T3.Add_date,
COUNT(T4.PO_Type) AS 'Spot Buy Added'

FROM Sales_v_User AS T1
JOIN Common_v_Position as T2
ON T1.position_key=T2.position_key
JOIN Sales_v_PO AS T3
ON T1. User_no=T3.add_by
JOIN Sales_v_PO_type AS T4
ON T3.po_type_key=T4.po_type_key
WHERE T3.add_date BETWEEN @Add_Date_From AND @Add_date_to
AND T2.Position LIKE '%Sales%'
AND T1.Last_name LIKE '%' + @last_name + '%'
AND T1.First_name LIKE '%' + @first_name +'%'
AND T4.PO_Type='spot buy'
Group BY T1.last_name,T1.first_name,T2.position,T3.add_date

If I substitute COUNT(T4.PO_Type) AS 'Spot Buy Added' with COUNT(T4.PO_Type) AS 'Blanket Added' I also get accurate results for the blanket order. IE separately they work just fine. If I try to combine the two that is where the trouble begins.
What am I doing incorrectly when I try to add the criteria/code for the additional column to count the blanket orders?

Thanks so much.

probably best to ask this in the SQL server forum...

couldn't you just move this thread over there?

This may work

Hi Wim.

Thanks so much for the response! Actually I had figured it out (pls see the code I used below) on Friday nite and my numbers are verified as tried and true. So I am over that hurdle. It is so refreshing to see that I was on the right track. However, I have another issue: Now, the query should include data from 2 other tables (and I am working on this) with obviously different data sources (which was what prohibited me from using any UNION clauses in the first place--I had developed 6 indivudal queries that all worked now I have to pull them all together). I am thinking I can still use the CASE statements even tho' I now need to join two different tables? See my code below that I figured out on Friday:

SELECT
T1.Last_name,
T1.First_name,
T2.Position,
T3.Add_date,
COUNT(T4.Po_type)AS 'spot buy',
COUNT(DISTINCT CASE WHEN T4.Po_type='blanket' THEN t4.Po_type END) AS 'blanket'
FROM Sales_v_User AS T1
JOIN Common_v_Position as T2
ON t1.position_key=t2.position_key
JOIN Sales_v_PO AS T3
ON T1.User_no=t3.add_by
JOIN Sales_v_PO_type AS T4
ON t3.po_type_key=t4.po_type_key
WHERE T3.add_date BETWEEN @Add_Date_From AND @Add_date_to
AND T2.Position LIKE '%Customer Service%'
AND T1.Last_name LIKE '%' + @last_name + '%'
AND T1.First_name LIKE '%' + @first_name +'%'
Group BY T1.last_name,T1.first_name,t2.position,t3.add_date ,t4.po_type

Wim, may I keep you posted on the progress (I'll submit more code after I figure it out) to see if I am on the right track again? I appreciate all of your help.

You are awesome!

If this is the wrong forum can someone provide the link to the proper forum for me?

Thanks.

I really wonder. The following code cannot deliver any good results. No way. (Unless I deeply misinterpret your needs). You should test more.
What should COUNT(T4.Po_type) AS 'spot buy', give as result? Now it will count the number of records with the column T4.Po_type containing "a" value, be it 'spot buy' or 'blanket' or 'blablabla' (NULLs will not be counted). Looking at the column name you gave it, I don't think that is what you want. If T4.Po_type is NOT NULL, it will behave as a COUNT(*).

COUNT(DISTINCT CASE WHEN T4.Po_type='blanket' THEN t4.Po_type END) AS 'blanket'
Can only give 0 or 1 as result. It doesn't give the number of 'blanket's, but if there is at least one 'blanket' in the result set (result = 1). When there are none, the result is 0. But perhaps that is what you want.

You can keep using the CASEs, even when JOINING with more tables. But your CASES are seriously flawed (I think). You need to correct them.

Hey Wim.

I like your advice very much so. Well, I can attach a screenshot that shows the results if you like. However I do see what you are saying. I verified the numbers with the individual reports that I had coded as separate and the results are consistent (ie count the number of spot buys and blanket POs added by personnel in customer service for any given date range). I am going to create another version in the query writer (post your code snippets) and see the results. I must admit that I did not do that because I thought since I received results last Friday nite that were correct, I assumed everything was OK with my CASE statements.

Be back in a flash Wim.

Thanks again.

This actually does make sense to me. If a "blanket PO" covers all of the "planned" buys, for example specifying a large purchase to be delivered in installments over time then those planned deleveries would have a NULL po_type (and probably every other column of the invoice as well). The "spot buy" rows would have a separate invoice, so their po_type would have a value.

I'm emphatically NOT saying that this code is correct, I don't know enough to say that. What I'm saying is that in some scenarios it could make perfect sense.

-PatP

Hey Pat and Wim.

OK yes, Pat you are correct. The blanket POs are "planned" large purchases for delivery over a period time (installments) and the spot buys do have a separate invoice as far as my understanding (this is sales process is new to me and am learning the database). By the way Wim, I created a 2nd version of the query using your code but I was unable to obtain any results. I attached a screenshot as a word doc of both result sets so you can see what I mean. I really value both of your inputs and hope that we can continue to work together as I fine tune and develop the report to incorporate other data.

I figured I could continue with the CASE statements--thanks Wim for confirming this.

Vickie

I tried incorporating various CASE statements based on an individual query that I developed (also listed) to the code below and the results are not correct. What am I doing wrong? The database is Sales and the view is the Shipper table (sales_v_shipper). The goal is to add a column to the existing CASE query that lists all of the shipped_by orders (alias 'Releases') by personnel in the customer service department. Both queries work separately.

Existing CASE query:
@Last_Name VARCHAR(50) = '',
@First_Name VARCHAR(50) = '',
@Add_Date_From SMALLDATETIME = '',
@Add_Date_To SMALLDATETIME = ''

SELECT
T1.Last_name,
T1.First_name,
T2.Position,
T3.Add_date,
COUNT(T4.Po_type)AS 'spot buy',
COUNT(DISTINCT CASE WHEN T4.Po_type='blanket' THEN T4.Po_type END) AS 'blanket'
FROM Sales_v_User AS T1
JOIN Common_v_Position as T2
ON T1.position_key=T2.position_key
JOIN Sales_v_PO AS T3
ON T1.User_no=T3.add_by
JOIN Sales_v_PO_type AS T4
ON T3.po_type_key=T4.po_type_key
WHERE T3.add_date BETWEEN @Add_Date_From AND @Add_date_to
AND T2.Position LIKE '%Customer Service%'
AND T1.Last_name LIKE '%' + @last_name + '%'
AND T1.First_name LIKE '%' + @first_name +'%'
Group BY T1.last_name,T1.first_name,T2.position,T3.add_date ,T4.po_type

Individual query:
@Last_Name VARCHAR(50) = '',
@First_Name VARCHAR(50) = '',
@Add_Date_From SMALLDATETIME = '',
@Add_Date_To SMALLDATETIME = ''
SELECT
T1.Last_name,
T1.First_name,
T2.Add_date,
COUNT(T3.Shipped_by) AS 'Releases'
FROM Sales_v_user AS T1
JOIN Common_v_Position AS T4
ON T1.Position_key = T4.Position_key
JOIN Sales_v_Shipper AS T2
ON T1.user_no = T2.add_by
JOIN Sales_v_Shipper AS T3
ON T2.Shipper_key = T3.Shipper_key
WHERE T2.add_date BETWEEN @Add_Date_From AND @Add_date_to
AND T1.First_Name LIKE '%' + @First_Name + '%'
AND T1. Last_Name LIKE '%' + @Last_Name + '%'
AND T4.position LIKE '%Customer Service%'
GROUP BY T1.First_Name, T1.Last_Name, T2.Add_date

Thank you and still trying, obviously.