Results 1 to 33 of 33

Thread: SQL Server Query - count if any rows have a certain value

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    SQL Server Query - count if any rows have a certain value

    I'm trying to write a query and will continue to try but am posting it here to get help as well.
    I need to look at a group of rows and see if any of them has a column equal to a certain value.
    The key is SONumber.
    I want to return one row of the 6 in the resultset in this attachment.
    Because one of those six rows is disposition = 'S' I want to return a column with a default value of zero.
    If none of the rows has a disposition of 'S', I want to return the EXTPRICE value of EQ_PORT, 24835.30 in said column.
    I greatly simplied my query to make it easier to understand (and I try to always start simple and build), so let me know if this makes sense or if you need more info.
    I appreciate your help!

    Name:  vbForums 2.jpg
Views: 259
Size:  24.3 KB
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    And now that i am looking at this, I probably gave it a stupid title. At first I was thinking I wanted to count the S's and if > 0, return an amount of zero. If EQ 0, return EXTPRICE of the ITEM = EQ_PORT row. I'm not sure if that would be a COUNT or not.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: SQL Server Query - count if any rows have a certain value

    Get all the EQ_PORTs ...
    Code:
    select SONumber, EXTPRICE where disposition = 'EQ_PORT'
    Get the "S" rows:
    Code:
    select SONumber, EXTPRice where disposition = 'S'
    And combine:
    Code:
    select T.SONumber, case when S.EXTPRICE is not null then 0 else EP.EXTPRICE end as EXTPRICE
    from table T
    left join (select SONumber, EXTPRice where disposition = 'S') S on T.SONumber = S.SONumber
    left join (select SONumber, EXTPRICE where disposition = 'EQ_PORT') EP on T.SONumber = EP.SONumber
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - count if any rows have a certain value

    Tested in SQLite.
    Syntax might differ to SQL-Server

    Code:
    SELECT M1.SONumber, IIF(M2.CountS>0,0,M3.EXTPRICE) AS Result FROM tbl_mock AS M1 
    LEFT JOIN 
    (SELECT SONumber, COUNT(disposition) AS CountS FROM tbl_mock WHERE disposition='S' GROUP BY SONumber) AS M2 
    ON M1.SONumber=M2.SONumber 
    LEFT JOIN tbl_mock AS M3 
    ON M3.SONumber=M1.SONumber AND M3.ITEM='EQ_PORT'  
    GROUP BY M1.SONumber
    Last edited by Zvoni; Nov 30th, 2022 at 10:24 AM. Reason: GROUP BY in first LEFT JOIN missing as pointed out by wqweto
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - count if any rows have a certain value

    Quote Originally Posted by techgnome View Post
    Get all the EQ_PORTs ...
    Code:
    select SONumber, EXTPRICE where disposition = 'EQ_PORT'
    Get the "S" rows:
    Code:
    select SONumber, EXTPRice where disposition = 'S'
    And combine:
    Code:
    select T.SONumber, case when S.EXTPRICE is not null then 0 else EP.EXTPRICE end as EXTPRICE
    from table T
    left join (select SONumber, EXTPRice where disposition = 'S') S on T.SONumber = S.SONumber
    left join (select SONumber, EXTPRICE where disposition = 'EQ_PORT') EP on T.SONumber = EP.SONumber
    -tg
    eh... No.... EQ_Port is in Column ITEM
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: SQL Server Query - count if any rows have a certain value

    Quote Originally Posted by Zvoni View Post
    eh... No.... EQ_Port is in Column ITEM
    Can I claim temporary insanity as I hadn't had my coffee yet? Still ... it should work with that tweak.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: SQL Server Query - count if any rows have a certain value

    This

    Code:
    (SELECT SONumber, COUNT(disposition) AS CountS FROM tbl_mock WHERE disposition='S') AS M2
    . . . is missing a GROUP BY SONumber

    This

    Code:
    LEFT JOIN tbl_mock AS M3 
    ON M3.SONumber=M1.SONumber AND M3.ITEM='EQ_PORT'
    . . . might return more than one row. It's not clear as table schema is missing.

    I would defensively guard against duplicate EQ_PORT rows for the same SONumber i.e. add GROUP BY SONumber and take MAX(M3.EXTPRICE) in a derived table.

    cheers,
    </wqw>

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - count if any rows have a certain value

    Quote Originally Posted by wqweto View Post
    This

    Code:
    (SELECT SONumber, COUNT(disposition) AS CountS FROM tbl_mock WHERE disposition='S') AS M2
    . . . is missing a GROUP BY SONumber


    Agreed. Missed that one (SQlite doesn't complain about such stuff).

    This

    Code:
    LEFT JOIN tbl_mock AS M3 
    ON M3.SONumber=M1.SONumber AND M3.ITEM='EQ_PORT'
    . . . might return more than one row. It's not clear as table schema is missing.

    I would defensively guard against duplicate EQ_PORT rows for the same SONumber i.e. add GROUP BY SONumber and take MAX(M3.EXTPRICE) in a derived table.

    cheers,
    </wqw>
    Agreed. But as you said: Table_Schema would explain it.
    As i understood it, there can be only one row with ITEM='EQ_Port', so there is probably a combined UNIQUE-Constraint consisting of SONumber and ITEM
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: SQL Server Query - count if any rows have a certain value

    Quote Originally Posted by Zvoni View Post
    . . . so there is probably a combined UNIQUE-Constraint consisting of SONumber and ITEM
    Could be but don't count on it :-)))

    cheers,
    </wqw>

  10. #10

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    Thanks, I love you guys, though it bothers me I could not do my job without you!

    So I took tg's query and turned it into this:
    Code:
    select T.SONumber, case when S.EXTPRICE is not null then 0 else EP.EXTPRICE end as EXTPRICE
    from Jobs T
    join xqryP21LinesRelated p21 on p21.SONO = T.SONumber 
    left join 
    	(select SONumber, EXTPRice 
    	from Jobs T
    	join xqryP21LinesRelated p21 on p21.SONO = T.SONumber 
    	where disposition = 'S') S on T.SONumber = S.SONumber
    left join 
    	(select SONumber, EXTPRICE 
    		from Jobs T
    	join xqryP21LinesRelated p21 on p21.SONO = T.SONumber 
    	where ITEM = 'EQ_PORT') EP on T.SONumber = EP.SONumber
    
    WHERE T.SONumber = '1066152'
    And I belive that looks good. I am running one SONumber at a time to take a look at the results. That query returns me 6 rows that are duplicates (EXTPRICE always zero) so I can just throw a distinct at the results.

    My next one returned me 23 duplicate rows where EXPRICE is always null and that is because there is no ITEM = EQ_PORT in the results. Again, just need a DISTINCT.

    The next one returned me 180 rows. 90 are duplicates with each other. That is because there is more than one ITEM = EQ_PORT; there are two, so two sets. I have to ask my users if they want EXTPRICE added together if more than one.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - count if any rows have a certain value

    Have you tried my solution?
    adding the sum of EXTPRICE in my query is not black magic

    btw: your query doesn‘t correspond with your task.
    “if there is a line with S in disposition return 0 otherwise return EXTPRICE where EQ_PORT in ITEM“
    i don‘t see you evaluating the count of S
    Last edited by Zvoni; Nov 30th, 2022 at 12:22 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: SQL Server Query - count if any rows have a certain value

    There wasn't a _need_ to count the number of S rows ... just that at least ONE exists. If one does, then the price is 0... otherwise, get the price from the EQ_PORT row.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - count if any rows have a certain value

    Quote Originally Posted by techgnome;[URL="tel:5587642"
    5587642[/URL]]There wasn't a _need_ to count the number of S rows ... just that at least ONE exists. If one does, then the price is 0... otherwise, get the price from the EQ_PORT row.

    -tg
    Ok, missed that.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  14. #14

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    So the requirements for this have taken another turn and honestly I don't think I have the sql skills for this and I do not expect to hand it over to you guys 100%. I thought all I needed was some guidance to get started. Now they are telling me that if there are no ITEM = EQ_PORT rows that i need to add all items with a disposition = 'A'. I said I would like to write a c# program to do this because I can't do it in a query. If they aboslutely need a query, maybe I can hire one of you so you at least get more compensation than a thank you!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  15. #15

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    Ugh, I forget that vbForums doesn't email you that you received replies and I neglected to refresh this and see those past few posts.
    Requirements for this are trickling in. Let me see if I can summarize what i need to do, but as I said the sql to do it may be beyond me.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  16. #16

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    @zvoni - My apologies for not trying your solution. I tried tg's and it was going the right way. I didn't see the need for two. I will take a look at yours now.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  17. #17

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    @zvoni, I got this far but the problem which no one else could've know about because I did not give you guys the full schema is that Jobs.SONumber has be joined to xqryP21LinesRelated.SONO and it is in the xqry view where Disposition is defined. So I don't know how to tweak the 2nd part of your query or perhaps the fact that it is not the same table changes the query.

    Code:
    -- zvoni's way:
    SELECT M1.SONumber, IIF(M2.CountS>0,0,M3.EXTPRICE) AS Result 
    FROM Jobs AS M1 
    LEFT JOIN 
    	(SELECT SONumber, COUNT(disposition) AS CountS 
    	FROM Jobs 
    	join xqryP21LinesRelated p21 on p21.SONO = Jobs.SONumber
    	WHERE disposition='S' 
    	GROUP BY SONumber) AS M2 
    ON M1.SONumber=M2.SONumber 
    LEFT JOIN tbl_mock AS M3 
    ON M3.SONumber=M1.SONumber AND M3.ITEM='EQ_PORT'  
    GROUP BY M1.SONumber
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  18. #18
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - count if any rows have a certain value

    That‘s the part i don‘t understand:
    in which table is disposition now? In jobs or in this P21?

    edit: ah, ok, got it.
    just qualify disposition with p21
    ….. p21.disposition inside the subquery

    and the change in requirements #14 don‘t sound too complicated

    i‘ll have a look tomorrow, since it’s late in the evening in Germany
    Last edited by Zvoni; Nov 30th, 2022 at 04:27 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  19. #19
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - count if any rows have a certain value

    OK, i've cleaned up my code a bit

    I'm assuming EXTPRICE and ITEM are a Columns of Jobs.
    If it's somewhere else, do tell.
    Code:
    -- zvoni's way:
    SELECT M1.SONumber, IIF(M2.CountS>0,0,M3.EXTPRICE) AS Result 
    FROM Jobs AS M1 
    LEFT JOIN 
        (SELECT J1.SONumber, COUNT(P21.disposition) AS CountS 
        FROM Jobs AS J1 
        INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J1.SONumber
        WHERE P21.disposition='S' 
        GROUP BY J1.SONumber) AS M2 
    ON M1.SONumber=M2.SONumber 
    LEFT JOIN 
        (SELECT J2.SONumber, J2.EXTPRICE 
        FROM Jobs AS J2 
        INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J2.SONumber
        WHERE J2.ITEM='EQ_PORT') AS M3 
    ON M3.SONumber=M1.SONumber 
    GROUP BY M1.SONumber
    And now what's this with "If no EQ_PORTS in ITEM" add what of disposition='A''?

    EDIT: Extended with that "Add Items with Disposition='A'
    I had to guess, which Fields to sum up (EXTPRICE here)
    Code:
    -- zvoni's way:
    SELECT M1.SONumber, COALESCE(IIF(M2.CountS>0,0,M3.EXTPRICE), M4.SumPrice) AS Result 
    FROM Jobs AS M1 
    LEFT JOIN 
        (SELECT J1.SONumber, COUNT(P21.disposition) AS CountS 
        FROM Jobs AS J1 
        INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J1.SONumber
        WHERE P21.disposition='S' 
        GROUP BY J1.SONumber) AS M2 
    ON M1.SONumber=M2.SONumber 
    LEFT JOIN 
        (SELECT J2.SONumber, J2.EXTPRICE 
        FROM Jobs AS J2 
        INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J2.SONumber
        WHERE J2.ITEM='EQ_PORT') AS M3 
    ON M3.SONumber=M1.SONumber 
    LEFT JOIN 
        (SELECT J3.SONumber, SUM(J3.EXTPRICE) AS SumPrice 
        FROM Jobs AS J3 
        INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J3.SONumber
        WHERE P21.disposition='A' 
        GROUP BY J3.SONumber) AS M4 
    ON M4.SONumber=M1.SONumber 
    GROUP BY M1.SONumber
    EDIT2: Just looked at it again, and i might see some points to optimize the query, but it would depend, in which table/view which column is.
    Like what you said, that column "disposition" actually comes from P21.
    I would need to know, where EXTPRICE and ITEM are located

    Code:
    -- zvoni's way:
    SELECT M1.SONumber, COALESCE(IIF(M2.CountS>0,0,M3.EXTPRICE), M4.SumPrice, 'Ooops') AS Result 
    FROM Jobs AS M1 
    LEFT JOIN 
        /*Optimized: There is no need to JOIN Jobs with P21, since we can get all Fields from P21*/
        (SELECT P21.SONO AS SONumber, COUNT(P21.disposition) AS CountS 
        FROM xqryP21LinesRelated AS P21 
        WHERE P21.disposition='S' 
        GROUP BY P21.SONO) AS M2 
    ON M1.SONumber=M2.SONumber 
    LEFT JOIN 
        --Alternative if you want a single row for multiple lines with EQ_PORT with EXTPRICE summed up, or use any other Aggregate-Function: MAX, MIN, AVG whatever
        --(SELECT J2.SONumber, SUM(J2.EXTPRICE) AS EXTPRICE 
        --FROM Jobs AS J2
        --INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J2.SONumber
        --WHERE J2.ITEM='EQ_PORT'
        --GROUP BY J2.SONumber) AS M3
        (SELECT J2.SONumber, J2.EXTPRICE 
        FROM Jobs AS J2 
        INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J2.SONumber
        WHERE J2.ITEM='EQ_PORT') AS M3 
    ON M3.SONumber=M1.SONumber 
    LEFT JOIN 
        (SELECT J3.SONumber, SUM(J3.EXTPRICE) AS SumPrice 
        FROM Jobs AS J3 
        INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J3.SONumber
        WHERE P21.disposition='A' 
        GROUP BY J3.SONumber) AS M4 
    ON M4.SONumber=M1.SONumber 
    GROUP BY M1.SONumber
    and wqweto's Question is still valid: Is it possible to have more than 1 line with ITEM=EQ_PORT per SONumber?

    EDIT: OK, just saw that you answered that in Post #10

    EDIT again:
    Explanation how it works:
    Since we're dealing with LEFT JOINS there might be NULL's coming back from the JOIN's

    How does this work: COALESCE(IIF(M2.CountS>0,0,M3.EXTPRICE), M4.SumPrice, 'Ooops') AS Result

    IIF is a shortcut for "If Then Else"
    If M2.CountS>0 Then 0 Else M3.EXTPRICE --> I agree with tg, that there might be NULL coming back for CountS, so i don't know how CountS>0 behaves (does it return True or False)

    Let's Say, CountS=0 then the Else-Part comes into play --> M3. EXTPRICE

    Now the COALESCE starts working.
    COALESCE returns the FIRST NON-Null Value of its arguments

    COALESCE evaluates its first Argument, which is the IIF.
    We said, the IIF returns the M3.EXTPRICE (because CountS was 0).

    Now if M3 returns a valid EXTPRICE all is well (because in ITEM there was at least one "EQ_PORT")

    But M3 returns NULL for EXTPRICE if there is no "EQ_PORT" in ITEM
    In that case COALESCE jumps to its next Argument, which is M4.EXTPRICE, which it returns provided M4.EXTPRICE is NON-Null. That's the reason for the "Ooops" in the third argument
    Last edited by Zvoni; Dec 1st, 2022 at 07:23 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  20. #20

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    I'm still looking at this myself to see if I can figure out what it wants, but these are my errors when I run that query:

    Msg 8120, Level 16, State 1, Line 2
    Column 'M2.CountS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    Msg 8120, Level 16, State 1, Line 2
    Column 'M3.EXTPRICE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    Msg 8120, Level 16, State 1, Line 2
    Column 'M2.CountS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    Msg 8120, Level 16, State 1, Line 2
    Column 'M3.EXTPRICE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    Msg 8120, Level 16, State 1, Line 2
    Column 'M4.SumPrice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    Msg 8120, Level 16, State 1, Line 2
    Column 'M4.SumPrice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    EXTPRICE and ITEM are in the P21 view, since you asked. I made that change.
    Code:
    -- zvoni's way:
    SELECT M1.SONumber, COALESCE(IIF(M2.CountS>0,0,M3.EXTPRICE), M4.SumPrice, 'Ooops') AS Result 
    FROM Jobs AS M1 
    LEFT JOIN 
        /*Optimized: There is no need to JOIN Jobs with P21, since we can get all Fields from P21*/
        (SELECT P21.SONO AS SONumber, COUNT(P21.disposition) AS CountS 
        FROM xqryP21LinesRelated AS P21 
        WHERE P21.disposition='S' 
        GROUP BY P21.SONO) AS M2 
    ON M1.SONumber=M2.SONumber 
    LEFT JOIN 
        (SELECT J2.SONumber, P21.EXTPRICE 
        FROM Jobs AS J2 
        INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J2.SONumber
        WHERE P21.ITEM='EQ_PORT') AS M3 
    ON M3.SONumber=M1.SONumber 
    LEFT JOIN 
        (SELECT J3.SONumber, SUM(P21.EXTPRICE) AS SumPrice 
        FROM Jobs AS J3 
        INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J3.SONumber
        --WHERE P21.disposition='A' 
    	WHERE P21.disposition IS NULL 
        GROUP BY J3.SONumber) AS M4 
    ON M4.SONumber=M1.SONumber 
    GROUP BY M1.SONumber
    This is a big pain in the ass and I am still trying to get answers from my management but FYI I may be doing this in C# code after all. My boss said if she can click a button to populate a grid and export it to Excel she's fine with that, so realize I have other alternatives. If I could just nail down the specs that would be so nice! And you've done so much work I feel you are invested and would feel bad if I copped out. But again, more specs may change our (your) work so far.

    P.S. You mentioned you are in Germany. I took my daughters to Berlin and Ralswiek in May, traveling from the USA. Kristina loves your music over there and went to Mono Inc and Santiano concerts, in Berlin and Ralswiek respectively. This trip has been on her wishlist for a few years; glad I could give it to her :-).
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  21. #21
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - count if any rows have a certain value

    OK, my Mistake.
    Remove the LAST GROUP BY

    and EXTPRICE comes from P21, so here goes
    Code:
    SELECT M1.SONumber, COALESCE(IIF(M2.CountS>0,0,M3.EXTPRICE), M4.SumPrice, 'Ooops') AS Result 
    FROM Jobs AS M1 
    LEFT JOIN 
        /*Optimized: There is no need to JOIN Jobs with P21, since we can get all Fields from P21*/
        (SELECT P21.SONO AS SONumber, COUNT(P21.disposition) AS CountS 
        FROM xqryP21LinesRelated AS P21 
        WHERE P21.disposition='S' 
        GROUP BY P21.SONO) AS M2 
    ON M1.SONumber=M2.SONumber 
    LEFT JOIN 
        /*Optimized: There is no need to JOIN Jobs with P21, since we can get all Fields from P21*/     
        (SELECT P21.SONO AS SONumber, P21.EXTPRICE 
        FROM xqryP21LinesRelated AS P21 
        WHERE P21.ITEM='EQ_PORT') AS M3 
    ON M3.SONumber=M1.SONumber 
    LEFT JOIN 
        /*Optimized: There is no need to JOIN Jobs with P21, since we can get all Fields from P21*/ 
        (SELECT P21.SONO AS SONumber, SUM(P21.EXTPRICE) AS SumPrice 
        FROM xqryP21LinesRelated AS P21 
        --WHERE P21.disposition='A' 
        WHERE P21.disposition IS NULL 
        GROUP BY P21.SONumber) AS M4 
    ON M4.SONumber=M1.SONumber 
    /*WHERE M1.SONumber=1066152*/
    The Aggregation (and the need for a GROUP BY) happens in the inner Selects within the LEFT JOINS
    So SQL-Server run afoul of the last group by

    I've removed the GROUP BY, and added a comment with a WHERE-Clause for you to check

    P.S. You mentioned you are in Germany. I took my daughters to Berlin and Ralswiek in May, traveling from the USA. Kristina loves your music over there and went to Mono Inc and Santiano concerts, in Berlin and Ralswiek respectively. This trip has been on her wishlist for a few years; glad I could give it to her :-).
    I'm in Southwestern Germany (close to Switzerland), and frankly: Santiano is really not my style.
    I like it loud and heavy *headbang*
    Last edited by Zvoni; Dec 1st, 2022 at 01:06 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  22. #22

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    Also, since you have plunged into this so deeply I will share with you where I am headed. I was trying to start simple but you mentioned something about joining to a table I may not need. This is my original query, with the columns I ultimately need to select, that my boss said had something wrong with it:
    Code:
    select Jobs.Control, Jobs.SONumber, Jobs.JobType, xtblSaleType.Description as SaleType, Jobs.CustNo, Jobs.CustomerName, 
    [dbo].[fn_GetJobShopTask](Jobs.Control, null) as CST,
    dlr.SaleAmount, dlr.SaleDate, convert(nvarchar(max), Jobs.Details),
    sum(p21.EXTPRICE) as SumDispositionA
    from Jobs 
    join xtblForecastDealer dlr on dlr.JobControl = Jobs.Control
    join [xtblSaleType] on [xtblSaleType].Control = Jobs.SaleType
    left join xqryP21LinesRelated p21 on p21.SONO = Jobs.SONumber 
    where JObs.OrderIncludesActivities != -1
    and JObs.Status != 'CP'
    and JObs.JobType != 'Sale - MR'
    and p21.disposition is null
    and LEFT(Jobs.SONumber, 1) != 'C'
    group by Jobs.Control, JObs.SONumber, Jobs.JobType, xtblSaleType.Description, JObs.CustNo, Jobs.CustomerName, 
    [dbo].[fn_GetJobShopTask](Jobs.Control, null),
    dlr.SaleAmount, dlr.SaleDate, convert(nvarchar(max), Jobs.Details)
    order by jobs.Control
    The problem was simply getting a sum of P21.EXTPRICE. There are conditions (the criteria you've been coding) that have to be considered before we can accumulate the EXTPRICE.

    Again, tell me you've had it if you want. Or wait until I have more info on what *exactly* I need to select.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  23. #23

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    OK, hold up. I am going to post something but it's long and will take me a few minutes to compose and I didn't want us cross posting. So wait until you see my next post.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  24. #24

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    P.S. Just out of curiosity, name a few loud and heavy headbanging groups. I believe there has to be some overlap. I mean Rammstein is an obvious mention and Kristina saw them here on their stadium tour in Sept (we had seem them several years ago on Long Island New York), and her friend from Lithuania, who she met online in a Rammstein fan group, visited us for a week so they could go together. (Sandra had also seen them in a Baltic country a month or two before). I am half-Lithuanian so it was fun hosting her! (Mock is one syllable of my 3-syllable last name, LOL).
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  25. #25

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    I am running the following query against one SONumber to take a closer look (SONumber = SONO = order_no).

    Note, customer_part_number is what we've been calling ITEM.

    Code:
    SELECT customer_part_number, P21.dbo.p21_view_oe_line.extended_price, P21.dbo.p21_view_oe_line.disposition, P21.dbo.p21_view_oe_line.parent_oe_line_uid
    FROM P21.dbo.p21_view_oe_line
    WHERE P21.dbo.p21_view_oe_line.order_no = '1066152'
    It returns the results in my snip below. My goal is add up the extended_price column according to these conditions to get total *allocated* inventory.
    We always skip HOLD.
    In this case, we skip EQ_PORT's price of $24,835 because there's a child record with a disposition S. (Any row that has a non zero parent-uid is a child).
    We skip SHIP's price of $248 because its a non-inventory item (which I might just always be able to skip SHIP).
    The other 3 are exlcuded because they are actual child records.
    Therefore answer/sum is 0.

    Name:  vbForums 3.jpg
Views: 245
Size:  22.8 KB

    But see how complicated and specific this is, which is why I feel I could control it better if I did it in a sequential programming language? I could get this resultset (and all the others) and examine them more easily looping through each set. And that might just be me that finds that way easier but you might not be here next year when I have to enhance it! (I mean, I'm sure you'd still be here, but maybe too busy with your own work!)
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  26. #26
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: SQL Server Query - count if any rows have a certain value

    Starting with this:
    Code:
    SELECT customer_part_number, P21.dbo.p21_view_oe_line.extended_price, P21.dbo.p21_view_oe_line.disposition, P21.dbo.p21_view_oe_line.parent_oe_line_uid
    FROM P21.dbo.p21_view_oe_line
    WHERE P21.dbo.p21_view_oe_line.order_no = '1066152'
    That gets your initial results...

    Step 0 - apply aliases to clean it up:
    Code:
    SELECT tblP21.customer_part_number, tblP21.extended_price, tblP21.disposition, tblP21.parent_oe_line_uid
    FROM P21.dbo.p21_view_oe_line tblP21
    WHERE tblP21.order_no = '1066152'

    Now applying the rest of the requirements one by one:

    * We always skip HOLD.
    Code:
    SELECT tblP21.customer_part_number, tblP21.extended_price, tblP21.disposition, tblP21.parent_oe_line_uid
    FROM P21.dbo.p21_view_oe_line tblP21
    WHERE 
      tblP21.customer_part_number <> 'HOLD' 
        and tblP21.order_no = '1066152'

    * In this case, we skip EQ_PORT's price of $24,835 because there's a child record with a disposition S. (Any row that has a non zero parent-uid is a child).
    Code:
    SELECT tblP21.customer_part_number, 
      case when tblP21.customer_part_number = 'EQ_PORT' and coalesce(SRowsCount.rowCount, 0) <> 0 then then 0 else p21_view_oe_line.extended_price end as extended_price, 
      p21_view_oe_line.disposition, p21_view_oe_line.parent_oe_line_uid
    FROM P21.dbo.p21_view_oe_line tblP21
    left join (select id, sum(1) as rowCount from P21.dbo.p21_view_oe_line where disposition = 'S' and tblP21.order_no = order_no) SRowsCount
    WHERE 
      tblP21.customer_part_number <> 'HOLD' 
        and tblP21.p21_view_oe_line.order_no = '1066152'

    * We skip SHIP's price of $248 because its a non-inventory item (which I might just always be able to skip SHIP).
    Code:
    SELECT tblP21.customer_part_number, 
      case when tblP21.customer_part_number = 'EQ_PORT' and coalesce(SRowsCount.rowCount, 0) <> 0 then then 0 else p21_view_oe_line.extended_price end as extended_price, 
      p21_view_oe_line.disposition, p21_view_oe_line.parent_oe_line_uid
    FROM P21.dbo.p21_view_oe_line tblP21
    left join (select id, sum(1) as rowCount from P21.dbo.p21_view_oe_line where disposition = 'S' and tblP21.order_no = order_no) SRowsCount
    WHERE 
      tblP21.customer_part_number <> 'HOLD'
        and tblP21.customer_part_number <> 'SHIP'
        and tblP21.p21_view_oe_line.order_no = '1066152'

    * The other 3 are exlcuded because they are actual child records.
    Code:
    SELECT tblP21.customer_part_number, 
      case when tblP21.customer_part_number = 'EQ_PORT' and coalesce(SRowsCount.rowCount, 0) <> 0 then then 0 else p21_view_oe_line.extended_price end as extended_price, 
      p21_view_oe_line.disposition, p21_view_oe_line.parent_oe_line_uid
    FROM P21.dbo.p21_view_oe_line tblP21
    left join (select id, sum(1) as rowCount from P21.dbo.p21_view_oe_line where disposition = 'S' and tblP21.order_no = order_no) SRowsCount
    WHERE 
      tblP21.customer_part_number <> 'HOLD'
        and tblP21.customer_part_number <> 'SHIP'
        and tblP21.parent_oe_line_uid <> 0
        and tblP21.p21_view_oe_line.order_no = '1066152'
    * Therefore answer/sum is 0.
    Code:
    SELECT tblP21.order_no, 
      SUM(case when tblP21.customer_part_number = 'EQ_PORT' and coalesce(SRowsCount.rowCount, 0) <> 0 then then 0 else p21_view_oe_line.extended_price end as extended_price) as Total
    FROM P21.dbo.p21_view_oe_line tblP21
    left join (select id, sum(1) as rowCount from P21.dbo.p21_view_oe_line where disposition = 'S' and tblP21.order_no = order_no) SRowsCount
    WHERE 
      tblP21.customer_part_number <> 'HOLD'
        and tblP21.customer_part_number <> 'SHIP'
        and tblP21.parent_oe_line_uid <> 0
        and tblP21.p21_view_oe_line.order_no = '1066152'
    GROUP BY tblP21.order_no

    that should work... no guarnatees though.,...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  27. #27
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - count if any rows have a certain value

    You have two „then“ after the coalesce
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  28. #28
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - count if any rows have a certain value

    Quote Originally Posted by MMock View Post
    P.S. Just out of curiosity, name a few loud and heavy headbanging groups. I believe there has to be some overlap. I mean Rammstein is an obvious mention and Kristina saw them here on their stadium tour in Sept (we had seem them several years ago on Long Island New York), and her friend from Lithuania, who she met online in a Rammstein fan group, visited us for a week so they could go together. (Sandra had also seen them in a Baltic country a month or two before). I am half-Lithuanian so it was fun hosting her! (Mock is one syllable of my 3-syllable last name, LOL).
    I've seen Rammstein in the early "00" in what you would consider "small" venues (capacity some 5-6K people)
    As for german "loud and heavy", the probably two "known" names would be
    Scorpions and Accept
    who are both pretty much "old socks".
    And there is nothing new on the horizon... :-(
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  29. #29

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    I wrote the C# code so I will compare proffered solutions to my output... Stay tuned.

    Scorpions - I did not know they are German!
    Accept - I have to ask my daughter.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  30. #30
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: SQL Server Query - count if any rows have a certain value

    Accept are 100% German (I mean: Udo Dirkschneider ??!! )
    Also you are forgetting Helloween (pumpkins, booo!) and Running Wild (pirates, aaarggg!).
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  31. #31

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    So just for the fun of it, I am going to post my C# code for comparison, and I also have something to say about the maintenance I mentioned before.
    I am pulling a dataset from sql and adding onto it the new column that I need to have calculated.
    gcOpenOrderAllocate is my gridcontrol.
    Here's that:
    Code:
            
    private void LoadGrid()
            {
                // The Fill will run this query:
                //      select Jobs.Control, SONumber, JobType, xtblSaleType.Description as SaleType, CustNo, CustomerName, 
                //      [dbo].[fn_GetJobShopTask](Jobs.Control, null) as CST,
                //      dlr.SaleAmount, dlr.SaleDate, Details
                //      from Jobs
                //      join xtblForecastDealer dlr on dlr.JobControl = Jobs.Control
                //      join[xtblSaleType] on[xtblSaleType].Control = Jobs.SaleType
                //      where OrderIncludesActivities != -1
                //      and Status != 'CP'
                //      and JobType != 'Sale - MR'
                //      order by SONumber
                // Each row wants to go in the grid.  To this dataset we want to add a column for Allocated.  So we have to take each row's SONumber and run another query to get results to calculate that amound.
                using (dsOpenOrderAllocatedTableAdapters.DataTable1TableAdapter ta = new dsOpenOrderAllocatedTableAdapters.DataTable1TableAdapter())
                {
                    using (dsOpenOrderAllocated.DataTable1DataTable dt = new dsOpenOrderAllocated.DataTable1DataTable())
                    {
                        ta.Fill(dt);
    
                        dt.Columns.Add(new DataColumn("AllocatedAmount",System.Type.GetType("System.Decimal")));
    
    
                        foreach (dsOpenOrderAllocated.DataTable1Row dr in dt.Rows)
                        {
                            dr["AllocatedAmount"] = CalculateAllocated(dr.SONumber);
                        }
                    
                        gcOpenOrderAllocate.DataSource = dt;
                    }
                }
    
            }
    Here is CalculateAllocated() with the logic we've come to know and love:
    Code:
            private decimal CalculateAllocated(string SONumber)
            {
                decimal totalAmount = 0;
                string sFilter;
                //dsOpenOrderAllocated.DataTable2DataTable dtChildLinesDispositionA;
                DataTable dtChildLinesDispositionA;
    
                using (dsOpenOrderAllocatedTableAdapters.DataTable2TableAdapter ta = new dsOpenOrderAllocatedTableAdapters.DataTable2TableAdapter())
                {
                    using (dsOpenOrderAllocated.DataTable2DataTable dt = new dsOpenOrderAllocated.DataTable2DataTable())
                    {
                        ta.Fill(dt, SONumber);
                        foreach (dsOpenOrderAllocated.DataTable2Row dr in dt.Rows)
                        {
                            //  We are going to get a set of rows back and decided if we want to accumulate their extended_price.
                            //  Sometimes this will depends on what's in the other rows or what's in the row's other columns.
                            //  It's complicated
    
                            // We don't calculate HOLD's
                            if (dr.customer_part_number == "HOLD")
                                continue;
                            if (dr.class_id4 == null)
                            {
                                totalAmount += dr.extended_price; 
                            }
                            else
                            {
                                switch (dr.class_id4)
                                {
                                    case "EQ":
                                        // Accumulate EQ's extended price unless it's a child line
                                        if (dr.parent_oe_line_uid == 0)
                                            totalAmount += dr.extended_price;
                                        break;
                                    case "ITEM":
                                        // Accumulate the ITEM's extended price
                                        totalAmount += dr.extended_price;
                                        break;
                                    case "LABOR":
                                    case "OC":
                                    case "OPTION":
                                    case "PROJ":
                                    case "SA":
                                    case "WARR":
                                        break;
                                    case "PACKAGE":
                                        // Confirm there's a child and its disposition is A, if so accumulate the PACKAGE's extended price.
                                        sFilter = "parent_oe_line_uid > 0 AND disposition = 'A'";
                                        using (DataView view = new DataView(dt, sFilter, "", DataViewRowState.CurrentRows))
                                        {
                                            dtChildLinesDispositionA = view.ToTable();
                                            if (dtChildLinesDispositionA.Rows.Count > 0)
                                                totalAmount += dr.extended_price;
                                        }
    
                                        break;
                                }
    
                            }
                        }
                    }
                }
                return totalAmount;
    
            }
    By the way, disposition = null is the same as dispostion A, I can't recall what I've said about that, whether you know NULL is "converted" to A. But they are one and the same for our purposes.
    So given that code, we already found a logic error. The VP just pointed out to me that we want to not accumulate any disposition = 'S'. That is very easy for me to add into the top of the foreach loop. I haven't worked with the query yet, but I am afraid it would not be so very easy to add it there. I know I asked a question in my OP, about how to write a query and you are helping me with that, but as the specs evolved and my boss said ultimately she just needs an Excel file (which is a simple button-click operation in my code as I export grids to Excel all the time and have the code), that is why I'm saying maybe C# code is a better way.

    Thoughts?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  32. #32

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    Quote Originally Posted by sapator View Post
    Also you are forgetting ... Running Wild (pirates, aaarggg!).
    Pirates are why I believe she likes Santiano, sea shanty music!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  33. #33

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - count if any rows have a certain value

    And you'll never guess what just happened? Now they want an unallocated bucket in addition to an allocated bucket. So my local accumulating value totalAmount just got tossed and I have global variables totalAllocatedAmount and totalUnallocatedAmount. And CalculateAllocated() (which is now a misnomer!), goes like this:

    Code:
    /// <summary>
            /// CalculateAllocated() - 12/01/22 Written.
            /// 12/02/22 - Originally it returned the allocated amount.  Dawn just asked me if we could also have the unallocated amount.  So a couple changes to do that.  I will make its return type void
            ///  and the two amounts will be global variables.
            /// </summary>
            /// <param name="SONumber"></param>
            /// <returns></returns>
            private void CalculateAllocated(string SONumber)
            {
                //decimal totalAmount = 0;
                totalAllocatedAmount = 0;
                totalUnallocatedAmount = 0;
    
                string sFilter;
                //dsOpenOrderAllocated.DataTable2DataTable dtChildLinesDispositionA;
                DataTable dtChildLinesDispositionA;
    
                using (dsOpenOrderAllocatedTableAdapters.DataTable2TableAdapter ta = new dsOpenOrderAllocatedTableAdapters.DataTable2TableAdapter())
                {
                    using (dsOpenOrderAllocated.DataTable2DataTable dt = new dsOpenOrderAllocated.DataTable2DataTable())
                    {
                        ta.Fill(dt, SONumber);
                        foreach (dsOpenOrderAllocated.DataTable2Row dr in dt.Rows)
                        {
                            //  We are going to get a set of rows back and decide if we want to accumulate their extended_price as allocated or unallocated.
                            //  Sometimes this will depend on what's in the other rows or what's in the row's other columns.
                            //  It's complicated
    
                            // We don't accumulate HOLD's.  Ever.
                            if (dr.customer_part_number == "HOLD")
                            {
                                continue;
                            }
    
                            // 12/02/22 - New requirement because of the SONumber Dawn and I just went through, 1067098.  It has like 45 rows so it's a good test.
                            if (dr.disposition == "S" || dr.disposition == "B" || dr.disposition == "H")
                            {
                                totalUnallocatedAmount += dr.extended_price;
                                continue;
                            }
    
                            if (dr.class_id4 == null)
                            {
                                totalAllocatedAmount += dr.extended_price; 
                            }
                            else
                            {
                                switch (dr.class_id4)
                                {
                                    case "EQ":
                                        // Accumulate EQ's extended price unless it's a child line
                                        if (dr.parent_oe_line_uid == 0)
                                            totalAllocatedAmount += dr.extended_price;
                                        else
                                        {
                                            if (dr.disposition == "A")
                                                ;
                                            else
                                               totalUnallocatedAmount += dr.extended_price;
                                        }
                                        break;
                                    case "ITEM":
                                        // Accumulate the ITEM's extended price
                                        totalAllocatedAmount += dr.extended_price;
                                        break;
                                    case "LABOR":
                                    case "OC":
                                    case "OPTION":
                                    case "PROJ":
                                    case "SA":
                                    case "WARR":
                                        break;
                                    case "PACKAGE":
                                        // Confirm there's a child and its disposition is A, if so accumulate the PACKAGE's extended price.
                                        sFilter = "parent_oe_line_uid > 0 AND disposition = 'A'";
                                        using (DataView view = new DataView(dt, sFilter, "", DataViewRowState.CurrentRows))
                                        {
                                            dtChildLinesDispositionA = view.ToTable();
                                            if (dtChildLinesDispositionA.Rows.Count > 0)
                                                totalAllocatedAmount += dr.extended_price;
                                            else
                                                totalUnallocatedAmount += dr.extended_price;
    
                                        }
    
                                        break;
                                }
                            }
                        }
                    }
                }
                
                //return totalAmount;
    
            }
    This is why I usually have a glass of wine when I go home at the end of the workday...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

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



Click Here to Expand Forum to Full Width