complex query help - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

complex query help

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-05-15 : 17:22:28

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
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-15 : 17:25:40

What is the datatype of the column month?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-05-15 : 17:48:43

Hi,

here is datatype for all the columns

prodid,custid,spid --- int

monthid -- varchar

unitprice - decimal

cnt - int

thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-15 : 18:05:32

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/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-15 : 19:23:58

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

www.elsasoft.org

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-15 : 19:59:46

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources