April 15, 2004 at 5:12 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 16, 2004 at 2:14 am
Easy, stick all of the data into a temp table (but you'll need to "create" table first and then use INSERT and then report from that table before dropping it.
He shoots! He misses?
April 16, 2004 at 4:44 am
Do this homework by yourself!
Take a look at DATEDIFF in BOL and see if you can find out yourself and avoid these annoying cross posts!
--
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 6:43 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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply