Sacar fecha del último pedido de cada cliente
Publicado por José Luis (1 intervención) el 26/01/2024 13:12:51
De esta consulta me gustaría sacar la fecha (DocDate) más reciente de cada cliente (CardCode)
select tbl.*,
(SELECT TOP(1) CAST(Name AS NVARCHAR(100)) FROM OCST WHERE Code=CAST(tbl.State1 AS NVARCHAR(3))) AS NomProv,
CAST((SELECT TOP(1) U_Descripcion
FROM dbo.[@SERCO45_Tipped] WHERE U_Tipo=CAST(Tbl.U_SERCO45_Tipo AS NVARCHAR(3))) AS NVARCHAR(100)) AS TipoPedo
from(
SELECT T0.[CardCode], T0.[CardName], T0.[GroupCode], T1.[GroupName], T0.[Phone1], T0.[Cellular],T0.[E_Mail], T0.[U_SERCO45_borrar], T0.[U_SERCO45_codcomer],
T0.[Address], T0.[City], T0.[ZipCode], T0.[State1], T0.[U_SERCO45_ruta], T3.[U_SERCO45_Tipo], T2.[Name], T0.[County],
(T3.[DocTotal]) doctotal, T3.[DocDate], T3.[DocNum],
T3.[U_SERCO45_Comercial],isnull(T4.[Tel1],'') tel1,T3.[U_GISA_TCO],
T0.State1 as CodProv,T5.[U_CochePropio], T5.U_VisitasConSello as visitas, T5.U_Comercial, T5.U_Nombre, T5.U_Terminal
FROM [dbo].[OCRD] T0
INNER JOIN [dbo].[OCRG] T1 ON T0.GroupCode = T1.GroupCode
LEFT JOIN OCST T2 ON T0.State1 = T2.Code
INNER JOIN ordr T3 ON T0.CardCode = T3.CardCode
LEFT JOIN OCPR T4 ON T0.CardCode = T4.CardCode
INNER JOIN [@SERCO45_COMER] T5 on T3.[U_SERCO45_Comercial]=T5.U_Comercial
WHERE T3.[DocDate] >='01/01/2024' and T3.[DocDate] <='18/01/2024' and (T0.[U_SERCO45_borrar] ='N' or T0.[U_SERCO45_borrar] is null)
AND ((T3.DOCSTATUS='C' AND ISNULL(T3.[U_SERCO45_liquida],0)<>0) OR (T3.DOCSTATUS<>'C' AND ISNULL(T3.[U_SERCO45_liquida],0)=0))
group by T0.[CardCode], T0.[CardName], T0.[GroupCode], T1.[GroupName], T0.[Phone1], T0.[Cellular],T0.[E_Mail], T0.[U_SERCO45_borrar], T0.[U_SERCO45_codcomer],
T0.[Address], T0.[City], T0.[ZipCode], T0.[State1], T0.[U_SERCO45_ruta], T3.[U_SERCO45_Tipo], T2.[Name], T0.[County],
T3.[DocDate], T3.[DocNum], t3.doctotal,
T3.[U_SERCO45_Comercial],isnull(T4.[Tel1],'') ,T3.[U_GISA_TCO],
T0.State1 ,
T5.[U_CochePropio], T5.U_VisitasConSello , T5.U_Comercial, T5.U_Nombre, T5.U_Terminal,
T3.[U_SERCO45_Tipo]
) tbl
ORDER BY tbl.docnum, tbl.[GroupCode], tbl.[ZipCode], tbl.[CardName]
select tbl.*,
(SELECT TOP(1) CAST(Name AS NVARCHAR(100)) FROM OCST WHERE Code=CAST(tbl.State1 AS NVARCHAR(3))) AS NomProv,
CAST((SELECT TOP(1) U_Descripcion
FROM dbo.[@SERCO45_Tipped] WHERE U_Tipo=CAST(Tbl.U_SERCO45_Tipo AS NVARCHAR(3))) AS NVARCHAR(100)) AS TipoPedo
from(
SELECT T0.[CardCode], T0.[CardName], T0.[GroupCode], T1.[GroupName], T0.[Phone1], T0.[Cellular],T0.[E_Mail], T0.[U_SERCO45_borrar], T0.[U_SERCO45_codcomer],
T0.[Address], T0.[City], T0.[ZipCode], T0.[State1], T0.[U_SERCO45_ruta], T3.[U_SERCO45_Tipo], T2.[Name], T0.[County],
(T3.[DocTotal]) doctotal, T3.[DocDate], T3.[DocNum],
T3.[U_SERCO45_Comercial],isnull(T4.[Tel1],'') tel1,T3.[U_GISA_TCO],
T0.State1 as CodProv,T5.[U_CochePropio], T5.U_VisitasConSello as visitas, T5.U_Comercial, T5.U_Nombre, T5.U_Terminal
FROM [dbo].[OCRD] T0
INNER JOIN [dbo].[OCRG] T1 ON T0.GroupCode = T1.GroupCode
LEFT JOIN OCST T2 ON T0.State1 = T2.Code
INNER JOIN ordr T3 ON T0.CardCode = T3.CardCode
LEFT JOIN OCPR T4 ON T0.CardCode = T4.CardCode
INNER JOIN [@SERCO45_COMER] T5 on T3.[U_SERCO45_Comercial]=T5.U_Comercial
WHERE T3.[DocDate] >='01/01/2024' and T3.[DocDate] <='18/01/2024' and (T0.[U_SERCO45_borrar] ='N' or T0.[U_SERCO45_borrar] is null)
AND ((T3.DOCSTATUS='C' AND ISNULL(T3.[U_SERCO45_liquida],0)<>0) OR (T3.DOCSTATUS<>'C' AND ISNULL(T3.[U_SERCO45_liquida],0)=0))
group by T0.[CardCode], T0.[CardName], T0.[GroupCode], T1.[GroupName], T0.[Phone1], T0.[Cellular],T0.[E_Mail], T0.[U_SERCO45_borrar], T0.[U_SERCO45_codcomer],
T0.[Address], T0.[City], T0.[ZipCode], T0.[State1], T0.[U_SERCO45_ruta], T3.[U_SERCO45_Tipo], T2.[Name], T0.[County],
T3.[DocDate], T3.[DocNum], t3.doctotal,
T3.[U_SERCO45_Comercial],isnull(T4.[Tel1],'') ,T3.[U_GISA_TCO],
T0.State1 ,
T5.[U_CochePropio], T5.U_VisitasConSello , T5.U_Comercial, T5.U_Nombre, T5.U_Terminal,
T3.[U_SERCO45_Tipo]
) tbl
ORDER BY tbl.docnum, tbl.[GroupCode], tbl.[ZipCode], tbl.[CardName]
Valora esta pregunta
0