April 13, 2012 at 3:45 pm
he just told me i cant help u with code,so i need help from you,
i already provide sample data and everything.
company's data are private , i cannot put it in web
April 13, 2012 at 3:46 pm
hbtkp (4/13/2012)
simple logic,
I did not ask for 'simple logic', I asked for the definition of the function. As in the CREATE FUNCTION statement.
Also, what do you want to do if the date is found?
What do you want to do if the date is not found?
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
April 13, 2012 at 3:50 pm
create function function_name(@returndata [varbinary](max))
return table
field1 dattype,
field2 datatype
)
April 13, 2012 at 3:51 pm
if i didint found ,i need fetch from another function and put into this function
April 13, 2012 at 3:57 pm
hbtkp (4/13/2012)
create function function_name(@returndata [varbinary](max))return table
field1 dattype,
field2 datatype
)
That is not a function definition.
if i didint found ,i need fetch from another function and put into this function
For the 3rd time, you CANNOT add data to a function, you add data to a table and without seeing the actual and complete definition of the function I cannot assist you any further.
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
April 13, 2012 at 3:58 pm
hbtkp (4/13/2012)
he just told me i cant help u with code,so i need help from you,i already provide sample data and everything.
company's data are private , i cannot put it in web
Well, I think it is time to walk away. You refuse to read and follow an article that would step you through exactly the steps needed to post the information we need from you to help you.
We don't expected you to post live company data, but we do expect you to know the data you are working with well enough to create sample data the is representative of your companies data.
Ask yourself, if I really want help from these volunteers (we aren't getting paid byanyone to help you succeed at the job you are getting paid to do), what to I need to do to help them help me? Because, right now you haven't done anything to help us help you.
April 13, 2012 at 4:08 pm
at least help me with my code
how to check while loop and increment date by 1 month in while loop
April 13, 2012 at 4:11 pm
hbtkp (4/13/2012)
at least help me with my codehow to check while loop and increment date by 1 month in while loop
we have no code to help you with, you have not posted it. also you probably do not need a while loop but we cant tell since you have to yet post code that makes any sense.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 13, 2012 at 4:14 pm
hbtkp (4/13/2012)
how to check while loop and increment date by 1 month in while loop
The code you posted does exactly that.
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
April 13, 2012 at 6:12 pm
its not working as ,its not going thru loop.
and doesn not get anything from select statement, i need to increament date by 1 month.
it does not match the condition as well
April 13, 2012 at 8:07 pm
hbtkp (4/13/2012)
its not working as ,its not going thru loop.and doesn not get anything from select statement, i need to increament date by 1 month.
it does not match the condition as well
No kidding Sherlock. How long have you been working with MS SQL Server and T-SQL?
Here is your code with the WHILE statement:
declare @row1 datetime
set @row1 = @fromYTD
while(@row1 = @Todate)
begin
select thrudate from item3
set @row1 = DATEADD(MONTH , 1 , @row1)
end
Unless the value entered for @fromYTD equals the value for @Todate, you will NEVER enter your loop. You are only in the loop as long as the condition evaluates to TRUE.
April 13, 2012 at 8:10 pm
i m fresher in sql,then how to do this ,if nto with while
give me example
April 13, 2012 at 8:20 pm
hbtkp (4/13/2012)
i m fresher in sql,then how to do this ,if nto with whilegive me example
No, I'm not. I want you to actually do some real reading and research. Open up Books Online and search for WHILE. Read what you find.
April 13, 2012 at 8:28 pm
hbtkp (4/13/2012)
i m fresher in sql,then how to do this ,if nto with whilegive me example
Of course, if you took the time to read that article everyone keeps tell you to read and posted the information we keep asking you to provide we could provide you with much BETTER answers. So how about reading that article and provide us with the DDL for the tables, sample data, the code (and all of the code, not little pieces here and there that are meaningless), and maybe you'd finally get the help you are so desperately seeking.
April 13, 2012 at 9:56 pm
i am doing my best now.
CREATE procedure Dashboard
------Required parameter----------------
@FromDate datetime,
@ToDate datetime,
@Date datetime,
as
begin
declare @ReportData varbinary(max)
exec item
---------required parameter------------------
@ReportData = @ReportData out,
@FromDate = @ToDate,
@ToDate = @ToDate,
declare @ReportData2 varbinary(max)
exec item1
@ReportData = @ReportData2 out,
@Date = @date,
declare @ReportData1 varbinary(max)
exec item2
--------required parameter--------------
@ReportData = @ReportData1 out,
@FromDate = @FromDate,
@ToDate = @ToDate,
declare @my_var int
declare @ReportData3 varbinary(max)
exec [APXUser].[item3]
---------Required Parameter--------------
@ReportData = @ReportData3 out,
@FromDate = @FromDate,
@ToDate = @ToDate
create table #temp1(name nvarchar(72),
account nvarchar(32),
IDate date,
"Yesterday's Chg" float,
MTD float,
MV float)
insert into #temp1
select DISTINCT vpo.Name, Account ,phd.IDate, YTD = ph.Dtd,
MTD = case when ph.MTD is null then (exp(sum(cast(log(CASE WHEN phd.I1 = 0.0 THEN 1 WHEN phd.I1 IS NULL THEN 1 WHEN phd.I1 = -100 THEN 1
ELSE 1+phd.I1/100.0 END ) as float)/100) )-1.0)* 10000
else ph.MTD
end,
sum(distinct(fa.MV)) as MV
from
dbo.fitem2(@Reportdata1) phd
left join fitem3 VP
ON VP.BaseID = phd.BaseID
left join fitem1 (@ReportData2) fa on fa.BaseID = phd.BaseID
left join vBase pb on pb.BaseCode = VP.BaseCode
left join vport vpo on vpo.ID = phd.ID
left join APXuser.fitem(@ReportData) ph on ph.BaseID = phd.BaseID
select @my_var = COUNT(*) from #temp1
if (@my_var = 0)
select DISTINCT vpo.Name,
Account ,
' ' as IDate,
' ' as "YTD",
' ' as " MTD",
' ' as mv
from dbo.fitem2(@Reportdata1) phd
left join fitem3 VP
ON VP.BaseID = phd.BaseID
left join fitem1 (@ReportData2) fa on fa.BaseID = phd.BaseID
left join vBase pb on pb.BaseCode = VP.BaseCode
left join vport vpo on vpo.ID = phd.ID
left join APXuser.fitem(@ReportData) ph on ph.BaseID = phd.BaseI
else
select * from #temp1
end
i am executing this sp like this
exec Dashboard
@fromdate = '09/30/2010'
@todate = '12/31/2011'
@date = '12/31/2011'
----------------------------------------------------
this code works fine.
now see
MTD = case when ph.MTD is null then (exp(sum(cast(log(CASE WHEN phd.I1 = 0.0 THEN 1 WHEN phd.I1 IS NULL THEN 1 WHEN phd.I1 = -100 THEN 1
ELSE 1+phd.I1/100.0 END ) as float)/100) )-1.0)* 10000
else ph.MTD
end,
here i am using 1 formula, in which i am using I1(which i am getting from fitem2),now this I1 is value from ' 09/30/2010 to 12/30/2011',
so ite like this
9/30/2010 10/30/2010 I1 = 10
10/30/2010 11/30/2010 I1 = 15
11/30/2010 12/31/2010 i1 -----------missing period
so this period is missing in fitem2,
but htis period is there in fitem3, so i need to find out which period is missing , and their I1 value i need to add in 1 table. and the this I1 value i need t add in
MTD formula , so i will get total I1.
todate is what i am passing while executing my sp.but fromdate i need to calulate from 1 udf which is like this
create FUNCTION [fGetDate](@dateName [dbo].[name32], @asOfDate [datetime])
RETURNS [datetime] WITH EXECUTE AS OWNER
AS
EXTERNAL NAME [SqlClrUtil]
so for example, i need to have start dATE for MTD, so what i will pass here is select [fgetdate], so todate here i am passing is 12/31/2011,this function will give me
11/30/2011 , and for ytd it will give me 12/31/2010
so i have startdate and todate for MTD and YTD for for fitem3.
now i need to see if this startdate and todate , is there in fitem2 , if not i need to get check from item 3 and if it is there i need get their i1 value
and add to this formula(which is for MTD)
help plzzzzzzzzzzzzzzzzz
Viewing 15 posts - 31 through 45 (of 87 total)
You must be logged in to reply to this topic. Login to reply