|
|
Posted by Cindy on 06/11/07 20:51
So sorry - something about typing up the request helped me think of a
different solution -
I changed the SQL to
SELECT @SQLString = N'UPDATE FD__UR_Randoms SET RecordChosen = ''' +
'T' + ''''
SELECT @SQLString = @SQLString + N'WHERE SelectDate = ''' + @TodaySt
+ '''' + ' AND SelectType = 1 AND Admit_DOCID IN '
SELECT @SQLString = @SQLString + N' (SELECT TOP 12 Admit_DOCID FROM
FD__UR_Randoms ORDER BY NEWID())'
Does the trick nicely and I can get rid of the temp table!!
On Jun 11, 4:31 pm, Cindy <ckspot-t...@yahoo.com> wrote:
> 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
Navigation:
[Reply to this message]
|