Buen dia chicos del foro.
Quisiera una ayuda de ustedes expertos, ya que tengo un query que me esta repitiendo una consulta de facturación, incluso puse el distinct y el group by, no me resulta positivo. Solo si pongo el distinct solo, pero el detalle es que quiero ordenarlo mediante la linea
SELECT distinct T3.LineNum, T0.DocEntry, T0.CardCode, T0.Comments, T5.StreetB + ' ' + ISNULL(T5.StreetNoB, '') AS Address, 'D' AS Detalle, T0.WTSum, T0.DocTotalFC, CASE WHEN T3.[Currency] = '' THEN t0.DocCur ELSE T3.[Currency] END AS Currency,
T3.Rate, CASE WHEN T0.[DocCur] = 'USD' THEN T0.DocTotalFC ELSE T0.[DocTotal] END AS DocTotal, CASE WHEN T0.[DocCur] = 'USD' THEN T3.TotalFrgn ELSE T3.[LineTotal] END AS LineTotal,
CASE WHEN T0.[DocCur] = 'USD' THEN T3.TotalFrgn ELSE T3.[LineTotal] END AS Expr1, UPPER(T4.PymntGroup) AS CondPago, CASE WHEN T3.[Currency] = '' THEN t0.DocCur ELSE T3.[Currency] END AS Expr2,
UPPER(ISNULL
((SELECT Name
FROM SBOSIP_ASC2.dbo.OCST AS T30
WHERE (T5.StateB = Code)), '')) AS State1, T0.CardName, T2.LicTradNum, T0.PeyMethod, T3.Quantity, T3.unitMsr, T3.Dscription, T3.U_Unidad, T3.Price, T3.TotalFrgn,
CASE WHEN T0.[DocCur] = 'USD' THEN T0.VatSumFC ELSE T0.[VatSum] END AS VatSum, T0.DocNum, T0.DocCur, T0.DocDueDate, T4.PymntGroup, T0.DocDate, LEN(T0.DocTime) AS long,
' ' + CASE WHEN len(T0.[DocTime]) = 3 THEN '0' + (LEFT(CONVERT(varchar(4), T0.[DocTime]), CASE WHEN (len(T0.[DocTime])) = 3 THEN 1 ELSE 2 END)) ELSE (LEFT(CONVERT(varchar(4), T0.[DocTime]),
CASE WHEN (len(T0.[DocTime])) = 3 THEN 1 ELSE 2 END)) END + ':' + RIGHT(CONVERT(varchar(4), T0.DocTime), 2) + ':00' AS Tiempo,
CASE WHEN T0.U_B1SYS_MainUsage = 'G01' THEN 'G01-Adquisición de mercancias' WHEN t0.U_B1SYS_MainUsage = 'G02' THEN 'G02-Devoluciones, descuentos o bonificaciones' WHEN t0.U_B1SYS_MainUsage
= 'G03' THEN 'G03-Gastos en general' WHEN t0.U_B1SYS_MainUsage = 'I01' THEN 'I01-Construcciones' WHEN t0.U_B1SYS_MainUsage = 'I02' THEN 'I02-Mobilario y equipo de oficina por inversiones' WHEN t0.U_B1SYS_MainUsage
= 'I03' THEN 'I03-Equipo de transporte' WHEN t0.U_B1SYS_MainUsage = 'I04' THEN 'I04-Equipo de computo y accesorios' WHEN t0.U_B1SYS_MainUsage = 'I05' THEN 'I05-Dados, troqueles, moldes, matrices y herramental'
WHEN t0.U_B1SYS_MainUsage = 'I06' THEN 'I06-Comunicaciones telefónicas' WHEN t0.U_B1SYS_MainUsage = 'I07' THEN 'I07-Comunicaciones satelitales' WHEN t0.U_B1SYS_MainUsage = 'I08' THEN 'I08-Otra maquinaria y equipo'
WHEN t0.U_B1SYS_MainUsage = 'D01' THEN 'D01-Honorarios médicos, dentales y gastos hospitalarios' WHEN t0.U_B1SYS_MainUsage = 'D02' THEN 'D02-Gastos médicos por incapacidad o discapacidad' WHEN
t0.U_B1SYS_MainUsage = 'D03' THEN 'D03-Gastos funerales' WHEN t0.U_B1SYS_MainUsage = 'D04' THEN 'D04-Donativos' WHEN t0.U_B1SYS_MainUsage = 'D05' THEN 'D05-Intereses reales efectivamente pagados por créditos hipotecarios (casa habitación)'
WHEN T0.U_B1SYS_MainUsage = 'D06' THEN 'D06-Aportaciones voluntarias al SAR' WHEN t0.U_B1SYS_MainUsage = 'D07' THEN 'D07-Primas por seguros de gastos médicos' WHEN T0.U_B1SYS_MainUsage
= 'D08' THEN 'D08-Gastos de transportación escolar obligatoria' WHEN t0.U_B1SYS_MainUsage = 'D09' THEN 'D09-Depósitos en cuentas para el ahorro, primas que tengan como base planes de pensiones' WHEN
T0.U_B1SYS_MainUsage = 'D10' THEN 'D10-Pagos por servicios educativos (colegiaturas)' WHEN t0.U_B1SYS_MainUsage = 'P01' THEN 'P01-Por definir' WHEN T0.U_B1SYS_MainUsage = '-9' THEN '-9-Definir de nuevo'
END AS UsoCFDI, T3.U_Referencia, T5.ZipCodeB AS ZipCode,
CASE WHEN T0.[U_ASC_METPAG] = 'PPD' THEN 'PPD - Pago Por Parcialidades' WHEN T0.[U_ASC_METPAG] = 'PUE' THEN 'PUE - Pago en una sola exhibición' END AS MetodoPago, T5.StreetNoB, T5.StreetNoS,
ISNULL(T5.BlockB, '') AS Block, T5.CityB AS City, T6.[Folio], T6.[FechaTimbrado], T6.[NoCertificadoSAT], T6.[SelloCFD], T6.[SelloSAT], T6.[UUID], T6.[Version], T6.[UsoCFDI], T6.[Rfc]
FROM SBOSIP_ASC2.dbo.OINV AS T0 INNER JOIN
SBOSIP_ASC2.dbo.OCPR AS T1 ON T0.CntctCode = T1.CntctCode INNER JOIN
SBOSIP_ASC2.dbo.OCRD AS T2 ON T0.CardCode = T2.CardCode INNER JOIN
SBOSIP_ASC2.dbo.INV1 AS T3 ON T0.DocEntry = T3.DocEntry INNER JOIN
SBOSIP_ASC2.dbo.OCTG AS T4 ON T0.GroupNum = T4.GroupNum INNER JOIN
SBOSIP_ASC2.dbo.INV12 AS T5 ON T0.DocEntry = T5.DocEntry INNER JOIN
SBOSIP_ASC2.dbo.SBO_SIP_Facturas AS T6 ON T0.Docnum = T6.Folio
where T0.Docentry=23074 group by T3.LineNum,T0.Docentry, T0.Comments, T0.CardCode, T5.StreetB,
T0.DocTotalFC, T0.WTSum, T5.StreetNoB,T3.[Currency], t0.DocCur, T3.Rate, T0.[DocTotal], T3.TotalFrgn,
T3.[LineTotal], T4.PymntGroup, T0.CardName, T5.StateB, T0.PeyMethod, T2.LicTradNum, T3.Quantity, T3.unitMsr,
T3.Dscription, T3.U_Unidad, T3.Price, T0.VatSumFC, T0.[VatSum],T0.DocNum, T0.DocCur, T0.DocDueDate, T4.PymntGroup,
T0.DocDate,T0.DocTime,t0.U_B1SYS_MainUsage, T0.U_B1SYS_MainUsage, T3.U_Referencia, T5.ZipCodeB,T0.[U_ASC_METPAG],
T5.StreetNoS,T5.BlockB, T5.CityB,T6.[Folio], T6.[FechaTimbrado], T6.[NoCertificadoSAT], T6.[SelloCFD], T6.[SelloSAT],
T6.[UUID], T6.[Version], T6.[UsoCFDI], T6.[Rfc],T3.LineNum,State1,T0.CntctCode, T1.CntctCode, T0.CardCode, T2.CardCode,
T0.DocEntry, T3.DocEntry, T0.GroupNum, T4.GroupNum, T0.DocEntry, T5.DocEntry, T0.Docnum, T6.Folio,T3.LineNum
order by T3.LineNum
En teoría solo debería mostrar las líneas 0,1,2