asked
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
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.
Took off the community wiki tag. This is a good question and you deserve the points on the answer.
@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...
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.
