February 17, 2009 at 1:29 pm
Hi,
I need to return some values on some specific conditions.
here is my query:
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_searsSSCC.boxCompleted IS NOT NULL
GROUP BY searsPO, TB_searsPreColisage.SSCC, boxCompleted
and here is the result of that query:
PO SSCC boxCompleted
1038217 00006205092038217012 NULL
1038218 00006205092038218019 NULL
1038219 00006205092038218123 2009-15-02 00:00:00
1038219 00006205092038218888 NULL
In this case, I need to retrieve only the rows where all the box are completed on a P/O. So, in this example, no rows should be returned, because P/O 1038219 still have an uncompleted box. But when the other box number (00006205092038218888 in this case) is completed, I need the result to give something like this:
PO SSCC boxCompleted
1038219 00006205092038218123 2009-15-02 00:00:00
1038219 00006205092038218888 2009-17-02 00:00:00
So, how can I build a query to do so? I thought about playing with cursor, but I red somewhere it's not good pratice.
thanks a lot for your time and help,
Dominic Gagné
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply