November 17, 2017 at 10:08 am
Hi,
I would like to add new variable(Current Complete Week -1) to the SP for below condition.
Current Complete Week -1:
Intention is to return the week previous to current week.
If Max(Sales_date) is Sunday, then count back two Mondays to the next Sunday,
else count back three Mondays to the next Sunday.
Please find attachment as SP.
Thank you !!
November 17, 2017 at 10:19 am
OK, but what's your question?
Are you asking us to modify that huge proc for you?
The people replying here do so for free, in their own time, so asking them to do your job for you is not really fair.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 17, 2017 at 10:57 am
Yes i agree. I don't want you debug my big SP. It is not at all fair.
I am requesting help to add the logic for 'f_current_complete_week-1' to below code part.so that i can add this to my SP.
some how below code part is not working for my condition.
=====================================================================================
BEGIN
DECLARE @f_current_complete_week-1$in_sales_date DATETIME
DECLARE @f_current_complete_week-1$_value_argument FLOAT(53)
SET @f_current_complete_week-1$in_sales_date = @v_param_date
SET @f_current_complete_week-1$_value_argument = @v_current_complete_week
BEGIN
DECLARE @f_current_complete_week-1$v_dsd_key FLOAT(53)
,@f_current_complete_week-1$v_sql VARCHAR(max)
,@f_current_complete_week-1$v_week_start DATETIME
,@f_current_complete_week-1$v_week_end DATETIME
,@f_current_complete_week-1$v_prev_week_start DATETIME
,@f_current_complete_week-1$v_prev_week_end DATETIME
BEGIN TRY
SELECT @f_current_complete_week-1$v_dsd_key = DSD_DEFN_CAN.DSD_KEY
FROM dbo.DSD_DEFN_CAN
WHERE DSD_DEFN_CAN.DSD_CRITERIA = 'CURRENT_COMPLETE_WEEK'
PRINT 'Start********Current complete week population'
PRINT 'v_dsd_key :' + ISNULL(CAST(@f_current_complete_week-1$v_dsd_key AS NVARCHAR(max)), '')
SELECT DISTINCT @f_current_complete_week-1$v_week_start = SLS_CAN_SALESWEEK.SLS_SALES_WEEK_STARTING_DATE
,@f_current_complete_week-1$v_week_end = SLS_CAN_SALESWEEK.SLS_SALES_WEEK_ENDING_DATE
FROM dbo.SLS_CAN_SALESWEEK
WHERE @f_current_complete_week-1$in_sales_date BETWEEN SLS_CAN_SALESWEEK.SLS_SALES_WEEK_STARTING_DATE
AND SLS_CAN_SALESWEEK.SLS_SALES_WEEK_ENDING_DATE
PRINT 'V_WEEK_START :' + ISNULL(CAST(@f_current_complete_week-1$v_week_start AS NVARCHAR(max)), '') + '--' + ' V_WEEK_END :' + ISNULL(CAST(@f_current_complete_week-1$v_week_end AS NVARCHAR(max)), '')
IF @f_current_complete_week-1$in_sales_date = @f_current_complete_week-1$v_week_end
SET @f_current_complete_week-1$v_sql = 'INSERT INTO dbo.DSD_REFERENCE_CAN SELECT SALES_DATE_KEY,' + ISNULL(CAST(@f_current_complete_week-1$v_dsd_key AS NVARCHAR(max)), '') + ',USER,SYSDATETIME() FROM SLS_CAN_SALESTIME_SDP_LKUP
WHERE SALES_DATE BETWEEN ''' + ISNULL(CAST(@f_current_complete_week-1$v_week_start AS NVARCHAR(max)), '') + ''' AND ''' + ISNULL(CAST(@f_current_complete_week-1$v_week_end AS NVARCHAR(max)), '') + ''' ORDER BY 1'
ELSE
BEGIN
IF @f_current_complete_week-1$in_sales_date < @f_current_complete_week-1$v_week_end
BEGIN
SELECT TOP (1) @f_current_complete_week-1$v_prev_week_start = fci.SLS_SALES_WEEK_STARTING_DATE
,@f_current_complete_week-1$v_prev_week_end = fci.SLS_SALES_WEEK_ENDING_DATE
FROM (
SELECT TOP 1 SLS_CAN_SALESWEEK.SLS_SALES_WEEK_STARTING_DATE
,SLS_CAN_SALESWEEK.SLS_SALES_WEEK_ENDING_DATE
FROM dbo.SLS_CAN_SALESWEEK
WHERE SLS_CAN_SALESWEEK.SLS_SALES_WEEK_STARTING_DATE < @f_current_complete_week-1$v_week_start
ORDER BY SLS_CAN_SALESWEEK.SLS_SALES_WEEK_STARTING_DATE DESC
) AS fci
SET @f_current_complete_week-1$v_sql = 'INSERT INTO dbo.DSD_REFERENCE_CAN SELECT SALES_DATE_KEY,' + ISNULL(CAST(@f_current_complete_week-1$v_dsd_key AS NVARCHAR(max)), '') + ',USER,SYSDATETIME() FROM dbo.SLS_CAN_SALESTIME_SDP_LKUP
WHERE SALES_DATE BETWEEN ''' + ISNULL(CAST(@f_current_complete_week-1$v_prev_week_start AS NVARCHAR(max)), '') + ''' AND ''' + ISNULL(CAST(@f_current_complete_week-1$v_prev_week_end AS NVARCHAR(max)), '') + ''' ORDER BY 1'
END
END
PRINT @f_current_complete_week-1$v_sql
PRINT '********End'
EXECUTE (@f_current_complete_week-1$v_sql)
SET @f_current_complete_week-1$_value_argument = 0
--GOTO f_current_complete_week-1$_LABEL
END TRY
BEGIN CATCH
SET @v_ret_code = ERROR_NUMBER();
SET @ErrorLine = ERROR_LINE();
SET @ErrorMessage = ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
PRINT 'No data found for CURRENT_COMPLETE_WEEK-1 criteria'
PRINT 'Actual error number: ' + CAST(@v_ret_code AS VARCHAR(10));
PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));
SET @f_current_complete_week-1$_value_argument = - 1
--GOTO f_current_complete_week-1$_LABEL
END CATCH
END
f_current_complete_week-1$_LABEL:
SET @v_current_complete_week = @f_current_complete_week-1$_value_argument
END
=======================================================================================================
November 17, 2017 at 2:49 pm
adisql - Friday, November 17, 2017 10:08 AMHi,I would like to add new variable(Current Complete Week -1) to the SP for below condition.
Current Complete Week -1:
Intention is to return the week previous to current week.
If Max(Sales_date) is Sunday, then count back two Mondays to the next Sunday,
else count back three Mondays to the next Sunday.Please find attachment as SP.
Thank you !!
It's Friday and I have a lot of other things on my mind besides work as well. How about showing us what you are trying to accomplish with some examples.
November 17, 2017 at 4:32 pm
Lynn Pettis - Friday, November 17, 2017 2:49 PMadisql - Friday, November 17, 2017 10:08 AMHi,I would like to add new variable(Current Complete Week -1) to the SP for below condition.
Current Complete Week -1:
Intention is to return the week previous to current week.
If Max(Sales_date) is Sunday, then count back two Mondays to the next Sunday,
else count back three Mondays to the next Sunday.Please find attachment as SP.
Thank you !!
It's Friday and I have a lot of other things on my mind besides work as well. How about showing us what you are trying to accomplish with some examples.
I have prepared the attached code part and included in my SP. PFA.
November 17, 2017 at 5:06 pm
adisql - Friday, November 17, 2017 4:32 PMLynn Pettis - Friday, November 17, 2017 2:49 PMadisql - Friday, November 17, 2017 10:08 AMHi,I would like to add new variable(Current Complete Week -1) to the SP for below condition.
Current Complete Week -1:
Intention is to return the week previous to current week.
If Max(Sales_date) is Sunday, then count back two Mondays to the next Sunday,
else count back three Mondays to the next Sunday.Please find attachment as SP.
Thank you !!
It's Friday and I have a lot of other things on my mind besides work as well. How about showing us what you are trying to accomplish with some examples.
I have prepared the attached code part and included in my SP. PFA.
That's nice, but it doesn't show me what you are asking for at this time. Given date x, what do you expect in return? Show me that, not your code.
November 17, 2017 at 5:32 pm
Lynn Pettis - Friday, November 17, 2017 5:06 PMadisql - Friday, November 17, 2017 4:32 PMLynn Pettis - Friday, November 17, 2017 2:49 PMadisql - Friday, November 17, 2017 10:08 AMHi,I would like to add new variable(Current Complete Week -1) to the SP for below condition.
Current Complete Week -1:
Intention is to return the week previous to current week.
If Max(Sales_date) is Sunday, then count back two Mondays to the next Sunday,
else count back three Mondays to the next Sunday.Please find attachment as SP.
Thank you !!
It's Friday and I have a lot of other things on my mind besides work as well. How about showing us what you are trying to accomplish with some examples.
I have prepared the attached code part and included in my SP. PFA.
That's nice, but it doesn't show me what you are asking for at this time. Given date x, what do you expect in return? Show me that, not your code.
Current complete week:
2017-10-30 00:00:00 2017-11-05 00:00:00
we need to add one more new variable for "Current complete week-1" to this sp ,that should displays the data for the week of below.
Current complete week-1 :
2017-10-23 00:00:00 2017-10-29 00:00:00
November 17, 2017 at 5:41 pm
adisql - Friday, November 17, 2017 5:32 PMLynn Pettis - Friday, November 17, 2017 5:06 PMadisql - Friday, November 17, 2017 4:32 PMLynn Pettis - Friday, November 17, 2017 2:49 PMadisql - Friday, November 17, 2017 10:08 AMHi,I would like to add new variable(Current Complete Week -1) to the SP for below condition.
Current Complete Week -1:
Intention is to return the week previous to current week.
If Max(Sales_date) is Sunday, then count back two Mondays to the next Sunday,
else count back three Mondays to the next Sunday.Please find attachment as SP.
Thank you !!
It's Friday and I have a lot of other things on my mind besides work as well. How about showing us what you are trying to accomplish with some examples.
I have prepared the attached code part and included in my SP. PFA.
That's nice, but it doesn't show me what you are asking for at this time. Given date x, what do you expect in return? Show me that, not your code.
Current complete week:
2017-10-30 00:00:00 2017-11-05 00:00:00we need to add one more new variable for "Current complete week-1" to this sp ,that should displays the data for the week of below.
Current complete week-1 :
2017-10-23 00:00:00 2017-10-29 00:00:00
In Application, if we click the button "Current complete week" , it displays the below week.
2017-10-23 00:00:00 2017-10-29 00:00:00
In the same manner..
In Application, if we click the button "Current complete week-1" then it should display the below week.
2017-10-23 00:00:00 2017-10-29 00:00:00
November 20, 2017 at 10:30 am
Still incomplete. I need to know the date that is input and the date (or dates) that are output, not that "I click a button and this is returned.
The date 2017-10-20 is entered, what is returned for each scenario.
The date 2017-10-19 is entered, what is returned for each scenario.
The date 2017-10-22 is entered, what is returned for each scenario.
That is what I am looking for, not what is done in the application to get what you are looking for here.
November 20, 2017 at 10:47 am
I would like to add new variable(Current Complete Week -1) to the SP for below condition.
Current Complete Week -1:
Intention is to return the week previous to current week.
I haven't reviewed the code, but if you've already computed "Current Week", can't you just subtract 7 days to get the week before that one?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 20, 2017 at 12:57 pm
Lynn Pettis - Monday, November 20, 2017 10:30 AMStill incomplete. I need to know the date that is input and the date (or dates) that are output, not that "I click a button and this is returned.
The date 2017-10-20 is entered, what is returned for each scenario.
The date 2017-10-19 is entered, what is returned for each scenario.
The date 2017-10-22 is entered, what is returned for each scenario.That is what I am looking for, not what is done in the application to get what you are looking for here.
Those are out put dates.
November 20, 2017 at 1:04 pm
adisql - Monday, November 20, 2017 12:57 PMLynn Pettis - Monday, November 20, 2017 10:30 AMStill incomplete. I need to know the date that is input and the date (or dates) that are output, not that "I click a button and this is returned.
The date 2017-10-20 is entered, what is returned for each scenario.
The date 2017-10-19 is entered, what is returned for each scenario.
The date 2017-10-22 is entered, what is returned for each scenario.That is what I am looking for, not what is done in the application to get what you are looking for here.
Those are out put dates.
What are output dates? Given the first dates above, what dates are you expecting for the two different scenarios?
November 20, 2017 at 1:29 pm
Lynn Pettis - Monday, November 20, 2017 1:04 PMadisql - Monday, November 20, 2017 12:57 PMLynn Pettis - Monday, November 20, 2017 10:30 AMStill incomplete. I need to know the date that is input and the date (or dates) that are output, not that "I click a button and this is returned.
The date 2017-10-20 is entered, what is returned for each scenario.
The date 2017-10-19 is entered, what is returned for each scenario.
The date 2017-10-22 is entered, what is returned for each scenario.That is what I am looking for, not what is done in the application to get what you are looking for here.
Those are out put dates.
What are output dates? Given the first dates above, what dates are you expecting for the two different scenarios?
Expecting dates:
--current complete wk
2017-11-06 00:00:00 2017-11-12 00:00:00
--current complete wk-1
2017-10-30 00:00:00 2017-11-05 00:00:00
November 20, 2017 at 1:47 pm
See if this helps, it is based on us-English settings.
DECLARE @TestDate DATETIME;
SET @TestDate = '2017-11-19';
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 2, 0), DATEADD(DAY,6,DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 2, 0));
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 3, 0), DATEADD(DAY,6,DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 3, 0));
SET @TestDate = '2017-11-20';
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 2, 0), DATEADD(DAY,6,DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 2, 0));
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 3, 0), DATEADD(DAY,6,DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 3, 0));
SET @TestDate = '2017-11-25';
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 2, 0), DATEADD(DAY,6,DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 2, 0));
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 3, 0), DATEADD(DAY,6,DATEADD(WEEK,DATEDIFF(WEEK,0,@TestDate) - 3, 0));
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply