|
|
Posted by Cindy on 06/11/07 20:31
I'm trying to use the NEWID function in dynamic SQL and get an error
message Incorrect syntax near the keyword 'ORDER'. Looks like I can't
do an insert with an Order by clause.
Here's the code:
SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,
Client_ID, SelectDate, SelectType,RecordChosen)'
SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + '
Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROM
FD__UR_Randoms '
SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +
@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDER
BY NEWID())'
execute sp_executesql @SQLString
My goal is to get a random percentage of records.
The full SP follows. In a nutshell - I pull a set of records from
FD__Restart_Prog_Admit into a temporary table called FD__UR_Randoms.
I need to retain the set of all records that COULD be eligible for
selection. Based on the count of those records, I calculate how many
need to be pulled - and then need to mark those records as "chosen".
I'd just as soon not use the TMP_UR_Randoms table - I went that route
because I ran into trouble with a #Tmp table in the above SQL.
Can anyone help with this? Thanks in advance.
Full SQL:
CREATE PROCEDURE TP_rURRandomReview @ReviewType varchar(30)
--Review type will fill using Crystal Parameter (setting defaults)
AS
/* 6.06.2007
UR Requirements:
(1) Initial 4-6 month review: 15% of eligible admissions
(eligible via days in program and not yet discharged) must be reviewed
4-6 months after admission. This review will be done monthly -
meaning we'll have a moving target of names (with overlaps) which
could be pulled from each month. (Minimum 5 records)
(2) Subsequent 6-12 month review: Out of those already reviewed
(in #1), we must review 25% of them (minimum of 5 records)
(3) Initial 6-12 month review: Exclude any included in 1 or 2 -
review 25% of admissions in program from 6-12 months (minimum 5)
*/
DECLARE @CodeRevType int
DECLARE @PriorRec int -- number of records already marked
eligible (in case user hits button more than once on same day for same
type of review)
DECLARE @CurrRec int --number of eligible admits
DECLARE @RequFiles int
DECLARE @SQLString nvarchar(1000)
DECLARE @RequFilesSt varchar(100)
DECLARE @CodeRevTypeSt char(1)
DECLARE @TodayNotime datetime
DECLARE @TodaySt varchar(10)
--strip the time off today
SELECT @TodayNotime = DateAdd(day,datediff(day,0,GetDate()),0)
--convert the review type to a code
Select @CodeRevType = Case @ReviewType when 'Initial 4 - 6 Month' then
1 when 'Initial 6 - 12 Month' then 2 when 'Subsequent 6 - 12 month'
then 3 END
--FD__UR_Randoms always gets filled when this is run (unless it was
previously run)
--Check to see if the review was already pulled for this record
SELECT @PriorRec = (Select Count(*) FROM FD__UR_Randoms where
SelectType = @CodeRevType and SelectDate = @TodayNotime)
If @PriorRec > 0 GOTO ENDThis
--************************************STEP A: Populate FD__UR_Randoms
table with records that are candidates for review
************************
If @CodeRevType = 1
BEGIN
INSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,
SelectType,RecordChosen)
(SELECT pa.OP__DOCID, pa.Client_ID,
Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'
FROM dbo.FD__RESTART_PROG_ADMIT pa
Inner join FD__Client c
On pa.Client_ID = c.Client_ID
WHERE Left(c.Fullname,2) <> 'TT' AND (Date_Discharge IS NULL)
AND
(DATEDIFF(d, Date_Admission, GETDATE()) > 119)
AND (DATEDIFF(d, Date_Admission, GETDATE()) <= 211)
AND pa.OP__DOCID not in (Select Admit_DOCID from FD__UR_Randoms
where RecordChosen = 'T'))
END
If @CodeRevType = 2
--only want those that were selected in a batch 1 - in program 6-12
months; selected for first review
BEGIN
INSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,
SelectType,RecordChosen)
(SELECT pa.OP__DOCID, pa.Client_ID,
Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'
FROM dbo.FD__RESTART_PROG_ADMIT pa
Inner join FD__Client c
On pa.Client_ID = c.Client_ID
WHERE Left(c.Fullname,2) <> 'TT' AND (Date_Discharge IS NULL)
AND
(DATEDIFF(d, Date_Admission, GETDATE()) > 211)
AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)
AND pa.OP__DOCID in (Select Admit_DOCID from FD__UR_Randoms
where SelectType = 1 AND RecordChosen
= 'T'))
END
If @CodeRevType = 3
--only want those that were not in batch 1 or 2 - in program 6 to 12
months
BEGIN
INSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,
SelectType,RecordChosen)
(SELECT pa.OP__DOCID, pa.Client_ID,
Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'
FROM dbo.FD__RESTART_PROG_ADMIT pa
Inner join FD__Client c
On pa.Client_ID = c.Client_ID
WHERE Left(c.Fullname,2) <> 'TT' AND (Date_Discharge IS NULL)
AND
(DATEDIFF(d, Date_Admission, GETDATE()) > 211)
AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)
AND pa.OP__DOCID NOT in (Select Admit_DOCID from FD__UR_Randoms
where SelectType < 3 AND RecordChosen
= 'T'))
END
SELECT @CurrRec = (Select Count(*) FROM FD__UR_Randoms where
SelectType = @CodeRevType and SelectDate = @TodayNoTime)
--*************************************STEP B Pick the necessary
percentage **************************************
--if code type = 1, 15% otherwise 25%
If @CodeRevType = 1
BEGIN
SELECT @RequFiles = (@CurrRec * .15)
END
ELSE
BEGIN
SELECT @RequFiles = (@CurrRec * .25)
END
--make sure we have at least 5
If @RequFiles < 5
BEGIN
SELECT @RequFiles = 5
End
--*************************************STEP C Randomly select that
many files**************************************
--convert all variables to strings
SELECT @RequFilesSt = Convert(Varchar(100),@RequFiles)
SELECT @CodeRevTypeSt = Convert(Char(1),@CodeRevType)
SELECT @TodaySt = Convert(VarChar(10),@TodayNoTime,101)
SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,
Client_ID, SelectDate, SelectType,RecordChosen)'
SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + '
Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROM
FD__UR_Randoms '
SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +
@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDER
BY NEWID())'
print @SQLString
execute sp_executesql @SQLString
SELECT * FROM TMP_UR_Randoms
/*
--This select statement gives me what i want but I need to somehow
mark these records and/or move this subset into the temp table
Select Top @RequFiles
FROM FD__UR_Randoms
WHERE SelectType = @CodeRevType and SelectDate =
Convert(varchar(10),GetDate(),101))
ORDER BY NewID()
*/
ENDTHIS:
GO
[Back to original message]
|