February 17, 2009 at 1:38 pm
[font="Verdana"]I think using a WHERE clause is probably the right approach. You have one commented out. Did that not do what you want?[/font]
February 17, 2009 at 1:41 pm
You would either do that with Where Not Exists or with Where Not In, and then a query of the SSCC values that have incomplete boxCompleted values. Would look something like:
INSERT INTO #PO
SELECT distinct TB_searsPreColisage.searsPO,
TB_searsPreColisage.SSCC,
TB_searsSSCC.boxCompleted
FROM TB_searsPreColisage
INNER JOIN TB_searsOrder ON TB_searsPreColisage.searsPO = TB_searsOrder.orderNO
inner JOIN TB_searsSSCC ON TB_searsSSCC.SSCC = TB_searsPreColisage.SSCC
WHERE NOT EXISTS
(SELECT *
FROM TB_searsSSCC
WHERE boxCompleted IS NULL
AND SSCC = TB_searsPreColisage.SSCC)
GROUP BY searsPO, TB_searsPreColisage.SSCC, boxCompleted
[/code]
Or:
INSERT INTO #PO
SELECT distinct TB_searsPreColisage.searsPO,
TB_searsPreColisage.SSCC,
TB_searsSSCC.boxCompleted
FROM TB_searsPreColisage
INNER JOIN TB_searsOrder ON TB_searsPreColisage.searsPO = TB_searsOrder.orderNO
inner JOIN TB_searsSSCC ON TB_searsSSCC.SSCC = TB_searsPreColisage.SSCC
WHERE TB_searsPreColisage.SSCC NOT IN
(SELECT SSCC
FROM TB_searsSSCC
WHERE boxCompleted IS NULL)
GROUP BY searsPO, TB_searsPreColisage.SSCC, boxCompleted
Try both of those variations, see which one works better. They should get you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 17, 2009 at 1:55 pm
after trying both solution, it gives me the following result:
PO SSCC boxCompleted
1038219 00006205092038218123 2009-01-01 00:00:00
but it should'nt return any result in this case, because there is still a box (#SSCC) uncompleted. Result recordset should give all completed #SSCC per P/O (in this case there is 2) or none at all.
thanks for your time, I really appreciate,
Dominic
February 17, 2009 at 1:59 pm
Please try:
DECLARE @TB_searsOrder TABLE (orderNO VARCHAR(20))
DECLARE @TB_searsPreColisage TABLE (searsPO VARCHAR(20), SSCC VARCHAR(20))
DECLARE @TB_searsSSCC TABLE (SSCC VARCHAR(20),boxCompleted DATETIME)
INSERT INTO @TB_searsOrder (orderNO)
SELECT '1038217' UNION ALL
SELECT '1038218' UNION ALL
SELECT '1038219'
INSERT INTO @TB_searsPreColisage (searsPO, SSCC)
SELECT '1038217','00006205092038217012' UNION ALL
SELECT '1038218','00006205092038218019' UNION ALL
SELECT '1038219','00006205092038218123' UNION ALL
SELECT '1038219','00006205092038218888'
INSERT INTO @TB_searsSSCC (boxCompleted,SSCC)
SELECT NULL,'00006205092038217012' UNION ALL
SELECT NULL,'00006205092038218019' UNION ALL
SELECT '02/15/09','00006205092038218123' UNION ALL
SELECT NULL,'00006205092038218888'
SELECT a.searsPO,
a.SSCC,
c.boxCompleted
FROM @TB_searsPreColisage a
INNER JOIN @TB_searsOrder b ON a.searsPO = b.orderNO
INNER JOIN @TB_searsSSCC c ON c.SSCC = a.SSCC
WHERE a.searsPO NOT IN (SELECT c.searsPO
FROM @TB_searsSSCC s
INNER JOIN @TB_searsPreColisage c
ON s.SSCC = c.SSCC
WHERE s.boxCompleted IS NULL)
UPDATE @TB_searsSSCC SET boxCompleted = '02/15/09' WHERE SSCC = '00006205092038218888'
SELECT a.searsPO,
a.SSCC,
c.boxCompleted
FROM @TB_searsPreColisage a
INNER JOIN @TB_searsOrder b ON a.searsPO = b.orderNO
INNER JOIN @TB_searsSSCC c ON c.SSCC = a.SSCC
WHERE a.searsPO NOT IN (SELECT c.searsPO
FROM @TB_searsSSCC s
INNER JOIN @TB_searsPreColisage c
ON s.SSCC = c.SSCC
WHERE s.boxCompleted IS NULL)
February 17, 2009 at 2:09 pm
After trying your solution, it looks good and seems to work fine!! 😀
I'll check it more in details tomorrow morning!
thank you very much!! 🙂
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply