question

EdithCamarillo avatar image
EdithCamarillo 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
t-sqlqueryouter-join
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
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.
13 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Took off the community wiki tag. This is a good question and you deserve the points on the answer.
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
@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...
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
@Grant - thanks for that!
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
@Grant - apparently you spoke too soon!
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.