April 15, 2004 at 5:09 pm
Your challenge,
The SQL below is in a stored procedure...
it returns all the correct data required...
How can the output be forced to be one recordset and not two
Regards Stan
P.S. Happy to take constructive critisism of code...
P.P.S Have done all the usual searching, but not sure what I am searching for!
--------------------------------------------------------------------------------------------------------
Declare @Transdate datetime
Set @Transdate = getdate()-1
Declare @loopCounter as integer
Set @loopCounter = -10
WHILE @loopcounter < 0
BEGIN
set nocount on
select SNUMBER,dbo.SINFORM.NAME, convert(Datetime,CAST(FLOOR(CAST(@Transdate AS FLOAT))AS DATETIME),106) as RecDate
from dbo.SINFORM
where SNUMBER
not in(select SNUMBER
from dbo.DATARECS
where DATE = CAST(FLOOR(CAST(@Transdate AS FLOAT))AS DATETIME))
order by SNUMBER
Set @loopcounter = @loopcounter + 1
Set @Transdate = @Transdate - 1
END
--------------------------------------------------------------------------------------------------------
Example of current output
-----------------------------
SNUMBER,NAME,RecDate
somedata1,somedata1,somedata1
somedata2,somedata2,somedata2
SNUMBER,NAME,RecDate
somedata3,somedata3,somedata3
somedata4,somedata4,somedata4
somedata5,somedata5,somedata5
-----------------------------
Required output
-----------------------------
SNUMBER,NAME,RecDate
somedata1,somedata1,somedata1
somedata2,somedata2,somedata2
somedata3,somedata3,somedata3
somedata4,somedata4,somedata4
somedata5,somedata5,somedata5
-----------------------------
April 15, 2004 at 5:17 pm
hmmm.. how about loading all primary keys for SINFORM table produced from the WHILE loop into a table variable or temporary table and then join that table with the SINFORM table?
HTH
Billy
April 16, 2004 at 4:45 am
Cross post!
See http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=111549
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 16, 2004 at 5:30 am
I'm probably not getting the point here, but..
Why the loop-thingy at all?
Seems unnecessary to me - just get what you're after within the given daterange - at least that what the example suggests to me...
And what's up with the funky cast date->float->date thing?
What's the purpose of that?
=;o)
/Kenneth
(the lost one)
April 16, 2004 at 6:41 am
Declare @sdate datetime, @edate datetime
Set @edate = CONVERT(varchar(10),GETDATE(),102)
Set @sdate = @edate - 11
select s.SNUMBER, s.NAME, d.[DATE] as RecDate
from dbo.SINFORM s
left outer join dbo.DATARECS d
on d.SNUMBER = s.SNUMBER
and d.[DATE] >= @sdate
and d.[DATE] < @edate
where d.SNUMBER IS NULL
order by s.SNUMBER
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply