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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SOLVED - MAX Date in sub?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssimon
Starting Member

USA
16 Posts

Posted - 08/20/2013 :  14:55:54  Show Profile  Visit ssimon's Homepage  Reply with Quote
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)


	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


Edited by - ssimon on 08/20/2013 16:55:00

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/20/2013 :  15:35:43  Show Profile  Reply with Quote
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

USA
16 Posts

Posted - 08/20/2013 :  16:54:22  Show Profile  Visit ssimon's Homepage  Reply with Quote
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'


Worked out for me
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/21/2013 :  10:43:45  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000