December 18, 2013 at 12:10 pm
I have two tables
select * from snsystemcertification
where fiscalyear=2013 and IsActive=1
--282 rows
select * from SNLunchSevereNeeds
where HHFKOverride=1 and HHFKProcess=1
--430 records
--Need to check the list who are eligible for last year 2013 with two tables(with HHFKoverride and HHFKprocess =1 )
--get that list and update in current year(2014)
How to write a query for this any help!!1
December 18, 2013 at 12:12 pm
mcfarlandparkway (12/18/2013)
I have two tablesselect * from snsystemcertification
where fiscalyear=2013 and IsActive=1
--282 rows
select * from SNLunchSevereNeeds
where HHFKOverride=1 and HHFKProcess=1
--430 records
--Need to check the list who are eligible for last year 2013 with two tables(with HHFKoverride and HHFKprocess =1 )
--get that list and update in current year(2014)
How to write a query for this any help!!1
not enough information; on your side, you probably know what relationship exists between snsystemcertification and SNLunchSevereNeeds, but based on what you posted, we cannot imply the relationship.
based on what you had in your other thread with the update, are you sure you don't need to insert new rows, or are you sure you need to update the existing table?
Lowell
December 18, 2013 at 12:25 pm
Yes,the relationship is systemcertificationid from both the tables.
we need to select the list from last year 2013 and update like in the same query but current year 2014
December 18, 2013 at 12:38 pm
you skipped the second part of my question:
... are you sure you don't need to insert new rows, or are you sure you need to update the existing table?
if you update all records that are 2013 to be 2014, don't you lose the history of 2013?
maybe you need to insert new rows for 2014 instead?
SELECT
snsystemcertification.*,
SNLunchSevereNeeds.*
FROM snsystemcertification
INNER JOIN SNLunchSevereNeeds
ON SNLunchSevereNeeds.snsystemcertificationID = snsystemcertification.snsystemcertificationID
WHERE snsystemcertification.fiscalyear = 2013
AND snsystemcertification.IsActive = 1
AND SNLunchSevereNeeds.HHFKOverride = 1
AND SNLunchSevereNeeds.HHFKProcess = 1
Lowell
December 18, 2013 at 12:42 pm
I guess you didn't like my response on your first version of this question? http://www.sqlservercentral.com/Forums/Topic1524214-149-1.aspx
When you create multiple threads on the same topic your responses get fragmented and it is really difficult for us to help.
Help us to help you by posting some details and we can help.
_______________________________________________________________
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 1:04 pm
No,we don't need to insert new rows into 2014 year.I had written sp please correct me
-->first we get systemcertificationid from 2013 year using all the conditions I got 126 records and using same query with fiscal year 2014 I got 304 records so we have to take this systemcertification id and go to lunchsevereneeds table match the systemcertification id and need to update it using the conditions hhfkoverride =1 and hhfkprocess =1
Note:we are updating only current year record.
declare @SystemId Varchar(10),@FiscalYear int
AS
BEGIN
SET NOCOUNT ON;
IF(ISNULL(@FiscalYear,0)=2013)
BEGIN
SELECT *
FROM SNSystemCertification AS SFA
INNER JOIN SNLunchSevereNeeds AS LS ON SFA.SystemCertificationID = LS.SystemCertificationID
WHERESFA.FiscalYear=@fiscalyear
and SFA.IsActive=1
and LS.HHFKOverride=1
and LS.HHFKProcess=1
END
ELSE IF(ISNULL(@FiscalYear,0)=2014)
BEGIN
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)
END
END
GO
December 18, 2013 at 2:12 pm
Need help please!!
December 18, 2013 at 2:38 pm
mcfarlandparkway (12/18/2013)
Need help please!!
Help us to help you by posting some details and we can help. This is at least the third time in this thread alone you have been asked to provide details.
We can't see your screen, we don't know your data, we are not familiar with your project, we have no idea what you are trying do.
Post ddl as create table statements, sample data as insert statements and an explanation of what you are trying to do. We can solve this in a matter of minutes once you take the time to provide the details needed for somebody to answer.
_______________________________________________________________
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply