February 5, 2014 at 8:09 am
Hello,
I am creating an SSRS report for that I written a stored procedure where it describes the total amount based on ReimTypeid's
The expected output was I need all the three amounts in one line like single dataset so that I can drag these fields easily into my report
amnt1 amnt2 amnt3
$125.3 $3566.5 $21.8
But when I execute the below procedure I am getting output as
amnt1
$21.8
$3566.5
Alter procedure GrandTotal
@year varchar(4),
@Systemid varchar(10)
AS
BEGIN
Select AMOUNT as amnt1 from Grand g Inner join request r on g.grandsystemid = r.grandsystemid
inner join Invoice I on r.reqid = I.reqid
where g.year = @year and g.systemid =@systemid and I.ReimTypeid IN (34,35)
group by year,g.systemid
UNION
Select AMOUNT as amnt2 from Grand g Inner join request r on g.grandsystemid = r.grandsystemid
inner join Invoice I on r.reqid = I.reqid
where g.year = @year and g.systemid =@systemid and I.ReimTypeid IN (34,35,36)
group by year,g.systemid
UNION
Select AMOUNT as amnt2 from Grand g Inner join request r on g.grandsystemid = r.grandsystemid
inner join Invoice I on r.reqid = I.reqid
where g.year = @year and g.systemid =@systemid and I.ReimTypeid IN (37,38,53)
group by year,g.systemid
END
Will anyone help me how I can get these amounts like amnt1,amnt2,amnt3 into a single line ..
February 5, 2014 at 9:06 am
Your query looks all wrong for one thing, you are using group by with no aggregate so I assume you want to SUM(AMOUNT) for different conditions. And you really don;t want UNION for this case. If I have quessed what you are wanting to do correctly you can try this:
Select amnt1 = sum(case when I.ReimTypeid IN (34,35) then AMOUNT else 0 end),
amnt2 = sum(case when I.ReimTypeid IN (34,35,36) then AMOUNT else 0 end),
amnt3 = sum(case when I.ReimTypeid IN (37,38,53) then AMOUNT else 0 end)
from Grand g Inner join request r on g.grandsystemid = r.grandsystemid
inner join Invoice I on r.reqid = I.reqid and I.ReimTypeid IN (34,35,36,37,38,53)
where g.year = @year and g.systemid =@systemid
group by year,g.systemid
The probability of survival is inversely proportional to the angle of arrival.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply