List Missing Period Days in SQL Server

Asked

Viewed 499 times

1

How would it be possible to do with what in SQL Server when confronting a table that stores the status records of a given code with a virtual table of the period (Calendar of days) it brings me all the codes that are missing on those given days

For example my table keeps only valid records

DATA - COD - STATUS
01/07/2018 - 123456 - Ok
01/07/2018 - 987654 - Ok
02/07/2018 - 123456 - Ok
03/07/2018 - 123456 - Ok
04/07/2018 - 987654 - OK
05/07/2018 - 987654 - OK
06/07/2018 - 123456 - Ok
08/07/2018 - 987654 - OK
09/07/2018 - 123456 - Ok
09/07/2018 - 987654 - Ok
10/07/2018 - 123456 - Ok

It would be possible to get the following result by Select where bring me the days that are missing for example?

DATA - COD - STATUS
01/07/2018 - 123456 - Ok
01/07/2018 - 987654 - Ok
02/07/2018 - 123456 - Ok
02/07/2018 - 987654 - Fail
03/07/2018 - 123456 - Ok
03/07/2018 - 987654 - Fail
04/07/2018 - 123456 - Fail
04/07/2018 - 987654 - OK
05/07/2018 - 123456 - Fail
05/07/2018 - 987654 - OK
06/07/2018 - 123456 - Ok
06/07/2018 - 987654 - Fail
07/07/2018 - 123456 - Fail
07/07/2018 - 987654 - Fail
08/07/2018 - 123456 - Fail
08/07/2018 - 987654 - OK
09/07/2018 - 123456 - Ok
09/07/2018 - 987654 - Ok
10/07/2018 - 123456 - Ok
10/07/2018 - 987654 - Fail

Complement - 30/07/2018

SELECT B.COD, B.DATA, B.[E] ENTRADA, B.[S] SAIDA
  FROM (SELECT P.COD, P.DATA, P.TPMARCA, P.HORA
          FROM PREG P
         WHERE P.DATA BETWEEN '20180701' AND '20180715'
           AND P.COD = '003100'
           AND P.TPMC <> 'D'
           AND P.REP <> ' '
       ) A
 PIVOT (SUM(A.HORA)
   FOR A.TPMARCA IN ([E], [S])) AS B

This is my SELECT and this is the result of it

COD DATA    ENTRADA SAIDA
003100  20180701    6,53    19,13
003100  20180702    7,18    18,03
003100  20180706    6,54    19,06
003100  20180707    6,34    13,08
003100  20180708    7,18    NULL
003100  20180709    NULL    18,9
003100  20180712    6,48    18,43
003100  20180714    7,02    18,11

But the result I need is 

COD DATA    ENTRADA SAIDA
003100  20180701    6,53    19,13
003100  20180702    7,18    18,03
003100  20180705    NULL    NULL
003100  20180706    6,54    19,06
003100  20180707    6,34    13,08
003100  20180708    7,18    NULL
003100  20180709    NULL    18,9
003100  20180710    NULL    NULL
003100  20180711    NULL    NULL
003100  20180712    6,48    18,43
003100  20180713    NULL    NULL
003100  20180714    7,02    18,11
003100  20180715    NULL    NULL

I mean, I need you to bring back the days that don’t exist

sql sql-server
  • 1

    Is that so? https://answall.com/questions/62925/not-in-ou-not-exists-qual-use

1 answer

2

Your example is unclear. We should consider only the Cods of the listed period?

Independent of the answer to the above question:

Yes, it is possible to generate a continuous list of dates virtually (without even creating a temporary table) like this:

Select data from(
SELECT TOP (365) DATEADD(day, ROW_NUMBER() OVER (ORDER BY number), '2018/01/01') as data FROM [master]..spt_values) datas
where data between '2018-07-01' and '2018-07-31'

see working on Sqlfiddle.

The problem is knowing which Cods you want.

I created an example extly with your data sample and called the ACTIVITY table.

We can assume that the entire COD sample is in your example, so the query below is valid:

select aux2.data, aux1.cod from
(select distinct cod from atividade) aux1
cross join
(Select data from(
SELECT TOP (365) DATEADD(day, ROW_NUMBER() OVER (ORDER BY number), '2018/01/01') as data FROM [master]..spt_values) datas
where data between '2018-07-01' and '2018-07-31') aux2

see on Sqlfiddle

This creates a result with all possible dates and Cods.

Now just cross-reference the information using the left join

select distinct aux3.data, aux3.cod, coalesce(atividade.status,'fail') as status
FROM
(select aux2.data, aux1.cod from
  (select distinct cod from atividade) aux1
   cross join
  (Select data from(
       SELECT TOP (365) DATEADD(day, ROW_NUMBER() OVER (ORDER BY number), '2018/01/01') as data FROM [master]..spt_values) datas
   where data between '2018-07-01' and '2018-07-31') aux2) aux3
left join atividade on atividade.data=aux3.data and atividade.cod=aux3.cod
 order by aux3.data, aux3.cod

Once again, here’s the Sqlfiddle

Note:

If there are Cods that should appear, but are not in your example, simply subsitituite

select distinct cod from atividade

for

select cod from CODs  -- lista de CODs válidos para o período.

  • I understood what you put, I will leave a more concrete example of what I really need

  • Will always be a single COD, ex. P.COD = '003100', and always a fixed date range?

  • can contain multiple codes and goes without always in an interval, for example the last 15 days or 7 days

  • We need you to give us an example of what you want to do. The idea will always be the same, to make a Join with a sequential set of dates. What will change is the determination of the set of Cods. If there are multiple Cods the query will be different from a single COD.

  • The Example is well defined, I only put a code so it doesn’t get too long, I just need SELECT to also fill the days that don’t exist in this table, bringing somehow these non-existent records.

  • This way it gets complicated. At every moment your need changes. Here is a more or less genesis fiddle for the core of your query. It will bring all Cods and PREG table dates plus the missing dates for each COD. I can’t provide your complete query because I don’t have your tables. If you want something complete, provide a fiddle with a minimum achievable example of what you want to do. Make sure this is it: http://sqlfiddle.com/#! 18/a4505/5

  • The need has not changed, I changed the examples to try to facilitate an understanding, because the need is that SELECT also bring the days that do not exist.

  • Analyze the last fiddle. Make sure this is what you want. Otherwise, provide a fiddle with a testable data model.

Show 3 more comments

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.