January 13, 2009 at 10:07 pm
Hi!,
I hoped someone can help me regarding this problem in T-SQL. I doing this since yesterday and later they need the output :crying:. Here it is, For example I have the given fields and data below.
Material|ProdQty|ProdDateTime______
TS-L6 |10 |1/14/2009 8:15:52 AM
TS-L6 |12 |1/14/2009 8:30:53 AM
TS-L6 |30 |1/14/2009 9:00:51 AM
BS-L1 |32 |1/14/2009 8:15:52 AM
BS-L1 |38 |1/14/2009 8:30:53 AM
BS-L1 |43 |1/14/2009 9:00:51 AM
What I need to do is to get the difference of the two latest production material. So based from the above example, The latest production of TS-L6 material are 1/14/2009 8:30:53 AM and 1/14/2009 9:00:51 AM so I have to produce the difference of 30 and 12(30-12) which is 18. Same with the other materials.
Sample output will be
Material|Difference
TS-L6 |18 |
BS-L1 |5 |
Hoping that someone could help.
Advance thanks
Morris
Thanks,
Morris
January 13, 2009 at 10:43 pm
These are kinda a pain to do in SQL 2000. With SQL 2005, they're cake. Here's one way of doing it in 2000. Please notice the way I posted your sample data at the top. This is how you should post your sample data for future posts. See the article in my signature for more pointers on how to accomplish this.
[font="Courier New"]CREATE TABLE #A(
Mat VARCHAR(10),
Qty INT,
DT DATETIME)
INSERT INTO #A(Mat, Qty, DT)
SELECT 'TS-L6', 10, '1/14/2009 8:15:52 AM' UNION ALL
SELECT 'TS-L6', 12, '1/14/2009 8:30:53 AM' UNION ALL
SELECT 'TS-L6', 30, '1/14/2009 9:00:51 AM' UNION ALL
SELECT 'BS-L1', 32, '1/14/2009 8:15:52 AM' UNION ALL
SELECT 'BS-L1', 38, '1/14/2009 8:30:53 AM' UNION ALL
SELECT 'BS-L1', 43, '1/14/2009 9:00:51 AM'
SELECT LastQty.Mat,
LastQty.Qty - NTLastQty.Qty
FROM
(SELECT A.Mat, MAX(A.Qty) Qty -- Max in case 2 date entries that are the same for 1 mat
FROM #A A
INNER JOIN (SELECT Mat, MAX(DT) DT FROM #A GROUP BY Mat) MD
ON A.Mat = MD.Mat AND A.DT = MD.DT
GROUP BY A.Mat) LastQty
INNER JOIN
(SELECT A.Mat, MAX(A.Qty) Qty -- Max in case 2 date entries that are the same for 1 mat
FROM #A A
INNER JOIN (SELECT A2.Mat, MAX(DT) DT FROM #A A2 WHERE DT <
(SELECT MAX(DT) FROM #A WHERE Mat = A2.Mat)
GROUP BY A2.Mat) MD2
ON A.Mat = MD2.Mat AND A.DT = MD2.DT
GROUP BY A.Mat) NTLastQty
ON LastQty.Mat = NTLastQty.Mat
DROP TABLE #A[/font]
January 13, 2009 at 10:53 pm
THANK YOU SO MUCH! :w00t: This solve my problem!!! I've been working for this since yesterday. It really gives me headache. Thanks again!!
Thanks,
Morris
January 14, 2009 at 6:52 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply