fetching date from 1 sp to another

Question

Post reply

fetching date from 1 sp to another

Viewing 15 posts - 16 through 30 (of 87 total)

You must be logged in to reply to this topic. Login to reply

hbtkp SSCommitted Points: 1731 More actions · Answer 1

coz while executing sp, i am passing fromdate and todate.

so its taking only that date, in both function

CapnHector SSCoach Points: 16143 More actions · Answer 2

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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

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]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hbtkp SSCommitted Points: 1731 More actions · Answer 4

ok.

just tell me logic

how can i get if this dates r missing in field

CapnHector SSCoach Points: 16143 More actions · Answer 5

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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

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]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

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://qa.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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hbtkp SSCommitted Points: 1731 More actions · Answer 7

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,

@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

hbtkp SSCommitted Points: 1731 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 10

hbtkp (4/13/2012)
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

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hbtkp SSCommitted Points: 1731 More actions · Answer 11

fromdate suppuse = 10-30-2010

todate = 10 - 30- 2011

i need to check wheather this date is there in item2 function

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hbtkp SSCommitted Points: 1731 More actions · Answer 13

result set ,and it has 2 fileds (fromdate and todate)

hbtkp SSCommitted Points: 1731 More actions · Answer 14

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 15

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.