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.
@Grant, According to @Edith's tweets, it errors when she tries to add new comments.
Not good. I'll report it up the chain. Thanks. And thanks @Edith
I'm trying to get Edith to log a call with JIRA too. And to hand over details of the error message. Gawd knows if it'll fit in 140 characters!
@Grant Fritchey There is a possibility that those users who are still using IE 6 are not able to add new comments. Here is one example: I posted the answer here:
http://ask.sqlservercentral.com/questions/17821/splitting-strings-containing-rows-and-columns This was a very good question which did require some thinking, so I took a chance to exploit my tendency to make the short stories long. I received positive feedback from Fatherjack and William, but not from OP, and so I added a comment that it was too bad that the answer was not good enough for him. Then I received a comment from OP some time later stating that he "cannot seem to add comments from my office PC (IE 6)", but has no problems doing it at home. This means that either big brother at work and/or IE 6 could be to blame for some users' inability to add comments.
Oh, and I've had problems with some "Big Brothers" when adding more than a few hundred characters as answers or comments, which has been frustrating. Fortunately, the current workplace is fine. That, and using my own laptop and GPRS connection... ;-)
There was a question about problems with posting answers:
http://ask.sqlservercentral.com/questions/15014/meta-is-there-any-limit-on-how-many-characters-i-can-post-as-an-answer I was able to answer it exactly because in the past I had fallen the victim of the Symantec Endpoint Protection built-in functionality to make IE go bisserk when something looking like dangerous SQL is typed in any IE window (drop table etc). I had to ask our network admins to add me to the untouchables AD group. This cured the problem right away :)
I had problems with the work firewall when we moved from SE as the comments (and answers I thought) use ajax scripts from a url that was blocked. something like
ajax.googleapis.com or something ...
