vendredi 12 août 2016

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

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:

enter image description here

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