Results 1 to 10 of 10

Thread: Need SQL Help

  1. #1
    Join Date
    Jan 2012
    Location
    Bartlett IL
    Posts
    5

    Unanswered: Need SQL Help

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably best to ask this in the SQL server forum...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem View Post
    probably best to ask this in the SQL server forum...
    couldn't you just move this thread over there?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This may work
    Code:
    SELECT
    	T1.Last_name,
    	T1.First_name,
    	T2.Position,
    	T3.Add_date,
    	SUM(CASE WHEN T4.PO_Type = 'spot buy' THEN 1 ELSE 0 END) AS 'Spot Buy Added',
    	SUM(CASE WHEN T4.PO_Type = 'blanket' THEN 1 ELSE 0 END) AS 'Blanket 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 IN ('spot buy', 'blanket')
    Group BY T1.last_name,T1.first_name,T2.position,T3.add_date
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2012
    Location
    Bartlett IL
    Posts
    5

    Response To Wim-I hope this is posted in the correct SQL Forum

    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.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    ... my numbers are verified as tried and true. ...
    I really wonder. The following code cannot deliver any good results. No way. (Unless I deeply misinterpret your needs). You should test more.
    Code:
    COUNT(T4.Po_type) AS 'spot buy',
    COUNT(DISTINCT CASE WHEN T4.Po_type='blanket' THEN t4.Po_type END) AS 'blanket'
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jan 2012
    Location
    Bartlett IL
    Posts
    5

    Reply to Wim

    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.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jan 2012
    Location
    Bartlett IL
    Posts
    5

    Response To Pat and Wim

    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

  10. #10
    Join Date
    Jan 2012
    Location
    Bartlett IL
    Posts
    5

    Hi Wim I am back again

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •