SQL Server Forums
Profile | Register | 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?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssimon
Starting Member

USA
14 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

4608 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
14 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

4608 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  
 New 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