Many Thanks for help, MinDate/FromDate is working perfect, but MaxDate/fromDate if Crew A working "OD" on next / other shift then getting last OD MaxDate/From Date.
DROP TABLE #DATA
GO
CREATE TABLE #DATA
(
SHEDNO INT IDENTITY(1,1) NOT NULL,
CREW VARCHAR(1),
TYPE VARCHAR(2),
[DATE] DATE
)
GO
SET DATEFORMAT DMY
INSERT INTO #DATA SELECT 'A','OD','19-01-2020'
INSERT INTO #DATA SELECT 'A','OD','20-01-2020'
INSERT INTO #DATA SELECT 'A','OD','21-01-2020'
INSERT INTO #DATA SELECT 'A','OD','22-01-2020'
INSERT INTO #DATA SELECT 'A','OD','23-01-2020'
INSERT INTO #DATA SELECT 'A','OD','24-01-2020'
INSERT INTO #DATA SELECT 'A','RS','03-02-2020'
INSERT INTO #DATA SELECT 'A','RS','04-02-2020'
INSERT INTO #DATA SELECT 'A','RS','05-02-2020'
INSERT INTO #DATA SELECT 'A','RS','06-02-2020'
INSERT INTO #DATA SELECT 'A','RS','07-02-2020'
INSERT INTO #DATA SELECT 'B','OD','13-01-2020'
INSERT INTO #DATA SELECT 'B','OD','14-01-2020'
INSERT INTO #DATA SELECT 'B','OD','15-01-2020'
INSERT INTO #DATA SELECT 'B','OD','16-01-2020'
INSERT INTO #DATA SELECT 'B','OD','17-01-2020'
INSERT INTO #DATA SELECT 'B','RS','25-01-2020'
INSERT INTO #DATA SELECT 'B','RS','26-01-2020'
INSERT INTO #DATA SELECT 'B','RS','28-01-2020'
INSERT INTO #DATA SELECT 'B','RS','29-01-2020'
INSERT INTO #DATA SELECT 'A','OD','25-02-2020'
INSERT INTO #DATA SELECT 'A','OD','26-02-2020'
INSERT INTO #DATA SELECT 'A','OD','28-02-2020'
INSERT INTO #DATA SELECT 'A','OD','29-02-2020'
GO
;WITH CTE AS (
SELECT CREW
, TYPE
, MIN([date]) as minData
, MAX([date]) as maxDate
FROM #DATA
GROUP BY CREW, TYPE
)
SELECT #DATA.SHEDNO,#DATA.CREW
, #DATA.TYPE
, #DATA.[DATE]
, CTE.minData as FromDate
, CTE.maxDate as ToDate
FROM #DATA
INNER JOIN CTE
ON #DATA.CREW = CTE.CREW
AND #DATA.TYPE = CTE.TYPE
order by SHEDNO
Then the result is wrong
Looking for below result. Get the result shift wise minDate/FromDate and MaxDate/To Date, minDate/From Date is coming correctly, but maxDate is getting Max from column