March 21, 2018 at 9:06 am
I have results from my query
account value rank fixdate payabledate
22 100 1 01/01/2018
22 200 2 01/01/2018
53 80 1 01/04/2018
53 90 2 01/04/2018
tbl dates
paydates
01/02/2018
02/02/2018
3/02/2018
from above dates table I want to populate the payable date with date next larger than the fixdate( for rank1 ie 01/02/2018) and populate second larger than fix date(for rank2) i.e,02/02/2018
March 21, 2018 at 9:39 am
select <current columns>, paydate from <whatever tables are in your query>
CROSS APPLY (SELECT TOP(1) paydate FROM tbldates WHERE tbldates.paydate > payabledate ORDER BY paydate) AS NextPayDate
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2018 at 9:46 am
Looks like this worked...use tempdb;
GO
CREATE TABLE PayDates(PayDate DATE PRIMARY KEY);
GO
INSERT INTO PayDates(PayDate) VALUES ('01/02/2018'),('02/02/2018'),('3/02/2018');
CREATE TABLE Acct(account int, amt int, rnk tinyint, FixDate Date);
INSERT INTO Acct VALUES(
22, 100, 1 , '01/01/2018'),
(22, 200, 2, '01/01/2018'),
(53, 80, 1, '01/04/2018'),
(53, 90, 2, '01/04/2018');
SELECT account
, amt
, rnk
, FixDate
, npd.NextPayDate
FROM Acct a
CROSS APPLY (SELECT MIN(PayDate) AS NextPayDate FROM PayDates WHERE Paydate>a.FixDate) npd;
March 21, 2018 at 10:04 am
pietlinden - Wednesday, March 21, 2018 9:46 AMLooks like this worked...use tempdb;
GOCREATE TABLE PayDates(PayDate DATE PRIMARY KEY);
GO
INSERT INTO PayDates(PayDate) VALUES ('01/02/2018'),('02/02/2018'),('3/02/2018');CREATE TABLE Acct(account int, amt int, rnk tinyint, FixDate Date);
INSERT INTO Acct VALUES(
22, 100, 1 , '01/01/2018'),
(22, 200, 2, '01/01/2018'),
(53, 80, 1, '01/04/2018'),
(53, 90, 2, '01/04/2018');SELECT account
, amt
, rnk
, FixDate
, npd.NextPayDate
FROM Acct a
CROSS APPLY (SELECT MIN(PayDate) AS NextPayDate FROM PayDates WHERE Paydate>a.FixDate) npd;
Thank you but its returning the same date for all the rank records. If the rank is 1 then it should get 02/02/2018 and for record with rank =2 it should return 03/02/3018. Please help
March 21, 2018 at 10:10 am
So for both of these, it should return the 3rd Feb?
(22, 200, 2, '01/01/2018')
(53, 90, 2, '01/04/2018')
In other words, is the date selected only based on the rank, not the FixDate, or is it based on both?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2018 at 10:19 am
GilaMonster - Wednesday, March 21, 2018 10:10 AMSo for both of these, it should return the 3rd Feb?(22, 200, 2, '01/01/2018')
(53, 90, 2, '01/04/2018')In other words, is the date selected only based on the rank, not the FixDate, or is it based on both?
its by both. first we need to check the rank if it is 1 then get the next paydate for fix date of 01/01/2018 and it will be 02/02/2018, next we we see that the rank is 2 for fix date of 01/02/2018 so we get the second highest that is 03/02/2018..
Also the dates are in US format so it is feb 2 and march 2.
March 21, 2018 at 10:22 am
If they're datetimes (not strings) it won't matter to SQL what they are. I'm not American, your format confuses me.
One more thing. What should be returned if there are not enough dates? If say there's a row that wants rank 3 for a date '01/04/2018', and there are only two dates greater than that in the PayDates table?
Options are NULL for the column, or don't return the row at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2018 at 10:31 am
GilaMonster - Wednesday, March 21, 2018 10:22 AMIf they're datetimes (not strings) it won't matter to SQL what they are. I'm not American, your format confuses me.One more thing. What should be returned if there are not enough dates? If say there's a row that wants rank 3 for a date '01/04/2018', and there are only two dates greater than that in the PayDates table?
Options are NULL for the column, or don't return the row at all.
The system does not allow the rank more than 2 so there should never be a rank 3(but.. we know for error handling null should be good.)
thanks for all your help. i appreciate it.
March 21, 2018 at 10:36 am
Using Pietlinden's sample tables above (thanks), this looks like it should give what's wanted.
SELECT account
, amt
, rnk
, FixDate
, npd.NextPayDate
FROM Acct a
OUTER APPLY (SELECT PayDate AS NextPayDate, ROW_NUMBER() OVER (ORDER BY PayDate) AS PaydateRank FROM PayDates WHERE Paydate>a.FixDate) npd
WHERE a.rnk = npd.PaydateRank OR PaydateRank IS NULL;
Will only be online again tomorrow if there's still problems with it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2018 at 10:53 am
Angela4eva - Wednesday, March 21, 2018 10:19 AMGilaMonster - Wednesday, March 21, 2018 10:10 AMSo for both of these, it should return the 3rd Feb?(22, 200, 2, '01/01/2018')
(53, 90, 2, '01/04/2018')In other words, is the date selected only based on the rank, not the FixDate, or is it based on both?
its by both. first we need to check the rank if it is 1 then get the next paydate for fix date of 01/01/2018 and it will be 02/02/2018, next we we see that the rank is 2 for fix date of 01/02/2018 so we get the second highest that is 03/02/2018..
Also the dates are in US format so it is feb 2 and march 2.
To avoid ambiguity you should format your dates as YYYYMMDD or YYYY-MM-DD
March 21, 2018 at 11:06 am
GilaMonster - Wednesday, March 21, 2018 10:36 AMUsing Pietlinden's sample tables above (thanks), this looks like it should give what's wanted.
SELECT account
, amt
, rnk
, FixDate
, npd.NextPayDate
FROM Acct a
OUTER APPLY (SELECT PayDate AS NextPayDate, ROW_NUMBER() OVER (ORDER BY PayDate) AS PaydateRank FROM PayDates WHERE Paydate>a.FixDate) npd
WHERE a.rnk = npd.PaydateRank OR PaydateRank IS NULL;Will only be online again tomorrow if there's still problems with it.
this is working great. Thank you for all your help.
March 21, 2018 at 11:59 am
Here's another way, also using pietlinden's sample data:
SELECT A.*, PayDate
FROM Acct A OUTER APPLY
(
SELECT PayDate FROM PayDates WHERE PayDate>FixDate ORDER BY PayDate ASC OFFSET rnk-1 ROWS FETCH NEXT 1 ROW ONLY
)x
Cheers!
March 23, 2018 at 10:17 am
>> have results from my query <<
In the future please follow forum rules and post DDL? We don't really care about your query results, so much as we need to know what your tables look like. The picture you posted has lots of mistakes in it. You don't know the standard date format in ANSI-ISO SQL, and you've used some local dialect. It's incredibly ambiguous. You don't know the ISO 11179 naming rules.
Here's my guess at what you might have meant.
CREATE TABLE Foobar
(acct_nbr CHAR(3) NOT NULL,
something_rank INTEGER NOT NULL
CHECK (something_rank IN (1,2)),
PRIMARY KEY (acct_nbr, something_rank), -- my guess!
foobar_value INTEGER NOT NULL,
fix_date DATE NOT NULL,
payable_date DATE);
You don't seem to know that a table must have a key, by definition. Did I guess correctly?
INSERT INTO Foobar
VALUES
('022', 1, 100, '2018-01-01', NULL),
('053', 1, 80, '2018-01-04', NULL),
('022', 2, 200, '2018-01-01', NULL),
('053', 2, 90, '2018-01-04', NULL);
You might want to Google the use of "tbl_" in the DDL. It's considered so bad it has the name "Tibble" to make fun of it. You can read some of the pieces that Phil Factor has written on this sort of redundancy.
>> from above dates table I want to populate [sic: update?] the payable date with date next larger than the fix_date (for something_rank = 1 ie '2018-01-02') and populate second larger than fix_date(for something_rank = 2) i.e, '2018-02-02' <<
CREATE TABLE Pay_Dates
(paydate DATE NOT NULL PRIMARY KEY);
INSERT INTO Pay_Dates
VALUES ('2018-01-02'), ('2018-02-02'), ('2018-03-02');
At this point, I can't quite figure out what your rules are. Can you try again?
Please post DDL and follow ANSI/ISO standards when asking for help.
March 23, 2018 at 12:46 pm
Angela4eva - Wednesday, March 21, 2018 9:06 AM
>> have results from my query <<
In the future please follow forum rules and post DDL? We don't really care about your query results, so much as we need to know what your tables look like. The picture you posted has lots of mistakes in it. You don't know the standard date format in ANSI-ISO SQL, and you've used some local dialect. It's incredibly ambiguous. You don't know the ISO 11179 naming rules.
Here's my guess at what you might have meant.
CREATE TABLE Foobar
(acct_nbr CHAR(3) NOT NULL,
something_rank INTEGER NOT NULL
CHECK (something_rank IN (1,2)),
PRIMARY KEY (acct_nbr, something_rank), -- my guess!
foobar_value INTEGER NOT NULL,
fix_date DATE NOT NULL,
payable_date DATE);
You don't seem to know that a table must have a key, by definition. Did I guess correctly?
INSERT INTO Foobar
VALUES
('022', 1, 100, '2018-01-01', NULL),
('053', 1, 80, '2018-01-04', NULL),
('022', 2, 200, '2018-01-01', NULL),
('053', 2, 90, '2018-01-04', NULL);
You might want to Google the use of "tbl_" in the DDL. It's considered so bad it has the name "Tibble" to make fun of it. You can read some of the pieces that Phil Factor has written on this sort of redundancy.
>> from above dates table I want to populate [sic: update?] the payable date with date next larger than the fix_date (for something_rank = 1 ie '2018-01-02') and populate second larger than fix_date(for something_rank = 2) i.e, '2018-02-02' <<
CREATE TABLE Pay_Dates
(paydate DATE NOT NULL PRIMARY KEY);
INSERT INTO Pay_Dates
VALUES ('2018-01-02'), ('2018-02-02'), ('2018-03-02');
At this point, I can't quite figure out what your rules are. Can you try again?
wow.I think you'd guessed a lot of things about me and that I do not know a lot of things. I will check the forum rules and follow them. I appreciate everyone help with this questions.
I have received the solution yesterday and marked it as the correct answer.
March 27, 2018 at 9:15 am
jcelko212 32090 - Friday, March 23, 2018 10:17 AMAngela4eva - Wednesday, March 21, 2018 9:06 AM>> have results from my query <<
In the future please follow forum rules and post DDL? We don't really care about your query results, so much as we need to know what your tables look like. The picture you posted has lots of mistakes in it. You don't know the standard date format in ANSI-ISO SQL, and you've used some local dialect. It's incredibly ambiguous. You don't know the ISO 11179 naming rules.
Here's my guess at what you might have meant.
CREATE TABLE Foobar
(acct_nbr CHAR(3) NOT NULL,
something_rank INTEGER NOT NULL
CHECK (something_rank IN (1,2)),
PRIMARY KEY (acct_nbr, something_rank), -- my guess!
foobar_value INTEGER NOT NULL,
fix_date DATE NOT NULL,
payable_date DATE);You don't seem to know that a table must have a key, by definition. Did I guess correctly?
INSERT INTO Foobar
VALUES
('022', 1, 100, '2018-01-01', NULL),
('053', 1, 80, '2018-01-04', NULL),
('022', 2, 200, '2018-01-01', NULL),
('053', 2, 90, '2018-01-04', NULL);You might want to Google the use of "tbl_" in the DDL. It's considered so bad it has the name "Tibble" to make fun of it. You can read some of the pieces that Phil Factor has written on this sort of redundancy.
>> from above dates table I want to populate [sic: update?] the payable date with date next larger than the fix_date (for something_rank = 1 ie '2018-01-02') and populate second larger than fix_date(for something_rank = 2) i.e, '2018-02-02' <<
CREATE TABLE Pay_Dates
(paydate DATE NOT NULL PRIMARY KEY);INSERT INTO Pay_Dates
VALUES ('2018-01-02'), ('2018-02-02'), ('2018-03-02');At this point, I can't quite figure out what your rules are. Can you try again?
wow.I think you'd guessed a lot of things about me and that I do not know a lot of things. I will check the forum rules and follow them. I appreciate everyone help with this questions.
I have received the solution yesterday and marked it as the correct answer.
Welcome to Joe Celko's world... He tends to rant on about ISO standards and such, and not use the best English, and his typing is coming from translated voice commands, so there's that for him to overcome as well. He means well, but his execution leaves many folks frustrated and wondering what just happened - especially those relatively new to the forum. You can usually safely ignore most of what he has to say. There was nothing wrong with what you posted from a forum rules perspective, and while it's suggested that you try to post CREATE TABLE and INSERT statements that show sample data that demonstrates the problem you're running into, it's sometimes impractical to do so without spending far more time than you have available, to do things such as recreate the real-world problem, or even just sufficiently sanitize the data. Best you can do at times is post the query, and when performance is an issue, the query plan as well, when available.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply