Calculation on same table with same column

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)

Calculation on same table with same column

Author

Topic

vinothrao84
Starting Member

21 Posts

Posted - 2007-10-23 : 00:00:50

Hi,

I have the following below table.

Tablename : tblMutFundPrice

Name...........NAV...........Date
Growth1.... 0.3919......10/12/2007
Growth2.... 1.4258......10/12/2007
Growth3.... 0.8895......10/12/2007
Growth4.... 0.4270......10/12/2007
Growth5.... 1.1284......10/12/2007
Growth6.... 0.7629......10/12/2007
Growth7.... 0.7825......10/12/2007
Growth8.... 0.3825......10/12/2007
Growth9.... 0.6854......10/12/2007
Growth1.... 0.4552......10/13/2007
Growth2.... 1.2114......10/13/2007
Growth3.... 0.4751......10/13/2007
Growth4.... 0.8745......10/13/2007
Growth5.... 1.1235......10/13/2007
Growth6.... 0.1425......10/13/2007
Growth7.... 0.2855......10/13/2007
Growth8.... 0.1456......10/13/2007
Growth9.... 0.1114......10/13/2007

Name...........NAV.......Change....Change %......Date
Growth1.... 0.4552.... -0.0050.... -1.26......10/13/2007
Growth2.... 1.2114.... -0.0168.... -1.16......10/13/2007
Growth3.... 0.4751.... +0.0004.... +0.04......10/13/2007
Growth4.... 0.8745.... -0.0059.... -1.36......10/13/2007
Growth5.... 1.1235.... +0.0004.... +0.04......10/13/2007
Growth6.... 0.1425.... -0.0080.... -1.04......10/13/2007
Growth7.... 0.2855.... -0.0091.... -1.15......10/13/2007
Growth8.... 0.1456.... -0.0037.... -0.96......10/13/2007
Growth9.... 0.1114.... -0.0090.... -1.30......10/13/2007

Ok this is what i want....

The result of the select statement will calculate Change and Change %
in comparison of 10/12/2007 with 10/13/2007 records based on NAV column.

Hope u guys can help me. Thx..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 00:03:46

is there something wrong with the sample data posted ? I don't see any changes of value between 10/12 and 10/13

KH
[spoiler]Time is always against us[/spoiler]

vinothrao84
Starting Member

21 Posts

Posted - 2007-10-23 : 00:06:54

quote:
Originally posted by khtan

is there something wrong with the sample data posted ? I don't see any changes of value between 10/12 and 10/13

KH
[spoiler]Time is always against us[/spoiler]

Opps i did copy n paste for sample data....i made correction....thx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 00:30:14

Think either there are still something wrong with the C&P or my formula is wrong

Anyway, you take a look at it

DECLARE @tblMutFundPrice TABLE
(
	[Name]		varchar(10),
	[NAV]		money,
	[Date]		datetime
)
INSERT INTO @tblMutFundPrice
SELECT	'Growth1', 0.3919, '10/12/2007' UNION ALL
SELECT	'Growth2', 1.4258, '10/12/2007' UNION ALL
SELECT	'Growth3', 0.8895, '10/12/2007' UNION ALL
SELECT	'Growth4', 0.4270, '10/12/2007' UNION ALL
SELECT	'Growth5', 1.1284, '10/12/2007' UNION ALL
SELECT	'Growth6', 0.7629, '10/12/2007' UNION ALL
SELECT	'Growth7', 0.7825, '10/12/2007' UNION ALL
SELECT	'Growth8', 0.3825, '10/12/2007' UNION ALL
SELECT	'Growth9', 0.6854, '10/12/2007' UNION ALL
SELECT	'Growth1', 0.4552, '10/13/2007' UNION ALL
SELECT	'Growth2', 1.2114, '10/13/2007' UNION ALL
SELECT	'Growth3', 0.4751, '10/13/2007' UNION ALL
SELECT	'Growth4', 0.8745, '10/13/2007' UNION ALL
SELECT	'Growth5', 1.1235, '10/13/2007' UNION ALL
SELECT	'Growth6', 0.1425, '10/13/2007' UNION ALL
SELECT	'Growth7', 0.2855, '10/13/2007' UNION ALL
SELECT	'Growth8', 0.1456, '10/13/2007' UNION ALL
SELECT	'Growth9', 0.1114, '10/13/2007'

DECLARE	@date	datetime

SELECT	@date	= '20071013'

SELECT	c.[Name], 
	c.[NAV], 
	Change 		= p.[NAV] - c.[NAV],
	[Change %]	= (p.[NAV] - c.[NAV]) * 100 / p.[NAV],
	c.[Date]
FROM	@tblMutFundPrice c 
	left JOIN @tblMutFundPrice p
	ON	c.[Name]	= p.[Name]
	AND	c.[Date] - 1	= p.[Date]
WHERE	c.[Date] = @date

/*
Name       NAV                   Change                Change %              Date                                                   
---------- --------------------- --------------------- --------------------- -----------
Growth1                    .4552                -.0633              -16.1520 2007-10-13 
Growth2                   1.2114                 .2144               15.0371 2007-10-13 
Growth3                    .4751                 .4144               46.5879 2007-10-13 
Growth4                    .8745                -.4475             -104.8009 2007-10-13 
Growth5                   1.1235                 .0049                 .4342 2007-10-13 
Growth6                    .1425                 .6204               81.3212 2007-10-13 
Growth7                    .2855                 .4970               63.5143 2007-10-13 
Growth8                    .1456                 .2369               61.9346 2007-10-13 
Growth9                    .1114                 .5740               83.7467 2007-10-13 
*/

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 02:07:34

[code]DECLARE @Sample TABLE ([Name] VARCHAR(10), [NAV] MONEY, [Date] DATETIME)

SET DATEFORMAT MDY

INSERT @Sample
SELECT 'Growth1', 0.3919, '10/12/2007' UNION ALL
SELECT 'Growth2', 1.4258, '10/12/2007' UNION ALL
SELECT 'Growth3', 0.8895, '10/12/2007' UNION ALL
SELECT 'Growth4', 0.4270, '10/12/2007' UNION ALL
SELECT 'Growth5', 1.1284, '10/12/2007' UNION ALL
SELECT 'Growth6', 0.7629, '10/12/2007' UNION ALL
SELECT 'Growth7', 0.7825, '10/12/2007' UNION ALL
SELECT 'Growth8', 0.3825, '10/12/2007' UNION ALL
SELECT 'Growth9', 0.6854, '10/12/2007' UNION ALL
SELECT 'Growth1', 0.4552, '10/13/2007' UNION ALL
SELECT 'Growth2', 1.2114, '10/13/2007' UNION ALL
SELECT 'Growth3', 0.4751, '10/13/2007' UNION ALL
SELECT 'Growth4', 0.8745, '10/13/2007' UNION ALL
SELECT 'Growth5', 1.1235, '10/13/2007' UNION ALL
SELECT 'Growth6', 0.1425, '10/13/2007' UNION ALL
SELECT 'Growth7', 0.2855, '10/13/2007' UNION ALL
SELECT 'Growth8', 0.1456, '10/13/2007' UNION ALL
SELECT 'Growth9', 0.1114, '10/13/2007'

DECLARE @Date DATETIME
SET @Date = '20071013'

-- Khtan (30 reads)
SELECT c.[Name],
c.[NAV],
Change = p.[NAV] - c.[NAV],
[Change %] = (p.[NAV] - c.[NAV]) * 100 / p.[NAV],
c.[Date]
FROM @Sample c
LEFT JOIN @Sample p ON c.[Name] = p.[Name]
AND c.[Date] - 1 = p.[Date]
WHERE c.[Date] = @Date

-- Peso (only 3 reads)
SELECT [Name],
Today AS [NAV],
Today - Yesterday AS Change,
100.0 * (Today - Yesterday) / Yesterday AS [Change %],
@Date AS [Date]
FROM (
SELECT [Name],
MAX(CASE WHEN [Date] = @Date - 1 THEN [NAV] ELSE 0 END) AS Yesterday,
MAX(CASE WHEN [Date] = @Date THEN [NAV] ELSE 0 END) AS Today
FROM @Sample
GROUP BY [Name]
) AS d[/code]

E 12°55'05.25"
N 56°04'39.16"

vinothrao84
Starting Member

21 Posts

Posted - 2007-10-23 : 04:30:36

hi khtan & peso,

a very impressive sql code you guys given. thank you thank you.

i have run through the code and having some small issues as i was editing the codes too add more column, but seems i having errors.

i will put all the table with its sample data here.

tablename : tblMutFunds

code.......shortname......number......display_web
G1..........Growth1......... 1...............1
G2..........Growth2......... 3...............1
G3..........Growth3......... 5...............1
G4..........Growth4......... 2...............0
G5..........Growth5......... 4...............1
G6..........Growth6......... 8...............1
G7..........Growth7......... 7...............0
G8..........Growth8......... 6...............1
G9..........Growth9......... 9...............0

tablename : tblMutFundPrice

fund_code.......fund_nav.......fund_date
G1...................0.2896.......01/12/2007
G2...................0.4246.......01/12/2007
G3...................0.3794.......01/12/2007
G4...................0.8001.......01/12/2007
G5...................0.4246.......01/12/2007
G6...................0.9347.......01/12/2007
G7...................0.3794.......01/12/2007
G8...................0.2896.......01/12/2007
G9...................0.3971.......01/12/2007
G1...................0.1122.......01/13/2007
G2...................0.4220.......01/13/2007
G3...................0.3770.......01/13/2007
G4...................0.8090.......01/13/2007
G5...................0.4225.......01/13/2007
G6...................0.9471.......01/13/2007
G7...................0.3841.......01/13/2007
G8...................0.2159.......01/13/2007
G9...................0.3111.......01/13/2007

The RESULT i want is....

Number......Fund_Name.......NAV.......Change.....Change%......Date............Display_Web
1...............Growth1.......0.1122.....-0.0050....-1.26......01/13/2007.................1
3...............Growth2.......0.4246.....+0.0004....+0.04......01/13/2007.................1
4...............Growth5.......0.4225.....-0.0059....-1.36......01/13/2007.................1
5...............Growth3.......0.3794.....+0.0004....+0.04......01/13/2007.................1
6...............Growth8.......0.2159.....-0.0080....-1.04......01/13/2007.................1
8...............Growth6.......0.9471.....-0.0037....-0.96......01/13/2007.................1

For the CHANGE and CHANGE% column data are just sample, incorrect data.

Another thing, ONLY records with display_web column value = "1" is display the result. Records value with "0" are not displayed.

Well i hope u guys pls pls help me.....THX.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 05:13:25

What is the problem now?
Why can't you add a WHERE column value = 1 somewhere in the code?

E 12°55'05.25"
N 56°04'39.16"

vinothrao84
Starting Member

21 Posts

Posted - 2007-10-23 : 05:20:21

quote:
Originally posted by Peso

What is the problem now?
Why can't you add a WHERE column value = 1 somewhere in the code?

E 12°55'05.25"
N 56°04'39.16"

I not sure on how to add because the display_web column having in tblMutFunds table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 05:23:15

Can yuo provide some sample data in the format as khtan and I did earlier?

E 12°55'05.25"
N 56°04'39.16"

vinothrao84
Starting Member

21 Posts

Posted - 2007-10-23 : 05:25:29

quote:
Originally posted by Peso

Can yuo provide some sample data in the format as khtan and I did earlier?

E 12°55'05.25"
N 56°04'39.16"

you mean sql statement or the result?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 05:26:19

The DECLARE and INSERT statements.

E 12°55'05.25"
N 56°04'39.16"

vinothrao84
Starting Member

21 Posts

Posted - 2007-10-23 : 05:58:39

quote:
Originally posted by Peso

The DECLARE and INSERT statements.

E 12°55'05.25"
N 56°04'39.16"

DECLARE tblMutFunds TABLE ([Code] VARCHAR(10), [shortname] VARCHAR(20), [number] INT, [display_web] INT)

INSERT tblMutFunds
SELECT 'G1', 'Growth1', 1, 1 UNION ALL
SELECT 'G2', 'Growth2', 3, 1 UNION ALL
SELECT 'G3', 'Growth3', 5, 1 UNION ALL
SELECT 'G4', 'Growth4', 2, 0 UNION ALL
SELECT 'G5', 'Growth5', 4, 1 UNION ALL
SELECT 'G6', 'Growth6', 8, 1 UNION ALL
SELECT 'G7', 'Growth7', 7, 0 UNION ALL
SELECT 'G8', 'Growth8', 6, 1 UNION ALL
SELECT 'G9', 'Growth9', 9, 0 UNION ALL

DECLARE tblMutFundPrice TABLE ([Fund_Code] VARCHAR(10), [Fund_NAV] MONEY, [Fund_Date] DATETIME)

INSERT tblMutFundPrice
SELECT 'G1', 0.2896, '01/12/2007' UNION ALL
SELECT 'G2', 0.4246, '01/12/2007' UNION ALL
SELECT 'G3', 0.3794, '01/12/2007' UNION ALL
SELECT 'G4', 0.8001, '01/12/2007' UNION ALL
SELECT 'G5', 0.4246, '01/12/2007' UNION ALL
SELECT 'G6', 0.9347, '01/12/2007' UNION ALL
SELECT 'G7', 0.3794, '01/12/2007' UNION ALL
SELECT 'G8', 0.2896, '01/12/2007' UNION ALL
SELECT 'G9', 0.3971, '01/12/2007' UNION ALL
SELECT 'G1', 0.1122, '01/13/2007' UNION ALL
SELECT 'G2', 0.4220, '01/13/2007' UNION ALL
SELECT 'G3', 0.3770, '01/13/2007' UNION ALL
SELECT 'G4', 0.8090, '01/13/2007' UNION ALL
SELECT 'G5', 0.4225, '01/13/2007' UNION ALL
SELECT 'G6', 0.9471, '01/13/2007' UNION ALL
SELECT 'G7', 0.3841, '01/13/2007' UNION ALL
SELECT 'G8', 0.2159, '01/13/2007' UNION ALL
SELECT 'G9', 0.3111, '01/13/2007' UNION ALL

Thx.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 06:21:17

Have a look at the part in red because there is where you filter!

-- Prepare sample data
DECLARE	@tblMutFunds TABLE (Code VARCHAR(10), ShortName VARCHAR(20), Number INT, DisplayWeb INT)

INSERT	@tblMutFunds
SELECT	'G1', 'Growth1', 1, 1 UNION ALL
SELECT	'G2', 'Growth2', 3, 1 UNION ALL
SELECT	'G3', 'Growth3', 5, 1 UNION ALL
SELECT	'G4', 'Growth4', 2, 0 UNION ALL
SELECT	'G5', 'Growth5', 4, 1 UNION ALL
SELECT	'G6', 'Growth6', 8, 1 UNION ALL
SELECT	'G7', 'Growth7', 7, 0 UNION ALL
SELECT	'G8', 'Growth8', 6, 1 UNION ALL
SELECT	'G9', 'Growth9', 9, 0

DECLARE	@tblMutFundPrice TABLE (FundCode VARCHAR(10), FundNAV MONEY, FundDate DATETIME)

SET DATEFORMAT MDY

INSERT	@tblMutFundPrice
SELECT	'G1', 0.2896, '01/12/2007' UNION ALL
SELECT	'G2', 0.4246, '01/12/2007' UNION ALL
SELECT	'G3', 0.3794, '01/12/2007' UNION ALL
SELECT	'G4', 0.8001, '01/12/2007' UNION ALL
SELECT	'G5', 0.4246, '01/12/2007' UNION ALL
SELECT	'G6', 0.9347, '01/12/2007' UNION ALL
SELECT	'G7', 0.3794, '01/12/2007' UNION ALL
SELECT	'G8', 0.2896, '01/12/2007' UNION ALL
SELECT	'G9', 0.3971, '01/12/2007' UNION ALL
SELECT	'G1', 0.1122, '01/13/2007' UNION ALL
SELECT	'G2', 0.4220, '01/13/2007' UNION ALL
SELECT	'G3', 0.3770, '01/13/2007' UNION ALL
SELECT	'G4', 0.8090, '01/13/2007' UNION ALL
SELECT	'G5', 0.4225, '01/13/2007' UNION ALL
SELECT	'G6', 0.9471, '01/13/2007' UNION ALL
SELECT	'G7', 0.3841, '01/13/2007' UNION ALL
SELECT	'G8', 0.2159, '01/13/2007' UNION ALL
SELECT	'G9', 0.3111, '01/13/2007'

-- Initialize search parameter
DECLARE	@Date DATETIME
SET @Date = '20070113'

-- Show the expected output
SELECT		d.Number,
		d.ShortName AS FundName,
		Today AS [NAV],
		Today - Yesterday AS Change,
		100.0 * (Today - Yesterday) / Yesterday AS [Change %],
		@Date AS [Date],
		1 AS DisplayWeb
FROM		(
			SELECT		f.Number,
					f.ShortName,
					MAX(CASE WHEN p.FundDate = @Date - 1 THEN p.FundNAV ELSE 0 END) AS Yesterday,
					MAX(CASE WHEN p.FundDate = @Date THEN p.FundNAV ELSE 0 END) AS Today
			FROM		@tblMutFunds AS f
			INNER JOIN	@tblMutFundPrice AS p ON p.FundCode = f.Code
			WHERE		f.DisplayWeb = 1
			GROUP BY	f.Number,
					f.ShortName
		) AS d
ORDER BY	d.Number

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 07:05:01

And here is my version

-- Prepare sample data
DECLARE	@tblMutFunds TABLE (Code varchar(10), ShortName varchar(20), Number int, DisplayWeb int)

INSERT	@tblMutFunds
SELECT	'G1', 'Growth1', 1, 1 UNION ALL
SELECT	'G2', 'Growth2', 3, 1 UNION ALL
SELECT	'G3', 'Growth3', 5, 1 UNION ALL
SELECT	'G4', 'Growth4', 2, 0 UNION ALL
SELECT	'G5', 'Growth5', 4, 1 UNION ALL
SELECT	'G6', 'Growth6', 8, 1 UNION ALL
SELECT	'G7', 'Growth7', 7, 0 UNION ALL
SELECT	'G8', 'Growth8', 6, 1 UNION ALL
SELECT	'G9', 'Growth9', 9, 0

DECLARE	@tblMutFundPrice TABLE (FundCode varchar(10), FundNAV money, FundDate datetime)

SET DATEFORMAT MDY

INSERT	@tblMutFundPrice
SELECT	'G1', 0.2896, '01/12/2007' UNION ALL
SELECT	'G2', 0.4246, '01/12/2007' UNION ALL
SELECT	'G3', 0.3794, '01/12/2007' UNION ALL
SELECT	'G4', 0.8001, '01/12/2007' UNION ALL
SELECT	'G5', 0.4246, '01/12/2007' UNION ALL
SELECT	'G6', 0.9347, '01/12/2007' UNION ALL
SELECT	'G7', 0.3794, '01/12/2007' UNION ALL
SELECT	'G8', 0.2896, '01/12/2007' UNION ALL
SELECT	'G9', 0.3971, '01/12/2007' UNION ALL
SELECT	'G1', 0.1122, '01/13/2007' UNION ALL
SELECT	'G2', 0.4220, '01/13/2007' UNION ALL
SELECT	'G3', 0.3770, '01/13/2007' UNION ALL
SELECT	'G4', 0.8090, '01/13/2007' UNION ALL
SELECT	'G5', 0.4225, '01/13/2007' UNION ALL
SELECT	'G6', 0.9471, '01/13/2007' UNION ALL
SELECT	'G7', 0.3841, '01/13/2007' UNION ALL
SELECT	'G8', 0.2159, '01/13/2007' UNION ALL
SELECT	'G9', 0.3111, '01/13/2007'

-- Initialize search parameter
DECLARE	@Date datetime
SET 	@Date = '20070113'

SELECT	f.[Number],
	f.[ShortName], 
	c.[FundNAV],
	Change 		= p.[FundNAV] - c.[FundNAV],
	[Change %]	= (p.[FundNAV] - c.[FundNAV]) * 100 / p.[FundNAV],
	c.[FundDate],
	f.[DisplayWeb]
FROM	@tblMutFunds f
	INNER JOIN @tblMutFundPrice c
	ON	f.Code	= c.[FundCode]
	LEFT JOIN @tblMutFundPrice p
	ON	c.[FundCode]		= p.[FundCode]
	AND	c.[FundDate] - 1	= p.[FundDate]
WHERE	f.[DisplayWeb]	= 1
AND	c.[FundDate] 	= @Date
ORDER BY f.[Number]

KH
[spoiler]Time is always against us[/spoiler]

vinothrao84
Starting Member

21 Posts

Posted - 2007-10-23 : 22:29:49

quote:

Originally posted by Peso

Have a look at the part in red because there is where you filter!

-- Prepare sample data
DECLARE	@tblMutFunds TABLE (Code VARCHAR(10), ShortName VARCHAR(20), Number INT, DisplayWeb INT)

INSERT	@tblMutFunds
SELECT	'G1', 'Growth1', 1, 1 UNION ALL
SELECT	'G2', 'Growth2', 3, 1 UNION ALL
SELECT	'G3', 'Growth3', 5, 1 UNION ALL
SELECT	'G4', 'Growth4', 2, 0 UNION ALL
SELECT	'G5', 'Growth5', 4, 1 UNION ALL
SELECT	'G6', 'Growth6', 8, 1 UNION ALL
SELECT	'G7', 'Growth7', 7, 0 UNION ALL
SELECT	'G8', 'Growth8', 6, 1 UNION ALL
SELECT	'G9', 'Growth9', 9, 0

DECLARE	@tblMutFundPrice TABLE (FundCode VARCHAR(10), FundNAV MONEY, FundDate DATETIME)

SET DATEFORMAT MDY

INSERT	@tblMutFundPrice
SELECT	'G1', 0.2896, '01/12/2007' UNION ALL
SELECT	'G2', 0.4246, '01/12/2007' UNION ALL
SELECT	'G3', 0.3794, '01/12/2007' UNION ALL
SELECT	'G4', 0.8001, '01/12/2007' UNION ALL
SELECT	'G5', 0.4246, '01/12/2007' UNION ALL
SELECT	'G6', 0.9347, '01/12/2007' UNION ALL
SELECT	'G7', 0.3794, '01/12/2007' UNION ALL
SELECT	'G8', 0.2896, '01/12/2007' UNION ALL
SELECT	'G9', 0.3971, '01/12/2007' UNION ALL
SELECT	'G1', 0.1122, '01/13/2007' UNION ALL
SELECT	'G2', 0.4220, '01/13/2007' UNION ALL
SELECT	'G3', 0.3770, '01/13/2007' UNION ALL
SELECT	'G4', 0.8090, '01/13/2007' UNION ALL
SELECT	'G5', 0.4225, '01/13/2007' UNION ALL
SELECT	'G6', 0.9471, '01/13/2007' UNION ALL
SELECT	'G7', 0.3841, '01/13/2007' UNION ALL
SELECT	'G8', 0.2159, '01/13/2007' UNION ALL
SELECT	'G9', 0.3111, '01/13/2007'

-- Initialize search parameter
DECLARE	@Date DATETIME
SET @Date = '20070113'

-- Show the expected output
SELECT		d.Number,
		d.ShortName AS FundName,
		Today AS [NAV],
		Today - Yesterday AS Change,
		100.0 * (Today - Yesterday) / Yesterday AS [Change %],
		@Date AS [Date],
		1 AS DisplayWeb
FROM		(
			SELECT		f.Number,
					f.ShortName,
					MAX(CASE WHEN p.FundDate = @Date - 1 THEN p.FundNAV ELSE 0 END) AS Yesterday,
					MAX(CASE WHEN p.FundDate = @Date THEN p.FundNAV ELSE 0 END) AS Today
			FROM		@tblMutFunds AS f
			INNER JOIN	@tblMutFundPrice AS p ON p.FundCode = f.Code
			WHERE		f.DisplayWeb = 1
			GROUP BY	f.Number,
					f.ShortName
		) AS d
ORDER BY	d.Number

E 12°55'05.25"
N 56°04'39.16"

Wow.....thank you very much Peso.....it was an advance code after all....never did that kind of statement before....thx a lot.....

vinothrao84
Starting Member

21 Posts

Posted - 2007-10-23 : 22:30:26

quote:

Originally posted by khtan

And here is my version

-- Prepare sample data
DECLARE	@tblMutFunds TABLE (Code varchar(10), ShortName varchar(20), Number int, DisplayWeb int)

INSERT	@tblMutFunds
SELECT	'G1', 'Growth1', 1, 1 UNION ALL
SELECT	'G2', 'Growth2', 3, 1 UNION ALL
SELECT	'G3', 'Growth3', 5, 1 UNION ALL
SELECT	'G4', 'Growth4', 2, 0 UNION ALL
SELECT	'G5', 'Growth5', 4, 1 UNION ALL
SELECT	'G6', 'Growth6', 8, 1 UNION ALL
SELECT	'G7', 'Growth7', 7, 0 UNION ALL
SELECT	'G8', 'Growth8', 6, 1 UNION ALL
SELECT	'G9', 'Growth9', 9, 0

DECLARE	@tblMutFundPrice TABLE (FundCode varchar(10), FundNAV money, FundDate datetime)

SET DATEFORMAT MDY

INSERT	@tblMutFundPrice
SELECT	'G1', 0.2896, '01/12/2007' UNION ALL
SELECT	'G2', 0.4246, '01/12/2007' UNION ALL
SELECT	'G3', 0.3794, '01/12/2007' UNION ALL
SELECT	'G4', 0.8001, '01/12/2007' UNION ALL
SELECT	'G5', 0.4246, '01/12/2007' UNION ALL
SELECT	'G6', 0.9347, '01/12/2007' UNION ALL
SELECT	'G7', 0.3794, '01/12/2007' UNION ALL
SELECT	'G8', 0.2896, '01/12/2007' UNION ALL
SELECT	'G9', 0.3971, '01/12/2007' UNION ALL
SELECT	'G1', 0.1122, '01/13/2007' UNION ALL
SELECT	'G2', 0.4220, '01/13/2007' UNION ALL
SELECT	'G3', 0.3770, '01/13/2007' UNION ALL
SELECT	'G4', 0.8090, '01/13/2007' UNION ALL
SELECT	'G5', 0.4225, '01/13/2007' UNION ALL
SELECT	'G6', 0.9471, '01/13/2007' UNION ALL
SELECT	'G7', 0.3841, '01/13/2007' UNION ALL
SELECT	'G8', 0.2159, '01/13/2007' UNION ALL
SELECT	'G9', 0.3111, '01/13/2007'

-- Initialize search parameter
DECLARE	@Date datetime
SET 	@Date = '20070113'

SELECT	f.[Number],
	f.[ShortName], 
	c.[FundNAV],
	Change 		= p.[FundNAV] - c.[FundNAV],
	[Change %]	= (p.[FundNAV] - c.[FundNAV]) * 100 / p.[FundNAV],
	c.[FundDate],
	f.[DisplayWeb]
FROM	@tblMutFunds f
	INNER JOIN @tblMutFundPrice c
	ON	f.Code	= c.[FundCode]
	LEFT JOIN @tblMutFundPrice p
	ON	c.[FundCode]		= p.[FundCode]
	AND	c.[FundDate] - 1	= p.[FundDate]
WHERE	f.[DisplayWeb]	= 1
AND	c.[FundDate] 	= @Date
ORDER BY f.[Number]

KH
[spoiler]Time is always against us[/spoiler]

Wow.....thank you very much KHTan.....it was an advance code after all....never did that kind of statement before....thx a lot.....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 22:37:31

Duplicate thanks

SELECT 	'Wow.....thank you very much ' + poster + '.....it was an advance code after ALL....never did that kind of statement before....thx a lot.....'
FROM	(SELECT poster = 'Peso' UNION SELECT 'KHTan')p

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 22:38:11

You are most welcome vinoth

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources