December 18, 2013 at 9:54 am
have a requirement that to update data from last year to current year.(means like last year what is the data eligible this year also should eligible) the below are two querys (I need to do innerjoin for second query and I need to put in cursor and top query need to update it like every time loop runs and we can use ASC systemid in update and need to set three colums in SNLunchSevereNeeds table in the update statement where HHFKoverride = 1 and HHFkprocess =1 and payfrom =7
Basically needs to update from last year to current year
Created a SP,not sure its right or not?can anyone help me!!1
CREATE PROCEDURE [dbo].[usp_SNSixcentsEligibilityList]
@SystemId Varchar(10),@FiscalYear int
AS
BEGIN
declare @SystemCertificationID int
SET NOCOUNT ON;
declare EligibleList cursor for
Select * from SNLunchSevereNeeds where SystemCertificationID in(select SC.SystemCertificationID
from SNSystemCertification SC INNER JOIN SNLunchSevereNeeds LS ON SC.SystemCertificationID = LS.SystemCertificationID
where FiscalYear=@FiscalYear and LS.IsActive = 1)
open EligibleList
FETCH NEXT FROM EligibleList INTO @SystemCertificationID,@SystemId,@Fiscalyear
while @@FETCH_STATUS=0
begin
Select * from SNLunchSevereNeeds Where SystemCertificationID in(
Select SystemCertificationID from SNSystemCertification where FiscalYear=@FiscalYear and
IsActive=1 order by SystemID ASC )
--write an update to set the values.
update SNLunchSevereNeeds
set HHFKOverride =1,
HHFKProcess = 1,
HHFKPayFrom = 7
FROM SNLunchSevereNeeds where IsActive=1
FETCH NEXT FROM EligibleList INTO @SystemCertificationID,@SystemId,@Fiscalyear
End
Close EligibleList
deallocate EligibleList
END
GO
December 18, 2013 at 10:02 am
Hi and welcome to the forums. Why do you need a cursor for this? From what you posted I think you can replace the entire body of your stored proc with this.
update SNLunchSevereNeeds
set HHFKOverride = 1,
HHFKProcess = 1,
HHFKPayFrom = 7
from SNLunchSevereNeeds
where SystemCertificationID in
(
select SC.SystemCertificationID
from SNSystemCertification SC
INNER JOIN SNLunchSevereNeeds LS ON SC.SystemCertificationID = LS.SystemCertificationID
where FiscalYear = @FiscalYear
and LS.IsActive = 1
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 18, 2013 at 10:47 am
Using these two querys
Select * from SNLunchSevereNeeds Where SystemCertificationID in(
Select SystemCertificationID from SNSystemCertification where FiscalYear=2014 and
SystemID=749 And IsActive=1 )
Select * from SNLunchSevereNeeds Where SystemCertificationID in(
Select SystemCertificationID from SNSystemCertification where FiscalYear=2013
And IsActive=1)
How can I update data from last year to current year (need to check system certification id from both the tables)
see the current fiscal year for all the systems who are eligible.(updating from last year to current year)
December 18, 2013 at 10:50 am
mcfarlandparkway (12/18/2013)
Using these two querysSelect * from SNLunchSevereNeeds Where SystemCertificationID in(
Select SystemCertificationID from SNSystemCertification where FiscalYear=2014 and
SystemID=749 And IsActive=1 )
Select * from SNLunchSevereNeeds Where SystemCertificationID in(
Select SystemCertificationID from SNSystemCertification where FiscalYear=2013
And IsActive=1)
How can I update data from last year to current year (need to check system certification id from both the tables)
see the current fiscal year for all the systems who are eligible.(updating from last year to current year)
You have me at an extreme disadvantage here. I have no idea what your tables and data are like. Take a look at the first link in my signature about best practices when posting questions. With some details about your data and what you want to do it we can help you figure out how to do it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply