December 22, 2020 at 8:27 pm
CREATE Table TABLEA(N varchar(50),Q int,R int,Dt1 datetime,Dt2 datetime)
DECLARE @I int = 1
DECLARE @R int = 2
DECLARE @Q int = 3
DECLARE @Q1 int
SET @Q1 =@Q
DECLARE @N varchar(100)
SET @N = 'NameA'
WHILE (@I <= @R+1)
BEGIN
WHILE (@I <= @Q)
BEGIN
SELECT @N as Name,@I,@R,@Q
SET @Q = @Q - 1
END
SET @Q = @Q1
SET @R = @R -1
END
N I R Q Dt1 Dt2
NameA123 2020-12-23
NameA122 2021-01-22
NameA121 2020-02-21 2020-02-16
NameA113 2020-03-23
NameA112 2020-04-22
NameA111 2020-05-22 2020-05-15
NameA103 2020-06-21
NameA102 2020-07-21
NameA101 2020-08-20 2020-08-13
NameB 1 1 2 2020-12-23
NameB 1 1 1 2020-12-24 2020-12-23
NameB 1 0 2 2020-12-25
NameB 1 0 1 2020-12-26 2020-12-25
When I exec the below
exec [dbo].[SP] 'NameA',10,2,'2020-12-23'My result should be as above.
Name A:
My Dt1 should be increasing by 1 month for every new record as we see.I will be using this function DATEADD(MONTH,1,@Dt1) to increase the date for every new record.
My values for Dt1 and Dt2 should be as above.
I could get I R and Q as above but not sure how to get Dt1 and Dt2
Condition is When my Q value goes to 1 for each similar value of R and difference between Dt1 values for similar values of R is more than 7 days between (Q max and when Q becomes 1) then Dt2 should be 7 days before Dt1 for that row.
Ex:
NameA123 2020-12-23
NameA122 2021-01-22
NameA121 2020-02-21 2020-02-16
2020-12-23 and 2020-02-21 Difference between these 2 dates is more than 7 days so Dt2 will be 7 days less than 2020-02-21 and that will be 2020-020-16
NameB: incrementing 1 day each - DATEADD(DAY,1,@Dt1)
Dt1 value increments by only 1 day each so
NameB 1 1 2 2020-12-23
NameB 1 1 1 2020-12-24 2020-12-23
Condition when my Q value goes to 1 for each similar value of R and difference between Dt1 values for similar values of R is not more than 7 days between (Q max and when Q becomes 1) then Dt2 should be Dt1 value when Q value is maximum(2 above)
So when Q is max(2 in above) example (it could be anything 3 or 4 etc as per SP input) that Dt1 should be Dt2 value when Q becomes 1 as difference between (Q max and when Q becomes 1) is less than 7 days.
Dt1 values for Q values 2 and 1 is less than 7 days. So Dt2 is 2020-12-25
NameB 1 0 2 2020-12-25
NameB 1 0 1 2020-12-26 2020-12-25
Thanks
December 22, 2020 at 10:08 pm
You don't seem to know that a table must have a key to be valid. This is usually covered the first week of any RDBMS class. What you posted is basically deck of 1960s punchcards written in SQL and since all of the columns are NLL-able it can never have a key.
You also don't know the difference between a row and a record. Your code looks like its old Fortran, right down to the commas in the wrong place and single letter variable names. Things like "_date" are what we call attribute characteristics. Based on the first principle of all logic, the law of identity (to be is to be something in particular; to be nothing in particular or something. In general, is to be nothing at all), we need to have a "something in particular date" and not generic.
>> My something_date should be increasing by 1 month for every new record [sic] as we see.I will be using this function DATEADD(MONTH,1,@something_date) to increase the date for every new record [sic]. <<
SQL is a declarative language, but your writing it like it was 1960s Fortran. You even write your code in uppercase short named variables just like I did over 50 years ago when I was a Fortran programmer!
Declarative languages do not have loops. Declarative languages do not have if-then-else constructs. Declarative languages also hate the use of local variables. Rows in a given table always have the same number of columns in them. Some of your rows have an extra date column in them. Are they NULL? Why are you using T-SQL procedural to mimic FORTRAN?
Can you please start over and explain what you are trying to do.
Please post DDL and follow ANSI/ISO standards when asking for help.
December 22, 2020 at 10:13 pm
To me this seems like a weird request, but there are a few things I am unclear on with it. First, does this need to be in a while loop? Second, does this need to be done in SQL? Next, what causes the N value to change and how do you decide what value comes next?
so I am seeing this as being a stored procedure based on your exec [dbo].[SP] portion listed above. From that, I can see where NameA comes from (parameter 1 in the stored procedure) and where the first value for Dt1 comes from (parameter 4), but what is the "10" and "2" for? I am thinking the 2 is the max R value. Looking at Dt1 for NameA, you say it is incrementing by 1 month, but it looks like it is incrementing by 30 days, not 1 month (quick eyeball... didn't actually confirm this).
But if you want to go the loop route, you would need a counter (could use Q1-Q + 1) and use that counter as your number of months to add. That should handle your Dt1 value. As for Dt2, do a check if Q=1 then use dateadd twice - once to get the correct month and then once again to get 1 week prior.
Now for NameB you'd have a different bit of logic but still similar. Use the counter (Q1-Q+1) and do a dateadd of that number of days instead of months and then if Q=1 then Dt2 becomes dateadd (Q1-Q).
Now doing this without a loop would be much more fun and efficient. SQL is not efficient at loops, it prefers working with sets of data. So if you can generate N, I, R and Q, then you can generate Dt1 and Dt2 on the entire set rather than on the row pretty easily using multiple methods. My first thought is to use a CTE which would be used to get the MAX Q value partitioned by the N and R values and use that max Q along with Q to do the Dt1 and Dt2 calculations.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 23, 2020 at 1:48 am
Was anything resolved on the earlier q where you asked about this?
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".
December 23, 2020 at 5:36 am
Yes it did for most part of it.
The stored procedure takes Name, month or day as input and generates records accordingly.The records are generated as expected but for Dt2 column whose values are based on the Dt1 ,Q and R columns they are not as expected.I am getting min value of the entire Dt1 column value when difference in Dt1(Max Q1 and Min Q1(1 always) is less than 7 days. But what I should be getting is min value of Dt1 for each Q iteration (and same R values) as below.
Q iterates for each R value until Q becomes 1.
3 1 01-01-2020
2 1 01-02-2020
1 1 01-03-2020 01-01-2020
As mentioned Dt2 is the problem here.
Dt1 is provided as an input from the stored procedure and records are generated accordingly using this - DATEADD(MONTH,1,@Dt1) and also based on Q and R values.
If Q max (input from stored procedure is 3 and R max input from stored procedure is say 1 then 6 records are generated .
For same set of R values - Difference of Dt1 between Q Max and Q min is more than 7 days then Dt2 should be 7 days prior to corresponding Dt1 's value - So 02-23-2020 for 03-01-2020 and so on,
Q R Dt1 Dt2
3 1 01-01-2020
2 1 02-01-2020
1 1 03-01-2020 02-23-2020
3 0 04-01-2020
2 0 05-01-2020
1 0 06-01-2020 05-23-2020
But when records are generated by days and Dt1 values are less than 7 days between Q Max and Q Min values(always 1) then Dt2 should be equal to Dt1 value from the record where Q is Max for that iteration as below
Q R Dt1 Dt2
3 1 01-01-2020
2 1 01-02-2020
1 1 01-03-2020 01-01-2020
3 0 01-04-2020
2 0 01-05-2020
1 0 01-06-2020 01-04-2020
Thanks
December 23, 2020 at 5:37 am
December 23, 2020 at 9:42 am
Hello Brain, I guess I was not clear enough before....
I know he's clever, but still probably prefers to be called Brian 🙂
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
December 23, 2020 at 7:50 pm
Heh, I enjoy being called:
1 - brain
2 - clever
I feel smart! S-M-R-T. But Phil is correct - I prefer being called Brian.
But yes, I think that using CTE's and DATEADD based on some math with the max Q value and current Q ((max Q-current Q + 1)* increment value) will get you Dt1 pretty easy. Dt2 you just need to know what you are incrementing by and what max Q is and current Q. Something along the lines of:
IF current Q = 1
BEGIN
IF max Q * increment > 7
BEGIN
dateadd(day,-7,Dt1)
END
ELSE
BEGIN
dateadd(day,max Q * increment * -1, Dt1)
END
ELSE
NULL
END
NOTE syntax above is not 100% TSQL code, but more pseudo code.
If you are still stumped, posting what you have tried will make it easier for us to help. I know I like working off of the code you tried already as it sounds like you are CLOSE to a solution, just Dt2 is not being calculated quite right.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 27, 2020 at 9:14 pm
The stored procedure takes Name, month or day as input
It would seem that the inputs are a wee bit important as to figuring out if the proc will be by month or by day. How do you determine which type of run it will be?
My recommendation is that you should post all of the inputs the proc takes and the full section of code from that proc you're working with so that we can stop guess on the gazintas.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 27, 2020 at 9:17 pm
Also, on the other thread for this subject, I posted a question...
Please, just so we know, why does this has to be a While loop? For example, will you be calling a stored procedure in every iteration of the loop?
That question has not yet been answered.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2020 at 4:54 pm
CREATE PROCEDURE [dbo].[ABCD]
(
@M int=0,@D int=0,
@F int=0,@Q int=0,@T datetime,@R int=0,
@RR datetime = NULL,@IO int=0
)
AS
BEGIN
DECLARE @I int = 1
DECLARE @QC int
SET @QC = @Q
DECLARE @Tm DATETIME
BEGIN
WHILE (@I <= @R +1 )
BEGIN
WHILE (@I <= @Q)
BEGIN
INSERT INTO [dbo].[DTable] SELECT @M,@D,@F,@T,@Q,@R,@RR,@IO
UPDATE [dbo].[DTable] SET [RR] = @T - 7 where @Q = 1
SET @Q = @Q - 1
SET @Tm = CASE
WHEN @M = 1 AND @F = 1 THEN DATEADD(MONTH,1,@T)
WHEN @D = 1 AND @F = 1 THEN DATEADD(HOUR,24,@T)
END
SET @T = @Tm
END
SET @Q = @QC
SET @R = @R - 1
END
END
END
Example 1:
CURRENTLY WRONG SET
TQRRR
12/31/20 22:30511/2/21 22:30
1/1/21 22:30411/2/21 22:30
1/2/21 22:30311/2/21 22:30
1/3/21 22:30211/2/21 22:30
1/4/21 22:30111/2/21 22:30
1/5/21 22:30501/2/21 22:30
1/6/21 22:30401/2/21 22:30
1/7/21 22:30301/2/21 22:30
1/8/21 22:30201/2/21 22:30
1/9/21 22:30101/2/21 22:30
Difference of days between Q Max(2) and Q Min(1) is < 7 days so RR should be Min(T) for all rows where R=0 so RR should be 12/31/20 22:30
Difference of days between Q Max(2) and Q Min(1) is < 7 days so RR should be Min(T) for all rows where R=0 so RR should be 1/5/21 22:30
SHOULD BE THIS
TQRRR
12/31/20 22:305112/31/20 22:30
1/1/21 22:304112/31/20 22:30
1/2/21 22:303112/31/20 22:30
1/3/21 22:302112/31/20 22:30
1/4/21 22:301112/31/20 22:30
1/5/21 22:30501/5/21 22:30
1/6/21 22:30401/5/21 22:30
1/7/21 22:30301/5/21 22:30
1/8/21 22:30201/5/21 22:30
1/9/21 22:30101/5/21 22:30
Example 2:
CURRENTLY WRONG SET
TQRRR
12/31/20 22:30213/21/21 22:30
1/31/21 22:30113/21/21 22:30
2/28/21 22:30203/21/21 22:30
3/28/21 22:30103/21/21 22:30
Difference of days between Q Max(2) and Q Min(1) is > 7 days so RR should be Max(T) - 7 days for all rows where R=1 so RR should be 1/24/2021 22:30
Difference of days between Q Max(2) and Q Min(1) is > 7 days so RR should be Max(T) - 7 days for all rows where R=0 so RR should be 3/21/2021 22:30
SHOULD BE THIS
TQRRR
12/31/2020 22:30211/24/2021 22:30
1/31/2021 22:30111/24/2021 22:30
2/28/2021 22:30203/21/2021 22:30
3/28/2021 22:30103/21/2021 22:30
exec [dbo].[ABCD] 1,0, 1,2,'2020-12-31 22:30',1,'','',1
M D F Q T R IO
If M value is provided then D should be 0
If D value is provided then M should be 0
Thanks
December 31, 2020 at 5:25 pm
So, I think the problem you are having is that you are doing the update based on a VARIABLE not a COLUMN VALUE. Change:
UPDATE [dbo].[DTable] SET [RR] = @T - 7 where @Q = 1
to
UPDATE [dbo].[DTable] SET [RR] = [T] - 7 where= 1
and you should be good to go. And that bit doesn't even need to be in a loop. Otherwise your logic is all goofy because you are not picking any row to work off of, you are just changing everything once @Q = 1.
Not sure if that will fix ALL of your problems, but it should at least fix SOME of your problems. Overall, I think your code is the wrong approach to this problem. I don't think a LOOP is what you need except MAYBE to populate the initial data (not sure what your data looks like).
What I'd recommend is once you have the data populated, do the rest outside of a loop. It looks pretty easy to do outside the loop once you have T, Q and R... something similar to (note this is untested, and may not be the most optimal solution, plus the below is just to SEE the data; you'd need to modify this into an UPDATE statement to make the value for RR be persisted to the table, but that is pretty easy to do):
WITH cte AS (
SELECT [T],, [R], MAX([T]) OVER (PARTITION BY [R]) AS [MAX_T], MIN([T]) OVER (PARTITION BY [R]) AS [MIN_T]
FROM [dbo].[DTable]
)
SELECT [T],, [R], CASE WHEN DATEDIFF(DAY,[MAX_T],[MIN_T]) < 7 THEN [MIN_T] ELSE DATEADD(day,-7,[MAX_T]) END AS [RR]
FROM cte
No loop and it should be calculating RR as you indicated. I would be building some "sanity" into the stored procedure if it was me though. Step 1 of the sanity - make sure the input is valid. The only rule I see for your input is that if M is provided, D should be 0. If D is provided, M should be 0. My first check would be to verify that either M OR D is provided AND that the value is expected. The way I would do this would be to add the absolute values of both M and D together. If the value is 1, continue, if it is not 1, return an error code ("RETURN 10" for example).
As the column RR is a calculated column in the table, so I don't think there is much point in having a variable for RR, especially as an input variable.
That is just my 2 cents though... and to be completely honest, I don't think the loops are required either.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 31, 2020 at 7:25 pm
I appreciate the unabridged version of the code but, like I asked above and on your previous post about this code, I see absolutely no reason at all for this to be relegated to a WHILE loop.
With that, I'll ask again, why do you insist this must be a WHILE loop? The answer to that question just might allow us to surprise you with a much better bit of code that does what you want but without the unnecessary overhead of a WHILE loop.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2020 at 9:53 pm
Instead of
INSERT INTO [dbo].[DTable] SELECT @m,@D,@F,@T,@Q,@R,@RR,@IO
UPDATE [dbo].[DTable] SET [RR] = @T - 7 where @Q = 1
Should it be
INSERT INTO [dbo].[DTable]
SELECT @m,@D,@F,@T,@Q,@R,case when @Q=1 then @T-7 else @RR end,@IO
_____________
Code for TallyGenerator
January 1, 2021 at 8:02 am
Jeff, My guess, that is what he was told to do. Not sure but this looks like it may be an assignment of some sort.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply