Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SOLVED - MAX Date in sub?

Author  Topic 

ssimon
Starting Member

16 Posts

Posted - 2013-08-20 : 14:55:54
I am trying to query only the Max date dependant on quantity


Create Table dbo.TestParts
(Part char(30), Desc1 char(50), Desc2 char(50));
Create Table dbo.TestStructure
(Model char(30), Part char(30), EDATE smalldatetime, QtyPer float);
GO
Insert INTO dbo.TestParts Values('101111','Widget A', 'Batteries Not Included'),
('101112','Widget B', 'Batteries Included'),
('101113','Widget C', 'USB'),
('101114','Widget D', 'Serial');
Insert INTO dbo.TestStructure Values
('AAA1', '101111','02/01/2012','5'),
('AAA1', '101111','07/01/2013', '0'),
('AAA1', '101112', '12/01/2012', '1'),
('AAA1', '101112', '12/02/2012', '2'),
('AAA1', '101112', '12/03/2012', '3'),
('AAA1', '101113', '12/01/2012', '1'),
('AAA1', '101113', '12/02/2012', '0'),
('AAA1', '101113', '12/03/2012', '5'),
('AAA1', '101114', '12/01/2012', '1');
GO

Select ts.Part, RTRIM(tp.Desc1) + ' ' + RTRIM(tp.Desc2) as Description,
ts.EDATE, ts.QtyPer
FROM testing.dbo.TestStructure ts
Inner Join testing.dbo.TestParts tp
ON ts.Part = tp.Part
WHERE ts.Model = 'AAA1'


Looking for a return of:

PART Description EDATE QtyPer
101112 Widget B ..... 2012-12-03 3
101113 Widget C ..... 2012-12-03 5
101114 Widget D ..... 2012-12-01 1

---------------
Widget A should not show because the last date the qty was changed to Zero

Closest that I have come..........(which is pulling the part 101111 which should be incorrect)

[CODE]
Select ts.Part, RTRIM(tp.Desc1) + ' ' + RTRIM(tp.Desc2) as Description,
ts.EDATE, ts.QtyPer
FROM testing.dbo.TestStructure ts
Inner Join (Select Part,MAX(EDATE) as Date
FROM testing.dbo.TestStructure
WHERE QTYPER <> '0'
Group By Part) m
Inner Join testing.dbo.TestParts tp on m.Part=tp.Part
on ts.Part = m.Part AND
ts.EDATE = m.Date
where ts.Model='AAA1'
Order By ts.Part
[/CODE]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-20 : 15:35:43
Maybe this weill help get you going:
SELECT *
FROM
dbo.TestParts
INNER JOIN
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Part ORDER BY EDATE DESC) AS RowNum
FROM dbo.TestStructure
) AS TS
ON TestParts.Part = TS.Part
WHERE
TS.RowNum = 1
AND TS.QtyPer > 0.0


Thanks for supplying sample data!
Go to Top of Page

ssimon
Starting Member

16 Posts

Posted - 2013-08-20 : 16:54:22
Thank you very much Lamprey ....... (and your welcome for the sample data)

[Code]
SELECT ts.Part,RTRIM(tp.Desc1) + ' ' + RTRIM(tp.Desc2) as Description, ts.EDATE,ts.QtyPer
FROM
dbo.TestParts tp
INNER JOIN
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Part ORDER BY EDATE DESC) AS RowNum
FROM dbo.TestStructure WHERE Model='AAA1'
) AS TS
ON tp.Part = TS.Part
WHERE
TS.RowNum = 1
AND
TS.QtyPer <> '0'[/code]

Worked out for me
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-21 : 10:43:45
Quick question. I see that you are comparing a string to a float value (TS.QtyPer <> '0'). Any reason for that? It'd be better to not use a string so you don't have implicit conversions happening.
Go to Top of Page
   

- Advertisement -