April 12, 2012 at 11:06 am
i have one sp(sp1) which return values for the specific periods
now i have another sp (sp2),which has all the values for all the periods.
now what i ahve to do it for missing periods in sp1, i need to get values from sp 2.
i need to do it in third sp3.how to check for what priods is missing and get values
April 12, 2012 at 11:10 am
hbtkp (4/12/2012)
i have one sp(sp1) which return values for the specific periodsnow i have another sp (sp2),which has all the values for all the periods.
now what i ahve to do it for missing periods in sp1, i need to get values from sp 2.
i need to do it in third sp3.how to check for what priods is missing and get values
Yes No Maby So ?? again we need CREATE TABLE statements, sample data and in this case the code for the stored procedures.
and again the link in my signature has all the proper ways we like to see those requested items.
and if this is a duplicate post again you will get the same request for information.
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 12, 2012 at 10:56 pm
April 13, 2012 at 8:42 am
fine.
i am giving sample data.
i have 1 stored prc sp1,which has table values function ,so its return table.
output.
fromdate todate value
06/30/2009 07/30/2009 12.30
7/30/2009 08/30/2009 4.5
8/30/2009 09/30/2009 5.6
now i have another sp2
fromdate todate value
06/30/2009 07/30/2009 12.30
7/30/2009 08/30/2009 4.5
8/30/2009 09/30/2009 5.6
9/30/2009 10/30/2009 5.6
now see in sp1 this(9/30/2009 10/30/2009 5.6) is missing.
i need to add this thing and i need to make it dynamic.
i have third sp3, in which i need to implement this logic.
now in my sp3 i am execute like this
exec sp3
fromdate = ' '
todate = ' '
i need to check in sp3 whatever values are missing in sp1.
if i am check using fromdate , to date its not working. need to check whatever dates are missing between them and then i need to add values
plz help me
April 13, 2012 at 9:07 am
you have been asked and asked. if you cant read the link in my signature for how we like to see the sample data and table scripts after being given that link several times by my self and others im not even going to look at your sample data and try to format it into something i can use in SSMS to test on. make it easy for us to help you and you will get your answers much quicker.
i also asked for the code for the stored procedures. with out that i cant help you.
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 10:08 am
Your design is fundamentally flawed. You're trying to use your stored procedure like a function. Here is what Microsoft has to say on the issue.
Stored procedures are different from functions in that they do not return values in place of their names and they cannot be used directly in an expression.
You need to rethink your design. We can help you with that, but we need the stored proc and table definitions. Given your refusal to provide such in numerous other threads, it seems that we've reached a deadlock and your issue has been chosen as the deadlock victim.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 13, 2012 at 11:23 am
it snot my design,this is how my database is working
i m just employee ,i dotn have right to change anything
so plz help
April 13, 2012 at 11:27 am
hbtkp (4/13/2012)
it snot my design,this is how my database is workingi m just employee ,i dotn have right to change anything
so plz help
ok so help us help you work within your limitations and post the data we ask for. we all understand politics and limitations to what we can change and do. but if you dont give us what we need to help you we cant help you.
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 11:28 am
I think if you READ some of the comments and provide the correct data, we might be able to help. Just asking for help won't get you any unless you help us help you.:-)
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 13, 2012 at 11:31 am
i think i will lost my job , i am on dead line
i m trying to help u as much as possible.
there is nothing in sp, sp calling udf inside and getting table in result
i already told you how i execute my sp.
my prob is how can i check, it date is missing between two dates ,
April 13, 2012 at 11:35 am
hbtkp (4/13/2012)
i think i will lost my job , i am on dead linei m trying to help u as much as possible.
there is nothing in sp, sp calling udf inside and getting table in result
i already told you how i execute my sp.
my prob is how can i check, it date is missing between two dates ,
thats a good question and with out being able to see what you are looking at we have no idea what you mean.
right now you are asking the equilivent of the following question:
i need you to solve for x, y=5 and z=9
in order to solve it we need to know context which means DDL and sample data and if we are working with a stored procedure we need the code of the stored procedure. with out any of that we cant help you.
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 11:43 am
CREATE procedure [pPerformance1]
------Required parameter----------------
@FromDate datetime,
@ToDate datetime,
begin
declare @ReportData3 varbinary(max)
exec [pitem]
---------Required Parameter--------------
@ReportData = @ReportData3 out,
@FromDate = @FromDate,
@ToDate = @ToDate ,
declare @ReportData1 varbinary(max)
exec [pitem4]
--------required parameter--------------
@ReportData = @ReportData1 out,
@FromDate = @FromDate,
@ToDate = @ToDate
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)
--
-- end
--GO
--EXEC pPerformance1
-- @ReportData = @ReportData1 out,
-- @FromDate = '06/30/2009',
-- @ToDate = '12/31/2011',
---------------------------------------------
ok. i paste my code
this two sp calling function that starts with f.
now ge tme solution
April 13, 2012 at 11:51 am
hbtkp (4/13/2012)
now get me solution
Wow. um no. considering i still dont have all the info i requested in a nice readable format and now you seem to be demanding an answer?? might be a culture break down but im done.
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 11:53 am
You can't insert into a function as you're trying here
INSERT INTO fitem4 (@ReportData1)(IRRReal)
SELECT IRR FROM fitem(@ReportData1)pb WHERE @DATE1 = pb.FromDate
and ThruDate = DATEADD(MONTH,1,@DATE1)
Your code has so many syntax errors I don't know where to start. There's commas there there shouldn't be, there's a missing end and there's the insert problem I mentioned above
CREATE PROCEDURE [pPerformance1]
------Required parameter----------------
@FromDate DATETIME ,
@ToDate DATETIME,
AS
BEGIN
DECLARE @ReportData3 VARBINARY(MAX)
EXEC [pitem] @ReportData = @ReportData3 OUT, @FromDate = @FromDate, @ToDate = @ToDate
DECLARE @ReportData1 VARBINARY(MAX)
EXEC [pitem4] @ReportData = @ReportData1 OUT, @FromDate = @FromDate, @ToDate = @ToDate
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)
-- END
--GO
So, can you explain clearly what it is you are trying to do?
Can you post the definitions of the procedures pitem and pitem4 and of the functions fitem and fitem4?
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 11:57 am
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
Viewing 15 posts - 1 through 15 (of 87 total)
You must be logged in to reply to this topic. Login to reply