Menu
Is it possible to do the following:
I have a table that looks like this:
declare @tran_TABLE TABLE(
EOMONTH DATE,
AccountNumber INT,
CLASSIFICATION_NAME VARCHAR(50),
Value Float
)
INSERT INTO @tran_TABLE VALUES('2018-11-30','123','cat1',10)
INSERT INTO @tran_TABLE VALUES('2018-11-30','123','cat1',15)
INSERT INTO @tran_TABLE VALUES('2018-11-30','123','cat1',5 )
INSERT INTO @tran_TABLE VALUES('2018-11-30','123','cat2',10)
INSERT INTO @tran_TABLE VALUES('2018-11-30','123','cat3',12)
INSERT INTO @tran_TABLE VALUES('2019-01-31','123','cat1',5 )
INSERT INTO @tran_TABLE VALUES('2019-01-31','123','cat2',10)
INSERT INTO @tran_TABLE VALUES('2019-01-31','123','cat2',15)
INSERT INTO @tran_TABLE VALUES('2019-01-31','123','cat3',5 )
INSERT INTO @tran_TABLE VALUES('2019-01-31','123','cat3',2 )
INSERT INTO @tran_TABLE VALUES('2019-03-31','123','cat1',15)
EOMONTH AccountNumber CLASSIFICATION_NAME Value
2018-11-30 123 cat1 10
2018-11-30 123 cat1 15
2018-11-30 123 cat1 5
2018-11-30 123 cat2 10
2018-11-30 123 cat3 12
2019-01-31 123 cat1 5
2019-01-31 123 cat2 10
2019-01-31 123 cat2 15
2019-01-31 123 cat3 5
2019-01-31 123 cat3 2
2019-03-31 123 cat1 15
I want to produce a result where it will check whether in each month, for each AccountNumber (just one in this case) there exists a CLASSIFICATION_NAME cat1, cat2, cat3.
If all 3 exist for the month, then return 1 but if any are missing return 0.
The result should look like:
EOMONTH AccountNumber CLASSIFICATION_NAME
2018-11-30 123 1
2019-01-31 123 1
2019-03-31 123 0
But I want to do it as compactly as possible, without first creating a table that groups everything by CLASSIFICATION_NAME, EOMONTH and AccountNumber and then selects from that table.
For example, in the pseudo code below, is it possible to use maybe an EXISTS statement to do the group by?
SELECT
EOMONTH
,AccountNumber
,CASE WHEN EXISTS (CLASSIFICATION_NAME = 'cat1' AND 'cat2' AND 'cat3') THEN 1 ELSE 0 end
,SUM(Value) AS totalSpend
FROM @tran_TABLE
GROUP BY
EOMONTH
,AccountNumber
759
The result of EXISTS is a boolean value True or False. It can be used in a SELECT, UPDATE, INSERT or DELETE statement.
IN can be used as a replacement for multiple OR operators. To determine if any values are returned or not, we use EXISTS. 2. IN works faster than the EXISTS Operator when If the sub-query result is small.
The SQL EXISTS OperatorThe EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.
The GROUP BY Clause is used to group rows with same values. The GROUP BY Clause is used together with the SQL SELECT statement. The SELECT statement used in the GROUP BY clause can only be used contain column names, aggregate functions, constants and expressions. The HAVING clause is used to restrict the results returned by the GROUP BY clause.
The ORDER BY clause then sorts the rows within each group. If you have no GROUP BY clause, then the statement considers the entire table as a group, and the ORDER BY clause sorts all its rows according to the column (or columns) that the ORDER BY clause specifies. To illustrate this point, consider the data in the SALES table.
The GROUP BY clause is a SQL command that is used to group rows that have the same values. The GROUP BY clause is used in the SELECT statement. Optionally it is used in conjunction with aggregate functions to produce summary reports from the database.
The distinct clause is used to filter unique records out of the duplicate records that satisfy the query criteria. The Group by clause is often used to arrange the identical duplicate data into groups with the select statement. This clause works with the select specific list of items, for that we can use HAVING, and ORDER BY clauses.
You could emulate this behavior by counting the distinct classifications that answer this condition (per group):
SELECT
EOMONTH
,AccountNumber
,CASE COUNT(DISTINCT CASE WHEN classification_name IN ('cat1', 'cat2', 'cat3') THEN classification_name END)
WHEN 3 THEN 1
ELSE 0
END
,SUM(Value) AS totalSpend
FROM @tran_TABLE
GROUP BY
EOMONTH
,AccountNumber
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
