How to construct query to change the output of an sql table

Question

I need some little help here...So, let me introduce you my problem.

I have the following SQL Server table:

| RankCode | SeaPortInd | WatchKeepingInd |      EffectiveDate      | VesselCode |        FromDate         |         ToDate          |        LastDate         | LastUser |
+----------+------------+-----------------+-------------------------+------------+-------------------------+-------------------------+-------------------------+----------+
| C/E      |          0 |               0 | 1900-01-01 00:00:00.000 |        031 | 1900-01-01 05:00:00.000 | 1900-01-01 07:00:00.000 | 2016-08-11 12:40:00.000 | d.baltas |
| C/E      |          0 |               0 | 2016-06-02 00:00:00.000 |        031 | 1900-01-01 00:00:00.000 | 1900-01-01 00:00:00.000 | 1900-01-01 00:00:00.000 | d.baltas |
| C/E      |          0 |               1 | 2016-06-01 00:00:00.000 |        031 | 1900-01-01 01:00:00.000 | 1900-01-01 02:00:00.000 | 2016-08-11 17:58:00.000 | d.baltas |
| C/E      |          0 |               1 | 2016-06-02 00:00:00.000 |        031 | 1900-01-01 01:00:00.000 | 1900-01-01 02:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
| C/E      |          1 |               1 | 2016-06-01 00:00:00.000 |        031 | 1900-01-01 03:00:00.000 | 1900-01-01 04:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
| MSTR     |          0 |               0 | 2016-06-02 00:00:00.000 |        031 | 1900-01-01 16:00:00.000 | 1900-01-01 22:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
| MSTR     |          0 |               1 | 2016-06-01 00:00:00.000 |        031 | 1900-01-01 08:00:00.000 | 1900-01-01 12:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
| MSTR     |          1 |               0 | 2016-06-03 00:00:00.000 |        031 | 1900-01-01 08:00:00.000 | 1900-01-01 14:00:00.000 | 2016-08-11 15:00:00.000 | d.baltas |
+----------+------------+-----------------+-------------------------+------------+-------------------------+-------------------------+-------------------------+----------+

I want to take an output like this table:

Some more explanation of table:

Scheduled daily work hours at sea means SeaPortInd = 1

Scheduled daily work hours at port means SeaPortInd = 0

Watchkeeping means WatchkeepingInd = 1

NonWatchkeeping means WatchkeepingInd = 0

I managed to take to the following table:

+----------+--------------------+
| RankCode | SeaNonWatchkeeping |
| C/E      |  00:00 - 00:00     |
|          |  05:00 - 07:00     |
| MSTR     |  16:00 - 22:00     |
+----------+--------------------+

with the query:

SELECT CASE 
        WHEN row_number() OVER (
                PARTITION BY RankCode ORDER BY FromDate asc
    ) = 1
            THEN RankCode
        ELSE ''
        END AS RankCode

    ,substring(convert(VARCHAR(255), FromDate, 120), 11, 6) + ' -' + substring(convert(VARCHAR(255), ToDate, 120), 11, 6) AS SeaNonWatchkeeping

FROM WorkingHoursSchedule WHERE SeaPortInd = 0 AND watchkeepingind = 0

Can you please help me how to get the cases SeaportInd = 0 and Watchkeeping= 1 etc?

I am using SQL Server 2008 but the query will also run at some previous versions with minimum SQL Server 2005

Thanks in advance!

Paweł Dyl · Accepted Answer · 2016-08-12 14:09:20Z

This one should work:

WITH WorkingHoursSchedule AS
(
    SELECT * FROM (VALUES
    ('C/E ', 0, 0, '1900-01-01 00:00:00.000', '031', '1900-01-01 05:00:00.000', '1900-01-01 07:00:00.000', '2016-08-11 12:40:00.000', 'd.baltas'),
    ('C/E ', 0, 0, '2016-06-02 00:00:00.000', '031', '1900-01-01 00:00:00.000', '1900-01-01 00:00:00.000', '1900-01-01 00:00:00.000', 'd.baltas'),
    ('C/E ', 0, 1, '2016-06-01 00:00:00.000', '031', '1900-01-01 01:00:00.000', '1900-01-01 02:00:00.000', '2016-08-11 17:58:00.000', 'd.baltas'),
    ('C/E ', 0, 1, '2016-06-02 00:00:00.000', '031', '1900-01-01 01:00:00.000', '1900-01-01 02:00:00.000', '2016-08-10 17:58:00.000', 'd.baltas'),
    ('C/E ', 1, 1, '2016-06-01 00:00:00.000', '031', '1900-01-01 03:00:00.000', '1900-01-01 04:00:00.000', '2016-08-10 17:58:00.000', 'd.baltas'),
    ('MSTR', 0, 0, '2016-06-02 00:00:00.000', '031', '1900-01-01 16:00:00.000', '1900-01-01 22:00:00.000', '2016-08-10 17:58:00.000', 'd.baltas'),
    ('MSTR', 0, 1, '2016-06-01 00:00:00.000', '031', '1900-01-01 08:00:00.000', '1900-01-01 12:00:00.000', '2016-08-10 17:58:00.000', 'd.baltas'),
    ('MSTR', 1, 0, '2016-06-03 00:00:00.000', '031', '1900-01-01 08:00:00.000', '1900-01-01 14:00:00.000', '2016-08-11 15:00:00.000', 'd.baltas')
    )T(RankCode, SeaPortInd, WatchKeepingInd, EffectiveDate, VesselCode, FromDate, ToDate, LastDate, LastUser)
), Src AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY RankCode ORDER BY RankCode, SeaPortInd, WatchKeepingInd) RN, RankCode
    FROM WorkingHoursSchedule
), SeaWatchKeeping AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY RankCode ORDER BY FromDate) RN, RankCode,
        SUBSTRING(CONVERT(VARCHAR(255), FromDate, 120), 12, 5) + ' - ' + SUBSTRING(CONVERT(VARCHAR(255), ToDate, 120), 12, 5) SeaWatchKeeping
    FROM WorkingHoursSchedule
    WHERE SeaPortInd = 0 AND WatchKeepingInd = 1
), SeaNonWatchKeeping AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY RankCode ORDER BY FromDate) RN, RankCode,
        SUBSTRING(CONVERT(VARCHAR(255), FromDate, 120), 12, 5) + ' - ' + SUBSTRING(CONVERT(VARCHAR(255), ToDate, 120), 12, 5) SeaNonWatchKeeping
    FROM WorkingHoursSchedule
    WHERE SeaPortInd = 0 AND WatchKeepingInd = 0
), LandWatchKeeping AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY RankCode ORDER BY FromDate) RN, RankCode,
        SUBSTRING(CONVERT(VARCHAR(255), FromDate, 120), 12, 5) + ' - ' + SUBSTRING(CONVERT(VARCHAR(255), ToDate, 120), 12, 5) LandWatchKeeping
    FROM WorkingHoursSchedule
    WHERE SeaPortInd = 1 AND WatchKeepingInd = 1
), LandNonWatchKeeping AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY RankCode ORDER BY FromDate) RN, RankCode,
        SUBSTRING(CONVERT(VARCHAR(255), FromDate, 120), 12, 5) + ' - ' + SUBSTRING(CONVERT(VARCHAR(255), ToDate, 120), 12, 5) LandNonWatchKeeping
    FROM WorkingHoursSchedule
    WHERE SeaPortInd = 1 AND WatchKeepingInd = 0
)
SELECT CASE WHEN S.RN=1 THEN S.RankCode ELSE NULL END RankCode, SeaWatchKeeping, SeaNonWatchKeeping, LandWatchKeeping, LandNonWatchKeeping
FROM Src S
LEFT JOIN SeaNonWatchKeeping SN ON S.RN=SN.RN AND S.RankCode=SN.RankCode
LEFT JOIN SeaWatchKeeping SW ON S.RN=SW.RN AND S.RankCode=SW.RankCode
LEFT JOIN LandNonWatchKeeping LN ON S.RN=LN.RN AND S.RankCode=LN.RankCode
LEFT JOIN LandWatchKeeping LW ON S.RN=LW.RN AND S.RankCode=LW.RankCode
WHERE SeaWatchKeeping IS NOT NULL OR SeaNonWatchKeeping IS NOT NULL OR LandWatchKeeping IS NOT NULL OR LandNonWatchKeeping IS NOT NULL

The rows are collapsed according to space occupied by RankCode and sorted by FromDate:

RankCode SeaWatchKeeping SeaNonWatchKeeping LandWatchKeeping LandNonWatchKeeping
-------- --------------- ------------------ ---------------- -------------------
C/E      01:00 - 02:00   00:00 - 00:00      03:00 - 04:00    NULL
NULL     01:00 - 02:00   05:00 - 07:00      NULL             NULL
MSTR     08:00 - 12:00   16:00 - 22:00      NULL             08:00 - 14:00

Khris Roberts · Accepted Answer · 2016-08-12 13:44:13Z

The below SQL is using CASE expressions to evaluate based on your requirements.

SELECT  [RankCode]
    ,   CASE
            WHEN [SeaPortInd] = 1 AND [WatchKeepingInd] = 1 THEN substring(convert(VARCHAR(255), [FromDate], 120), 11, 6) + ' -' + substring(convert(VARCHAR(255), [ToDate], 120), 11, 6)
        END as 'At Sea: Watchkeeping (from...to)'
    ,   CASE
            WHEN [SeaPortInd] = 1 AND [WatchKeepingInd] = 0 THEN substring(convert(VARCHAR(255), [FromDate], 120), 11, 6) + ' -' + substring(convert(VARCHAR(255), [ToDate], 120), 11, 6)
        END as 'At Sea: Non-Watchkeeping duties (from...to)'    
    ,   CASE
            WHEN [SeaPortInd] = 0 AND [WatchKeepingInd] = 1 THEN substring(convert(VARCHAR(255), [FromDate], 120), 11, 6) + ' -' + substring(convert(VARCHAR(255), [ToDate], 120), 11, 6)
        END as 'In Port: Watchkeeping (from...to)'
    ,   CASE
            WHEN [SeaPortInd] = 0 AND [WatchKeepingInd] = 0 THEN substring(convert(VARCHAR(255), [FromDate], 120), 11, 6) + ' -' + substring(convert(VARCHAR(255), [ToDate], 120), 11, 6)
        END as 'In Port: Non-Watchkeeping duties (from...to)'   
  FROM [dbo].[WorkingHoursSchedule]

The results are as follows:

SMM · Accepted Answer · 2016-08-12 15:00:13Z

0

I assumed that each vessel has its own schedule and allowed for multiple vessel schedule retrieved at once. This solution uses a tally table to create the slots.

--original data
declare @WorkingHoursSchedule table (
        Ident           int identity(1,1),
        RankCode        varchar(5),
        SeaPortInd      bit,
        WatchKeepingInd bit,
        EffectiveDate   datetime,
        VesselCode      varchar(5),
        FromDate        datetime,
        ToDate          datetime,
        LastDate        datetime,
        LastUser        varchar(128));

insert @WorkingHoursSchedule values
('C/E',0,0,'1900-01-01 00:00:00.000','031','1900-01-01 05:00:00.000','1900-01-01 07:00:00.000','2016-08-11 12:40:00.000','d.baltas'),
('C/E',0,0,'2016-06-02 00:00:00.000','031','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','1900-01-01 00:00:00.000','d.baltas'),
('C/E',0,1,'2016-06-01 00:00:00.000','031','1900-01-01 01:00:00.000','1900-01-01 02:00:00.000','2016-08-11 17:58:00.000','d.baltas'),
('C/E',0,1,'2016-06-02 00:00:00.000','031','1900-01-01 01:00:00.000','1900-01-01 02:00:00.000','2016-08-10 17:58:00.000','d.baltas'),
('C/E',1,1,'2016-06-01 00:00:00.000','031','1900-01-01 03:00:00.000','1900-01-01 04:00:00.000','2016-08-10 17:58:00.000','d.baltas'),
('MSTR',0,0,'2016-06-02 00:00:00.000','031','1900-01-01 16:00:00.000','1900-01-01 22:00:00.000','2016-08-10 17:58:00.000','d.baltas'),
('MSTR',0,1,'2016-06-01 00:00:00.000','031','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','2016-08-10 17:58:00.000','d.baltas'),
('MSTR',1,0,'2016-06-03 00:00:00.000','031','1900-01-01 08:00:00.000','1900-01-01 14:00:00.000','2016-08-11 15:00:00.000','d.baltas');

--create and populate tally table if you don't already a permanent one - arbitrary 1000 rows for demo...you should figure out if that is enough
declare @Tally table (N int PRIMARY KEY);
insert  @Tally
select  top (1000) row_number() over (order by o1.object_id) from sys.columns o1, sys.columns o2 order by 1;
--select    * from @Tally order by N;

with ranked_slots_cte as (  --ranked slots by vessel/rank for each combination of indicator
        select  *, row_number() over (partition by VesselCode, RankCode, SeaPortInd, WatchKeepingInd order by FromDate) time_slot
        from    @WorkingHoursSchedule
        --where EffectiveDate <= getdate() and (LastDate is null or LastDate > getdate())   --it looks like this might be a good place to check these values
        --where VesselCode = '031'  --I assumed getting schedules for all vessels with grouping, but you could filter here instead.
)
select      max_slots.VesselCode, max_slots.RankCode, slots.N TimeSlot
            , substring(convert(VARCHAR(255), r1.FromDate, 120), 11, 6) + ' -' + substring(convert(VARCHAR(255), r1.ToDate, 120), 11, 6)
            , substring(convert(VARCHAR(255), r2.FromDate, 120), 11, 6) + ' -' + substring(convert(VARCHAR(255), r2.ToDate, 120), 11, 6)
            , substring(convert(VARCHAR(255), r3.FromDate, 120), 11, 6) + ' -' + substring(convert(VARCHAR(255), r3.ToDate, 120), 11, 6)
            , substring(convert(VARCHAR(255), r4.FromDate, 120), 11, 6) + ' -' + substring(convert(VARCHAR(255), r4.ToDate, 120), 11, 6)
from        (--total slots per vessel/rank
            select  VesselCode, RankCode, max(time_slot) max_slot
            from    ranked_slots_cte
            group   by VesselCode, RankCode
            --order by VesselCode, RankCode
            ) max_slots
join        @Tally slots    --create the appropriate number of slots per vessel/rank
            on slots.N <= max_slots.max_slot
--join each of the appropriate indicator combinations by ranked time slot
left join   ranked_slots_cte r1     --At Sea/Watch
            on  r1.VesselCode = max_slots.VesselCode 
            and r1.RankCode = max_slots.RankCode
            and r1.time_slot = slots.N
            and r1.SeaPortInd = 1
            and r1.WatchKeepingInd = 1
left join   ranked_slots_cte r2     --At Sea/Not Watch
            on  r2.VesselCode = max_slots.VesselCode 
            and r2.RankCode = max_slots.RankCode
            and r2.time_slot = slots.N
            and r2.SeaPortInd = 1
            and r2.WatchKeepingInd = 0
left join   ranked_slots_cte r3     --In Port/Watch
            on  r3.VesselCode = max_slots.VesselCode 
            and r3.RankCode = max_slots.RankCode
            and r3.time_slot = slots.N
            and r3.SeaPortInd = 0
            and r3.WatchKeepingInd = 1
left join   ranked_slots_cte r4     --In Port/Not Watch
            on  r4.VesselCode = max_slots.VesselCode 
            and r4.RankCode = max_slots.RankCode
            and r4.time_slot = slots.N
            and r4.SeaPortInd = 0
            and r4.WatchKeepingInd = 0
order by    max_slots.VesselCode, max_slots.RankCode, slots.N;

Output looks like:

031  C/E   1  03:00 - 04:00  NULL           01:00 - 02:00  00:00 - 00:00
031  C/E   2  NULL           NULL           01:00 - 02:00  05:00 - 07:00
031  MSTR  1  NULL           08:00 - 14:00  08:00 - 12:00  16:00 - 22:00

edited Aug 12, 2016 at 15:00

answered Aug 12, 2016 at 14:34

SMM

2,2351 gold badge21 silver badges31 bronze badges

5 Comments

Dimitris Baltas Over a year ago

Thanks for your answer! I saw the answer from @Paweł Dyl and it's more simple for me. But I like your answer too and I'm wondering if you can explain me more the use of slots here? Thanks for your time!

SMM Over a year ago

It looks like from your sample output that the schedule for ship vs land should each be continuous slots of time. Without some way in the parent table of matching which non-watching timeslot goes with which watching timeslot (e.g. a step_num or something) the data looks like it supports timeslots starting at position 1 for each rank within each column. The solution figures out how the maximum timeslots required for each rank across all columns (max_slots subquery). It then joins to a tally table to actually create the correct number of slots. The other joins pull in the data column values.

SMM Over a year ago

Current data in your sample output image for C/E at sea looks like (Watch, Non-Watch, Watch, Non-Watch, Watch, Watch, Watch). If you need to support (Watch, Non-Watch, Watch, Watch, Non-Watch, Watch, Watch) then my solution will not work to handle that nor will Paweł Dyl's either I don't think. You would need to provide a way to specify where the non-watch timeslots line up or do some fancier time-matching.

SMM Over a year ago

Feel free to vote this answer up even if you don't make it the accepted answer :-) Also, if you do have gaps between non-watch like my previous comment wondered, let me know and I can rework this a little bit.

Dimitris Baltas Over a year ago

The Non watchkeeping hours are always a subset of watchkeeping hours. Also there is no standard way to match which non-watching slot goes with watching. Nevertheless, I liked your approach and I learned something new!! Thank you very much!

Collectives™ on Stack Overflow

How to construct query to change the output of an sql table

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related