Like bringing in salesmen who made and didn’t make sales yesterday?

Asked

Viewed 634 times

1

In this select I am bringing all employees who accessed the application yesterday PO.DAT_COLETA and who held blindfold yesterday PE.COD_PEDIDO. But I also want to bring those who accessed yesterday and did not make sales, today I do it by PROCV in excel, it is possible to do it by query ?

 SELECT

     FUV.COD_UNIDADE_VENDA
    ,UV.NOM_UNIDADE_VENDA
    ,FUV.COD_FUNCIONARIO
    ,FU.NOM_FUNCIONARIO
    ,PO.DAT_COLETA
    ,PE.COD_PEDIDO

    FROM FUNCIONARIO_UNIDADE_VENDA FUV

    JOIN POSICAO_FUNCIONARIO PO ON FUV.COD_FUNCIONARIO = PO.COD_FUNCIONARIO
    JOIN UNIDADE_VENDA UV ON FUV.COD_UNIDADE_VENDA = UV.COD_UNIDADE_VENDA
    JOIN FUNCIONARIO FU ON FUV.COD_FUNCIONARIO = FU.COD_FUNCIONARIO
    JOIN PEDIDO PE ON FUV.COD_FUNCIONARIO = PE.COD_FUNCIONARIO

    WHERE 
    PO.DAT_COLETA >= CONVERT(CHAR(10), GETDATE()-1, 101) and PO.DAT_COLETA < CONVERT(CHAR(10), GETDATE(), 101) 
AND PE.DAT_ENTREGA >= CONVERT(CHAR(10), GETDATE()-1, 101) and PE.DAT_ENTREGA < CONVERT(CHAR(10), GETDATE(), 101) 

    ORDER BY FUV.COD_UNIDADE_VENDA
sql sql-server

  • A doubt, because you convert your date to char and then compare with another date?

  • I’m just testing, then this will be taken out. In fact this query will have to be rotated 23:59, to bring all the people who accessed and made or did not make sales on the day. It is a retroactive report, do you understand? Because if the person access dat_collecting today is superimposed on the bank and stick the report.

  • @Lucaseduardodasilva (1) If the employee accesses the application several times on the same day, in the POSICAO_FUNCIONARIO table is there a line for each access on the day or only one and only line on the day? // (2) Each day the employee registers a single global sales order or can register multiple sales orders for the same day?

  • @Lucaseduardodasilva (1) How are declared the columns DAT_COLETA and DAT_ENTREGA? (date, datetime, string etc). // (2) If string, what format? (dd/mm/yyyy, mm/dd/yyyy etc)

  • @José Diz Sim, are declared as datetime, and each new collection that the device sends to the bank overlaps the last, that is, there is only one line with the most recent dat_collection ever, so this script will be scheduled to run always 23:59, hugs.

3 answers

3

Yes it is possible, make a UNION the first you already have that are the ones that made sale, already the second do almost same thing, however, no order table.

/* FUNCIONARIOS QUE REALIZARAM VENDA */
SELECT
 FUV.COD_UNIDADE_VENDA
,UV.NOM_UNIDADE_VENDA
,FUV.COD_FUNCIONARIO
,FU.NOM_FUNCIONARIO
,PO.DAT_COLETA
,PE.COD_PEDIDO
FROM FUNCIONARIO_UNIDADE_VENDA FUV
JOIN POSICAO_FUNCIONARIO PO ON FUV.COD_FUNCIONARIO = PO.COD_FUNCIONARIO
JOIN UNIDADE_VENDA UV ON FUV.COD_UNIDADE_VENDA = UV.COD_UNIDADE_VENDA
JOIN FUNCIONARIO FU ON FUV.COD_FUNCIONARIO = FU.COD_FUNCIONARIO
JOIN PEDIDO PE ON FUV.COD_FUNCIONARIO = PE.COD_FUNCIONARIO
WHERE PO.DAT_COLETA >= CONVERT(CHAR(10), GETDATE()-1, 101) and PO.DAT_COLETA    < CONVERT(CHAR(10), GETDATE(), 101) 
AND PE.DAT_ENTREGA >= CONVERT(CHAR(10), GETDATE()-1, 101) and PE.DAT_ENTREGA < CONVERT(CHAR(10), GETDATE(), 101) 
UNION
/* FUNCIONARIOS QUE NÃO REALIZARAM VENDA */
SELECT
 FUV.COD_UNIDADE_VENDA
,UV.NOM_UNIDADE_VENDA
,FUV.COD_FUNCIONARIO
,FU.NOM_FUNCIONARIO
,PO.DAT_COLETA
,0 AS COD_PEDIDO
  FROM FUNCIONARIO_UNIDADE_VENDA FUV
 JOIN POSICAO_FUNCIONARIO PO ON FUV.COD_FUNCIONARIO = PO.COD_FUNCIONARIO
 JOIN UNIDADE_VENDA UV ON FUV.COD_UNIDADE_VENDA = UV.COD_UNIDADE_VENDA
 JOIN FUNCIONARIO FU ON FUV.COD_FUNCIONARIO = FU.COD_FUNCIONARIO
 WHERE PO.DAT_COLETA >= CONVERT(CHAR(10), GETDATE()-1, 101) and  PO.DAT_COLETA < CONVERT(CHAR(10), GETDATE(), 101) 
 ORDER BY 1

It is also possible to do using LEFT JOIN with the table 'REQUEST', so if there is no request, the field of the order number will be null.

  • Ruberlei Cardoso, I believe I can not use a UNION because not everyone who accessed yesterday made sale. But some who accessed yesterday, made more than one sale! this gives difference in the number of records between the two querys making UNION impossible.

  • To do yes, when using UNION you do not need to have the same amount of records but the same amount of fields and that are of the same type.

  • Thank you very much, you solved the case!

  • I noticed that the sellers who made sale it brings everything right, but always brings a line with cod_order = 0 and then brings the lines with the orders effectively, is it possible to eliminate this line with cod_order = 0 only for sellers who had sales? Thank you!

2


This is a suggestion considering the information available.

-- código #1 v3
-- obtém horários de limite do dia 
declare @Data1 datetime, @Data2 datetime;
set @Data1= Cast(Current_timestamp as date);
set @Data2= DateAdd(ms, -3, DateAdd(day, +1, @Data1));

--
SELECT FUV.COD_UNIDADE_VENDA, UV.NOM_UNIDADE_VENDA,
       PO.COD_FUNCIONARIO, FU.NOM_FUNCIONARIO,
       case when exists (SELECT * from PEDIDO as PE 
                         where PE.COD_FUNCIONARIO = PO.COD_FUNCIONARIO
                               and PE.DAT_ENTREGA between @Data1 and @Data2)
            then 'Vendeu' 
            else 'Não vendeu' end as Status
  from POSICAO_FUNCIONARIO as PO
       inner join FUNCIONARIO_UNIDADE_VENDA as FUV on FUV.COD_FUNCIONARIO = PO.COD_FUNCIONARIO
       inner join UNIDADE_VENDA as UV on FUV.COD_UNIDADE_VENDA = UV.COD_UNIDADE_VENDA
       inner join FUNCIONARIO as FU on FU.COD_FUNCIONARIO = PO.COD_FUNCIONARIO
  where PO.DAT_COLETA between @Data1 and @Data2
  order by FUV.COD_UNIDADE_VENDA asc, Status desc, PO.COD_FUNCIONARIO asc;

The suggestion assumes that:

  • several orders can be registered on the same day, for the same official;
  • the columns DAT_COLETA and DAT_ENTREGA are declared as datetime.

  • Thanks for the suggestion! I’ll try tomorrow at work. I still need to develop a variant of this script, which brings both resellers who accessed the application and made and did not make sales, without bringing duplicate lines, because I get this information from the table

  • @Lucaseduardodasilva I made modifications to code #1, considering the additional information you provided. And I corrected a mistake because I’d forgotten end in the case. // I am only in doubt regarding the requests. In the REQUEST table there may be more than one request for the same employee, on the same day?

  • Yes, there can be multiple records in the REQUEST table for the same employee, he can make multiple sales during the day. Thankful!

  • @Lucaseduardodasilva: Ok. So it seems to me that code #1 is in agreement with what you requested, and without bringing repeat lines. I await return.

  • Yes, it worked perfectly, it did not bring repeated lines, it is a variant that I can present, thank you very much for the support ! I have little SQL time. Grateful !!

0

SELECT

 FUV.COD_UNIDADE_VENDA
,UV.NOM_UNIDADE_VENDA
,FUV.COD_FUNCIONARIO
,FU.NOM_FUNCIONARIO
,PO.DAT_COLETA
,PE.COD_PEDIDO

FROM FUNCIONARIO_UNIDADE_VENDA FUV

JOIN POSICAO_FUNCIONARIO PO ON FUV.COD_FUNCIONARIO = PO.COD_FUNCIONARIO
JOIN UNIDADE_VENDA UV ON FUV.COD_UNIDADE_VENDA = UV.COD_UNIDADE_VENDA
JOIN FUNCIONARIO FU ON FUV.COD_FUNCIONARIO = FU.COD_FUNCIONARIO
LEFT JOIN PEDIDO PE ON FUV.COD_FUNCIONARIO = PE.COD_FUNCIONARIO

WHERE 
PO.DAT_COLETA >= CONVERT(CHAR(10), GETDATE()-1, 101) and PO.DAT_COLETA < CONVERT(CHAR(10), GETDATE(), 101) AND PE.DAT_ENTREGA >= CONVERT(CHAR(10), GETDATE()-1, 101) and PE.DAT_ENTREGA < CONVERT(CHAR(10), GETDATE(), 101) 
ORDER BY FUV.COD_UNIDADE_VENDA

Another option is to use LEFT JOIN.

Browser other questions tagged sql sql-server

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