ajuda em query.... e pensamento lógico

Question

Bom dia,

Venho mais uma vez pedir a vossa ajudar a resolucionar um problema.

Tenho uma bd com algumas tabelas, uma delas "users" contendo os campos relativos ao utilizadores.

Ex: Tabela "Users"

Campos: "Nome", "Departamento", "Password"

Uma outra tabela é a "Fichas", que tambem contém varios campos.

Os utilizadores têm como obrigação de preencher todos os dias um formulário "Fichas".

A minha questão é.

Preciso de saber quais são os utilizadores que não preencheram a ficha todos os dias á 23:59h.

A minha ideia para fazer isto é:

Todos os "users" estão associados a um "Departamento"

Ex com 2 departamentos

Zé - Engenharia
Manuel - Arquitectura
Cristina - Engenharia
Fonseca - Arquitectura

Consigo saber quais são os utilizadores que preencheram a ficha na data actual, e consigo saber quantos users tem o departamento X ou Y, logo faço a diferença para saber quantos users não preencheram a ficha. Até aqui tudo bem, agora precissava era de saber o seu quais são os users que não preencheram a ficha e o seu nome.

Não sei se me consegui explicar muito bem

Obrigado
Tiago

 

 

Answer 1

 

 tem como colocar o script das tabelas ?, vc. tem uma tabela com os dias. pode ser uma procedure ?

 

Abs;

Answer 2

Posso ter scripts e procedures, mas não tenho nenhuma tabela com os dias.

Obrigado

Tiago

Answer 3

sem problemas para criar uma variavel table com as datas e simple segue exempli

 

 

 Declare @DataStart Datetime
 Declare @DataEnd Datetime

 Declare @Dias Table (Data Datetime)

 Select @DataStart = '2006-11-01 23:59:59'
 
 Select @DataEnd = '2006-11-30 23:59:59'

 While @DataStart <= @DataEnd
 Begin
   insert into @Dias (data) Values (@DataStart)

  Select @DataStart = dateAdd(Day,1,@DataStart)
 End

Select * From @Dias

 

Abs;

Answer 4

Esta era tabela iria servir para o que? É que nao estou a perceber :(

Obrigado

Answer 5

serve para colocar no join com a suas outras tabelas para ter todos os dias da semana, assim o que nao tiver ate a hora data, vc. vai identiticar o erro.

 

Answer 6

Esqueci-me de referir que a tabela "Fichas" contém um campo que é DataEntrega, que contém a data de entrega das fichas.

 

Answer 7

Este campo DataEntrega seria o campo que mostra a data diária da entregas destas fichas???

Se sim, segue:

select * from users where nome not in (select nome from fichas where datediff(day, dataEntrega, getdate()) = 0)

 

Espero que tenha te ajudado

Answer 8

isso mesmo, relacione com a variavel table para ver quem nao tem uma fica na data correspondente.

 

Abs;

Answer 9

Vou testar, mas pareçe-me bem

 

 

Answer 10

A query esta a dar erro:.

"Only one expression can ben specified in the select list when the subquery is not introduced with EXISTS."

Obrigado

Answer 11

nao use in use +- assim

 

select fichas,* , Ta.Data from fichas Left Join @tabela Ta on data_da_tabela_fichas = ta.data

 

Abs

Answer 12

então, eu percebi que tinha escrito errado e corrigi meu post.

Atencsaiomente,

Pedro

Answer 13

agora perdi-me

ta aqui a query que me diz os users que já introduziram a ficha:.

SELECT     Users_Fichas.UserName, Users_Fichas.DataFicha, Users_Fichas.Estado, Users_Divisao.Desc_Divisao
FROM         Users_Fichas INNER JOIN
                      Users ON Users_Fichas.UserName = Users.UserName INNER JOIN
                      Users_Divisao ON Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE     (CONVERT(varchar(11), Users_Fichas.DataFicha) = CONVERT(varchar(11), GETDATE())) AND (Users_Divisao.Desc_Divisao = @divisao)

Precisso da query daqueles que ainda não introduziram.

Obrigado

Answer 14

select

         user.*

from

          Users user

left join

            Fichas Users_Fichas

on  

              Users_Fichas.UserName = Users.UserName

where

            nome not in (

                         select

                               UserName

                         from

                             Fichas

                     where 

                           (CONVERT(varchar(11), Users_Fichas.DataFicha) = CONVERT(varchar(11), GETDATE()))

 

creio que nome e userName possuem a mesma informação, só que o nome das colunas são diferentes. Caso você relacione as tabelas usando um código do usuário, só mudar a clausula where e o select dentro do not in

Answer 15

SELECT     Users_Fichas.UserName, Users_Fichas.Estado, Users_Divisao.Desc_Divisao
FROM         Users_Fichas INNER JOIN
                      Users ON Users_Fichas.UserName = Users.UserName INNER JOIN
                      Users_Divisao ON Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE NOT IN (Select UserName From Fichas where (CONVERT(varchar(11), Users_Fichas.DataFicha) = CONVERT(varchar(11), GETDATE())) AND (Users_Divisao.Desc_Divisao = @divisao))

Não sei se é de ser segunda-feira, mas continua com erro.  "Incorrect syntax near the keyword 'IN'"

Answer 16

Você esqueceu de colocar o UserName na clausula where, ficando

ficando assim:

 

SELECT     Users_Fichas.UserName, Users_Fichas.Estado, Users_Divisao.Desc_Divisao
FROM         Users_Fichas RIGHT JOIN
                      Users ON Users_Fichas.UserName = Users.UserName INNER JOIN
                      Users_Divisao ON Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE Users.UserName NOT IN (Select UserName From Fichas where (CONVERT(varchar(11), Users_Fichas.DataFicha) = CONVERT(varchar(11), GETDATE())) AND (Users_Divisao.Desc_Divisao = @divisao))

Answer 17

Não sei se ajuda:

As tabelas

Answer 18

Agora não dá erro, e o result da query é basicamente os registos todos da tabela Users_Fichas.

Há aqui qualquer coisa que está a falhar

Exemplo do result:

USERNAME | ESTADO | Desc_Divisao
         ze          |    aprovado |  Divisão de Engenharia
      manuel     |   aprovado |  Divisão de Arquitectura
        Antonia  |   aprovado | DSNPF

... e na query eu faço

SELECT     Users_Fichas.UserName, Users_Fichas.Estado, Users_Divisao.Desc_Divisao
FROM         Users_Fichas RIGHT JOIN
                      Users ON Users_Fichas.UserName = Users.UserName INNER JOIN
                      Users_Divisao ON Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE Users.UserName NOT IN (Select UserName From Users_Fichas where (CONVERT(varchar(11), Users_Fichas.DataFicha) = CONVERT(varchar(11), GETDATE())) AND (Users_Divisao.Desc_Divisao LIKE @divisao))

o @divisão nesta query era "Divisão de Engenharia"

Obrigado

Answer 19

Veja se agora da certo:

 

SELECT
     Users_Fichas.UserName,
     Users_Fichas.Estado,
     Users_Divisao.Desc_Divisao
FROM
     Users
LEFT JOIN
    Users_Fichas
ON
    Users.UserName  = Users_Fichas.UserName
INNER JOIN
    Users_Divisao
ON
   Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE
   Users.UserName NOT IN (
      Select
           UserName
      From
           Users_Fichas
     where
          datediff(day, DataFicha, getdate()) = 0 AND
          (Users_Divisao.Desc_Divisao LIKE @divisao))

Answer 20

Tá na mesma, o result é igual.

Pus em cima a estrutura das tabelas e nao apareçem

Vai aqui em link:. http://img300.imageshack.us/img300/1071/dbdc1.th.jpg

link actualizado: http://img300.imageshack.us/img300/1071/dbdc1.jpg

Answer 21

Veja se agora da certo:

 

SELECT
     Users.UserName,
     Users.Estado,
     Users.Desc_Divisao
FROM
     Users
LEFT JOIN
    Users_Fichas
ON
    Users_Fichas.UserName  = Users.UserName
INNER JOIN
    Users_Divisao
ON
   Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE
   Users.UserName NOT IN (
      Select
           UserName
      From
           Users_Fichas
     where
          datediff(day, DataFicha, getdate()) = 0 AND
          (Users_Divisao.Desc_Divisao LIKE @divisao))

 

Espero que funcione....

e me desculpe pelo script errado, tinha um erro bobo que eu não percebi...

Answer 22

Continua na mesma.... buáaaaaaaaaaaaaaaaaaaaaaaaa

Abraços

Answer 23

bem, continuo bloqueado nesta situação, conheçem mais alguma alternativa para validar se o utilizador preencheu uma tabela?

Obrigado a todos.

Answer 24

cara veja se isso ajuda

 

/* Cria uma tabela de exemplo simulando seu problema

 Create Table #Fichas (Codigo int, Data Datetime)

 Insert into #fichas (codigo, Data) Values (1,'2006-01-01 10:00')
 Insert into #fichas (codigo, Data) Values (1,'2006-01-02 13:00')
 Insert into #fichas (codigo, Data) Values (1,'2006-01-04 11:00')
*/

-- criar uma variavel table para fazer o join

 Declare @Start Datetime
 Declare @End  Datetime
 Declare @Join Table (DateStart DateTime, DateEnd DateTime )
 Select @Start = '2006-01-01' , @End   = Dateadd(Month,1,@Start) -- imagine que vc. vai analisar o mes de jan inteiro.
 
 While @Start <= @End
 Begin
  Insert into @Join (DateStart, DateEnd) Values (@Start, DateAdd(n,-1,DateAdd(Day,1,@Start)))

  Select @Start = DateAdd(Day,1,@Start)
 End

 Select Grade.DateStart, #Fichas.Codigo, #Fichas.Data, Analise = Case when #Fichas.Data Is null Then  'Não Preencheu' Else 'Ok' End
 From @Join As Grade Left Join #Fichas On #Fichas.Data Between Grade.DateStart And Grade.DateEnd
 

 

Abs;

Answer 25

já funciona

Aqui fica a query:

SELECT     Users_Divisao.Desc_Divisao AS Divisao, Users.UserName
FROM         Users INNER JOIN
                      Users_Divisao ON Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE     (Users_Divisao.Desc_Divisao = @divisao) AND (Users.UserName NOT IN
                          (SELECT     UserName
                            FROM          Users_Fichas
                            WHERE      datediff(day, DataFicha, getdate()) = 0))

 

Obrigado a todos mais uma vez

Answer 26

Viva,

Agora necessito de fazer a query mas em vez de ser em relação á data actual (ver quem nao inseriu registo no próprio dia) ser nos últimos 7 dias.

Para isso alterei a query:

SELECT     Users_Divisao.Desc_Divisao AS Divisao, Users.UserName
FROM         Users INNER JOIN
                      Users_Divisao ON Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE     (Users_Divisao.Desc_Divisao = @divisao) AND (Users.UserName NOT IN
                          (SELECT     UserName
                            FROM          Users_Fichas
                            WHERE      DataFicha  >= DATEADD(d,-7,GETDATE())))

 

A query nao me devolve nenhum resultado.

O que estou a fazer mal

Obrigado a todos.

Answer 27

Veja se ajuda:

 

SELECT     Users_Divisao.Desc_Divisao AS Divisao, Users.UserName
FROM         Users INNER JOIN
                      Users_Divisao ON Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE     (Users_Divisao.Desc_Divisao = @divisao) AND (Users.UserName NOT IN
                          (SELECT     UserName
                            FROM          Users_Fichas
                            WHERE      datediff(day, DataFicha, getdate()) <= 7))

Answer 28

Viva Pedro,

Se fizer só esta query,

SELECT     UserName
                            FROM          Users_Fichas
                            WHERE      datediff(day, DataFicha, getdate()) <= 7))

funciona bem, mas eu necessito de ter o resto,

SELECT     Users_Divisao.Desc_Divisao AS Divisao, Users.UserName
FROM         Users INNER JOIN
                      Users_Divisao ON Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE     (Users_Divisao.Desc_Divisao = @divisao) AND (Users.UserName NOT IN
                          (SELECT     UserName
                            FROM          Users_Fichas
                            WHERE      datediff(day, DataFicha, getdate()) <= 7))

Acho que o problema deve acima do segundo select.

Obrigado

Answer 29

Viva,

Estou a precissar novamente da vossa ajuda, neste momento precisso de fazer basicamente o mesmo mas em relação ao mês actual.

Query em relação ao dia actual

SELECT     Users_Divisao.Desc_Divisao AS Divisao, Users.UserName, Users.Nome, Users.Cod_Categoria
FROM         Users INNER JOIN
                      Users_Divisao ON Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE     (Users_Divisao.Desc_Divisao = @divisao) AND (Users.UserName NOT IN
                          (SELECT     UserName
                            FROM          Users_Fichas
                            WHERE      datediff(day, DataFicha, getdate()) = 0)) AND (Users.Cod_Categoria = 1)

Agora em vez de comparar a data das fichas á data actual precisso de comparar a data das fichas a todos os dias (menos fim de semana) do mês actual.

Como posso fazer isto?

Obrigado ;)

Answer 30

SELECT     Users_Divisao.Desc_Divisao AS Divisao, Users.UserName, Users.Nome, Users.Cod_Categoria
FROM         Users INNER JOIN
                      Users_Divisao ON Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE    
 (Users_Divisao.Desc_Divisao = @divisao) AND
 
 (
  Users.UserName NOT IN
                        (
    SELECT     UserName
                             FROM          Users_Fichas
                             WHERE
     (datediff(month, DataFicha, getdate()) = 0) AND -- para que seja do mes atual
     (datepart(dw, DataFicha) <> 6)   AND -- para que nao pegue sabados
     (datepart(dw, DataFicha <> 7) -- para que nao pegue domingos
   )
 ) AND
 (Users.Cod_Categoria = 1)

Answer 31

viva, estou de volta com um dúvida,

 

na query acima (obrigado Pedro A. G. Carvalho) existe um problema no resultado aquando a utilização da [(datediff(month, DataFicha, getdate()) = 0 AND...]

 

O problema é que á query só me devolve os users que não preencheram nenhuma ficha no mes corrente, e não num determinado dia.

 

Está complicado para resolver isto

 

Se alguem tiver ideias

 

Obrigado

Tiago

Answer 32

 

Olá Tiago,

 

Tente mudar de "month" para Day, dentro do DATEDIFF.

 

 

Abraço

Answer 33

 

Viva Alexandre obrigado pela resposta,

 

O problema é que eu quero saber quais os users que não entregaram a ficha do mes currente. Se puser "day" dentro do DATEDIFF ele só me vai comparar as datas das fichas com o data actual (dd,mm,yyyy)

 

Abraço

Tiago

Answer 34

 

Tiago,

 

Desculpa, mas não entendi sua necessidade.

 

Sua questão é esta:

"O problema é que á query só me devolve os users que não preencheram nenhuma ficha no mes corrente, e não num determinado dia."

 

Você quer todos que não preencheram ficha durante somente o mês corrente, é isso? Não entendi sua última colocação "e não num determinado dia".

 

Explique um pouquinho melhor para que eu possa entender.

 

 

Abraço

Answer 35

Desculpa Alexandre, tens toda a razão

 

Enganei-me a formular a pergunta. O correcto é: Precisso de saber quais são os users que não preencheram a ficha durante o mês corrente. Não liguem ao "determinado dia".

 

Obrigado Alexandre,

Abraço

Tiago

 

Answer 36

 

Tiago,

 

Se você executar apenas esta parte:

 

Code Snippet

SELECT     UserName
                             FROM          Users_Fichas
                             WHERE
     (datediff(month, DataFicha, getdate()) = 0) AND -- para que seja do mes atual
     (datepart(dw, DataFicha) <> 6)   AND -- para que nao pegue sabados
     (datepart(dw, DataFicha <> 7) -- para que nao pegue domingos

 

 

Irá retornar todos usuários que preencheram a ficha durante o mês corrente. Mas, antes deste é feito um NOT IN, o que significa que irá pegar todos que NÃO preencheram naquele mês.

 

Pra mim, a query está completamente certa.

 

O que está acontecendo de errado no resultado que você está obtendo??

 

 

Abraço,

 

Alexandre

 

 

Answer 37

Viva Alexandre,

 

Vamos por partes,

 

Code Snippet

SELECT     Users_Divisao.Desc_Divisao AS Divisao, Users.UserName, Users.Nome, Users.Cod_Categoria
FROM         Users INNER JOIN
                      Users_Divisao ON Users.Cod_Divisao = Users_Divisao.Cod_Divisao
WHERE    
 (Users_Divisao.Desc_Divisao = 'Unidade Operacional de Normalização')

 

Aqui básicamente ele me devolve todos os users de um determinada divisão.

 

Code Snippet

 

AND
 
 (
  Users.UserName NOT IN
                        (
    SELECT     UserName
                             FROM          Users_Fichas
                             WHERE
     (datediff(month, DataFicha, getdate()) = 0) AND -- para que seja do mes atual
     (datepart(dw, DataFicha) <> 6)   AND -- para que nao pegue sabados
     (datepart(dw, DataFicha <> 7) -- para que nao pegue domingos
   )
 ) AND
 (Users.Cod_Categoria = 1)

 

Na segunda parte da query ele devolve todos os users que não preecheram a ficha do mês actual, o problema é que como a ficha é preenchida diáriamente, é quase impossivel de um user num mês inteiro não ter nenhuma ficha inserida, logo basta um user ter inserido somente uma ficha no respectivo mês para não apareçer no resultado da query. O que eu quero que a query me devolva é quais os dias (dd,mm,yyyy) em que ele não preencheu a ficha.

 

Imaginemos o seguinte:

 

Tabela Users_Fichas

____________________________

  |  Username   |    DataFicha    |

       Manuel          05/07/2007

        Tiago           01/07/2007

      Alexandre       06/07/2007

       Pascoal        10/07/2007

-------------------------------------------------

 

Tenho este users que preencheram as fichas nas datas que estão no campo "DataFicha"

 

A query teria de me devolver todos os dias do mês 07, Junho, menos os sabádos e domingos, e menos as fichas das datas que já foram inseridas, que é o caso dos dias 1,5,6 e 10 de Junho.

 

Acho que agora já me consegui perceber.

 

Abraço e obrigado

Tiago

 

 

 

 

 

 

 

Answer 38

Tive uma outra ideia,

 

E se eu fizer isto,

 

Code Snippet

DECLARE @RepMonth as datetime
SET @RepMonth = '2007-07-01'
;WITH DayList (DayDate) AS
(
SELECT @RepMonth
UNION ALL
SELECT DATEADD(d, 1, DayDate)
FROM DayList
WHERE (DayDate < DATEADD(d, -1, DATEADD(m, 1, @RepMonth)))
)
SELECT *
FROM DayList

 

Retiro todos os dias do mês que quero a partir do @RepMonth

 

Agora só tenho de comparar os dias do mês @RepMonth com a tabela Users_Fichas para me devolver as fichas não entregues.

 

O que acham?

 

Abraço

Tiago