Crew Scheduled

basit0079 · January 26, 2020, 7:34am

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.

Thanks

Basit.

harishgg1 · January 26, 2020, 9:52am

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

harishgg1 · January 26, 2020, 10:04am

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

basit0079 · January 26, 2020, 11:12am

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.

harishgg1 · January 26, 2020, 12:45pm

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

basit0079 · January 26, 2020, 1:00pm

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

Thanks
Basit.

harishgg1 · January 26, 2020, 2:07pm

Thanks,
WIM ..

JeffModen · January 27, 2020, 12:35am

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.

basit0079 · January 27, 2020, 4:40am

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

harishgg1 · January 27, 2020, 6:28am

hi

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

basit0079 · January 27, 2020, 6:34am

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

harishgg1 · January 27, 2020, 7:13am

Hi

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

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

jeffw8713 · January 27, 2020, 7:47pm

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.

harishgg1 · January 28, 2020, 11:32am

HI jeff

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

NICE