0

I'm having problems with logic, to think how can I put this code inside a procedure, as a field.Tried with CTE but failed too.

 SELECT r.cod_rep,r.cod_perito_primeiro,  tbSolicitacoes.datahora_solicitacao
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,'2015-07-01')
                AND R.cod_perito_primeiro in (2749,2751)
                AND R.cod_status in(7)
                and r.datahora_emissao >= convert(date,'2015-07-01')                                

                                            union all

 SELECT r.cod_rep,r.cod_perito_primeiro , tbSolicitacoes.datahora_solicitacao
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,'2015-07-01')
                AND R.cod_perito_primeiro in (2749,2751)
                AND R.cod_status in(2,3)

                                          union all
 SELECT r.cod_rep,r.cod_perito_primeiro , tbSolicitacoes.datahora_solicitacao
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                    join tbFiles on tbfiles.cod_rep=r.cod_rep
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,'2015-07-01')
                AND R.cod_perito_primeiro in (2749,2751)
                AND R.cod_status in(6)
                and tbfiles.file_assinado = 0

it will create something like this:

enter image description here

But, this is for a test purpose, because I was trying to create a temp table inside the procedure like this:

create table #temp ( cod_rep int, cod_perito_primeiro int, datahora_solicitacao datetime)
go
insert into #temp

 SELECT r.cod_rep,r.cod_perito_primeiro,  tbSolicitacoes.datahora_solicitacao
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,'2015-07-01')
                AND R.cod_perito_primeiro in (2749,2751)
                AND R.cod_status in(7)
                and r.datahora_emissao >= convert(date,'2015-07-01')                                

                                            union all

 SELECT r.cod_rep,r.cod_perito_primeiro , tbSolicitacoes.datahora_solicitacao
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,'2015-07-01')
                AND R.cod_perito_primeiro in (2749,2751)
                AND R.cod_status in(2,3)

                                          union all
 SELECT r.cod_rep,r.cod_perito_primeiro , tbSolicitacoes.datahora_solicitacao
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                    join tbFiles on tbfiles.cod_rep=r.cod_rep
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,'2015-07-01')
                AND R.cod_perito_primeiro in (2749,2751)
                AND R.cod_status in(6)
                and tbfiles.file_assinado = 0

select min(datahora_solicitacao)  from #temp
    group by cod_perito_primeiro
drop table #temp

I'm having problems with 2 things:

One: This query will need to run with parameters ( because it's already inside a procedure). the real query needs to be this way:

(SELECT COUNT(distinct r.cod_rep) 
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,@data)
                AND R.cod_perito_primeiro = cod_usuario
                AND R.cod_status in(7)
                and r.datahora_emissao >= convert(date,@data))                                

                                     union all

(SELECT COUNT(distinct r.cod_rep) 
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,@data)
                AND R.cod_perito_primeiro = tbUsuarios.cod_usuario
                AND R.cod_status in(2,3)) 

                                    union all

(SELECT COUNT(distinct r.cod_rep) 
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                    join tbFiles on tbfiles.cod_rep=r.cod_rep
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,@data)
                AND R.cod_perito_primeiro =cod_usuario
                AND R.cod_status in(6)
                and tbfiles.file_assinado = 0) as  'Casos_Pendente

s_Por_Perito',

So, How can I use this 3 union all, inside a procedure, as a single field? The purpose of this, is to select all data from these 3 selects, and grouping by cod_rep, get the minimum value ( min(tbSolicitacoes.datahora_solicitacao))

I tried to create a procedure and call it inside this procedure, but failed.

  • What was the error? – Erik Darling Jul 8 '16 at 23:04
  • SIntax error. But I could manage to fix it. The answer is what I did: – Racer SQL Jul 11 '16 at 10:49
0

After some time thinking, this is what I did. probably not the best ( neither the faster ) solution. PLease I would be glad to learn a better way to do this.

Beggining of the procedure with a lot of selects as fields...
,

 (select min(datahora_solicitacao) from (

       SELECT r.cod_rep,r.cod_perito_primeiro,  tbSolicitacoes.datahora_solicitacao
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                    join tbUsuarios on tbUsuarios.cod_usuario=r.cod_perito_primeiro
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,@data)
                AND R.cod_perito_primeiro = tbUsuarios.cod_usuario
                AND R.cod_status in(7)
                and r.datahora_emissao >= convert(date,@data)                                

                                            union all

 SELECT r.cod_rep,r.cod_perito_primeiro , tbSolicitacoes.datahora_solicitacao
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                    join tbUsuarios on tbUsuarios.cod_usuario=r.cod_perito_primeiro
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,@data)
                AND R.cod_perito_primeiro = tbUsuarios.cod_usuario
                AND R.cod_status in(2,3)

                                          union all
 SELECT r.cod_rep,r.cod_perito_primeiro , tbSolicitacoes.datahora_solicitacao
                FROM tbReps as R
                    join tbSolicitacoes on tbSolicitacoes.cod_rep=r.cod_rep
                    join tbFiles on tbfiles.cod_rep=r.cod_rep
                    join tbUsuarios on tbUsuarios.cod_usuario=r.cod_perito_primeiro
                WHERE
                convert(date,tbSolicitacoes.datahora_solicitacao) <= convert(date,@data)
                AND R.cod_perito_primeiro = tbUsuarios.cod_usuario
                AND R.cod_status in(6)
                and tbfiles.file_assinado = 0


       ) as teste
        where tbUsuarios.cod_usuario=teste.cod_perito_primeiro
           group by cod_perito_primeiro
           ) as 'Data_Caso_Mais_Antigo',

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.