CODEWITHSUNDEEP
sql-servert-sql
kojikhoo
kojikhoo

Reputation: 5

TSQL - Display the date which exist in all Model

MODEL     DateCreated 
----------------------
MODEL_1   2017-07-20
MODEL_1   2017-07-19
MODEL_1   2017-06-10
MODEL_1   2017-06-02
MODEL_2   2017-07-18
MODEL_2   2017-07-17
MODEL_2   2017-06-10
MODEL_2   2017-06-02
MODEL_3   2017-07-20
MODEL_3   2017-07-12
MODEL_3   2017-06-10
MODEL_3   2017-06-02
MODEL_3   2017-05-16

Expected result 

DateCreated_Exist_In_All_Model
------------------------------
2017-06-10
2017-06-02

This means, only those DateCreated exist in all model will be displayed

Thanks to everyone who willing to help me.

Upvotes: 0

Views: 31

Answers (2)

SQL006
SQL006

Reputation: 492

DECLARE @Temp table(Model varchar(1000), DateCreated date)

INSERT INTO @Temp

SELECT  'MODEL_1' , '2017-07-20'

UNION ALL

SELECT 'MODEL_1',   '2017-07-19'

UNION ALL

SELECT 'MODEL_1',   '2017-06-10'

UNION ALL

SELECT 'MODEL_1',   '2017-06-02'

UNION ALL

SELECT 'MODEL_2',   '2017-07-17'

UNION ALL

SELECT 'MODEL_2',   '2017-06-10'

UNION ALL

SELECT 'MODEL_2',   '2017-06-02'

UNION ALL

SELECT 'MODEL_3',   '2017-07-12'

UNION ALL

SELECT 'MODEL_3 ',  '2017-06-10'

UNION ALL

SELECT 'MODEL_3 ',  '2017-06-02'

UNION ALL

SELECT 'MODEL_3' ,  '2017-05-16'


DECLARE @ModelCount int

SELECT @ModelCount = COUNT(distinct Model) from @Temp



SELECT DISTINCT DateCreated FROM  (

SELECT

COUNT(MODEL) OVER(PARTITION BY DateCreated) AS Counts, Model,

DateCreated

FROM @Temp) AS D

WHERE D.Counts = @ModelCount



Or





SELECT DateCreated FROM  (

SELECT

ROW_NUMBER() OVER(PARTITION BY DateCreated ORDER BY DateCreated) as Rownum,

COUNT(MODEL) OVER(PARTITION BY DateCreated) AS Counts, Model,

DateCreated

FROM @Temp) AS D

WHERE D.Counts = @ModelCount and D.Rownum = 1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521674

One approach would be to aggregate on the creation date and then compare the count of distinct models appearing on each date against the total number of models appearing in the table.

SELECT
    DateCreated AS DateCreated_Exist_In_All_Model
FROM [yourTable]
GROUP BY DateCreated
HAVING COUNT(DISTINCT MODEL) = (SELECT COUNT(DISTINCT MODEL) FROM yourTable)

Note that even though there appear to be only 3 models in your data set, we can make the query more flexible by using a subquery which counts the number of distinct models in the entire table.

Upvotes: 1

Related Questions