September 4, 2015 at 10:40 pm
Hi,
I have a scenario where ID has three flags.
For example
ID flag1 flag2 flag3
1 0 1 0
2 1 0 0
1 1 0 0
1 0 0 1
2 0 1 0
3 0 1 0
Now I want the records having flag2=1 only.. I.e ID=3 has flag2=1 where as ID = 1 and 2 has flag1 and flag3 =1 along with flag2=1. I don't want ID=1 and 2.
I can't make ID unique or primary. I tried with case when statements but it I am somehow missing the basic logic. Can any1 of you would help me to get the answer to this?
September 5, 2015 at 12:41 am
hegdesuchi (9/4/2015)
Hi,I have a scenario where ID has three flags.
For example
ID flag1 flag2 flag3
1 0 1 0
2 1 0 0
1 1 0 0
1 0 0 1
2 0 1 0
3 0 1 0
Now I want the records having flag2=1 only.. I.e ID=3 has flag2=1 where as ID = 1 and 2 has flag1 and flag3 =1 along with flag2=1. I don't want ID=1 and 2.
I can't make ID unique or primary. I tried with case when statements but it I am somehow missing the basic logic. Can any1 of you would help me to get the answer to this?
Quick suggestion
😎
/* Safe place */
USE tempdb;
GO
SET NOCOUNT ON;
/* Table variable for the sample data
If the flag values are only 0 or 1
then consider using TINYINT rather
than INT. Bit is not appropriate as
it is invalid for aggregation.
*/
DECLARE @SAMPLE_DATA TABLE
(
ID INT NOT NULL
,flag1 TINYINT NOT NULL
,flag2 TINYINT NOT NULL
,flag3 TINYINT NOT NULL
);
/* Insert the sample data into the table variable */
INSERT INTO @SAMPLE_DATA(ID,flag1,flag2,flag3)
VALUES ( 1, 0, 1, 0)
,( 2, 1, 0, 0)
,( 1, 1, 0, 0)
,( 1, 0, 0, 1)
,( 2, 0, 1, 0)
,( 3, 0, 1, 0)
;
/* CTE for aggregating the flag values, grouping on the ID column */
;WITH BASE_DATA AS
(
SELECT
SD.ID AS ID
,MAX(SD.flag1) AS F1
,MAX(SD.flag2) AS F2
,MAX(SD.flag3) AS F3
FROM @SAMPLE_DATA SD
GROUP BY SD.ID
)
/* Select only the rows where only flag2 = 1 */
SELECT
BD.ID
,BD.F1
,BD.F2
,BD.F3
FROM BASE_DATA BD
WHERE BD.F1 = 0
AND BD.F2 = 1
AND BD.F3 = 0
;
Results
ID F1 F2 F3
---- ---- ---- ----
3 0 1 0
Edit: Code comment
September 5, 2015 at 9:05 am
Hey,
thank you very much. It does work.. But I have to repeat this scenario for 3 flags.. So I have to define CTE all three times.
Do we have any other option where we can capture three scenarios at once?
Regards,
chits
September 6, 2015 at 1:32 am
hegdesuchi (9/5/2015)
Hey,thank you very much. It does work.. But I have to repeat this scenario for 3 flags.. So I have to define CTE all three times.
Do we have any other option where we can capture three scenarios at once?
Regards,
chits
Can you post the DDL ( create table ), sample data as an insert statement, the full and complete requirements and the expected results?
😎
This kind of problems are relatively straight forward, it's guessing the missing parts of the question that is hard;-)
September 6, 2015 at 8:43 am
This is what something i want:
* Safe place */
GO
SET NOCOUNT ON;
/* Table variable for the sample data
If the flag values are only 0 or 1
then consider using TINYINT rather
than INT. Bit is not appropriate as
it is invalid for aggregation.
*/
DECLARE @SAMPLE_DATA TABLE
(
ID INT NOT NULL
,flag1 TINYINT NOT NULL
,flag2 TINYINT NOT NULL
,flag3 TINYINT NOT NULL
);
/* Insert the sample data into the table variable */
INSERT INTO @SAMPLE_DATA(ID,flag1,flag2,flag3)
VALUES ( 1, 0, 1, 0)
,( 2, 1, 0, 0)
,( 1, 1, 0, 0)
,( 1, 0, 0, 1)
,( 2, 0, 1, 0)
,( 3, 0, 1, 0)
,( 4, 1, 0, 0)
, (5, 1, 0, 1 )
;
/* CTE for aggregating the flag values, grouping on the ID column */
;WITH BASE_DATA AS
(
SELECT
SD.ID AS ID
,MAX(SD.flag1) AS F1
,MAX(SD.flag2) AS F2
,MAX(SD.flag3) AS F3
FROM @SAMPLE_DATA SD
GROUP BY SD.ID
)
/* Select only the rows where only flag2 = 1 */
SELECT
BD.ID
,BD.F1
,BD.F2
,BD.F3
FROM BASE_DATA BD
WHERE BD.F1 = 0
AND BD.F2 = 1
AND BD.F3 = 0
;
/* CTE for aggregating the flag values, grouping on the ID column */
;WITH BASE_DATA AS
(
SELECT
SD.ID AS ID
,MAX(SD.flag1) AS F1
,MAX(SD.flag2) AS F2
,MAX(SD.flag3) AS F3
FROM @SAMPLE_DATA SD
GROUP BY SD.ID
)
/* Select only the rows where only flag2 = 1 */
SELECT
BD.ID
,BD.F1
,BD.F2
,BD.F3
FROM BASE_DATA BD
WHERE BD.F1 = 1
AND BD.F2 = 0
AND BD.F3 = 0
;
/* CTE for aggregating the flag values, grouping on the ID column */
;WITH BASE_DATA AS
(
SELECT
SD.ID AS ID
,MAX(SD.flag1) AS F1
,MAX(SD.flag2) AS F2
,MAX(SD.flag3) AS F3
FROM @SAMPLE_DATA SD
GROUP BY SD.ID
)
/* Select only the rows where only flag2 = 1 */
SELECT
BD.ID
,BD.F1
,BD.F2
,BD.F3
FROM BASE_DATA BD
WHERE BD.F1 = 1
AND BD.F2 = 0
AND BD.F3 = 1
;
Result:
IDF1F2F3
3010
IDF1F2F3
4100
IDF1F2F3
5101
September 7, 2015 at 1:29 am
SELECT
BD.ID
,x.MyFilter
,BD.F1
,BD.F2
,BD.F3
FROM BASE_DATA BD
CROSS APPLY (
SELECT MyFilter = CASE
WHEN BD.F1 = 0 AND BD.F2 = 1 AND BD.F3 = 0 THEN 'F2 only'
WHEN BD.F1 = 1 AND BD.F2 = 0 AND BD.F3 = 0 THEN 'F1 only'
WHEN BD.F1 = 1 AND BD.F2 = 0 AND BD.F3 = 1 THEN 'F1 and F2'
END
) x
WHERE x.MyFilter IS NOT NULL;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply