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
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.
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:
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 appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten. Â Below is the stored procedure I've created:
ALTER PROCEDURE [dbo].[ap_CalcGrade]Â -- Add the parameters for the stored procedure here @studId int, @secId int, @grdTyCd char(2), @grdCdOcc int, @Numeric int output,
[Code] ....
And below is the "test query" I'm using:Â
--  *** Test Program *** Declare @LetterVal varchar(2), -- Letter Grade     @Numeric  int,     -- Numeric Grade     @Result   int     -- Procedure Status (0 = OK) Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3,Â
[Code] ....
This is resulting in an output of:Â
A+ 97 A+ 97 C- 72
but it should be returning the output below due to the 2nd data set not being valid/found in the sp query: Â A+ 97 No Find C- 72
I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it. Â Below is the assignment requirements:
Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:
1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE 2. Outputs the numeric grade and the letter grade back to the user 3. If the numeric grade is found, return 0, otherwise return 1 4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade
I have 3 tables: Suppliers, Documents and Verification, each document may have multiple verification where I need to get the last verification according to verification date. So I just need a "Select top 1 result from Documents, Verification where doc_iddoc=ver_iddoc and result='True' order by ver_date desc" so far I get the result of the last verification, but here's the problem:
I need to get a row with the count of documents for each supplier, I mean:
Supplier Name   Docs    NegativeVerification Acme Co         10           1
that is I would need to loop for each supplier and each document and get the last verification, if one of any of documents have negative verification then add it to negative results. Is it possible to achieve this with a query or do I have to do it through stored procedure?
Declare @StartDt date = '2015-03-15' Declare @EndDt date = DATEADD(M, 1, @StartDt) declare @Days int = DATEDIFF(d, @StartDt, @EndDt) declare @TBLSales as table(SaleDate date, Value money) DECLARE @Today date declare @TBLSalesCounts as table( StatusDesc varchar(100), Value money)
[Code] ....
I end up with the following result :
How would I alter my while loop to only insert the sum total of each day, instead of creating duplicates for each day.
E.g. 2015-04-22 1150.00 2015-04-21 Â 785.00 2015-04-20 Â 750.00
I have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX) @sql = ' insert into target select from table_1_currentdate a LEFT JOIN Table_2_currentdate-1 b on a.col1=b.col1  where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
Let's say if the date is 01/01/2015 till 01/01/2016
I want split these dates monthly format wise and then use them in variable in cursors to loop
For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
I used to loop through recordsets with ease in old classic .asp pages. Please Help me understand how Professionals now loop through and update tables using JUST SQL Query Analyzer using my pseudo-code provided below. I would love to learn how to do it to better develop my skills. SELECT * FROM zz_2007_Booth_Visitors WHERE COALESCE ([Product Interest - Other Actuator], [Product Interest - Chillers], [Product Interest - Other Chiller], [Product Interest - Electronic Products], [Product Interest - Other network interfaces], [Product Interest - Fittings], [Product Interest - High Vacuum], [Product Interest - Other high vacuum actuators], [Product Interest - Pick& Place and Transfer], [Product Interest - Teflon Products], [Product Interest - Training], [Product Interest - Valves& Manifolds], [Product Interest - Actuators]) Is Not Null Order BY [Contact Name]
IF [Product Interest - Actuators] IS NOT NULL THEN UPDATE Booth_Visitors_Data Set Act_Phuematic = 1 Where [Contact Name] = [Contact Name]
IF [Product Interest - Other Actuator] IS NOT NULL THEN UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = [Contact Name]
IF [Product Interest - Other Chillers] IS NOT NULL THEN UPDATE Booth_Visitors_Data Set Chiller = 1 Where [Contact Name] = [Contact Name]
Dear All. Have a nice day. I have db table, I need to update all fields of table. Please can you write code," for loop " how can update all fields of my table by loop. Thanks. Zahyea.
Hello everyone,I've got this While loop here which is giving me a problem:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN DECLARE @ProdId int, @ProdSize varchar, @ProdQuan int SELECT @ProdId = ProductId, @ProdSize = ProductSize, @ProdQuan = Quantity FROM _ShoppingCart WHERE CartId = @CartId If @ProdSize = 'XL' BEGIN UPDATE _ProductBatches SET XL = '0' WHERE ProductId = @ProdId END DELETE FROM _ShoppingCart WHERE ProductId = @ProdId AND CartId = @CartIdEND The problem is that the IF statement isn't being executed. And I know for a fact that 'XL' is ProductSize in my _ShoppingCart database. Whats even stranger is that my delete statement is being executed. So @ProdId is Being set correctly, but when it gets to the IF @ProdSize = 'XL' it doesn't get executed for some reason. If @ProdId is being recognized correctly in my DELETE statement, why is my @ProdSize not being reconized correctly in my IF statement. I checked my _ShoppingCart database, and my ProductSize is definitely 'XL'. Can any one see what could be going on here. Thank you, Alec
Hello everyone...... I'm trying to do the following but am having issues:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN execute code with item......... erase itemEND In the while loop I want to execute code from each item in my _ShoppingCart and then erase them until there are no more items. However the above code gives me the error: "Subquery returned more than 1 value. This is not permitted........" It works fine when there is only one item. Does any one know what format to use when dealing with more that one entry? Thank you, Alec
hi, I am trying to find a way of using a loop that won't be an endless loop because I have to insert parts of a string until the string reaches the end. I am unable to make the loop get to a point where the statement is false.
Is there anyway I can find out the length of the string so that I can tell the statement to loop while the statement is true?
HeaderLoop: for forHeader as curHeader dynamic scroll cursor for select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 do set AcctNum=lngALSHeadrID; exec "DBA".sp_ALSHeadr2Policy(AcctNum); set Cntr=Cntr+1 end for;
The above is the sybase version of a 'for loop' . The query select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 results in 1000 results. How do I change that in SQL?? Do we have a for loop ?? I checked in BOL but it is confusing with "browse" etc n some other options.
can I write like this?
for { Browse { declare curHeader dynamic cursor for select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 } set @AcctNum=lngALSHeadrID; exec "DBA".sp_ALSHeadr2Policy(@AcctNum); set @Cntr=@Cntr+1 }
I duno its just my guess, can any one help me out. @Cntr and @Acctnum are declared in the beginnning.
I have a loop is running with no end point. What I'm trying to do is get the Grand total of each row where BudgetNodeID = 120. Your help is much appreciated.
AV
Set NoCount on Declare @Amt as bigint Declare @Cont as bigint Declare @Mark as Bigint Declare @Total as bigint Declare @BudgetNodeID as Bigint Declare @GTotal as bigint Set @BudgetNodeID ='120' Set @Amt = 0 set @Cont = 0 set @Mark = 0 set @GTotal = 0
While exists (Select * from xBudgetNodeCosts where BudgetNodeID =@BudgetNodeID) Begin select @Amt = IsNull(xBudgetNodeCosts.Qty,0) * IsNull(xBudgetNodeCosts.CostRate,0) FROM xBudgetNode INNER JOIN xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR pc.Status='Approved')
select @Cont = @Amt * (xBudgetNodeCosts.Contingency/100) FROM xBudgetNode INNER JOIN xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID ) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR pc.Status='Approved') select @Mark = @Cont * (xBudgetNodeCosts.Markup/100) FROM xBudgetNode INNER JOIN xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR pc.Status='Approved') -- compute the sell
select @Total = @Amt + @Cont + @Mark
-- add to grand total Select @GTotal = Sum(@Total+ @GTotal)
I need to keep the first 4 values above 80 or the first 2 values above 90. If there are not enough, I need to keep as many values as possible. Should this be done with a while loop, if so, how would it be done.
