none
Datos repetidos RRS feed

  • Pregunta

  • Hola que tal? Espero que puedan ayudarme, les comento; tengo la siguiente consulta, pero me devuelve todos los registros repetidos, descubrí que el problema está cuando hago el INNER JOIN con tbsysCountry y tbsysState pero logro darme cuenta porque debido a eso repite toda la info (Cada linea se repite por todas las paises y a su vez por todas las provincias de esos países)

    Muchas gracias desde ya.

    Saludos!!!

    DECLARE @datfromdate AS DATE
    DECLARE @dattodate AS DATE
    DECLARE @intsm_id AS INT
    DECLARE @intCat_ID AS INT
    DECLARE @intSubCat_ID AS INT

    SET @datfromdate = '2016/07/01'
    SET @dattodate = '2016/07/31'
    SET @intsm_id = -1
    SET @intCat_ID = -1
    SET @intSubCat_ID = -1

    SELECT tct.ct_date AS 'Fecha', 
    tct.ct_kindOf+' -'+ STR(tct.ct_pointOfSale)+'-'+ tct.ct_docNumber AS 'Nº Comprobante',
    tck.ck_name AS 'Clase de cliente',
    tscy.country_desc AS 'País', 
    tss.state_desc AS 'Provincia', 
    tc.cust_city AS 'Localidad', 
    tc.cust_id AS 'Nº Cliente', 
    tc.cust_name AS 'Cliente', 
    tck.ck_name AS 'Clase de cliente',
    tc.cust_email AS 'Mail cliente',
    ti.item_code AS 'Codigo', 
    ti.item_desc AS 'Articulo', 
    --tb.brand_desc AS 'Marca', 
    tcat.cat_desc AS 'Categoría',
    --tsc.subcat_desc AS 'Subcategoría',
    --CAST(tit.it_qty AS NUMERIC) AS 'Cantidad', 
    CAST(tit.it_price AS DECIMAL(19, 2)) AS 'P.Vta ($) S/Imp', 
    --tit.it_priceOfCost AS 'Costo unitario (Sin I.V.A.)',
    --ts.sm_name AS 'Vendedor'
    tst.st_desc AS 'Forma de pago', 
    dbo.fnGetAStock (1, 33, ti.item_id,999999999) AS 'Stock web disponible',
    dbo.fnGetAStock (1, 1, ti.item_id,999999999) AS 'Stock canal disponible'/*,
    tck.ck_name AS 'Clase de cliente'*/
    FROM tbCommercialTransactions AS tct
    INNER JOIN tbItemTransactions AS tit ON tct.ct_transaction = tit.ct_transaction 
    INNER JOIN tbItem AS ti ON tit.item_id = ti.item_id
    INNER JOIN tbCustomer AS tc ON tct.cust_id = tc.cust_id
    INNER JOIN tbsysCountry AS tscy ON tc.country_id = tscy.country_id
    INNER JOIN tbsysState AS tss ON tscy.country_id = tss.country_id 
    INNER JOIN tbBrand AS tb ON ti.brand_id = tb.brand_id 
    INNER JOIN tbCategory AS tcat ON tcat.cat_id = ti.cat_id 
    INNER JOIN tbSubCategory AS tsc ON tsc.subcat_id = ti.subcat_id 
    INNER JOIN tbSalesman AS ts ON ts.sm_id = tct.sm_id
    INNER JOIN tbSalesTerms AS tst ON tct.st_id = tst.st_id
    INNER JOIN tbCustomerKind AS tck  ON tc.ck_id = tck.ck_id
    WHERE   (tc.ck_id = 21 OR tc.ck_id = 22 OR tc.ck_id = 26 OR tc.ck_id = 23 OR tc.ck_id = 16 OR tc.ck_id = 34) AND (((tct.ct_kindOf IN ('A', 'B', 'E')) AND
    (tct.sd_id = 1) AND 
    (@intSm_ID=-1) AND 
    (@intCat_ID=-1) AND
    (@intSubCat_ID=-1)AND
    (tct.ct_date>= @datfromdate) AND 
    (tct.ct_date <=@dattodate))
    OR
    ((tct.ct_kindOf IN ('A', 'B', 'E')) AND 
    (tct.sd_id = 1) AND 
    (tct.ct_date>= @datfromdate) AND 
    (tct.ct_date<= @dattodate) AND 
    (tct.sm_id = @intSm_ID) AND 
    (ti.cat_id=@intCat_ID) AND 
    (ti.subcat_id=@intSubCat_ID))
    OR
    ((tct.ct_kindOf IN ('A', 'B', 'E')) AND 
    (tct.sd_id = 1) AND 
    (tct.ct_date>= @datfromdate) AND 
    (tct.ct_date<= @dattodate) AND 
    (@intSm_ID=-1) AND 
    (ti.cat_id=@intCat_ID) AND 
    (ti.subcat_id=@intSubCat_ID))
    OR
    ((tct.ct_kindOf IN ('A', 'B', 'E')) AND 
    (tct.sd_id = 1) AND 
    (tct.ct_date>= @datfromdate) AND 
    (tct.ct_date<= @dattodate) AND 
    (@intSm_ID=-1) AND 
    (ti.cat_id=@intCat_ID) AND 
    (@intSubCat_ID=-1))
    OR
    ((tct.ct_kindOf IN ('A', 'B', 'E')) AND 
    (tct.sd_id = 1) AND 
    (tct.ct_date>= @datfromdate) AND 
    (tct.ct_date<= @dattodate) AND 
    (tct.sm_id = @intSm_ID) AND 
    (@intCat_ID=-1) AND 
    (@intSubCat_ID=-1))
    OR
    ((tct.ct_kindOf IN ('A', 'B', 'E')) AND 
    (tct.sd_id = 1) AND 
    (tct.ct_date>= @datfromdate) AND 
    (tct.ct_date<= @dattodate) AND 
    (tct.sm_id = @intSm_ID) AND 
    (ti.cat_id=@intCat_ID) AND 
    (@intSubCat_ID=-1)))
    ORDER BY tct.ct_date


    miércoles, 22 de febrero de 2017 18:22

Respuestas

Todas las respuestas