April 14, 2012 at 3:44 am
declare @row1 datetime
set @row1 = @fromYTD
while(@row1 < @Todate)
begin
select thrudate from item3
set @row1 = DATEADD(MONTH , 1 , @row1)
IF (<whatever the date to be checked is> NOT IN (SELECT <date column> FROM <some other function>) AND (<whatever the date to be checked is> IN (SELECT <date column> FROM <third function>) -- if the date is not returned by the fist function but is returned from the second function
INSERT INTO <whatever the base table name is> (<whatever the column name is>)
VALUES (@Row1)
end
That's the best I can do as the requirement is still vague and the required table names are no where to be found. You'll have to put the actual function names and actual table names in yourself.
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 14, 2012 at 6:55 am
thanks,but i need to check till @row1 = todate,can i use <=.
and for this thing
IF (<whatever the date to be checked is> NOT IN (SELECT <date column> FROM <some other function>) AND (<whatever the date to be checked is> IN (SELECT <date column> FROM <third function>) -- if the date is not returned by the fist function but is returned from the second function
INSERT INTO <whatever the base table name is> (<whatever the column name is>)
VALUES (@Row1)
i need to check like this , IF (@row1 not in (select fromdate from fitem2(@Reportdata1) then
i need to insert in fitem3,but as it is function,i cannot insert into it.so what i need to do,can i create temp table and then insert into function
April 14, 2012 at 7:11 am
You have provided code, now you need to provide the ddl to create the tables, sample data to load into those tables, and the expected results based on the sample data.
We are still shooting in the dark without that information.
April 14, 2012 at 7:20 am
what DDL u talkign abotu i didnt get it.
i need to insert this I1 values into udf,as i cannot ,i need to create temp table with all the the fields that udf has and then i need to insert into udf. or i can create temp table with just one filed I1 and i can add that to my formula.
the excepted result will give you only I1 values of the missing periods
April 14, 2012 at 7:29 am
hbtkp (4/14/2012)
i need to insert in fitem3,but as it is function,i cannot insert into it.so what i need to do,can i create temp table and then insert into function
No, there is no way at all you can insert into a function, you have to insert into the base table that the function queries.
As I said
INSERT INTO <whatever the base table name is> (<whatever the column name is>)
VALUES (@Row1)
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 14, 2012 at 7:42 am
my function doesnt have base table
it simplylike this
create function name(@Reportdate1)
return table
(
column1 datatypr,
column2 datatype)
April 14, 2012 at 7:53 am
You still haven't given us everything we need to help you. Sorry, but you are on your own until you do.
Again read the article we keep telling you read and post everything we need to be able to help you.
April 14, 2012 at 7:55 am
hbtkp (4/14/2012)
my function doesnt have base tableit simplylike this
create function name(@Reportdate1)
return table
(
column1 datatypr,
column2 datatype)
Not possible. That is not a complete function definition, the data that the function returns has to come from somewhere and functions do not store data themselves.
A function definition would be either
create function name(@Reportdate1)
return @Result table
(
column1 datatypr,
column2 datatype)
AS
BEGIN
--- function definition here
END
or
create function name(@Reportdate1)
return @Result table
(
column1 datatypr,
column2 datatype)
AS
EXTERNAL NAME <NAME OF CLR class>
If it's a CLR function you'll have to check the .NET code to see where the function gets its data. Or ask your boss or colleagues, one of them has to know
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 14, 2012 at 7:59 am
ok. i have second function wih external name.
now what i have to do
April 14, 2012 at 8:13 am
GilaMonster (4/14/2012)
If it's a CLR function you'll have to check the .NET code to see where the function gets its data. Or ask your boss or colleagues, one of them has to know
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 14, 2012 at 8:16 am
ok.thanks a lot,i will get back,once i am done with all this code.
thank u guys for your support
April 14, 2012 at 9:38 pm
ok. i hav tried this code
declare @row1 datetime
set @row1 = @fromYTD
while(@row1 <= @Todate)
begin
if ( @row1 not in(select fromdate from item2(@ReportData1)))
insert into #temp4
select I1 from item3(@Reportdata3) where fromdate = @row1
set @row1 = DATEADD(MONTH , 1 , @row1)
end
select * from #temp4
i am getting nothing in my result.its not going into loop. should @row1 , @Todate should be in same format liek mm-dd-yyyy.
i am not sure its same format or not,Is this one causing error
April 14, 2012 at 9:45 pm
They should both be definded as datetime or date datatype.
April 14, 2012 at 9:47 pm
they both are datetime.
how to compare two dates with <=
April 14, 2012 at 9:50 pm
i am passing todate like this @ToDate = '12/31/2011',
and fromdate is getting liek this 2010-12-31 00:00:00.000
is this a problem?
Viewing 15 posts - 46 through 60 (of 87 total)
You must be logged in to reply to this topic. Login to reply