October 30, 2018 at 11:26 am
From the below data, I need to develop a query to select minimum claim_date grouped by client_number for the date range between 1/1/2018 to 8/31/2018.
Then take that client number and go back 24 months from the minimum claim_date and check if that client_number had product_id = 6 in those 24 months.
If yes, then don't take that client_number, if no then take that client_number. Any suggestions as to how I can structure my query. Thank you in advance!
Create table client_info
(client_number number,
Claim_date date,
Product_id number
);
Insert into client_info
Values(1, to_date('1/1/2016', 'MM/DD/YYYY'), 6);
Insert into client_info
Values(1, to_date('2/3/2016', 'MM/DD/YYYY'), 6);
Insert into client_info
Values(1, to_date('1/1/2018', 'MM/DD/YYYY'), 18);
Insert into client_info
Values(1, to_date('5/11/2018', 'MM/DD/YYYY'), 18);
Insert into client_info
Values(2, to_date('3/8/2016','MM/DD/YYYY'), 18);
Insert into client_info
Values(2, to_date('9/20/2018', 'MM/DD/YYYY'), 18);
Insert into client_info
Values(2, to_date('3/1/2018', 'MM/DD/YYYY'), 18);
Insert into client_info
Values(3, to_date('4/12/2017', 'MM/DD/YYYY'), 6);
Insert into client_info
Values(3, to_date('4/13/2018', 'MM/DD/YYYY'), 8);
Insert into client_info
Values(3, to_date('2/10/2016', 'MM/DD/YYYY'), 18);
The output should have only client_number 2 because that client had product 18 for all the 24 months from their minimum claim_date
October 30, 2018 at 11:41 am
Which part are you stuck on? This place isn't an answer machine... If you post what you tried, people will be much more willing to help. So what have you tried? Please post it.
And where's the table for the second part of the question? It sounds like you are talking about Sales or something, but it's hard to tell from the data provided. What does "had product_id" mean? Is there a Sales table somewhere?
"Then take that client number and go back 24 months from the minimum claim_date and check if that client_number had product_id = 6 in those 24 months."
The second part sounds like you could use EXISTS and a correlated subquery.
October 30, 2018 at 12:49 pm
soldout6000 - Tuesday, October 30, 2018 11:26 AMFrom the below data, I need to develop a query to select minimum claim_date grouped by client_number for the date range between 1/1/2018 to 8/31/2018.
Then take that client number and go back 24 months from the minimum claim_date and check if that client_number had product_id = 6 in those 24 months.
If yes, then don't take that client_number, if no then take that client_number. Any suggestions as to how I can structure my query. Thank you in advance!
Create table client_info
(client_number number,
Claim_date date,
Product_id number
);
Insert into client_info
Values(1, to_date('1/1/2016', 'MM/DD/YYYY'), 6);Insert into client_info
Values(1, to_date('2/3/2016', 'MM/DD/YYYY'), 6);Insert into client_info
Values(1, to_date('1/1/2018', 'MM/DD/YYYY'), 18);Insert into client_info
Values(1, to_date('5/11/2018', 'MM/DD/YYYY'), 18);Insert into client_info
Values(2, to_date('3/8/2016','MM/DD/YYYY'), 18);Insert into client_info
Values(2, to_date('9/20/2018', 'MM/DD/YYYY'), 18);Insert into client_info
Values(2, to_date('3/1/2018', 'MM/DD/YYYY'), 18);Insert into client_info
Values(3, to_date('4/12/2017', 'MM/DD/YYYY'), 6);Insert into client_info
Values(3, to_date('4/13/2018', 'MM/DD/YYYY'), 8);Insert into client_info
Values(3, to_date('2/10/2016', 'MM/DD/YYYY'), 18);The output should have only client_number 2 because that client had product 18 for all the 24 months from their minimum claim_date
To_date, number data type is Oracle. No one can use your sample ddl, data on SQL Server.
Sue
October 30, 2018 at 1:02 pm
Here's what I used... modified from user's OP:USE TEMPDB;
GO
Create table client_info
(client_number int,
Claim_date date,
Product_id int
);
GO
Insert into client_info
Values(1, '1/1/2016', 6),
(1, '2/3/2016', 6),
(1, '1/1/2018', 18),
(1, '5/11/2018', 18),
(2, '3/8/2016', 18),
(2, '9/20/2018', 18),
(2, '3/1/2018', 18),
(3, '4/12/2017', 6),
(3, '4/13/2018', 8),
(3, '2/10/2016', 18);
-- minimum claim date by client number
-- between 1/1/2018 to 8/31/2018.SELECT client_number
, MIN(Claim_date) AS FirstClaimDate
FROM client_info
WHERE claim_date >= '1/1/2018'
AND claim_date <= '8/31/2018'
GROUP BY client_number;
Wasn't sure how to do the second part, completely.
Wasn't sure how to do the second part, completely.
October 30, 2018 at 1:19 pm
Looks like Oracle to me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply