Query Random Records With Sum Creteria
Sep 7, 2005
Dear All ...
im new memmber in this great forum ..
i need help ...
i have Table like :
ID Subject Points
---- ------- --------
1 1 10
2 1 20
3 2 10
4 3 5
5 1 15
6 2 10
7 3 10
8 1 20
i need to select random records from table where Subject = 1 and Total Sum for selected records Points = 25
any one can help me ..
thanks
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
View 1 Replies
View Related
Nov 16, 1999
Hi,
I'm trying to use the following query to select two random records from my database. Do you have any ideas of why the recrand field will not change? I am using MS Sql server 7.. Please email mark@dtdesign.com ..Cheers Mark
SELECT TOP 2 mytable.id AS RECID, RAND(mytable.id) AS recrand
FROM mytable
ORDER BY recrand
View 3 Replies
View Related
Feb 10, 2007
Hi every one.How can I get random 4 records in my table.
Example I want to get top 4 random records
Select top 4 from tbl_Products.....?
Thanks in appreciate
View 12 Replies
View Related
May 11, 2008
hi again,
i just want to ask if how can i randomly select 5 distinct records from a table w/ a hundreds of records everytime i exec a stored procedure??
thanks
View 3 Replies
View Related
Mar 13, 2004
hi im creating 2 random numbers, matching one of them (i) to the QuestionsNo column in my database, and then making it display the whole record. the code below generate 2 random numbers but i cannot get the rest of it to work. i need it to match the random number to the QuestionNo field in my database and display the whole record. can anyone help me and tell me where im going wrong please????
Dim i,j as integer
Randomize
i = CInt(Int((10 * Rnd()) + 1))
j = CInt(Int((10 * Rnd()) + 1))
example.InnerHtml = "Random Number : " & i
example1.InnerHtml = "Random Number : " & j
strSQL = "SELECT * FROM Table1 WHERE QuestionNo = " & i &""
Message.text=strSQL.ToString()
View 1 Replies
View Related
Jun 5, 2001
Hello,
I downloded the random record script from swynk site and when I run It gives me error msg
Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 73
Line 73: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.
Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 101
Line 101: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.
Can anyone guide me
I've copied the script
Thanks.
CREATE PROCEDURE PROC_RANDOM @VEZES INT , @TABELA CHAR(50) , @CAMPO CHAR(50) , @REPETIR CHAR(1) = "S"
AS
-- DECLARATION OF VARIABLES
DECLARE @CONTADOR INT , @VAL INT , @AUX CHAR(10)
DECLARE @TOTAL INT , @VALOR INT
DECLARE @NAME CHAR(50)
-- NO MESSAGES
SET NOCOUNT ON
-- CHECK FOR THE TABLE CALLED 'RETORNO'
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[RETORNO]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[RETORNO]
-- CREATES THE TABLE
CREATE TABLE RETORNO
(
CAMPO CHAR(50)
)
-- IF THE USER DON'T WANT SAME RESULTS , CREATE A TEMPORARY TABLE
IF @REPETIR = 'N'
BEGIN
CREATE TABLE #ALE
(
COD INT
)
END
-- GET THE TOTAL NUMBER OF RECORDS , USING THE SYSINDEXES TABLE
SELECT @TOTAL = ROWS FROM SYSINDEXES
WHERE ID = object_ID(@TABELA)
AND INDID < 2
-- IF THERE IS A TABLE....
IF @TOTAL IS NOT NULL
BEGIN
-- CLEAN THE COUNTER
SELECT @CONTADOR = 0
-- DO A LOOP SEVERAL TIMES TO GET THE NUMBER , AND RECORDS , OF RANDOM RECORDS THAT THE USER WANT
WHILE @CONTADOR <> @VEZES
BEGIN
-- INCREASE THE COUNTER
SELECT @CONTADOR = @CONTADOR + 1
-- GET A RANDOM NUMBER USING THE DATE....
SELECT @VAL = REVERSE((DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()))
SELECT @AUX = CONVERT(CHAR,@VAL * ( @CONTADOR ) )
SELECT @AUX = REVERSE(SUBSTRING(@AUX,3,5))
SELECT @VALOR = (CONVERT(INT,@AUX)%@TOTAL)+1
-- IF THE USER DON'T WANT TO REPEAT DATA...
IF @REPETIR = 'N'
BEGIN
-- CHECK FOR THE USE OF THIS NUMBER
IF NOT EXISTS(SELECT COD FROM #ALE WHERE COD = @VALOR)
BEGIN
-- CREATE A DYNAMIC CURSOR WITH SOME RECORDS. THE LAST WILL BE THAT RANDOM
EXECUTE("DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP " + @VALOR + " " + @CAMPO+ " FROM " +@TABELA )
OPEN CUR_MEU
-- GO TO THE LAST RECORD
FETCH LAST FROM CUR_MEU INTO @NAME
-- INSERT THE DATA IN THE RETURN TABLE
INSERT INTO RETORNO VALUES(@NAME)
CLOSE CUR_MEU
DEALLOCATE CUR_MEU
-- STORE THE RANDOM NUMBER IN A TABLE TO AVOID THE USE OF THIS NUMBER AGAIN
INSERT INTO #ALE VALUES(@VALOR)
END
ELSE
BEGIN
-- IF THIS NUMBER WAS USED THEN DECREASE THE COUNTER AND TRY AGAIN...
SELECT @CONTADOR = @CONTADOR - 1
END
END
ELSE
BEGIN
-- CREATE A DYNAMIC CURSOR WITH SOME RECORDS. THE LAST WILL BE THAT RANDOM
EXECUTE("DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP " + @VALOR + " " + @CAMPO+ " FROM " +@TABELA )
OPEN CUR_MEU
-- GO TO THE LAST RECORD
FETCH LAST FROM CUR_MEU INTO @NAME
-- INSERT THE DATA IN THE RETURN TABLE
INSERT INTO RETORNO VALUES(@NAME)
CLOSE CUR_MEU
DEALLOCATE CUR_MEU
END
END
SET NOCOUNT OFF
END
-- IF THE TABLE WAS CREATED , WE MUST DROP IT
IF @REPETIR = 'N'
BEGIN
DROP TABLE #ALE
END
-- RETURN TO THE USER ALL RANDOM RECORDS
SELECT CAMPO FROM RETORNO
-- DROP THE TABLE WITH THE RANDOM RECORDS
DROP TABLE RETORNO
GO
/* END OF THE STORED PROCEDURE */
View 1 Replies
View Related
Jun 25, 2002
I need a way to select records at random from a SQL Server 2000 table. If the record I've selected at random meets a few simple conditions (column X <> 'A' and column Y == 'Z') then return this record and get the next. Ideally I'd be able to do this in a stored proc where I'd call it with:
parameter 1: table name
parameter 2: number of random records to get
parameter 3: value for condition 1
parameter 4: value for condition 2
Any help will be greatly appreciated!
Patrick
View 4 Replies
View Related
Jun 5, 2001
Hello,
I downloded the random record script from swynk site and when I run It gives me error msg
Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 73
Line 73: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.
Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 101
Line 101: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.
Can anyone guide me
I've copied the script
Thanks.
CREATE PROCEDURE PROC_RANDOM @VEZES INT , @TABELA CHAR(50) , @CAMPO CHAR(50) , @REPETIR CHAR(1) = "S"
AS
-- DECLARATION OF VARIABLES
DECLARE @CONTADOR INT , @VAL INT , @AUX CHAR(10)
DECLARE @TOTAL INT , @VALOR INT
DECLARE @NAME CHAR(50)
-- NO MESSAGES
SET NOCOUNT ON
-- CHECK FOR THE TABLE CALLED 'RETORNO'
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[RETORNO]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[RETORNO]
-- CREATES THE TABLE
CREATE TABLE RETORNO
(
CAMPO CHAR(50)
)
-- IF THE USER DON'T WANT SAME RESULTS , CREATE A TEMPORARY TABLE
IF @REPETIR = 'N'
BEGIN
CREATE TABLE #ALE
(
COD INT
)
END
-- GET THE TOTAL NUMBER OF RECORDS , USING THE SYSINDEXES TABLE
SELECT @TOTAL = ROWS FROM SYSINDEXES
WHERE ID = object_ID(@TABELA)
AND INDID < 2
-- IF THERE IS A TABLE....
IF @TOTAL IS NOT NULL
BEGIN
-- CLEAN THE COUNTER
SELECT @CONTADOR = 0
-- DO A LOOP SEVERAL TIMES TO GET THE NUMBER , AND RECORDS , OF RANDOM RECORDS THAT THE USER WANT
WHILE @CONTADOR <> @VEZES
BEGIN
-- INCREASE THE COUNTER
SELECT @CONTADOR = @CONTADOR + 1
-- GET A RANDOM NUMBER USING THE DATE....
SELECT @VAL = REVERSE((DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()))
SELECT @AUX = CONVERT(CHAR,@VAL * ( @CONTADOR ) )
SELECT @AUX = REVERSE(SUBSTRING(@AUX,3,5))
SELECT @VALOR = (CONVERT(INT,@AUX)%@TOTAL)+1
-- IF THE USER DON'T WANT TO REPEAT DATA...
IF @REPETIR = 'N'
BEGIN
-- CHECK FOR THE USE OF THIS NUMBER
IF NOT EXISTS(SELECT COD FROM #ALE WHERE COD = @VALOR)
BEGIN
-- CREATE A DYNAMIC CURSOR WITH SOME RECORDS. THE LAST WILL BE THAT RANDOM
EXECUTE("DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP " + @VALOR + " " + @CAMPO+ " FROM " +@TABELA )
OPEN CUR_MEU
-- GO TO THE LAST RECORD
FETCH LAST FROM CUR_MEU INTO @NAME
-- INSERT THE DATA IN THE RETURN TABLE
INSERT INTO RETORNO VALUES(@NAME)
CLOSE CUR_MEU
DEALLOCATE CUR_MEU
-- STORE THE RANDOM NUMBER IN A TABLE TO AVOID THE USE OF THIS NUMBER AGAIN
INSERT INTO #ALE VALUES(@VALOR)
END
ELSE
BEGIN
-- IF THIS NUMBER WAS USED THEN DECREASE THE COUNTER AND TRY AGAIN...
SELECT @CONTADOR = @CONTADOR - 1
END
END
ELSE
BEGIN
-- CREATE A DYNAMIC CURSOR WITH SOME RECORDS. THE LAST WILL BE THAT RANDOM
EXECUTE("DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP " + @VALOR + " " + @CAMPO+ " FROM " +@TABELA )
OPEN CUR_MEU
-- GO TO THE LAST RECORD
FETCH LAST FROM CUR_MEU INTO @NAME
-- INSERT THE DATA IN THE RETURN TABLE
INSERT INTO RETORNO VALUES(@NAME)
CLOSE CUR_MEU
DEALLOCATE CUR_MEU
END
END
SET NOCOUNT OFF
END
-- IF THE TABLE WAS CREATED , WE MUST DROP IT
IF @REPETIR = 'N'
BEGIN
DROP TABLE #ALE
END
-- RETURN TO THE USER ALL RANDOM RECORDS
SELECT CAMPO FROM RETORNO
-- DROP THE TABLE WITH THE RANDOM RECORDS
DROP TABLE RETORNO
GO
/* END OF THE STORED PROCEDURE */
View 2 Replies
View Related
Jul 1, 2004
Once again - My table should consist of 100 new records for a field MobilePhone(of char type) and last 5 digits should be randomly choosed (should be like this: +381randomno1randomno2.. etc.(example: +38156465, where '+' sign makes it char type and digits after +381 are randomly choosed. :confused: Anyone knows how to solve this....PLEASE?
View 3 Replies
View Related
Mar 1, 2004
How would I create a statement that would select 10 random records from the SQL DB?
View 5 Replies
View Related
Apr 22, 2008
Hello,
Is it possible to select 10 random records from a table?
Thanks,
Miguel
View 1 Replies
View Related
Dec 1, 2006
I'm not sure if this is a completely dumb question, but please humor me:) I have a table of records, called Records, each of which has aCategory_ID that places it in a specific category; the details of thecategories are stored in another table called Category. What I need todo is retrieve a recordset that contains one record from each category,but where the records that are retrieved are random. I know how toretrieve one or more random records using "order by NewID()", but havenot been able to work out how to get one random record from eachcategory.Any assistance in this puzzler will be HUGELY appreciated!ThanksFEB
View 7 Replies
View Related
Sep 27, 2007
HI,
If i have the following data
Code Block
Create Table #Request (
[requestid] int ,
[customername] Varchar(30) ,
[age] int ,
[sex] char(1) ,
[address] Varchar(30) ,
[status] int
);
Insert Into #request Values('2342','Jack','23','M','Texas','0');
Insert Into #request Values('223452','Tom','45','M','Ohio','1');
Insert Into #request Values('22353','Bobby','23','M','Austin','0');
Insert Into #request Values('22362','Guck','23','M','Austin','0');
Insert Into #request Values('22392','Luck','23','M','Austin','1');
Insert Into #request Values('22362','Buck','23','M','Austin','0');
Insert Into #request Values('2564392','Jim','23','M','Austin','1');
Insert Into #request Values('2342','Jasm','23','M','Austin','0');
Insert Into #request Values('2765492','Chuck','23','M','Austin','1');
How can i return 30% random requestid's from this table?
thanks.
View 9 Replies
View Related
Feb 10, 2007
I've got a product table in SQL 2000 that contains say these rows:
ProductID intProductName varcharIsSpecial bit
I want to always return 4 random specials from a query and can do this fine by using:
SELECT TOP 4 ProductID,ProductNameFROM Products WHERE IsSpecial = 1ORDER BY NEWID()
This works ok if there are 4 products marked as specials but the problem is i always need 4 records returned but if only 2 products are marked as special then only 2 records are returned.
What i really need is something in there that says if <4 records are returned then just add random non-special products to make the total products returned up to 4. So it should always be 4 records but preference is given to specials if you see what i mean?
Is this possible?
Thanks
View 2 Replies
View Related
Aug 26, 2005
Hello,I have 1000 of records in my table. I wanna select random 100 of them? How?regards
View 3 Replies
View Related
Sep 22, 2000
Hello,
I need to select records by random order everytime user go to that page. QuestionID is the uniq field in this table.
SELECT * FROM QuestionMaster.
I tried to use the following statement to get the random records but I can't get the value in the variable out of this statement that I can use in my above select statement. Please HELP.
SELECT Convert(int,RAND()*97)
Thanks in a million.
Sarika
View 1 Replies
View Related
Jul 31, 2007
Hello,
Anyone have any suggestions on creating a query that will randomly select records from a table, but not use those records again. I have some code that does it, but it uses the same fields over again, and also throws in some blank records that I did not specify in the query. I am creating a test engine that has to randomly ask questions.
View 5 Replies
View Related
Sep 4, 2007
Dear all,
M new to SQL Query, i have some update query code that i run have something wrong. It's the following:
update prod_ticket
set prod_ticket.complete_date = '5000/01/01'
FROM prod_ticket INNER JOIN
prod_ticket_hdr ON prod_ticket.lay_id = prod_ticket_hdr.lay_id INNER JOIN
ms_employee ON prod_ticket.employee_no = ms_employee.employee_no INNER JOIN
ms_department ON ms_employee.department_no = ms_department.department_no INNER JOIN
prod_work ON prod_ticket.work_no = prod_work.work_no AND prod_ticket_hdr.prod_no = prod_work.prod_no
WHERE (prod_ticket.complete_date BETWEEN CONVERT(DATETIME, '2007-08-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-08-31 00:00:00', 102)) AND
(prod_work.piece_rate / 10)='0' and prod_ticket.employee_no='50502' and (SUM(prod_ticket.complete_qty))<'30'
I knew that the wrong code is the (SUM(prod_ticket.complete_qty))<'30' because if i delete this condition then the query was ok. Could u please help me to correct the query code for me. really really much appriciated.
View 4 Replies
View Related
Aug 4, 2005
I try to set up a testing sample table which contain one integer project_ID field for table Sample around 500 records, and want the project_ID to be random number within 1 to 99, how to implement script todo it?
thanks!
View 1 Replies
View Related
Aug 20, 2005
Hi
I want to make random record from both columns
Not just like this
"SELECT Firstname,Lasename FROM rndnames ORDER BY NewID()"
but more like this but this code dont work becouse i dont know how to put it into the code:
"SELECT Firstname FROM rndnames ORDER BY NewID()"
"SELECT Lasename FROM rndnames ORDER BY NewID()"
As you see i want both columns to be random placed..
Please help me...
View 1 Replies
View Related
Mar 12, 2006
hello i have a problem and i dont know if it can fixed or not i'm using asp.net 2 and sqlserver 2005 i have a table named questions in the database and that table has 5 columns questionid,question,answer1,answer2,answer3 every questions has 3 answers 1 right and the other 2 are wrong what i want to do is select 10 random questions and their right and wrong answers show in my asp.net page as application the answers will be in radio buttons so is that can be done or it's not possible.thanks all
View 17 Replies
View Related
Jun 6, 2006
I have a situation where i am trying to run two query's and retrieveone record from each query, then using Union combine them into onerecordset. The First Query is strait forward and should just returnthe matching record however the second query needs to find a randomrecord and return it. The union is causing me some trouble. It seemsthat any method i try to find a random record just causes an error.Here is an example of a query that's causing the problem.--------------------------------------------------------------------------------------------------------declare @variable1 smallintdeclare @variable2 intset @variable1 = 10002set @variable2 = 1001211720select col1, col2, col3, col4, col5, col6from table1where col1 = @variable1 and col2 = @variable2unionselect col1, col2, col3, col4, col5, col6from table2where col2 = @variable2order by newID()--------------------------------------------------------------------------------------------------------I have also tried the following which does not provide an error but icannot find a way only to return one record from the second query--------------------------------------------------------------------------------------------------------declare @variable1 smallintdeclare @variable2 intset @variable1 = 10002set @variable2 = 1001211720select newID() as colID, col1, col2, col3, col4, col5, col6from table1where col1 = @variable1 and col2 = @variable2unionselect newID() as colID, col1, col2, col3, col4, col5, col6from table2where col2 = @variable2order by colIDIf anyone has any ideas, fixes, or thoughts i would appreciate them...thanks in advance...FYI i am fairly inexperienced in SQL so please feel free to let me knowif you need more of an explination.
View 9 Replies
View Related
Jun 11, 2007
I'm trying to use the NEWID function in dynamic SQL and get an errormessage Incorrect syntax near the keyword 'ORDER'. Looks like I can'tdo 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 FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'execute sp_executesql @SQLStringMy goal is to get a random percentage of records.The full SP follows. In a nutshell - I pull a set of records fromFD__Restart_Prog_Admit into a temporary table called FD__UR_Randoms.I need to retain the set of all records that COULD be eligible forselection. Based on the count of those records, I calculate how manyneed 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 routebecause 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.2007UR Requirements:(1) Initial 4-6 month review: 15% of eligible admissions(eligible via days in program and not yet discharged) must be reviewed4-6 months after admission. This review will be done monthly -meaning we'll have a moving target of names (with overlaps) whichcould 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 intDECLARE @PriorRec int -- number of records already markedeligible (in case user hits button more than once on same day for sametype of review)DECLARE @CurrRec int --number of eligible admitsDECLARE @RequFiles intDECLARE @SQLString nvarchar(1000)DECLARE @RequFilesSt varchar(100)DECLARE @CodeRevTypeSt char(1)DECLARE @TodayNotime datetimeDECLARE @TodaySt varchar(10)--strip the time off todaySELECT @TodayNotime = DateAdd(day,datediff(day,0,GetDate()),0)--convert the review type to a codeSelect @CodeRevType = Case @ReviewType when 'Initial 4 - 6 Month' then1 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 waspreviously run)--Check to see if the review was already pulled for this recordSELECT @PriorRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNotime)If @PriorRec 0 GOTO ENDThis--************************************STEP A: Populate FD__UR_Randomstable with records that are candidates for review************************If @CodeRevType = 1BEGININSERT 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 paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE 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_Randomswhere RecordChosen = 'T'))ENDIf @CodeRevType = 2--only want those that were selected in a batch 1 - in program 6-12months; selected for first reviewBEGININSERT 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 paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE 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_Randomswhere SelectType = 1 AND RecordChosen= 'T'))ENDIf @CodeRevType = 3--only want those that were not in batch 1 or 2 - in program 6 to 12monthsBEGININSERT 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 paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE 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_Randomswhere SelectType < 3 AND RecordChosen= 'T'))ENDSELECT @CurrRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNoTime)--*************************************STEP B Pick the necessarypercentage **************************************--if code type = 1, 15% otherwise 25%If @CodeRevType = 1BEGINSELECT @RequFiles = (@CurrRec * .15)ENDELSEBEGINSELECT @RequFiles = (@CurrRec * .25)END--make sure we have at least 5If @RequFiles < 5BEGINSELECT @RequFiles = 5End--*************************************STEP C Randomly select thatmany files**************************************--convert all variables to stringsSELECT @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 FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'print @SQLStringexecute sp_executesql @SQLStringSELECT * FROM TMP_UR_Randoms/*--This select statement gives me what i want but I need to somehowmark these records and/or move this subset into the temp tableSelect Top @RequFilesFROM FD__UR_RandomsWHERE SelectType = @CodeRevType and SelectDate =Convert(varchar(10),GetDate(),101))ORDER BY NewID()*/ENDTHIS:GO
View 3 Replies
View Related
Dec 19, 2013
My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.
I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).
CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE
[Code] ....
But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:
Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.
I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...
The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)
but that's clearly not working. Is there a way to do this without resorting to a cursor?
View 9 Replies
View Related
May 18, 2015
I have these columns. TicketID, User, Date...I want to select 10 random tickets for each user for the past 30 days. I not sure whether to do this via sql or using VB in the report design.
View 5 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Mar 18, 2008
Hi All,
I've been asked to do a quick job for a client that involves getting random results back from a SQL query.
The query will have one paramater - @country - and this will be in the where eg.
select * from Holidays
where country = @country
What the client wants is to randomly pick 3 results and display them on each page load (the site is in Classic ASP, not .NET) and randomise the offers on each page load.
The query is simple but how can you factor in a Random into it?
Hope someone can help,
Brett
View 10 Replies
View Related
May 1, 2004
If we use: select * from ..... , normally, it will return an ordered result ( may be order by ID ), but how can we make an random order select statement. It mean every time we run the query, the result will be different from the other ?
View 1 Replies
View Related
Oct 5, 2006
use AFMIF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name='uye_idler') drop table uye_idlerCREATE TABLE uye_idler ( uye_no int NOT NULL, ) GO DECLARE @uye_numarası int Set @uye_numarası = 1 WHILE @uye_numarası < 15001 BEGIN INSERT INTO uye_idler VALUES (@uye_numarası) Set @uye_numarası = @uye_numarası + 1 EndHi , I am able to create 15000 records by the codes above .By the same way I want to create random texts as ASD ,WER,SAD,DFG etc. How can I do this ? Thanks ...
View 1 Replies
View Related
Aug 24, 2007
Application folks experiencing recurrence of problem previously reported. This is causing delays with multiple projects. Errors below are occuring when attempting to run a DTS package that will refresh data from prod to pilot.
Exception Details: System.Data.SqlClient.SqlException: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Another example reads:
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution. (Microsoft OLE DB Provider for SQL Server (80004005): Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.)
Step Error code: 8004043B
Step Error Help Fileqldts80.hlp
Step Error Help Context ID:1100
View 1 Replies
View Related
Jan 30, 2015
I'm trying to write a function that will retrieve the last backup date/time of a particular database on a remote server (i.e. by querying msdb.dbo.backupset). Unfortunately, you can't use sp_executesql in a function, so I can't figure out a way to pass the server name to the query and still be able to return the datetime value back to the calling TSQL code (so that rules out using EXEC().
View 3 Replies
View Related
Oct 14, 2004
I'm using ASP and SQL Serv 2000. What I need to get from 2 tables (company & customers) is random 10 customers from random 20 comp.
Anyone got an idea how to do this??? I've spent 2 days trying to get stored proc. or T-SQL to work, but nothing good came out of it. I can get 1 comp and 10 cust, but not a grouped list of 20 comp. w/ 10 cust. each.
Help is greatly appreciated.
View 1 Replies
View Related
