loop in sql to update record as per user
I have 2 tables in my sql server in 1st table I am saving official holidays(officialHolidays) and in second table I am saving Leave Applied by users(appliedLeave),
I stored all the saturday's and sundays of 2018 in officialHolidays Table
here is one scenerio
I have 2 users
user A Applied a leave from 1 dec 2018 to 10 dec 2018 so leaveApplied for this user is 7 days as 2nd and 9th dec is sunday 8th dec is non working saturday
user B Applied a leave from 1 dec 2018 to 5 dec 2018 so leaveApplied for this user is 4 days as 2nd dec is sunday
And Dec 1 is saturday this saturday is working saturday as per my db but now I am giving this saturday as official holiday, I updated officialHolidays and now I want to update appliedLeave table too so that the LeaveApplied for user A becomes 6 and user B becomes 3
I want to use loops to update the reord of user A then User B
here is my update query
UPDATE officialHolidays SET
Active = @Active
WHERE OfficialID = @OfficialID
this is what I Tried just now
DECLARE @HolidayDate AS DATE = (SELECT Date FROM officialHolidays WHERE OfficialID = @OfficialID)
IF(EXISTS(SELECT 1 FROM AppliedLeave WHERE AppliedFrom BETWEEN @HolidayDate AND @HolidayDate
OR AppliedTo BETWEEN @HolidayDate AND @HolidayDate))
BEGIN
DECLARE @LeaveTaken AS FLOAT
DECLARE @LeaveRemaining AS FLOAT
--I want to add Loop Here
END
how can I add a loop in this scenerio?
sql-server loops stored-procedures add a comment |
I have 2 tables in my sql server in 1st table I am saving official holidays(officialHolidays) and in second table I am saving Leave Applied by users(appliedLeave),
I stored all the saturday's and sundays of 2018 in officialHolidays Table
here is one scenerio
I have 2 users
user A Applied a leave from 1 dec 2018 to 10 dec 2018 so leaveApplied for this user is 7 days as 2nd and 9th dec is sunday 8th dec is non working saturday
user B Applied a leave from 1 dec 2018 to 5 dec 2018 so leaveApplied for this user is 4 days as 2nd dec is sunday
And Dec 1 is saturday this saturday is working saturday as per my db but now I am giving this saturday as official holiday, I updated officialHolidays and now I want to update appliedLeave table too so that the LeaveApplied for user A becomes 6 and user B becomes 3
I want to use loops to update the reord of user A then User B
here is my update query
UPDATE officialHolidays SET
Active = @Active
WHERE OfficialID = @OfficialID
this is what I Tried just now
DECLARE @HolidayDate AS DATE = (SELECT Date FROM officialHolidays WHERE OfficialID = @OfficialID)
IF(EXISTS(SELECT 1 FROM AppliedLeave WHERE AppliedFrom BETWEEN @HolidayDate AND @HolidayDate
OR AppliedTo BETWEEN @HolidayDate AND @HolidayDate))
BEGIN
DECLARE @LeaveTaken AS FLOAT
DECLARE @LeaveRemaining AS FLOAT
--I want to add Loop Here
END
how can I add a loop in this scenerio?
sql-server loops stored-procedures
1
loop in sql = bad idea. Do not use loop when they can be avoided (and most of the time they can be). SQL works best with a set based approach.
– Zohar Peled
Nov 23 '18 at 6:58
Sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 23 '18 at 7:40
Finally I achieved what I wanted thanks guys for your help
– user10249871
Nov 23 '18 at 8:05
No you did not. Your IF statement is completely wrong. @HolidayDate is a single, scalar value - using it as both upper and lower boundaries in a between statement is just nonsense. Using floats instead of integers to store simple numbers is not a good sign either.
– SMor
Nov 23 '18 at 13:08
@SMor leavetaken can be in digits and HolidayDate is only an example I have 2 different date to compare
– user10249871
Nov 24 '18 at 9:59
add a comment |
I have 2 tables in my sql server in 1st table I am saving official holidays(officialHolidays) and in second table I am saving Leave Applied by users(appliedLeave),
I stored all the saturday's and sundays of 2018 in officialHolidays Table
here is one scenerio
I have 2 users
user A Applied a leave from 1 dec 2018 to 10 dec 2018 so leaveApplied for this user is 7 days as 2nd and 9th dec is sunday 8th dec is non working saturday
user B Applied a leave from 1 dec 2018 to 5 dec 2018 so leaveApplied for this user is 4 days as 2nd dec is sunday
And Dec 1 is saturday this saturday is working saturday as per my db but now I am giving this saturday as official holiday, I updated officialHolidays and now I want to update appliedLeave table too so that the LeaveApplied for user A becomes 6 and user B becomes 3
I want to use loops to update the reord of user A then User B
here is my update query
UPDATE officialHolidays SET
Active = @Active
WHERE OfficialID = @OfficialID
this is what I Tried just now
DECLARE @HolidayDate AS DATE = (SELECT Date FROM officialHolidays WHERE OfficialID = @OfficialID)
IF(EXISTS(SELECT 1 FROM AppliedLeave WHERE AppliedFrom BETWEEN @HolidayDate AND @HolidayDate
OR AppliedTo BETWEEN @HolidayDate AND @HolidayDate))
BEGIN
DECLARE @LeaveTaken AS FLOAT
DECLARE @LeaveRemaining AS FLOAT
--I want to add Loop Here
END
how can I add a loop in this scenerio?
sql-server loops stored-procedures I have 2 tables in my sql server in 1st table I am saving official holidays(officialHolidays) and in second table I am saving Leave Applied by users(appliedLeave),
I stored all the saturday's and sundays of 2018 in officialHolidays Table
here is one scenerio
I have 2 users
user A Applied a leave from 1 dec 2018 to 10 dec 2018 so leaveApplied for this user is 7 days as 2nd and 9th dec is sunday 8th dec is non working saturday
user B Applied a leave from 1 dec 2018 to 5 dec 2018 so leaveApplied for this user is 4 days as 2nd dec is sunday
And Dec 1 is saturday this saturday is working saturday as per my db but now I am giving this saturday as official holiday, I updated officialHolidays and now I want to update appliedLeave table too so that the LeaveApplied for user A becomes 6 and user B becomes 3
I want to use loops to update the reord of user A then User B
here is my update query
UPDATE officialHolidays SET
Active = @Active
WHERE OfficialID = @OfficialID
this is what I Tried just now
DECLARE @HolidayDate AS DATE = (SELECT Date FROM officialHolidays WHERE OfficialID = @OfficialID)
IF(EXISTS(SELECT 1 FROM AppliedLeave WHERE AppliedFrom BETWEEN @HolidayDate AND @HolidayDate
OR AppliedTo BETWEEN @HolidayDate AND @HolidayDate))
BEGIN
DECLARE @LeaveTaken AS FLOAT
DECLARE @LeaveRemaining AS FLOAT
--I want to add Loop Here
END
how can I add a loop in this scenerio?
sql-server loops stored-procedures sql-server loops stored-procedures asked Nov 23 '18 at 6:51
user10249871
1
loop in sql = bad idea. Do not use loop when they can be avoided (and most of the time they can be). SQL works best with a set based approach.
– Zohar Peled
Nov 23 '18 at 6:58
Sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 23 '18 at 7:40
Finally I achieved what I wanted thanks guys for your help
– user10249871
Nov 23 '18 at 8:05
No you did not. Your IF statement is completely wrong. @HolidayDate is a single, scalar value - using it as both upper and lower boundaries in a between statement is just nonsense. Using floats instead of integers to store simple numbers is not a good sign either.
– SMor
Nov 23 '18 at 13:08
@SMor leavetaken can be in digits and HolidayDate is only an example I have 2 different date to compare
– user10249871
Nov 24 '18 at 9:59
add a comment |
1
loop in sql = bad idea. Do not use loop when they can be avoided (and most of the time they can be). SQL works best with a set based approach.
– Zohar Peled
Nov 23 '18 at 6:58
Sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 23 '18 at 7:40
Finally I achieved what I wanted thanks guys for your help
– user10249871
Nov 23 '18 at 8:05
No you did not. Your IF statement is completely wrong. @HolidayDate is a single, scalar value - using it as both upper and lower boundaries in a between statement is just nonsense. Using floats instead of integers to store simple numbers is not a good sign either.
– SMor
Nov 23 '18 at 13:08
@SMor leavetaken can be in digits and HolidayDate is only an example I have 2 different date to compare
– user10249871
Nov 24 '18 at 9:59
1
1
loop in sql = bad idea. Do not use loop when they can be avoided (and most of the time they can be). SQL works best with a set based approach.
– Zohar Peled
Nov 23 '18 at 6:58
loop in sql = bad idea. Do not use loop when they can be avoided (and most of the time they can be). SQL works best with a set based approach.
– Zohar Peled
Nov 23 '18 at 6:58
Sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 23 '18 at 7:40
Sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 23 '18 at 7:40
Finally I achieved what I wanted thanks guys for your help
– user10249871
Nov 23 '18 at 8:05
Finally I achieved what I wanted thanks guys for your help
– user10249871
Nov 23 '18 at 8:05
No you did not. Your IF statement is completely wrong. @HolidayDate is a single, scalar value - using it as both upper and lower boundaries in a between statement is just nonsense. Using floats instead of integers to store simple numbers is not a good sign either.
– SMor
Nov 23 '18 at 13:08
No you did not. Your IF statement is completely wrong. @HolidayDate is a single, scalar value - using it as both upper and lower boundaries in a between statement is just nonsense. Using floats instead of integers to store simple numbers is not a good sign either.
– SMor
Nov 23 '18 at 13:08
@SMor leavetaken can be in digits and HolidayDate is only an example I have 2 different date to compare
– user10249871
Nov 24 '18 at 9:59
@SMor leavetaken can be in digits and HolidayDate is only an example I have 2 different date to compare
– user10249871
Nov 24 '18 at 9:59
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53441891%2floop-in-sql-to-update-record-as-per-user%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53441891%2floop-in-sql-to-update-record-as-per-user%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
loop in sql = bad idea. Do not use loop when they can be avoided (and most of the time they can be). SQL works best with a set based approach.
– Zohar Peled
Nov 23 '18 at 6:58
Sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
– Zohar Peled
Nov 23 '18 at 7:40
Finally I achieved what I wanted thanks guys for your help
– user10249871
Nov 23 '18 at 8:05
No you did not. Your IF statement is completely wrong. @HolidayDate is a single, scalar value - using it as both upper and lower boundaries in a between statement is just nonsense. Using floats instead of integers to store simple numbers is not a good sign either.
– SMor
Nov 23 '18 at 13:08
@SMor leavetaken can be in digits and HolidayDate is only an example I have 2 different date to compare
– user10249871
Nov 24 '18 at 9:59