A simple one ?

Question

A simple one ?

Stan-32552

Valued Member

Points: 70
More actions
April 15, 2004 at 5:09 pm

#107963

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
-----------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

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

bp SSCarpal Tunnel Points: 4656 More actions · Answer 1

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Cross post!

See http://www.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]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

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)

David Burrows SSC Guru Points: 65142 More actions · Answer 4

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.