|
Emile
Starting Member
South Africa
5 Posts |
 Posted - 10/23/2012 : 07:07:02
|
hello?
I'm very new to T-SQL, I work part time as a programmer.
I have difficulty in adapting a stored proc to recieve a parameter from an exact replicant of itself.
This is the initial stored proc :
alter procedure mlmMeterUsageStats
@Action int=0
as
begin
set NOCOUNT on
declare
@Tot int,
@Date datetime
set @Date = substring(convert(varchar(10),getdate(),111),1,8) + '01'
select @Tot = count(*)
from mlmMeter
where TypeId = 0
and Disabled = 0
select CalcDate, left(datename(month,CalcDate),3) + ' ' + right(cast(year(CalcDate) as varchar(10)),2) as Descr,
sum(case when IsEstimate=0 then 1 else 0 end) as Actual,
sum(case when IsEstimate=1 and NoteCode >= 0 then 1 else 0 end) as Est,
sum(case when IsEstimate=1 and NoteCode < 0 then 1 else 0 end) as Calc,
@Tot-count(*) as NoRead, convert(varchar(10),getdate(),111) as TheDate, @Tot as Tot,
'<tr><td><b>' + left(datename(month,CalcDate),3) + ' ' + right(cast(year(CalcDate) as varchar(10)),2) +
'</td><td bgcolor="green" colspan="' + cast(sum(case when IsEstimate=0 then 1 else 0 end)*100/@Tot as varchar(10)) + '"><br></td><td bgcolor="red" colspan="' + cast(sum(case when IsEstimate=1 and NoteCode >= 0 then 1 else 0 end)*100/@Tot as varchar(10)) + '"><br></td><td bgcolor="yellow" colspan="' + cast(sum(case when IsEstimate=1 and NoteCode < 0 then 1 else 0 end)*100/@Tot as varchar(10)) + '"><br></td><td bgcolor="black" colspan="' + cast(100 - (sum(case when IsEstimate=1 and NoteCode < 0 then 1 else 0 end)*100/@Tot) - (sum(case when IsEstimate=0 then 1 else 0 end)*100/@Tot) - (sum(case when IsEstimate=1 and NoteCode < 0 then 1 else 0 end)*100/@Tot) as varchar(10)) + '"><br></td></tr>'
as HTML
from mlmMeter M
join mlmMeterUsage U
on M.MeterId = U.MeterId
and M.Disabled = 0
and M.TypeId = 0
and U.CalcDate >= dateadd(month,case when @Action = 0 then -11 else -2 end,@Date)
group by CalcDate, left(datename(month,CalcDate),3) + ' ' + right(cast(year(CalcDate) as varchar(10)),2)
order by CalcDate desc
return 0
end
Then I need to create a proc that will recieve the CalcDate |
|
Stored Proc
