I need some little help here...So, let me introduce you my problem. I have the following SQL 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 use SQL SERVER 2008 but the query will also run at some previous versions sith minimum SQL SERVER 2005
Thanks in advance!!
Aucun commentaire:
Enregistrer un commentaire