Hello Techie,
may any one please help me how to solve this puzzle Please.

/*
Rules


The resultset should appear only when fulfill all three criteria
1ST : For a group of Tree , seed, flower and Year there should always more than one distinct FLRNO
2ND:  The number of FLRNO in a group of Tree , seed, flower and Year should available with all available comment 1 in the group
3rd:   For a group of Tree , seed, flower and Year, the number of all comment 3, will also available with all comment1






Tree Banana will be in output, as group of Tree , seed,flower and Year have two distinct FLRNO which are available with all available comment1 in the group and comment 2 is also available with all available comment 1
Tree Mango should not be in output as it has only one comment that is 'ALL SMELL FLOWER', for a given group of tree, seed,flower and year
Tree KIWI should not be in output because only one comment available for a given group of tree, seed,flower and year
Tree PAPAYA should not be in output because FLRNO '81250' is not available with Bad Smell Flower
Tree Apple should not be in output because comment2 (God ) is not available with comment1 (SE; Bad SMELL FLOWER)


These all are treated as only one type of flower as 'ALL SMELL FLOWER' i need to check %smell flower% excluding 
value before and after semicolon (;)
 'SXT; ALL SMELL FLOWER'
'SE; ALL SMELL FLOWER;TX'
'TD;ALL SMELL FLOWER'

*/

create table #temp
(
TREE varchar (500),	
SEED varchar (500),	
FLOWER	varchar (500),
Year	varchar (500),
FLRNO	varchar (500),
COMMENT1 varchar (500),
COMMENT3 varchar (500),
hash AS hashbytes('MD5', [TREE] +  SEED +  FLOWER + YEAR + FLRNO + COMMENT1 + COMMENT3)

)

insert #temp

SELECT 'BANANA',	'Nis',	'Tit',	'2004',	'57101',	'Sweet Smell Flower',  '' UNION ALL
SELECT 'BANANA',	'Nis',	'Tit',	'2004',	'57101',	'Bad Smell flower'	,  '' UNION ALL
SELECT 'BANANA',	'Nis',	'Tit',	'2004',	'B0019B',	'Sweet Smell Flower',  'CATCH ME' UNION ALL
SELECT 'BANANA',	'Nis',	'Tit',	'2004',	'B0019B',   'Bad Smell flower',	   'CATCH ME' UNION ALL
SELECT 'MANGO',	    'Dod',	'char',	'2012',	'D935',	    'SXT; ALL SMELL FLOWER','' UNION ALL
SELECT 'MANGO',	    'Dod',	'char',	'2012',	'D935',	    'SE; ALL SMELL FLOWER;TX', '' UNION ALL
SELECT 'MANGO',	    'Dod',	'char',	'2012',	'D935',	    'TD;ALL SMELL FLOWER', '' UNION ALL
SELECT 'KIWI',	    'TOY',	'TAC',	'2004',	'S205',	    'W/T; Sweet Smell Flower','' UNION ALL
SELECT 'PAPAYA',	'LIK',	'VIM',	'1976',	'13122',	'Sweet Smell Flower','' UNION ALL
SELECT 'PAPAYA',	'LIK',	'VIM',	'1976',	'13122',	'Bad Smell flower','' UNION ALL
SELECT 'PAPAYA',	'LIK',	'VIM',	'1976',	'81250',	'Sweet Smell Flower','' UNION ALL
SELECT 'PAPAYA',	'LIK',	'VIM',	'1976',	'81250',	'Sweet Smell Flower',''  UNION ALL
SELECT 'APPLE',	    'ALE',	'PPL',	'2015',	'PRS13',	'SXT; SWEET SMELL FLOWER',	'God' UNION ALL
SELECT 'APPLE',	    'ALE',	'PPL',	'2015',	'PRS13',	'SE; Bad SMELL FLOWER',	'CATCH ME' UNION ALL
SELECT 'APPLE',	    'ALE',	'PPL',	'2015',	'PRS13',	'SXT; SWEET SMELL FLOWER',	'CATCH ME'

Expected Output

 'BANANA',	'Nis',	'Tit',	'2004',	'57101',	'Sweet Smell Flower',  '' 
 'BANANA',	'Nis',	'Tit',	'2004',	'57101',	'Bad Smell flower'	,  '' 
 'BANANA',	'Nis',	'Tit',	'2004',	'B0019B',	'Sweet Smell Flower',  'CATCH ME' 
 'BANANA',	'Nis',	'Tit',	'2004',	'B0019B',   'Bad Smell flower',	   'CATCH ME' 

Thanks a ton.

Hi,

you can try something like this.

SELECT *
FROM @temp AS a
WHERE EXISTS(
	SELECT *
	FROM @temp AS b
	WHERE a.TREE = b.TREE AND a.SEED = b.SEED AND a.FLOWER = b.FLOWER AND a.Year = b.Year AND a.FLRNO <> b.FLRNO)
AND EXISTS(
	SELECT *
	FROM @temp AS b
	WHERE a.TREE = b.TREE AND a.SEED = b.SEED AND a.FLOWER = b.FLOWER AND a.Year = b.Year AND a.FLRNO = b.FLRNO AND a.COMMENT1 <> b.COMMENT1)
AND EXISTS(
	SELECT *
	FROM @temp AS b
	WHERE a.TREE = b.TREE AND a.SEED = b.SEED AND a.FLOWER = b.FLOWER AND a.Year = b.Year AND a.COMMENT1 = b.COMMENT1 AND a.COMMENT3 <> b.COMMENT3)

Hi Pras05,

Use this query

SELECT * , '''' + TREE + '''' +',' + '''' + SEED + ''''+',' +'''' + FLOWER + ''''+',' +'''' + FLRNO + ''''+',' + '''' + COMMENT1 + ''''+',' + '''' + COMMENT3 + ''''FROM #temp

Hello Steelleg4: Unfortunately the logic is not working. 

I am trying to get records based on below criteria.

1.  ALL 'FLRNO'  from a group of TREE,SEED,FLOWER AND Year should present with all comment 1

2. All comment3 from the same group of TREE,SEED,FLOWER AND Year present with all comment 1

3. IF A GROUP of TREE,SEED,FLOWER AND Year have only one distinct FLRNO, should be excluded from output

4. In Case if any one 'FLRNO' is not available with all available comment 1, then entire group exclude from output.

Please share your expertise.  

Thanks

Hi Pras05,

I don't quite understand the point 2 "All comment3 from the same group of TREE,SEED,FLOWER AND Year present with all comment 1", can you be more specific?

For the rest 3 points, you can see the below sample.

;WITH Cte AS
(
--IF A GROUP of TREE,SEED,FLOWER AND Year have only one distinct FLRNO, should be excluded from output
SELECT * FROM #temp T WHERE EXISTS(SELECT 1 FROM #TEMP WHERE TREE=T.TREE AND SEED=T.SEED AND FLOWER=T.FLOWER AND [Year]=t.[Year] AND FLRNO<>T.FLRNO)
)
,Cte2 AS
(
SELECT TREE,SEED,FLOWER,[Year],COMMENT1,COUNT(FLRNO) CNT FROM Cte  GROUP BY TREE,SEED,FLOWER,[Year],COMMENT1
),
Cte3 AS
(
--ALL 'FLRNO'  from a group of TREE,SEED,FLOWER AND Year should present with all comment 1
--In Case if any one 'FLRNO' is not available with all available comment 1, then entire group exclude from output.
SELECT * FROM CTE2 T WHERE NOT EXISTS(SELECT 1 FROM Cte2 WHERE TREE=T.TREE AND SEED=T.SEED AND FLOWER=T.FLOWER AND [Year]=t.[Year] AND CNT<>T.CNT)
) SELECT * FROM #temp T WHERE EXISTS(SELECT 1 FROM Cte3 WHERE TREE=T.TREE AND SEED=T.SEED AND FLOWER=T.FLOWER AND [Year]=t.[Year])

If you have any question, feel free to let me know.

Reason: Why the above logic is not working.

 IF WE Change this line

'BANANA', 'Nis', 'Tit', '2004', 'B0019B', 'Bad Smell Flower',  'CATCH ME'
to 

'BANANA', 'Nis', 'Tit', '2004', 'B0019B', 'Sweet Smell Flower',  'CATCH ME'

then no record should appear in the output. 

but with above script 1 record still exist in the output.

Can anyone please please help me. 

• Edited by Pras05 Friday, August 28, 2015 7:01 AM

Thanks a Lot Eric: i will elaborate Point 2

lets assume

when we group Tree , seed, flower and Year we will get some FLRNO say (1,2,3,4,5) and some comment 1 say (c1,c2,c3,c4) and some comment2 say (cm1,cm2,cm3).

the second point is to check comment 2 (cm1,cm2,cm3) is existing with (c1,c2,c3,c4)

SELECT 'APPLE',	    'ALE',	'PPL',	'2015',	'PRS13',	'SXT; SWEET SMELL FLOWER',	'God' UNION ALL
SELECT 'APPLE',	    'ALE',	'PPL',	'2015',	'PRS13',	'SE; Bad SMELL FLOWER',	'CATCH ME' UNION ALL
SELECT 'APPLE',	    'ALE',	'PPL',	'2015',	'PRS13',	'SXT; SWEET SMELL FLOWER',	'CATCH ME'

Tree Apple should not be in output because comment2 (God ) is not available with comment1 (SE; Bad SMELL FLOWER).

Please suggest.

• Edited by Pras05 2 hours 42 minutes ago

Hi Eric. kindly let me know if scenario 2 is still not clear. 

when we group Tree , seed, flower and Year we will get some FLRNO say for example  (1,2,3,4,5) and some comment 1 say for example  (c1,c2,c3,c4) and some comment2 say for example  (cm1,cm2,cm3) 

the second point is to check comment 2 (cm1,cm2,cm3) is existing with (c1,c2,c3,c4) with FLRNO  (1,2,3,4,5)

Regards