How to remove Duplicate dates in sql?

Question

Good morning, I need some help for the following problem, I have this code

Which gives me the following result, but I’d like to remove the duplicate dates

From now on, thank you.

Answer 1

Hello, I also see as a problem the name of the database being "Master", is not a good practice, but it is possible.

About the SELECT, the tables Lfu_float and Equip_string are not used but are in the clause FROM. And the tables Tdl_float and Tli_float are not linked by a key

Answer 2

What I observed is that there are no repeated lines, but rather that the type of junction used was a Cartesian product, which gave the impression that there were repeated lines. In addition, in the FROM clause there are tables that are not used in the query, such as Lfu_float and Equip_string.

The tables are in the database master, which is a SQL Server control database. If possible create a specific database to store the tables.

The problem is restricted to carrying out the marriage between a shutdown and the respective shutdown, which are recorded in different tables. There are some ways to resolve what you need.

Here’s a solution:

-- código #1 v2
declare @DATAI1 datetime, @DATAF1 datetime;
set @DATAI1= convert(datetime, '7/8/2017 7:00', 103);
set @DATAF1= convert(datetime, '8/8/2017 6:59:59.997', 103);

with OffOn as (
SELECT TDL.DateAndTime as Deslig,
       (SELECT top (1) TLI.DateAndTime
          from TLI_Float as TLI
          where TLI.DateAndTime > TDL.DateAndTime
                and TLI.Val <> 0
          order by TLI.DateAndTime) as Relig
  from TDL_Float as TDL
  where TDL.DateAndTime between @DATAI1 and @DATAF1
        and TDL.Val <> 0
) 
SELECT Deslig as [Desligado às],
       Relig as [Religado às],
       cast( dateadd(second, datediff(second, Deslig, Relig), 0) as time(0)) as [Tempo parado]
  from OffOn
  order by Deslig;

The above solution assumes that for every recorded shutdown there is always a recorded shutdown and that the shutdown interval never reaches 24 hours (or more).