Crew Scheduled

Dear,

I have a table Called Crew Scheduled.

What im looking for with current date search and to get From Date and To Date

Example-1: If Current Date is 21-01-2020 the Crew-A FromDate is 19-01-2020 and ToDate is 24-01-2020

Example-2: If Current Date is 21-01-2020 the Crew-B FromDate is 25-01-2020 and ToDate is 29-01-2020

How to get this

The Table is below.

image

Thanks

Basit.

HI
i have created sample data script

please click arrow to the left for Drop Create Data Script
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'


GO 

SELECT * FROM #DATA
GO

Hi

Here's my solution
Please see if it is ok

DECLARE @CURRENT_DATE DATE = '21-01-2020'

; WITH CTE AS 
(
	SELECT 
		   CASE WHEN CREW = 'A' AND [DATE] BETWEEN DATEADD(DD,-2,@CURRENT_DATE) AND DATEADD(DD,3,@CURRENT_DATE) THEN 1 
				WHEN CREW = 'B' AND [DATE] BETWEEN DATEADD(DD,4 ,@CURRENT_DATE) AND DATEADD(DD,8,@CURRENT_DATE) THEN 1
		   END AS ROW_MARK
	   ,   * 
	FROM #DATA
  )
SELECT 
  * 
FROM 
   CTE 
     WHERE ROW_MARK = 1 

Many Many Thanks for your reply.

With the help of Schedule i want to know fromDate and ToDate of each shift. The exact output looking for is given below. Thanks once again.

image

HI

i know WIM has given a solution

this is another way of doing it ... using OVER clause
i think over clause .. release is sql server 2012

SELECT 
  MIN([DATE]) OVER(PARTITION BY CREW, TYPE) AS MINDATE, 
  MAX([DATE]) OVER(PARTITION BY CREW, TYPE) AS MAXDATE, 
  * 
FROM #DATA 

2 Likes

Many Many Thanks for your reply. That's looking for.

Thanks
Basit.

Thanks,
WIM ..

Will you ever reuse the Crew Letter for later dates for the same types?

If you do, then I don't believe the accepted answer will continue to work its magic.

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

hi

we can use SHEDNO ... to get the OUTPUT you want !!!

Many Thanks for your reply, but SHEDNO is increased by 1 in every row.

Hi

I tried to do this
Please see if this looks like what you want :slight_smile: :slight_smile:

please click arrow to the left for DROP CREATE Data
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 ROW_NUMBER() OVER(ORDER BY CREW) AS RN ,* FROM #DATA 
         ) , 
   CTE_GRP AS 
        (
	SELECT RN-SHEDNO AS GRP ,* FROM CTE 
        )   
SELECT MIN([DATE]) OVER(PARTITION BY CREW, TYPE , GRP) AS MINDATE, 
       MAX([DATE]) OVER(PARTITION BY CREW, TYPE , GRP) AS MAXDATE, 
	   *
FROM 
   CTE_GRP 
ORDER BY 
   SHEDNO 

This is a typical gaps and islands problem - and here is one way to solve:

   With dateGroups
     As (
 Select d.SHEDNO
      , d.CREW
      , d.[TYPE]
      , d.DATE
      , DateGroup = dateadd(day, -row_number() over(Partition By d.CREW, d.TYPE Order By d.DATE), d.DATE)
   From #DATA d
        )
 Select dg.SHEDNO
      , dg.CREW
      , dg.TYPE
      , dg.DATE
      , StartDate = min(dg.DATE) over(Partition By dg.CREW, dg.TYPE, dg.DateGroup)
      , EndDate = max(dg.DATE) over(Partition By dg.CREW, dg.TYPE, dg.DateGroup)
   From dateGroups dg;

The CTE creates the date groups - and then we can get the min/max for each date group.

HI jeff

THIS IS WHAT I DID ...BUT IN A BETTER WAY !!!

NICE

:+1: