I would like to update a decimal column in a temporary table based on
a set of Glcodes from another table. I search for a set of codes and
then want to sum the value for each row matching the Glcodes. The
problem is I keep getting multiple rows returned errors.
"Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated."
This is correct as there can be many rows matching the Glcodes for
each iteration of the case statement and I need to catch them all.
I have posted some of the code below and would appreciate any help as
I'm scratching my head over this one. It's all very much work in
progress again.
INSERT INTO #SummaryTempTable VALUES(199999, 1.1, 'Tot Ext Sales',
0.0)
INSERT INTO #SummaryTempTable VALUES(199999, 1.2, 'Tot Int Sales',
0.0)
INSERT INTO #SummaryTempTable VALUES(199999, 1.3, 'Inter Mark Up',
0.0)
INSERT INTO #SummaryTempTable VALUES(199999, 2.1, 'Tot Ext Costs',
0.0)
INSERT INTO #SummaryTempTable VALUES(199999, 2.2, 'Tot Int Costs',
0.0)
INSERT INTO #SummaryTempTable VALUES(199999, 2.3, 'Inter Mark Up
Charges', 0.0)
UPDATE #SummaryTempTable
SET [Summary Amount] = (SELECT sum(CASE
WHEN ((ACT_GL_NO between '4000' and '4059') or (ACT_GL_NO between
'4065' and '4999') or (ACT_GL_NO IN ('4062','4063'))) THEN GLD_Total
WHEN (ACT_GL_NO IN ('4060','4064')) THEN GLD_Total
WHEN (ACT_GL_NO = '4061') THEN GLD_Total
WHEN ((ACT_GL_NO between '5000' and '5059') or (ACT_GL_NO between
'5065' and '5401') or (ACT_GL_NO IN ('5805','5806','5062','5063')))
THEN GLD_Total
WHEN (ACT_GL_NO IN ('5060','5064')) THEN GLD_Total
WHEN (ACT_GL_NO = '5061') THEN GLD_Total
ELSE 0
END)
FROM howco_dw_test.dbo.cubeFinancePeriod
WHERE ([coid] = @COID) AND (GLD_SSN_BRH = @Branch) AND
(GLD_ACCTNG_PER like @TheYear) AND ACT_GL_NO BETWEEN 4000 AND 9999
AND GLD_CST_CTR IN ('008','021','031','041')
GROUP BY ACT_GL_NO, GLD_ACCTNG_PER)
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
Is it possible to use CASE in update statement? What is the syntax? I need to pass parameter @week, when @week = 0, 2, 4, then field = @Status and so on Thanks.
Is it possible to use CASE statement with INSERT /UPDATE statement?this is what i am trying to do i have a table like this Field1 Field2 Field3 FieldType1 FieldType2 FieldType3 1-when there is no data for a given combination of Field2 and Field3,i need to do "insert".i.e the very first time and only time 2-Second time,when there is already a row created for a given combination of Field2 and Field3,i would do the update onwards from there on. At a time value can be present for only one of the FieldType1,FieldType2,FieldType3) for insert or update I am passing a parameter to the stored procedure,which needs to be evaluated and wud determine,which field out of (FieldType1,FieldType2,FieldType3)has a value for insert or update .this is what i am trying to do in a stored procedure CREATE PROCEDURE dbo.StoredProcedure ( @intField1 int, @intField2 int, @intField3 int, @intFieldValue int , @evalFieldName varchar(4) )So i am trying something like CaseWHEN @evalFieldName ="Fld1" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,@intFieldValue,cast(null as int) fld2 ,cast(null as int) fld3) CaseWHEN @evalFieldName ="Fld2" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,@intFieldValue,cast(null as int) fld3) CaseWHEN @evalFieldName ="Fld3" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,cast(null as int) fld2,@intFieldValue) END similar trend needs to be followed for UPDATE as well..obiviousely its not working,gives me synatax error at case,when,then everywher.so can someone suggest me the alternative way?..i am trying to avoid writing stored procedure to insert/update for each individual fields..thanks a lot
Update ed_abcdeeh set category = case when name_of_school = '' then category = 'No Facility' else '' end,status = case when name_of_school = '' then status = 'Non-Compliant' else 'Compliant' end.
How to make this query right.. when name of school is blank i want to update my category to No facility, but if the name of school has data it will just make it blank. same to the status..
I am trying to run the below but I get an error of 'Incorrect syntax ')'' Â --- I have tried every angle I can think of around the parens to fix this but nothing I do is working.
UPDATE abcdefg SET [Date] = GETDate(), [readytogo] = ( CASE WHEN [customername] NOT IN (Select [customername] from [server].[database].[dbo].[view]) THEN 'Yes' ELSE 'Needs Verification'
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL THEN ves.VEmployeeID END FROM Employee E INNER JOIN VEmployeeStaging VES ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.  Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC' THEN (c.volume)  WHEN c.TYPE = 'DEF' THEN (c.volume)  WHEN c.Type = 'GHI' THEN (c.volume)  Else 0  END),  CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31'  WHEN c.Type = 'ABC' THEN '123.1'  WHEN c.Type = 'DEF' THEN '234.2' WHEN c.Type = 'GHI' THEN '567.1' END  from table1 a join table2 b  on a.Cust = b.Customer  join table3 c  on b.account = c.account and b.channel =c.channel
Why its not working properly? But if i use Select statement instead of update query its working properly.
table A: | ID | FRUIT | VEGETABLE | GOOD | -------------------------------------------- |  1 | orange | cabbage    |  no  | |  1 | apple | lettuce       | yes  | |  1 | kiwi    | broccoli     |  no  | |  1 | pear   | kale          | yes  |
table B: | ID | FRUIT | VEGETABLE | ------------------------------- | 1 | apple | lettuce      | | 2 | pear   |  kale        |
If the fruit and vegetable in table A is found in table B, then set the GOOD column = yes, else no.
This is what I have so far.
update tableA set GOOD = (case when tableA.id = C.id then 'yes' else 'no' end ) from (select tableA.id as id from tableA A left join tableB B on B.fruit = A.fruit and B.vegetable = A.vegetable) C
I am trying to create a stored procedure that will take a text value passed from an application and update a table using the corresponding integer value using a CASE statement. I get the error: Incorrect syntax near the keyword 'SET' when I execute the creation of the SP. What am I missing here? This looks to me like it should work. Here is my code.
CREATE PROCEDURE OfficeMove
-- Add the parameters for the stored procedure here
@UserName nvarchar(10),
@NewLocation nchar(5),
@NewCity nvarchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Execute as user = '***'
DELETE FROM [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
WHERE User_Name = @UserName
INSERT INTO [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):
UPDATE dbo.target_table set target_column = case when source_column_1= 'ABC'then 'XYZ' when source_column_2= '123'then 'PDQ'
[Code] ....
The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.
The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.
What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END, shipment_status_text = CASE [logic for condition 1] THEN 'Condition 1 text' WHEN [logic for condition 2] THEN 'Condition 2 text' WHEN [logic for condition 3] THEN 'Condition 3 text' WHEN [logic for condition 4] THEN 'Condition 4 text' ELSE 'Error' END, ...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block ...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END, ...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E
can any one help me in this? please give me a sample query.
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
material ========= material_id project_type project_id qty 1 AB Corporate 1 3 2 Other Project 2 7
i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project
sample query i write :--
select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id, ab.custname ,op.other_proj_id,op.other_custname,op. po case if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_id else if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_id end from material m,AB_Corporate_project ab,Other_project op
but this query not work,also it gives errors
i want sql query to show data as follows
material_id project_type project_id custname other_custname qty 1 AB Corporate 1 abc -- 3 2 Other Project 2 -- dsd 7
so plz help me how can i write sql query for to show the output plz send a sql query
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
I am trying to use a case statement in one of my stored proc but I am stuck a little bit.Here is a example, something like:declare @id int set @id =1case @id When 1 then select * from contactsend case but this keeps on giving me error: Incorrect syntax near the keyword 'case'. Any help is appreciated!
Hi I have some question regarding the sql case statment.Can i use the case statement with the where clause.Example: SELECT FirstName, IDFROM myTablewhere case when ID= '123' then id = '123' and id='124' endorder by idBut the above code does not work.
Hi all, I was wondering if there is any way in an sql statement to check whether the data your trying to get out of the DB is of a particular type, ie. Int, char etc. I was thinking about a case statement such as <code> CASE WHEN (MyNum <> INT) then 0 end AS MyNum </code>
This has to be included in the sql statement cause I need this field to get other data. Any thoughts on how to achieve this would be greatly appreciated.
If I’m in the wrong thread section please advise of best one to get help in.
Hi !!!i hope one of the sql specialists answer me about the best and most effeceint way to acheive what i am looking for Scenario:-------------i have a 3 tables related to each other Addresses, Groups and GroupAddressthe relation is for both addresses and groups is one to many in the GroupAddress.the behaviour in the application : user can add addresses to his address list and from the address list a user can add an address to many groups like if you have Group name "Freinds" and you add me in it and you have Football team group and you add me to it like that !!!not i have another function called "copy group"in the GroupAddress i have this data as example GroupID AddressID1 41 61 21 441 72 82 62 93 133 73 10and the group ID called "Freinds"i want to copy the group so i can have another group that has the same addresses by one click rather than collectiong them again one by one ...by the way the new copy will have a new group name ( as this is thebusiness logic so user can not have dupicate group name )so what is the best SQL statement that i need to copy the group ???i hope that clear enough!
I am trying determine if I can do something like the code below. I have done a left join on a table. In the select statement there are three possible values. Yes, No, or NULL. I could like to use a Case statement to determine if there is Null. If so, then output N/A in place of the Null. So then my possible valus are Yes, No, and N/A.
Any clues?
Thanks, John
SELECT TOP 100 OfferDressRoomYN.yesno as OfferDressRoom = CASE WHEN offerDressRoomYN.yesno IS NULL THEN 'N/A' END, FROM dataquestionnaire dq LEFT OUTER JOIN yesno OfferDressRoomYN ON dq.c3_1 = OfferDressRoomYN.yesnoid
In my query below i have the results ,The thing to observe in the result set it for the name "Acevedo" , "Abeyta" its not doing a group by and populating the results in the following column.Rather its addind a new row and adding it as 1 in the next row. I have to populate the counts in one row for common names.Shall i use a if condition within a case block.If yes how?any other work arounds would be appriciated. Please help Thanks
select isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname , case ProductID WHEN 22 then count(S.Product) Else 0 END AS Builders , case ProductID WHEN 23 then count(S.Product) Else 0 END AS Associates , case ProductID WHEN 24 then count(S.Product) Else 0 END AS Affiliates FROM vwpersons p with (nolock) join vwSubscriptions S with (nolock) on S.RecipientID = P.ID where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H) group by P.Lastname, S.ProductID having count(P.LastName)>=1 order by 1
I am trying to get avg score by site, by call type. Columns are Site(varchar), Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1, 2, 3, 4. I can do a straight average statement and only get one calltype. I want to do a CASE statement to get all average scores for all calltypes.
Select Site, avg(totalscore) as [Avg Score] FROM DB WHERE calltype = 'A' GROUP BY Site
Results
Site Avg Score (for A) 1 85 2 75.5 3 85.33
SELECT Site, AVG(CASE WHEN TotalScore > 0 AND CallType = 'A' THEN Totalscore ELSE 0 END) AS [Avg Score For A] FROM DB GROUP BY Site
Results
Site Avg Score For A 1 i get 8.5 2 i get 37.75 3 i get 36.57 Why am I getting a difference? Any help is greatly appreciated - thank you
Hi Ive got a simple query where I want to calculate an average of one number divided by the other ie: avg(x / y)
Im trying to use a case statement to return 0 in the event that y is 0, to avoid a division by zero error. My query is still returning a division by zero error anyway can anybody help?
SELECT CCode, CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE AVG(BSCLTradeCreditors / BS_TOTAL_ASSETS) END AS myaverage FROM [Company/Year] GROUP BY CCode, BS_TOTAL_ASSETS
i ahve one fucniton: create function fntotalcountcustclas
( @campaign varchar(50), @startdate datetime, @enddate datetime) RETURNS TABLE AS RETURN ( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount
from ( select vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas from vwcustnmbr vc join vwitemnbmr vi on vi.sopnumbe=vc.sopnumbe Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)
group by vi.itemnmbr,vc.custclas, vc.custclasdescription ) as t ) when i m executing it: select * from fntotalcountcustclas('copd','1/1/2008','4/11/2008') order by totalcount desc
i m getting results like: itemnmbr,custclas,custclasdescription,totalcount ------------------------------------------------ 06-5841 STANDARD Standard(web) 31 06-5840 STANDARD Standard(web) 30 kr-014 STANDARD Standard(web) 72 06-5841 INDPATIENT Patient 12 06-5840 INDPATIENT Patient 9 06-5845 INDPATIENT Patient 6 06-5841 PROGRAM Program 6 06-5841 INST-HOSPITAL Hospital 11 ...................
Basically, i ahve to use one condition to get corrrect output related to inputs:
like - i have to input @category varchar(50), @category_value varchar(50) and if category = 'campaign' then category_value = '' then output should be itemnmbr sum(totalcount) [whatever should be custclas or custclasdesscription] itemnmbr sumcount ----------------- 06-5840 52 06-541 101 06-452 26 kr-045 252
and if categroy = 'item' then category_value = any itemnmbrs(06-5840,06-5845,06-5841 etc..) then output should be itemnmbr custclas custclasdescription totalcount ----------------------------------------------------- 06-5840 STANDARD Standard(web) 31 06-5840 INDPATIENT Patient 9 06-5840 PROGRAM Program 6 06-5840 INS-HOSPITAL Hospital 17
like that..
can anyone help me to write case statement. thanks a lot!!
create function fntotalcountcustclas
( @campaign varchar(50), @startdate datetime, @enddate datetime, @category varchar(50), @category_value varchar(50)) RETURNS TABLE AS RETURN ( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount, case when category
from ( select vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas from vwcustnmbr vc join vwitemnbmr vi on vi.sopnumbe=vc.sopnumbe Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)
group by vi.itemnmbr,vc.custclas, vc.custclasdescription ) as t )
Im running the following sql statement but I dont see the expected output. There are few differences between acc & cl1, mcc & cl2 , ncr & cl3 but I dont see either 'ONE' or 'TWO' or 'THREE'. There is even a case where cl3 is null but the sql is not filling in either one or two or three. Query simply returns id & rest as null values.
SELECT P1.id, CASE WHEN p1.acc!= p1.cl1 then 'ONE' WHEN p1.mcc!= p1.cl2 then 'TWO' when p1.ncr!= p1.cl3 then 'THREE' Else NULL END As NOnMatchingColumn from (select id, acc, cl1,mcc,cl2,ncr,cl3 from dbo.ml)P1
I'm not sure if I am doing this the right way. In my table I have project ids. Each project id has several activities associated with it. Each project has a starting balance. Each activity posts an expense to the total balance of the project. If the project has enough money to handle the charges made by the activities, all the activity expenses can be "posted". If there isn't enough money, I want to loop through the activities, check to see if there is enough of a balance to "post" the first one, If there is, then I want to re-adjust the balance and check the second activity. I want to scroll through each project/activity to see what can be "posted". Here is what I have so far, but I cannot work out how to change the total balance amount. Hopefully what I am trying to do makes sense!
declare @testId nchar(6) declare @RowNum int declare @newBalance int select top 1 @testId=projID from #ProjIds set @RowNum = 0
WHILE @RowNum <= (Select Max(temp_id) from #ProjIds) BEGIN set @RowNum = @RowNum + 1 set @newBalance = (select top 1 Bal_2300 from #RevRecData where @testId=projId order by projID, activity)
select projId, activity, postCr, Bal_2300, 'New_Status' = Case when (postCr <= Bal_2300) then 'Can Clear' else 'Still Check' END, 'New_Balance' = Case when (postCr <= @newBalance) then (@newBalance - postCr) else @newBalance End from #RevRecData where @testId=projId order by projID, activity
select top 1 @testId=projId from #ProjIds where projId > @testID END