Unable to filter data from the query

  • Hi,

    As you know I am a newbie and learning myself how to query the database. I am unable to filter the data from the query below.

    SELECT DISTINCT a.InvoiceDate AS TransactionDate
    ,SUM(CASE
    WHEN a.TrnYear = '2022'
    AND (a.ProductClass NOT IN ( '_FRT%','CXXX'))
    THEN a.NetSalesValue
    ELSE 0
    END) AS Dailycheck
    ,a.ProductClass
    ,a.SalesOrder,ar.Name
    FROM TrnDetail AS a
    INNER JOIN Customer AS ar ON ar.Customer = a.Customer
    GROUP BY a.InvoiceDate
    ,a.StockCode
    ,a.ProductClass
    ,a.SalesOrder
    , ar.Name

    I could filter in where but not in Case statement. I like to do it in case statement so I could bring other similar queries.

     

    CREATE TABLE mytable(

    TransactionDate DATE NOT NULL PRIMARY KEY

    ,Dailycheck BIT NOT NULL

    ,ProductClass VARCHAR(4) NOT NULL

    ,SalesOrder INTEGER NOT NULL

    ,arName VARCHAR(7) NOT NULL

    );

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('13/09/2021',0,'CBB',8,'John');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('13/09/2021',0,'CBB',9,'Craig');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('13/09/2021',0,'GAR',46,'Allen');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('13/09/2021',0,'ILC',13,'Mark');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('13/09/2021',0,'ILC',80,'Chris');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('13/09/2021',0,'ILC',81,'Evan');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('13/09/2021',0,'LOC',5,'Jack');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('13/09/2021',0,'MBT',16,'James');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('13/09/2021',0,'_FRT',16,'James');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('14/09/2021',0,'_FRT',28,'Craig');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('14/09/2021',0,'_FRT',29,'Craig');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('14/09/2021',0,'_FRT',30,'Allen');

    INSERT INTO mytable(TransactionDate,Dailycheck,ProductClass,SalesOrder,arName) VALUES ('14/09/2021',0,'_FRT',31,'Wheeler');

    Please direct me how I could filter the data?

    Thanks

  • How do you want to filter the data?

    I like to do it in case statement so I could bring other similar queries.

    Can you explain that?

    If you have a group by, adding distinct is not necessary.

    Great job providing consumable data!!!

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Thanks, Michael.

    I have created a query but not filtering properly. for example and I like to show it on the report where I have struggles it so far.

     

    SELECT DISTINCT a.InvoiceDate AS TransactionDate
    ,SUM(CASE
    WHEN a.TrnYear = '2022'
    AND (a.ProductClass NOT IN ( '_FRT%','CXXX'))
    THEN a.NetSalesValue
    ELSE 0
    END) AS Dailycheck
    ,SUM(CASE
    WHEN a.TrnYear = '2021'
    AND (a.ProductClass NOT IN ( '_FRT%','CXXX'))
    THEN a.NetSalesValue
    ELSE 0
    END) AS Dailycheck21
    ,SUM(CASE
    WHEN a.TrnYear = '2022' AND Countrycode = GB
    AND (a.ProductClass NOT IN ( '_FRT%','CXXX'))
    THEN a.NetSalesValue
    ELSE 0
    END) AS DailycheckHome
    ,a.ProductClass
    ,a.SalesOrder,ar.Name
    FROM TrnDetail AS a
    INNER JOIN Customer AS ar ON ar.Customer = a.Customer
    GROUP BY a.InvoiceDate
    ,a.StockCode
    ,a.ProductClass
    ,a.SalesOrder
    , ar.Name
  • I have created a query but not filtering properly. for example and I like to show it on the report where I have struggles it so far.

    What is your question? It's difficult to help unless you are more specific - saying that your query is 'not filtering properly' is too vague.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Thanks, Phil for kind words. In my query I have been trying to filter following options but I know the query does not filter when I use a.TRNyear=2022 or 2020. I want to filter no prod class _FRT and CXXX but the query displays the column with _FRT .

    WHEN a.TrnYear = '2021'

    AND (a.ProductClass NOT IN ( '_FRT%','CXXX'))

    It is obvious I do not know how to properly filter the query in the case statement.

  • gazy007 wrote:

    Thanks, Phil for kind words. In my query I have been trying to filter following options but I know the query does not filter when I use a.TRNyear=2022 or 2020. I want to filter no prod class _FRT and CXXX but the query displays the column with _FRT .

    WHEN a.TrnYear = '2021' AND (a.ProductClass NOT IN ( '_FRT%','CXXX'))

    It is obvious I do not know how to properly filter the query in the case statement.

    Unfortunately, wildcards do not work when using the IN construct. Try this instead:

    WHEN a.TrnYear = '2021' AND a.ProductClass <> 'CXXX' AND a.ProductClass NOT LIKE '_FRT%'

     

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Hi Phil,

    It didn't work. I tried as you suggested but I get the result even though there is no data in TrnYear=2022 but the query bring all the data from 2021 but I don't want anything from 2021 on one of the case statement. In my view if there are no transactions in year 2022 then the query should not return any data. I may be wrong.

    SELECT DISTINCT a.InvoiceDate AS TransactionDate

    ,SUM(CASE

    WHEN a.TrnYear = '2022'

    AND (

    a.ProductClass NOT IN (

    '_FRT'

    ,'CXXX'

    )

    )

    THEN a.NetSalesValue

    ELSE 0

    END) AS Dailycheck

    ,SUM(CASE

    WHEN a.TrnYear = '2021'

    AND (

    a.ProductClass NOT IN (

    '_FRT'

    ,'CXXX'

    )

    )

    THEN a.NetSalesValue

    ELSE 0

    END) AS Dailycheck21

    ,SUM(CASE

    WHEN a.TrnYear = '2022'

    AND Countrycode = GB

    AND (

    a.ProductClass NOT IN (

    '_FRT%'

    ,'CXXX'

    )

    )

    THEN a.NetSalesValue

    ELSE 0

    END) AS DailycheckHome

    ,a.ProductClass

    ,a.SalesOrder

    ,ar.Name

    FROM TrnDetail AS a

    INNER JOIN Customer AS ar ON ar.Customer = a.Customer

    GROUP BY a.InvoiceDate

    ,a.StockCode

    ,a.ProductClass

    ,a.SalesOrder

    ,ar.Name

  • First, note that your CASE expressions do not filter any data out. To do that requires a WHERE clause.

    Let's be more specific and focus on DailycheckHome, for example.

    First of all, note that Countrycode = GB is a syntax error and should be Countrycode = 'GB'.

    What is the datatype of TrnDetail.TrnYear? You are treating is as a character string, but surely it should be an INT (even better, a SMALLINT)?

    Are you saying that DailycheckHome returns a non-zero result, even though there are no rows in the source data where TrnYear is 2022? That should not be possible.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • By the way, your query is far easier to read if it is formatted and presented inside a code block, like this:

    SELECT DISTINCT
    TransactionDate = a.InvoiceDate
    ,Dailycheck = SUM( CASE
    WHEN a.TrnYear = '2022'
    AND (a.ProductClass NOT IN ( '_FRT', 'CXXX' )) THEN
    a.NetSalesValue
    ELSE
    0
    END
    )
    ,Dailycheck21 = SUM( CASE
    WHEN a.TrnYear = '2021'
    AND (a.ProductClass NOT IN ( '_FRT', 'CXXX' )) THEN
    a.NetSalesValue
    ELSE
    0
    END
    )
    ,DailycheckHome = SUM( CASE
    WHEN a.TrnYear = '2022'
    AND Countrycode = GB
    AND (a.ProductClass NOT IN ( '_FRT%', 'CXXX' )) THEN
    a.NetSalesValue
    ELSE
    0
    END
    )
    ,a.ProductClass
    ,a.SalesOrder
    ,ar.Name
    FROM TrnDetail a
    INNER JOIN Customer ar
    ON ar.Customer = a.Customer
    GROUP BY a.InvoiceDate
    ,a.StockCode
    ,a.ProductClass
    ,a.SalesOrder
    ,ar.Name;

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Hi Phil,

    TrnDate datatype is decimal and length 5. I tried your suggestion but I should not get any data from 2022 because there isn't any but the query brings the all data.

    ,Dailycheck = SUM(   CASE
    WHEN a.TrnYear = '2022'
    AND (a.ProductClass NOT IN ( '_FRT', 'CXXX' )) THEN
    a.NetSalesValue
    ELSE
    0
    END
  • The query 'brings all the data' because you are not filtering any of it out. If you wish to filter out data, use a WHERE clause.

    I asked you about TrnYear, not TrnDate. If Decimal, this is the wrong datatype for storing years. I recommend that you change to SMALLINT, along with a suitable CHECK constraint to help prevent inadvertent entry of unlikely years (eg, 21 instead of 2021).

    With numeric data types (DECIMAL, INT, SMALLINT, etc), do not use quotes when dealing with literals. For example:

    WHEN a.TrnYear = '2022' should be WHEN a.TrnYear = 2022

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Sorry my mistake trn.year is decimal. I can not change it. Is it not possible to filter the year in Case statement?

  • gazy007 wrote:

    Sorry my mistake trn.year is decimal. I can not change it. Is it not possible to filter the year in Case statement?

    No.

    CASE is an expression, which returns a result. It does not perform filtering of the data which the query selects.

    It can return a date-dependent result, but this is merely down to the expression used in the CASE, not filtering of the underlying data.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply