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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 complex query help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thanksfor help
Posting Yak Master

105 Posts
Posted - 05/15/2007 :  17:22:28  Show Profile  Reply with Quote
Hi

From the following table below ( this is just sample data). I need to write a query to pull latest month. For eg, when there is different months for same prodid,custid,spid-- i need to pull the latest month.

prodid custid spid month unitprice cnt
14 88 2 Jan-07 2972 -4
69 24 2 Dec-06 33814.3594 -5
69 35 2 Dec-06 14447.7598 -5
96 24 2 Feb-07 27203 -3
96 24 2 Nov-06 19346 -6
96 35 2 Dec-06 15532.58985 -5
96 35 2 Feb-07 15532.5898 -3
96 35 2 Nov-06 19346 -6
110 35 2 Feb-07 10751.0303 -3
110 35 2 Mar-07 10751.03025 -2
115 35 2 Feb-07 9718.4697 -3
115 35 2 Mar-07 9718.46954 -2
115 174 2 Dec-06 20500 -5
160 83 2 Mar-07 5228.259945 -2
160 174 2 Apr-07 15000 -1
177 143 2 Dec-06 1386.31992 -5
177 143 2 Feb-07 1400.260027 -3
177 143 2 Jan-07 1400.260027 -4
177 143 2 Mar-07 1386.31992 -2
177 143 2 Nov-06 1386.319989 -6

Any help is very much appreciated.

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts
Posted - 05/15/2007 :  17:25:40  Show Profile  Visit dinakar's Homepage  Reply with Quote
What is the datatype of the column month?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

thanksfor help
Posting Yak Master

105 Posts
Posted - 05/15/2007 :  17:48:43  Show Profile  Reply with Quote
Hi,

here is datatype for all the columns

prodid,custid,spid --- int

monthid -- varchar

unitprice - decimal

cnt - int

thanks
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts
Posted - 05/15/2007 :  18:05:32  Show Profile  Visit dinakar's Homepage  Reply with Quote
Because the monthid is a varchar and not a proper datetime column and you need to get the "latest" in terms of time, you have to convert the value in the monthid to a datetime datatype. Your design itself is bad. To workaround try something like this:

NewCol = max( convert(datetime,'20' + substring(monthid,5,2) + substring(monthid,1,3) + '01') )


Now you can do a group by on the prodid, custid, spid and get a max of above concatenation.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2871 Posts
Posted - 05/15/2007 :  19:23:58  Show Profile  Visit jezemine's Homepage  Reply with Quote
varchar is more flexible that datetime. it can store any value!



www.elsasoft.org
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts
Posted - 05/15/2007 :  19:59:46  Show Profile  Visit dinakar's Homepage  Reply with Quote
quote:
Originally posted by jezemine

varchar is more flexible that datetime. it can store any value!



www.elsasoft.org


right.. I wonder why MS bothered to have offered other datatypes...just use varchar for everything and use string/date functions on the column. simple...

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
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.08 seconds. Powered By: Snitz Forums 2000