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.
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 cnt14 88 2 Jan-07 2972 -4 69 24 2 Dec-06 33814.3594 -5 69 35 2 Dec-06 14447.7598 -596 24 2 Feb-07 27203 -396 24 2 Nov-06 19346 -696 35 2 Dec-06 15532.58985 -596 35 2 Feb-07 15532.5898 -396 35 2 Nov-06 19346 -6110 35 2 Feb-07 10751.0303 -3110 35 2 Mar-07 10751.03025 -2115 35 2 Feb-07 9718.4697 -3115 35 2 Mar-07 9718.46954 -2115 174 2 Dec-06 20500 -5160 83 2 Mar-07 5228.259945 -2160 174 2 Apr-07 15000 -1177 143 2 Dec-06 1386.31992 -5177 143 2 Feb-07 1400.260027 -3177 143 2 Jan-07 1400.260027 -4177 143 2 Mar-07 1386.31992 -2177 143 2 Nov-06 1386.319989 -6Any 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 NethiSQL 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 columnsprodid,custid,spid --- intmonthid -- varcharunitprice - decimalcnt - intthanks |
|
|
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 NethiSQL 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 NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
|
|
|