x

retrieve date from a table only if isn't in the second table

this is the current query:

    SELECT
    Tie ,
    Nom ,
    Cli ,
    Prs ,
    Imp ,
    Tot - Imp IVA ,
    Tot ,
    Fec
FROM
    ( SELECT
        Tie ,
        Nom ,
        Cli ,
        SUM(Prs) Prs ,
        SUM(Imp) Imp ,
        SUM(Tot) Tot ,
        Fec
      FROM
        ( SELECT
            mvt_keytie [Tie] ,
            tie_nombre [Nom] ,
            cli_keycli [Cli] ,
            SUM(mvt_cantid) Prs ,
            ROUND(ROUND(( SUM(CASE WHEN mvt_tipmov = 'NC'
                                   THEN ( mvt_cantid * mvt_precio )
                                        + mvt_descto
                                   ELSE ( mvt_cantid * mvt_precio )
                                        - mvt_descto
                              END) / 1.16 ) * prv_porfac, 2) / SUM(mvt_cantid),
                  2) * SUM(mvt_cantid) Imp ,
            ROUND(ROUND(( SUM(CASE WHEN mvt_tipmov = 'NC'
                                   THEN ( mvt_cantid * mvt_precio )
                                        + mvt_descto
                                   ELSE ( mvt_cantid * mvt_precio )
                                        - mvt_descto
                              END) ) * prv_porfac, 2) / SUM(mvt_cantid), 2)
            * SUM(mvt_cantid) Tot ,
            CAST(CONVERT(VARCHAR(11), mvt_fecmov) AS SMALLDATETIME) Fec
          FROM
            pruebas1.dbo.pv_transacciones WITH ( NOLOCK )
          LEFT JOIN pv_productos WITH ( NOLOCK )
          ON
            mvt_keycom = prd_keycom
            AND mvt_keyprd = prd_keyprd
          LEFT JOIN pv_cliente_tienda WITH ( NOLOCK )
          ON
            mvt_keycom = cli_keycom
            AND mvt_keytie = cli_keytie
          LEFT JOIN pv_tiendas WITH ( NOLOCK )
          ON
            mvt_keycom = tie_keycom
            AND mvt_keytie = tie_keytie
          LEFT JOIN pv_proveedores WITH ( NOLOCK )
          ON
            prd_keycom = prv_keycom
            AND prd_keypro = prv_keypro
          LEFT JOIN PV_BIT_FAC_REPTIL WITH ( NOLOCK )
          ON
            mvt_keycom = BMF_KEYCOM
            AND mvt_keytie = BMF_KEYTIE
            AND MVT_FECMOV = bmf_fecha
          WHERE
            mvt_keycom = '002'
            AND mvt_tipmov IN ( 'VT', 'CT' )
            AND CAST(CONVERT(VARCHAR(11), mvt_fecmov) AS SMALLDATETIME) BETWEEN '2010-08-01'
                                                              AND
                                                              '2010-08-31'
            AND mvt_keycom IS NULL
            AND mvt_keytie IS NULL
            AND MVT_FECMOV IS NULL
          GROUP BY
            mvt_keytie ,
            tie_nombre ,
            cli_keycli ,
            prv_porfac ,
            CAST(CONVERT(VARCHAR(11), mvt_fecmov) AS SMALLDATETIME)
          HAVING
            ( SUM(mvt_cantid) > 0 )
        ) FacRep
      GROUP BY
        Tie ,
        Nom ,
        Cli ,
        Fec
    ) FacRep2

now I need to filter the data, I mean retrieve only the information thas isn't in this table -->PV_BIT_FAC_REPTIL

using this filters:

left join PV_BIT_FAC_REPTIL with (nolock) 
    on mvt_keycom = bmf_keycom 
    and mvt_keytie = bmf_keytie 
and mvt_fecmov = bmf_fecha
more ▼

asked Sep 01 '10 at 09:54 AM in Default

EdithCamarillo gravatar image

EdithCamarillo
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

put in an appropriate "WHERE" clause, such as

WHERE 
      PV_BIT_FAC_REPTIL.bmf_keycom IS NULL
  AND PV_BIT_FAC_REPTIL.bmf_keytie IS NULL
  AND PV_BIT_FAC_REPTIL.bmf_fecha IS NULL

(assuming the bmf_* fields are those in PV_BIT_FAC_REPTIL...)

EDIT:

Simplifying the problem slightly, this example shows what we're aiming at with the solution:

First off, create a couple of temporary tables:

DECLARE @T1 TABLE (ID INTEGER, str1 VARCHAR(10))
INSERT INTO @T1 VALUES (1, 't1.1')
INSERT INTO @t1 VALUES(2, 't1.2')

DECLARE @t2 TABLE (ID INTEGER, str2 VARCHAR(10))
INSERT INTO @t2 VALUES(1, 't2.1')

And now, we want to retrieve the values from T1 that don't have a corresponding value in T2:

SELECT * FROM @t1 t1 LEFT JOIN @t2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL

And that brings back the right / expected answer.

more ▼

answered Sep 01 '10 at 10:14 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.4k 14 20 44

Took off the community wiki tag. This is a good question and you deserve the points on the answer.
Sep 01 '10 at 10:25 AM Grant Fritchey ♦♦
@Grant - thanks for that!
Sep 01 '10 at 10:31 AM ThomasRushton ♦
@Grant - apparently you spoke too soon!
Sep 01 '10 at 10:48 AM ThomasRushton ♦

@Edith - I've just noticed that you're doing a filter on

mvt_Keycom = '002' AND 
...
mvt_keycom IS NULL and mvt_keytie is null and mvt_fecvmov IS NULL
This could be causing the problems...
Sep 01 '10 at 11:07 AM ThomasRushton ♦

See!

Hi @EdithCamarillo. When responding to an answer, please use the "add new comment" button so that you can respond directly to the answer. Otherwise you're posting an answer, which can be reordered depending on votes, which can really confuse the conversation. Thanks.
Sep 01 '10 at 11:22 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x362
x11

asked: Sep 01 '10 at 09:54 AM

Seen: 1117 times

Last Updated: Sep 02 '10 at 08:43 AM