Is this possible?? - 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)

Is this possible??

Author

Topic

vinothrao84
Starting Member

21 Posts

Posted - 2008-10-17 : 02:45:47

Hi,

I have a table (tblregfundprice) with the column name "Unit_Price, Fund_Code, and Date".

The table contains the following data.

Fund_Code.....Unit_Price.....Date
AF............0.1254.........10/17/2008
AF............0.5487.........10/16/2008
AF............0.2114.........10/15/2008
AF............0.8744.........10/14/2008
AF............0.1254.........10/13/2008
AF............0.5487.........10/12/2008
AF............0.2114.........10/11/2008
AF............0.8744.........10/10/2008
AF............0.1254.........10/09/2008
SC............0.5487.........10/14/2008
SC............0.2114.........10/13/2008
SC............0.8744.........10/12/2008
GH............0.1254.........10/11/2008
GH............0.5487.........10/10/2008
GH............0.2114.........10/09/2008
GH............0.8744.........10/08/2008

Now if you notice, the most recent date is 10/17/2008 and SC is having the most date of 10/14/2008. How can i do to create below result table for TOP 10 Fund_Code = "SC"?

Unit_Price.....Date
NULL..........10/17/2008
NULL..........10/16/2008
NULL..........10/15/2008
0.5487.......10/14/2008
0.2114.......10/13/2008
0.8744.......10/12/2008
0.1254.......10/11/2008
NULL..........10/10/2008
NULL..........10/09/2008
NULL..........10/08/2008

I need the NULL value to appear as i need it for my system to capture it... I hope there's a way for this.

Pls help....Many thx.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-10-17 : 02:56:43

try
select TOP 10 Case When FUND_CODE <> 'SC' THEN 'NULL' ELSE UNIT_PRICE END AS [UNIT PRICE],
DATE from tblregfundprice order by DATE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 02:59:40

Your expected output does not match your sample data. Unit price 0.1254 is not related to a SC record.

DECLARE	@Sample TABLE
	(
		fundCode CHAR(2),
		unitPrice SMALLMONEY,
		dt SMALLDATETIME
	)

INSERT	@Sample
SELECT	'AF', 0.1254, '10/17/2008' UNION ALL
SELECT	'AF', 0.5487, '10/16/2008' UNION ALL
SELECT	'AF', 0.2114, '10/15/2008' UNION ALL
SELECT	'AF', 0.8744, '10/14/2008' UNION ALL
SELECT	'AF', 0.1254, '10/13/2008' UNION ALL
SELECT	'AF', 0.5487, '10/12/2008' UNION ALL
SELECT	'AF', 0.2114, '10/11/2008' UNION ALL
SELECT	'AF', 0.8744, '10/10/2008' UNION ALL
SELECT	'AF', 0.1254, '10/09/2008' UNION ALL
SELECT	'SC', 0.5487, '10/14/2008' UNION ALL
SELECT	'SC', 0.2114, '10/13/2008' UNION ALL
SELECT	'SC', 0.8744, '10/12/2008' UNION ALL
SELECT	'GH', 0.1254, '10/11/2008' UNION ALL
SELECT	'GH', 0.5487, '10/10/2008' UNION ALL
SELECT	'GH', 0.2114, '10/09/2008' UNION ALL
SELECT	'GH', 0.8744, '10/08/2008'

SELECT TOP 10	MAX(CASE WHEN fundCode = 'SC' THEN unitPrice ELSE NULL END) AS unitPrice,
		dt
FROM		@Sample
GROUP BY	dt
ORDER BY	dt DESC

E 12°55'05.63"
N 56°04'39.26"

vinothrao84
Starting Member

21 Posts

Posted - 2008-10-17 : 03:07:23

Ohh i'm very sorry Peso.

I just create a sample data output to show whats i wanted.

I will try the both sql statement now. Thx to Sunil and Peso.

vinothrao84
Starting Member

21 Posts

Posted - 2008-10-17 : 03:11:57

Thx Sunil and Peso,

Both of ur sql works like a charm....You guys are true programmers...

Thx yaa..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 03:21:38

Sunil do have flaws in his suggestion. It returns duplicate dates.

My suggestion returns

unitPrice	dt
NULL	2008-10-17 00:00:00
NULL	2008-10-16 00:00:00
NULL	2008-10-15 00:00:00
0,5487	2008-10-14 00:00:00
0,2114	2008-10-13 00:00:00
0,8744	2008-10-12 00:00:00
NULL	2008-10-11 00:00:00
NULL	2008-10-10 00:00:00
NULL	2008-10-09 00:00:00
NULL	2008-10-08 00:00:00

And Sunil's suggestion returns

Cannot convert char value to smallmoney. The char value has incorrect syntax.

After fixing the error (remove single quotes around NULL), Sunil's suggestion returns

UNIT PRICE	dt
NULL	2008-10-08 00:00:00
NULL	2008-10-09 00:00:00
NULL	2008-10-09 00:00:00
NULL	2008-10-10 00:00:00
NULL	2008-10-10 00:00:00
NULL	2008-10-11 00:00:00
NULL	2008-10-11 00:00:00
NULL	2008-10-12 00:00:00
0,8744	2008-10-12 00:00:00
NULL	2008-10-13 00:00:00

Even if you put a distinct to Sunil's code, you still get

UNIT PRICE	dt
NULL	2008-10-08 00:00:00
NULL	2008-10-09 00:00:00
NULL	2008-10-10 00:00:00
NULL	2008-10-11 00:00:00
NULL	2008-10-12 00:00:00
0,8744	2008-10-12 00:00:00
NULL	2008-10-13 00:00:00
0,2114	2008-10-13 00:00:00
NULL	2008-10-14 00:00:00
0,5487	2008-10-14 00:00:00

See the double dates? And you have to put a DESC after the ORDER BY Date for Sunil's suggestion.

E 12°55'05.63"
N 56°04'39.26"

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-10-17 : 03:33:38

quote:
Originally posted by Peso

Sunil do have flaws in his suggestion. It returns duplicate dates.

My suggestion returns
unitPrice dt
NULL 2008-10-17 00:00:00
NULL 2008-10-16 00:00:00
NULL 2008-10-15 00:00:00
0,5487 2008-10-14 00:00:00
0,2114 2008-10-13 00:00:00
0,8744 2008-10-12 00:00:00
NULL 2008-10-11 00:00:00
NULL 2008-10-10 00:00:00
NULL 2008-10-09 00:00:00
NULL 2008-10-08 00:00:00
And Sunil's suggestion returns
Cannot convert char value to smallmoney. The char value has incorrect syntax.
After fixing the error (remove single quotes around NULL), Sunil's suggestion returns
UNIT PRICE dt
NULL 2008-10-08 00:00:00
NULL 2008-10-09 00:00:00
NULL 2008-10-09 00:00:00
NULL 2008-10-10 00:00:00
NULL 2008-10-10 00:00:00
NULL 2008-10-11 00:00:00
NULL 2008-10-11 00:00:00
NULL 2008-10-12 00:00:00
0,8744 2008-10-12 00:00:00
NULL 2008-10-13 00:00:00
Even if you put a distinct to Sunil's code, you still get
UNIT PRICE dt
NULL 2008-10-08 00:00:00
NULL 2008-10-09 00:00:00
NULL 2008-10-10 00:00:00
NULL 2008-10-11 00:00:00
NULL 2008-10-12 00:00:00
0,8744 2008-10-12 00:00:00
NULL 2008-10-13 00:00:00
0,2114 2008-10-13 00:00:00
NULL 2008-10-14 00:00:00
0,5487 2008-10-14 00:00:00
See the double dates? And you have to put a DESC after the ORDER BY Date for Sunil's suggestion.

E 12°55'05.63"
N 56°04'39.26"