Transact SQL :: While Loop Does Not End When Condition Is No Longer True
Dec 1, 2015
I have a Stored Procedure, wherein I need to use a while loop. essentially the loop looks like this.
Set @Kount = 1
--TestScript
Select 'TempReqTable', * from @TempReq
WHILE Exists(Select * from @TempReq WHERE TempID = @Kount)
BEGIN
--Do stuff with values from table
Set @Kount += 1
END
The test script confirms that there is only one row in the @TempReq table. However the loop never stops running and @Kount keeps being incremented and incremented until I manually stop the execution of the SP.
Is there some rule that I am not aware of that does not allow the use of an Exists statement in the condition of a While loop?
I am using SQL 2005. I have some data from an old application that did not follow the rules for normalization. The table is for Invoices, and the table allows for 13 purchase items per record. So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12. For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc. It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date"). My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.
I have customers named Alex (Cid=1), Bob (Cid=2), and Carrie (Cid=3) in a table customer.
Cid First_Name 1 Alex 2 Bob 3 Carrie
I have products name Gin (Pid=1), Scotch (Pid=2) and Vodka (Pid=3) in a table products.
Pid Product_Name 1 Gin 2 Scotch 3 Vodka
And I have a table that holds purchase called Customer_Purchases that contain the following records:
Cid Pid 1 1 1 2 2 1 2 3 3 2
I would like to make a marketing list for all customers that purchased Gin or Scotch but exclude customers that purchased Vodka. The result I am looking for would return only 2 records: Cid’s 1 (Alex) and 3 (Carrie) but not 2 (because Bob bought Vodka).
I know how to make a SELECT DISTINCT statement but as soon as I include Pid=2 This clearly doesn’t work :
SELECT DISTINCT Pid, Cid FROM Customer_Purchases WHERE (Cid = 1) OR (Cid = 3) OR (Cid <> 2)
The below query is for november month: (Apr to Nov)
select * from maintable where period between '01-04-2014' and '01-11-2014' then for December month : (Apr to Dec)
So I want to pass second row
select * from maintable where period between '01-04-2014' and '01-12-2014' ..... .... select * from maintable where period between '01-04-2015' and '01-12-2015'
Like wise I want to get month wise data of YTM data.
Hi, It seems like For Loop Container works like do-while loop in C++. I have set its EvalExpresstion to @[User::SyncStats]==0 , I have an Execute SQL Task before this container that sets the variable to 1 but Container still executes atleast one time.
I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False).
Hi we have a table with about 400000 records in it. It starting to take longer and longer to add a new record. I was thinking of creating another identical table and archiving off most of the records every month (we are now adding about about 4000 records a day) . Is this the best thing to do? I don't know a lot about sql server so any help or suggestions would be great
I want to edit(update,delete,insert) target table activity from front end for that I took grid view and that grid all activity come of single student studenid(unique key for each student) that you see design below like this
that activity present in target table that come as check in check and that activity not select student from activity activity table that come unchecked
I want to update above information means unchecked alredy check data should delete and new check data add , already check data update
I have use merge statement but problem is that at single time only one row execute that is check because data come from data gridview.
I am in assumption that we cannot do NULL value comparison in where clause directly (results will not be proper) we have to use ISNULL. Is my understanding correct? Is using ISNULL efficient when we have to this operation for large volume?
I have an order with the time 08/03/2015 7:30 08/03/2015 10:30 ..I have availability with the time 08/03/2015 07:00:00 to 08/03/2015 19:30:00...Here the availability falls for 3 hours only..My requirement is that For order if availability< 4hours i don't want to show employee.How to apply the condition.
I have a table called names (firstname, lastname, number) then i have a DECLARED variable called @displaynum...If @displaynum id true my select query should select the number column. if the variable is false , only the first two columns are selected.
The following code is taking longer and longer to run. I am not talking about the gradualy increase in size. this job has been taking 30-40 mins normaly and in the last few days it has gone 1hr to 2 hr to 3 hr... ANy ideas why this is happening? I can not see and other jobs running at this time.
declare @filename varchar(255)
set @filename = (select top 1 physical_device_name from ****.msdb.dbo.backupset bs, ****.msdb.dbo.backupmediafamily bf where bs.media_set_id=bf.media_set_id and database_name = 'Live_PRD' and backup_start_date>getdate()-1 and type = 'D' order by backup_start_date desc)
restore database REPORTS_REP from disk=@filename with move 'LIVE_PRD_Data' to 'T:SOUTHREPORTS_REP_Data.mdf', move 'LIVE_PRD_Log' to 'U:SOUTHREPORTS_REP_Log.ldf', move 'LIVE_PRD_Log2' to 'U:SOUTHREPORTS_REP_Log2.ldf', replace, stats=2, recovery
Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)
If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.
Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.
pn = 4donot have sdate and edate and status values dut not fall conditon
select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate order by case when pr.status=2 then 1 when pr.status=4 then 2 when pr.status=3 then 3 when pr.status=1 then 4 end ,pr.sdate )pr
but this query not given expected result.here when dos not fall between sdate and edate that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.
I am using SQL Server 2008.Each stock item will have default 4 document type (1, 5, 6, 7) and each will have 3 zone's (1, 2, 3) to qualify. Each zone will be updated to 1 for that document type if the item successfully pass through it. If all zone are NULL means no transaction. How to retrieve only the failed rows which means not all zone are 1 or NULL.In the image GJ-00064 has one row failed. So how to get the count of failed rows for each item
I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.
Above has 6 files entries for client id 22784 and LOAN_SANCTION_DATE 2014-02-03 out of which 3 are rejected ..
Now , i want to write a query to select those distinct client_id , LOAN_SANCTION_DATE from Client_Master where all files has been rejected ..
means by grouping client ID and LOAN_SANCTION_DATE all the files are rejected ..
I have wrote as below .. got the result but not satisfy with the query
SELECT DISTINCT CLIENT_ID,LOAN_SANCTION_DATE,COUNT(FILE_ID) AS No_Of_Files ,COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )AS No_Of_Rejected FROM dbo.FILE_MASTER GROUP BY CLIENT_ID ,LOAN_SANCTION_DATE HAVING COUNT(FILE_ID)=COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )
I've two audit tables, AUDIT_ORDERS and AUDIT_ORDER_LINES.
The AUDIT_ORDERS has these columns: AUDIT_ID, ORDER_ID, AUDIT_DATE and other ones.
The AUDIT_ORDER_LINES has these columns: AUDIT_ID, ORDER_ID, ORDER_LINE_ID, AUDIT_DATE and other ones.
I need to join these two tables in order to select for each order line row the first order having the related audit date lower than or equal to the audit date of the related order line.
I don't want to use the TOP 1 clause or a subquery. I think to complete a such statement:
SELECT OL.Order_Line_ID, O.Order_ID, OL.Audit_Date, O.Audit_Date FROM AUDIT_ORDER_LINES as OL INNER JOIN AUDIT_ORDERS as O on OL.Order_ID = O.Order_ID and O.Audit_Date <= OL.Audit_Date ...
I'd like to get the first row of the Audit_Orders with audit_date <= of the audit_date of the Audit_Order_Lines table by using the join clause.
So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
Hi,I need to convert this anonymous pl-sql block into transact-sql to runin Microsoft SQL Server Query Analyser.Does anyone know what the conversion syntax would be?I know that to declare variables to use the @.PRINT for printing the output.No idea for the LOOP.Any suggestions greatly appreciated.Many thanks.Thiko!____________________________________________SET SERVEROUTPUT ONDECLARErecordcount NUMBER;BEGINDBMS_OUTPUT.ENABLE(5000000); -- Sets buffer size.recordcount := 0;LOOPINSERT INTO RecoverTest VALUES (SYSDATE - (recordcount / 4));COMMIT; -- frees up rollback segment;recordcount := recordcount + 1;DBMS_OUTPUT.PUT_LINE('INSERT: ' || recordcount || ' - 45000 ');EXIT WHEN recordcount > 45000;END LOOP;END;/
I have a query that returns the data about test cases. Each test case can have multiple bugs associated to it. I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
Col1 to Col9 represent the account information, Col10 amount, Col 11 represents the month and Col12 represents year. According to the data there wasn't any activity for the month 1, 2 and 3.
CREATE TABLE dbo.MyEmployees ( EmployeeID smallint NOT NULL, FirstName nvarchar(30) NOT NULL, LastName nvarchar(40) NOT NULL, Title nvarchar(50) NOT NULL, DeptID smallint NOT NULL, ManagerID int NULL, CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) );
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL) ,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1) ,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273) ,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
My insert statement for #Data - I only need to process each @EmployeeID one time, which is why I thought a loop would be sufficient, but I let this process run for 2 hrs and it still had not completed, so I feel I must have set-up something incorrectly!
This is my syntax, I am creating a table of active users, then wanting to get data for each of those active users. But only get the data for each active user 1 time.
Declare @EmployeeID varchar(50) CREATE TABLE #ActiveUsers ( ID INT IDENTITY NOT NULL ,EmployeeID varchar(50) ,processed int ) Create Table #Data
I am trying to query a code where i need to loop a month in a specified date range. Inside the loop I need to return a result of data each month and need to update the table of the returned data. How do I do the update a field inside the loop? Here's my query:
