April 13, 2012 at 11:59 am
coz while executing sp, i am passing fromdate and todate.
so its taking only that date, in both function
April 13, 2012 at 12:01 pm
hbtkp (4/13/2012)
OK,BASICALLY PITEM AND PITEM4 Calling function fitem and fitem4
now this two function item has data for some fromdate and to date and values
item4 has fromdate,todate and vales,but for all date.
while in item4 function has some date ,that r missing in item function.
so i m trying to get that mising date and based on values to insert in item function.
using
DECLARE @DATE1 DATETIME
SELECT @DATE1 = ThruDate
FROM fitem(@ReportData1)
INSERT INTO fitem4 (@ReportData1)(IRRReal)
SELECT IRR
FROM fitem(@ReportData1) pb
WHERE @DATE1 = pb.FromDate
AND ThruDate = DATEADD(MONTH, 1, @DATE1)
but ite not working
finally exactly what you are trying to do. i think you have a severe missunderstanding of what a user defined function is. here is the BOL article on user defined functions. http://msdn.microsoft.com/en-us/library/aa214363(v=sql.80).aspx read that and if you still think you are doing something that is possible we have a bigger problem.
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 12:02 pm
Again, you cannot insert into a function.
Insert the missing dates into the base table.
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 2:41 pm
ok.
just tell me logic
how can i get if this dates r missing in field
April 13, 2012 at 2:43 pm
hbtkp (4/13/2012)
ok.just tell me logic
how can i get if this dates r missing in field
you need to add the missing dates to the data source. if the dates are not in the tables you can not get them out of the tables. so if you are missing dates make sure they are actually in the table you are pulling from. if they are not you are not really missing them since they are not there to be missed.
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 2:46 pm
Missing in that you have column with dates and you want to see which dates are not represented in the table.
First thing you need for that is a calendar table (http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx, http://www.sqlservercentral.com/articles/T-SQL/70482/)
Once you have that calendar table, it's a trivial outer join between the calendar table and your table that contains dates
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 2:56 pm
ok.
this dates are in 1 udf and i need to add in another udf.
let me explain you.
(1) i am executing sp , and am passing param @todate
(2) now i need to calculate fromdate ,that i m calling 1 udf ,and getting startdate
(3) so i have start and to date.now i need to compare this two with 2 fiels in udf ,which i s from1,start1
if its not their , i need to get it from another udf and put it into this udf.
this is sampel of my sp
create stored procedure sp(
Required parameter----------------
@FromDate datetime,
@ToDate datetime,
declare @ReportData3 varbinary(max)
EXEC ITEM
@ReportData = @ReportData3,
@FromDate = @FromDate,
@ToDate = @ToDate
declare @ReportData1 varbinary(max)
exec item3
@ReportData = @ReportData3,
@ReportData = @ReportData3,
@FromDate = @FromDate,
----------------------
--now this sp calling function whicxh has value function name - fitem and fitem3
i need to compare my fromdate and todate with the field item to and fromdate,if it is missing i need fetch it from fitem3 and put it int fitem.
plz help
@ToDate = @ToDate
April 13, 2012 at 3:21 pm
help me with this code
its not showind date
declare @row1 datetime
set @row1 = @fromYTD
while(@row1 = @Todate)
begin
select thrudate from item3
set @row1 = DATEADD(MONTH , 1 , @row1)
end
plz help me
April 13, 2012 at 3:28 pm
hbtkp (4/13/2012)
ok.this dates are in 1 udf and i need to add in another udf.
Once more with feeling...
You cannot add data to a udf. A udf is just a query against a table, it does not contain data and it cannot have data inserted into it. You need to insert data into the base table that the udf queries or you need to change the udf so that it returns the data that you need,
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:29 pm
hbtkp (4/13/2012)
help me with this codeits not showind date
declare @row1 datetime
set @row1 = @fromYTD
while(@row1 = @Todate)
begin
select thrudate from item3
set @row1 = DATEADD(MONTH , 1 , @row1)
end
What is this supposed to do?
What is it currently doing?
What are the values of @fromYTD and @Todate?
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:32 pm
fromdate suppuse = 10-30-2010
todate = 10 - 30- 2011
i need to check wheather this date is there in item2 function
April 13, 2012 at 3:35 pm
What's the definition of the function item2?
What does it return, a single value or a resultset?
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:37 pm
result set ,and it has 2 fileds (fromdate and todate)
April 13, 2012 at 3:41 pm
simple logic,
suppose i have number between 1 to 10.
i need to check that in 1 table
i have following number
1,2,3,4, 7,8
so s5, 6, 9, 10 are missing in 1 table.
how u will do in while or for loop,same thing i need to do for date
April 13, 2012 at 3:43 pm
Okay, some may think I will have crossed a line, but here it goes, the best advice I can give you.
Go to your boss and ask for help. You are obviously in over your head and you are expecting (no, more like demanding) that we solve your problem without any assistance from you in any way.
Your boss is in a much better position to provide you the assistance you need because he (or she) has access to everything you do.
Viewing 15 posts - 16 through 30 (of 87 total)
You must be logged in to reply to this topic. Login to reply