List Missing Period Days in SQL Server

Question

List Missing Period Days in SQL Server

Navigation

#1 by (2 votes)

1

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

For example, my table stores only the 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

Would it be possible to get the following result by Select where you 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

Add-on - 7/30/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

That is, I need to bring in the days that there are no records

sql sql-server

asked by anonymous 27.07.2018 / 21:15

1 answer

How to perform a query in HQL in C #? Form unconfigures DIV

score 2 · Answer 1

Your example is not clear. Should we consider only the CODs of the listed period?

Regardless of the answer to the above question:

Yes, it is possible to generate a continuous list of dates virtually (without even having to create 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 in SQLFiddle .

The problem is knowing which CODs you want.

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

We can assume that the entire COD sample is in its 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 SQLFiddle

This creates a result with all possible dates and CODs.

Now just cross the information using 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, just subsitituir

select distinct cod from atividade

by

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