how can i get invoice number that has only items from LOC_CD = 1 ?

[Ahmed_39]

dear all

i have tables(aztrd) it conatis invoice deatils like itm_code , date,loc_cd ..... ect

• in each in invoice they are itmes (itm_cd) from loc_cd "1" or loc_cd "2" and some invocies has items from loc_cd "1" only or loc_cd "2" only

i try this query to get only invoive number that has only one LOC_CD ( all items in invoive must be the same LOC_CD) but i got all invoice number - so how can i get only the invoice that has all items from on LOC_CD ( 1 for example ? )

SELECT tr_no,loc_cd FROM AZTRD 
WHERE LOC_CD = 1 and tr_ty = 102 and loc = 01  AND LOC_CD NOT IN (SELECT itm_cd WHERE LOC_CD = 2)
GROUP BY TR_NO,LOC_CD
ORDER BY TR_NO 

i have attach database backup has only table AZTRD to let you help me it works on SQL 2012 ... thanks a lot

[pritaeas]

If for every invoice you count the lines for each unique loc_cd, and then group them (by invoice and loc_cd), you can then select the invoices that have only one count.

[Ahmed_39]

can you please give me small example for the query from my old query ?

SELECT tr_no,loc_cd FROM AZTRD 
WHERE LOC_CD = 1 and tr_ty = 102 and loc = 01  AND LOC_CD NOT IN (SELECT itm_cd WHERE LOC_CD = 2)
GROUP BY TR_NO,LOC_CD
ORDER BY TR_NO 

thanks a lot

[pritaeas]

Something like this I think. Untested, but it should get you started.

SELECT * FROM (
    SELECT tr_no, loc_cd, COUNT(*)
    FROM aztrd
    GROUP BY tr_no, loc_cd
) t
GROUP BY tr_no
HAVING COUNT(*) = 1

[Ahmed_39]

sorry but it not working ... :( therew is err in query

[pritaeas]

I said it was untested, and meant to get you started towards your own solution.

[Ahmed_39]

i try to do it by

SELECT tr_no, loc_cd, COUNT(loc_cd) AS [itm_loc_count]  FROM aztrd  WHERE loc= 01 AND TR_TY = 102 AND loc_cd = 1
GROUP BY tr_no, loc_cd HAVING COUNT(*) = 1
ORDER BY TR_NO

but here it gives me all invoice that has 1 item from loc_cd = 1

• so please how can i fix it to give all invoice that has no item from loc_cd = 1 ?

[BitBlt]

Interesting little problem. I was able to get a result set that I think is what you're looking for, but it shows all the detail lines. It does, however demonstrate the technique, so I hope you can use it as a starting point for further refinement depending on your needs.

select 
loc as loc1,               -- included these so I could get the interesting items
tr_ty as tr_ty1,           -- at the beginning of the result set.
tr_no as tr_no1, 
ln_no as ln_no1, 
LOC_CD as loc_cd1, 
ITM_CD as itm_cd1,
*                          --  ...and the rest of the columns
from aztrd a               --  note the alias, used in the subselect below for a correlation name
where loc = 01             --  your criterion from above
and tr_ty = 102            --  your criterion from above
and not exists             --  here's the important part...
    (
    select 1 from aztrd b  --  note the alias!  Used to correlate the outside select!
    where b.loc = a.loc    --  correlations...
    and b.tr_ty = a.tr_ty 
    and b.tr_no = a.tr_no 
    and b.loc_cd <> a.loc_cd   -- And, finally, determining that the loc_cd values are different
    )
order by loc, tr_no, ln_no

Please note that inside the "not exists" clause, I select the value "1" rather than a column. The "not exists" returns either a "true" or a "false", so it is not necessary to return rows...so by returning a scalar, it won't actually retrieve any data. On a query of a large dataset, this could result in improved performance.

Hope this helps! Good luck!

[Ahmed_39]

finally i got it ... :)

WITH CTE_TRD AS
(
    SELECT 
       TR_NO 
    FROM AZTRD 
    WHERE 
       TR_TY = 102 AND
       LOC = 01 
    GROUP BY
       TR_NO 
    HAVING 
       MIN(LOC_CD) = MAX(LOC_CD)
)


SELECT 
      CTE_TRD.TR_NO,
      AZTRD.LOC_CD,
      AZLISTS.DSCR_AR
FROM CTE_TRD
INNER JOIN AZTRD
      ON AZTRD.TR_NO = CTE_TRD.TR_NO
INNER JOIN AZLISTS
     ON AZLISTS.CODE = AZTRD.LOC_CD
WHERE 
      AZLISTS.REC_TYPE = 10 AND 
      TR_TY = 102 AND 
      LOC = 01 AND 
      LOC_CD = 2 AND
      AZTRD.TR_DT BETWEEN '2013-11-01' AND '2013-11-05'
GROUP BY
      CTE_TRD.TR_NO, AZTRD.LOC_CD, AZLISTS.DSCR_AR
ORDER BY
      CTE_TRD.TR_NO