Forcing continuous output from Stored Proc
-
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
-----------------------------
-
I'm no expert, but why aren't you using a between statement when grabbing the date? In my mind it should look something like below. Note that I have returned [DATE] in the select clause rather than @Transdate.
Declaring @Transdate at the start is good, just in case the procedure is triggered on the last tick of the day.
Declare @Transdate datetime
Set @Transdate = getdate()-1
select
SNUMBER,
dbo.SINFORM.NAME,
convert( Datetime, CAST( FLOOR( CAST( [DATE] AS FLOAT ) ) AS DATETIME ),106 ) as RecDate
from dbo.SINFORM
where SNUMBER
not in
(
select
SNUMBER
from dbo.DATARECS
where [DATE]
BETWEEN CAST( FLOOR( CAST( DATEADD( day, -10, @Transdate ) AS FLOAT ) ) AS DATETIME )
AND CAST( FLOOR( CAST( @Transdate AS FLOAT ) ) AS DATETIME )
)
order by SNUMBER
-
In some cases you might use UNION to get 2 recordsets together
Sample usage:
select au_id, au_lname, au_fname from authors where au_lname='White'
select au_id, au_lname, au_fname from authors where au_lname='Smith'
returns 2 recordsets:
au_id au_lname au_fname
----------- ---------------------------------------- --------------------
172-32-1176 White Johnson
(1 row(s) affected)
au_id au_lname au_fname
----------- ---------------------------------------- --------------------
341-22-1782 Smith Meander
(1 row(s) affected)
select au_id, au_lname, au_fname from authors where au_lname='White'
UNION
select au_id, au_lname, au_fname from authors where au_lname='Smith'
returns a single recordset:
au_id au_lname au_fname
----------- ---------------------------------------- --------------------
172-32-1176 White Johnson
341-22-1782 Smith Meander
(2 row(s) affected)
-
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] -
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. -
Hey Dave,
what a cheap shot to increase your post count!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thought it would cheer you up, after your rather terse post, you old sour puss you
Far away is close at hand in the images of elsewhere.
Anon. -
Yes, enjoy your weekend, too! I'm going home now.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
A big thanks to all who have responded.
Sorry about the cross posting...thought I had it in the wrong forum...
Anyway...
The data selection criteria meets my needs...the output doesn't
fot those answering the 'try it this way' must consider this...
The returned data accounts for records that are not in table B for a given date where a record exists in table A...lets say table A is a list ten staff members and they should sign in each day...if they do a record is written to table B, if not then it isn't...
It can therefore be said that if the name of the staff member in table A has a corresponding record in table B for a particular date, they did as expected. Now lets assume they didn't...there would be no corresponding record in table B...hence the use of 'not in' with a where clause for a date...
Now, if they don't sign in on a particular day I want to report on it...the code above loops through the selection criteria and correctly reports who has not signed in on a particular date...again the reason for decrementing the date held in the @Transdate variable...if BETWEEN was used then it would not be reported as 'missing' because it exists for a different date, which would be included in the BETWEEN statement.
To conclude...the code for selecting the records I am more than happy with...the problem I have is that the output is in many 'recordsets' and I only want one returned...
Writing to a temp table is one option, but hey, I thought that there must be a way to concatonate the output recordsets into one...
I know I understand what that lot means....hope you do too
Keep the responses coming...I would really like to crack this one....once and for all.
-
Stan, you say:
"To conclude...the code for selecting the records I am more than happy with...the problem I have is that the output is in many 'recordsets' and I only want one returned...."
But you are missing the point of the previous posters that your code for selecting the records doesn't take full advantage of SQL. It returns the records you want for 1 day...but SQL is based on set logic, so what you can do for 1 day, you can do for many days.
David Burrows' code will do exactly what you asked. Please try it.
-
To conclude...the code for selecting the records I am more than happy with...the problem I have is that the output is in many 'recordsets' and I only want one returned...
This is easy - for one set to be returned, you make just one select
If I get this straight, you have table A and B.
A record is always found in A, and if correct also in B - you want to find those missing in B on a per day basis.
SELECT a.date, a.userid
FROM tableA a
LEFT JOIN tableB b
ON a.userid = b.userid
WHERE a.date BETWEEN startday AND endday
AND b.userid IS NULL
(untested, hope I got it right - and apoligies for jumping in late in the mostly unread thread)
There are many ways to solve this type of problem - without cursors or temptables
/Kenneth
-
Hi all,
Thanks for the feedback...
I have used the example from David and it does work, but does not give me what I want.
The resultset contains a list of all employees who have failed to sign in at some point between the start and end date. I would like to know which dates in detail.
E.G.
Employee Date
1 20/04/2004
2 20/04/2004
1 19/04/2004
Thanks.
-
Try changing David's example to create first a temporary date table covering the date range in which you are interested. You can then cross join taht to the SINFORM table and change the left outer join on the DATARECS table to the temp table. You can then bring back a list of each day on which each employee did not log in (or the days they did if you change the WHERE d.SNUMBER IS NULL clause to IS NOT NULL).
Declare @sdate datetime, @edate datetime
declare @Dates TABLE ([date1] datetime, [date2] datetime)
Set @edate = CONVERT(varchar(10),GETDATE(),102)
Set @sdate = @edate - 11
insert @Dates values (@sdate, CONVERT(varchar(10),@sdate,102)+' 23:59')
while @sdate<@edate
begin
set @sdate=dateadd(dd,1,@sdate)
insert @Dates values (@sdate, CONVERT(varchar(10),@sdate,102)+' 23:59')
end
select s.SNUMBER, s.NAME, d.[DATE] as RecDate
from dbo.SINFORM s
cross join @Dates a
left outer join dbo.DATARECS d
on d.SNUMBER = s.SNUMBER
and d.[DATE] >= a.date1
and d.[DATE] < a.date2
where d.SNUMBER IS NULL
order by s.SNUMBER
Tony -
I finally got it working...yipee.
I acted on the suggestion made by Tony Bater, and added a couple of additional bits I required. Thanks Tony
Thanks to all others who responded, I couldn't have done it without yeh...
Kind regards
Stan.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply