November 22, 2021 at 3:13 pm
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
November 22, 2021 at 4:27 pm
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/
November 22, 2021 at 4:57 pm
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
November 23, 2021 at 10:41 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 23, 2021 at 11:06 am
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.
November 23, 2021 at 12:29 pm
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%'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 23, 2021 at 3:12 pm
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
November 23, 2021 at 3:27 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 23, 2021 at 3:30 pm
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;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 24, 2021 at 5:31 am
This was removed by the editor as SPAM
November 24, 2021 at 5:31 am
This was removed by the editor as SPAM
November 25, 2021 at 10:30 am
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
November 25, 2021 at 11:22 am
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 25, 2021 at 12:23 pm
Sorry my mistake trn.year is decimal. I can not change it. Is it not possible to filter the year in Case statement?
November 25, 2021 at 1:06 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply