September 20, 2011 at 2:41 pm
daveriya (9/20/2011)
this function is the only my problem,nothing else
No your problem is that you ask for help and are not willing to give us what we need to give you a fully tested and working answer.
We don't see nor have access to what you see so you need to help us see that.
Otherwise, good luck with solving your own problem on your own.
September 20, 2011 at 8:04 pm
hi
this is my sample script.plz get me solution
CREATE PROCEDURE abc
(
@id varchar(15),
@vpa ='a08'
)
WITH RECOMPILE AS
BEGIN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Log 1 standard again
DECLARE
@Date_rp DATETIME,
SELECT @Date_rp = AS_OF_DATE,
FROM RunInteger
WHERE RUN_ID = @id
DECLARE SHIFTID varchar(max)
set SHIFTID = @rpSCENARIO_BASE+','+@rpSCENARIO_DN_MAX+','+@rpSCENARIO_UP_MAX
DECLARE validation_id varchar(50)
set @validation_id = 'SCN1'
select string as stvID into #stvID FROM dbo.fConvertCommaSeperatedString2Table (@parm_SHIFTID)
select string as EPA into #EPA FROM dbo.fConvertCommaSeperatedString2Table (@parm_EPA)
SELECT
a.id,
a.STVSHIFTID,
a.EXCEPT,vc.acct ,
a..Year,
SUM(ISNULL(a.INCOME,0)) AS CURR_INCOME,
sum( SUM(ISNULL(a.INCOME,0))) over() as total,
cast(round((datediff(day,@Date_rp,vc.acct_prd)/365.00),2) as decimal(10,2)) as datediff
into #customer1
from acc_cu a
left outer join accu_pu p
on a.ID = p.ID
and a.EPA = p.EPA
and a.ASSETCLASS = p.ASSETCLASS
and a.STVSHIFTID = p.STVSHIFTID
and a.ACPORTEXCEPT = p.ACPORTEXCEPT
and dbo.ufn_AddPeriod (a.id , a.ACCT_PRD, -1) = p.ACCT
inner join #stvID s
on a.STVSHIFTID = s.stvID
inner join #EPA e
on a.EPA = e.EPA
where a.id in (@id)
group by a.run_id ,a.STVSHIFTID, a.ACPortExcept, a.Year ,a.EPA,a.acct
ORDER BY a.id,
a.STVSHIFTID,
a.ACPortExcept, a.Year ,a.acct
if (@validation_id='SCN1' )
select
id, ACPORTEXCEPT, Val_Order,
CASE WHEN Val_Order=1 THEN 'Value'
WHEN Val_Order=2 THEN 'Min '
W1,
from
(
select run_id, ACPORTEXCEPT, Val_Order,
W1,
from (
select
run_id, ACPORTEXCEPT, 1 AS Val_Order,
SUM((CURR_INCOME/ total)* datediff) as W1,
from
#analytics1
WHERE ACPORTEXCEPT <> 'IRD' AND SHIFTID='001'
group by id, ACPORTEXCEPT ) A
inner join rpt r
on a.id = r.run_descr
AND a.ACPORTEXCEPT = r.level
where r.run_descr_ =@id and r.measure<>'IRR' and r.validation_id=@param_validation_id
group by a.run_id, a.ACPORTEXCEPT ) a
order by 1,2,3
To do W1 ,i used total which is not working,if i create UDF for total then its working but taking time.
so plz suggest me right way
September 21, 2011 at 12:59 am
Once more with feeling...
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2011 at 5:12 am
As requested 5 times already we also need sample data (insert statements).
This can generate thos for you in 2 sec.
September 21, 2011 at 6:48 am
We are still shooting in the dark. Everything we are asking you to provide is really needed in order to help you. Please helps us help you.
September 21, 2011 at 6:56 pm
Thanks everybody for response, there was some error in table. i got fix that
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply