September 23, 2011 at 12:45 am
I want to generate SSRS report for that i need to select
3 values PreviousNAV, TodaysNAV ,(TodaysNAV - PreviousNAV)
now i can get these values using following queries but i want to use
table alias for more simplicity please help..
SELECT ISNULL(@PreviousNAV,0) PreviousNAV
FROM Fireball..NAV
WHERE Date = @StartDate and PortfolioId = @PortfolioId
SELECT ISNULL(@TodaysNAV,0) TodaysNAV
FROM Fireball..NAV
WHERE Date = @EndDate and PortfolioId = @PortfolioId
SELECT (TodaysNAV - PreviousNAV) -- please correct if if i'm wrong here
September 23, 2011 at 1:33 am
Not sure why you think a table alias will simplify things for you, can you explain more?
Some general ideas you may want to consider..
The AS keyword before an alias name is syntactically optional but in my view aids readability and also helps protect against inadvertently missing a comma in the list of columns, it can be very easy to mean to type two column names, and instead get a query that treats what should have been a column name as a column alias. I really wish AS had been made mandatory, after all it does not take long to type.
Calculating the difference can easily be done as an expression in the report, so you could just return the two components form the query and do the sums in SSRS. Makes little difference in this case I suspect.
Mike John
September 23, 2011 at 2:01 am
Given that both queries return exactly one row, you have two options:
1) Use variables:
DECLARE @PreviousNAV decimal(21,6)
DECLARE @TodaysNAV decimal(21,6)
SELECT @PreviousNAV = ISNULL(@PreviousNAV,0) PreviousNAV
FROM Fireball..NAV
WHERE Date = @StartDate and PortfolioId = @PortfolioId
SELECT @TodaysNAV = ISNULL(@TodaysNAV,0) TodaysNAV
FROM Fireball..NAV
WHERE Date = @EndDate and PortfolioId = @PortfolioId
SELECT (@TodaysNAV - @PreviousNAV)
2) Use a CTE:
WITH Previous AS (
SELECT ISNULL(@PreviousNAV,0) PreviousNAV
FROM Fireball..NAV
WHERE Date = @StartDate and PortfolioId = @PortfolioId
),
Todays AS (
SELECT ISNULL(@TodaysNAV,0) TodaysNAV
FROM Fireball..NAV
WHERE Date = @EndDate and PortfolioId = @PortfolioId
)
SELECT (TodaysNAV - PreviousNAV)
FROM Previous
CROSS JOIN Todays
You have more options indeed, but I think these are the most simple ones IMHO.
If your queries return multiple rows, please define join criteria and I'll try to help.
-- Gianluca Sartori
September 23, 2011 at 3:01 am
hi
i have added dataset into my ssrs report SP but now..
problem is
only 'PreviousNAV' field is coming ...
because
my SP having select queries like this..
SELECT ISNULL(NAV,0) PreviousNAV
FROM Fireball..NAV
WHERE Date = @StartDate and PortfolioId = @PortfolioId
SELECT ISNULL(NAV,0) TodaysNAV
FROM Fireball..NAV
WHERE Date = @EndDate and PortfolioId = @PortfolioId
please let me know how can i use table alias so that i got both 2 fields??
September 23, 2011 at 7:23 am
ashuthinks (9/23/2011)
hii have added dataset into my ssrs report SP but now..
problem is
only 'PreviousNAV' field is coming ...
because
my SP having select queries like this..
SELECT ISNULL(NAV,0) PreviousNAV
FROM Fireball..NAV
WHERE Date = @StartDate and PortfolioId = @PortfolioId
SELECT ISNULL(NAV,0) TodaysNAV
FROM Fireball..NAV
WHERE Date = @EndDate and PortfolioId = @PortfolioId
please let me know how can i use table alias so that i got both 2 fields??
A table alias won't help. If you want both columns, select them both.
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply