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...
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 Relatedwriting 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
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 RelatedI have created a stored procedure in sql server 2005 which will fetch a particular record based on the value we supply for a column. It is working fine. The same task i need to implement in my web form. I have a textbox in my webform where i will enter the empage which is in the table. Based on the value the record(s) should get displayed in the gridview. How to acheive this. Pls help me with full coding. it is urgent.
Thanx is advance
hi,
i am having 2 columns in a table in a dataset.
i want to add those two columns and bind the resultant total as a single column to the datagrid.
is it possible.
if yes, how o acheive this?
please help me.
thanks in advance.
muppidi.
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
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
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
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()
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 */
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
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 */
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 RelatedHow would I create a statement that would select 10 random records from the SQL DB?
View 5 Replies View RelatedHello,
Is it possible to select 10 random records from a table?
Thanks,
Miguel
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 RelatedHI,
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.
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
Hello,I have 1000 of records in my table. I wanna select random 100 of them? How?regards
View 3 Replies View RelatedHello,
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
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
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.
Hi,
i'm importing data from a txt file to Sql server table through a DTS package. the table is created dynamically.
i want three new cloumns added in the front and one of the columns should have a sequence number generation.
Please advice
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!
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 RelatedI 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 RelatedI'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 RelatedMy 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?
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 RelatedPlease help me, i want to know how to make sql server columns case sensitive for a login table.
View 1 Replies View RelatedI have persons who speaks multiple languages and they are in one table, each row is added if he/she speaks multiple languages. Instead I want to add additional columns and load the data.(what I have shown in the desired output)
name language
-------------
ron english
ron french
ron spanish
andy english
andy hindi
kate english
Desired output
name language1 language2 language3 language4 language5 language6
-----------------------------------------------------------------
ron english french spanish
andy english hindi
Kate english
I have a table that has the records as shown below
Id From To
1 Delhi Noida
2 Faridabad Kanpur
3 Noida Delhi
4 Kanpur Faridabad
5 Banglore Mumbai
6 G H
7 I J
8 Mumbai Banglore
I want the results in the following order
5 Banglore Mumbai
8 Mumbai Banglore
1 Delhi Noida
3 Noida Delhi
2 Faridabad Kanpur
4 Kanpur Faridabad
6 G H
7 I J
Suppose as in above records There is a complete roundtrip from Banglore to Mumbai and Mumbai to Banglore.