October 29, 2016 at 8:50 am
Hi,
could anybody help me please with this:
I would like to write a query which retrieves this information: id , days_a_mat (days after maturity) from current date
AND than in the same result set: maximum of days after maturity (not only currenty from current date but from whole dataset and the date which this max. occured)
This is my attempt but it doesn´t work :-/ :
SELECT PRO.id, PRO.days_a_mat AS days_current, sub1.tot_max_days , sub2.tot_date
FROM PRODUCTS as PRO,
(SELECT id, max(days_a_mat) as tot_max_days FROM products AS PRO2 WHERE PRO2.ID IN(1,2,3,4,5,6) GROUP BY id) as sub1,
(SELECT id, date as tot_date FROM products AS PRO3 WHERE date=SUB1.date and PRO3.id IN(1,2,3,4,5,6) GROUP BY ID) as sub2
WHERE
date='2016_09_30'
AND PRO.id IN(1,2,3,4,5,6)
ORDER BY id ;
Thaks a lot for any help!!!!
October 29, 2016 at 9:04 am
sanda.jan00 (10/29/2016)
Hi,could anybody help me please with this:
I would like to write a query which retrieves this information: id , days_a_mat (days after maturity) from current date
AND than in the same result set: maximum of days after maturity (not only currenty from current date but from whole dataset and the date which this max. occured)
This is my attempt but it doesn´t work :-/ :
SELECT PRO.id, PRO.days_a_mat AS days_current, sub1.tot_max_days , sub2.tot_date
FROM PRODUCTS as PRO,
(SELECT id, max(days_a_mat) as tot_max_days FROM products AS PRO2 WHERE PRO2.ID IN(1,2,3,4,5,6) GROUP BY id) as sub1,
(SELECT id, date as tot_date FROM products AS PRO3 WHERE date=SUB1.date and PRO3.id IN(1,2,3,4,5,6) GROUP BY ID) as sub2
WHERE
date='2016_09_30'
AND PRO.id IN(1,2,3,4,5,6)
ORDER BY id ;
Thaks a lot for any help!!!!
First of all welcome to SSC.
From your description, this sounds pretty straightforward. Could you please post your DDL and some sample data for your products table?
October 29, 2016 at 1:19 pm
Ed Wagner (10/29/2016)
sanda.jan00 (10/29/2016)
Hi,could anybody help me please with this:
I would like to write a query which retrieves this information: id , days_a_mat (days after maturity) from current date
AND than in the same result set: maximum of days after maturity (not only currenty from current date but from whole dataset and the date which this max. occured)
This is my attempt but it doesn´t work :-/ :
SELECT PRO.id, PRO.days_a_mat AS days_current, sub1.tot_max_days , sub2.tot_date
FROM PRODUCTS as PRO,
(SELECT id, max(days_a_mat) as tot_max_days FROM products AS PRO2 WHERE PRO2.ID IN(1,2,3,4,5,6) GROUP BY id) as sub1,
(SELECT id, date as tot_date FROM products AS PRO3 WHERE date=SUB1.date and PRO3.id IN(1,2,3,4,5,6) GROUP BY ID) as sub2
WHERE
date='2016_09_30'
AND PRO.id IN(1,2,3,4,5,6)
ORDER BY id ;
Thaks a lot for any help!!!!
First of all welcome to SSC.
From your description, this sounds pretty straightforward. Could you please post your DDL and some sample data for your products table?
Thank you!!!!
This table is relatively simple: (service INTEGER PRIMARY KEY, id integer,
date DATE, days_a_mat INTEGER, rating INTEGER) ;
service id date days_a_mat rating
1 12024 2016-09-30 5 4
2 11045 2014-08-30 2 5
3 10044 2016-09-30 5 0
4 10044 NULL 2 1
5 1096 2014-05-30 4 6
October 29, 2016 at 1:53 pm
sanda.jan00 (10/29/2016)
Ed Wagner (10/29/2016)
sanda.jan00 (10/29/2016)
Hi,could anybody help me please with this:
I would like to write a query which retrieves this information: id , days_a_mat (days after maturity) from current date
AND than in the same result set: maximum of days after maturity (not only currenty from current date but from whole dataset and the date which this max. occured)
This is my attempt but it doesn´t work :-/ :
SELECT PRO.id, PRO.days_a_mat AS days_current, sub1.tot_max_days , sub2.tot_date
FROM PRODUCTS as PRO,
(SELECT id, max(days_a_mat) as tot_max_days FROM products AS PRO2 WHERE PRO2.ID IN(1,2,3,4,5,6) GROUP BY id) as sub1,
(SELECT id, date as tot_date FROM products AS PRO3 WHERE date=SUB1.date and PRO3.id IN(1,2,3,4,5,6) GROUP BY ID) as sub2
WHERE
date='2016_09_30'
AND PRO.id IN(1,2,3,4,5,6)
ORDER BY id ;
Thaks a lot for any help!!!!
First of all welcome to SSC.
From your description, this sounds pretty straightforward. Could you please post your DDL and some sample data for your products table?
Thank you!!!!
This table is relatively simple: (service INTEGER PRIMARY KEY, id integer,
date DATE, days_a_mat INTEGER, rating INTEGER) ;
service id date days_a_mat rating
1 12024 2016-09-30 5 4
2 11045 2014-08-30 2 5
3 10044 2016-09-30 5 0
4 10044 NULL 2 1
5 1096 2014-05-30 4 6
Please see the first link in my signature line below. It'll help you get help much more quickly.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2016 at 6:05 pm
You should definitely read the article Jeff suggested. It's the only reason I'm thinking I shouldn't post this yet. It's just one of those "Ask a better question to get a better answer" things. If you post like this, people can recreate your scenario at the click of a button... then you can get actual tested code... Since you're new, I'll show you. (Note the SQL tags in the shortcuts to the left!) I changed some names, because SERVICE is a reserved word in T-SQL, and so is DATE. I changed the types for days_a_mat and rating because you don't appear to need anything nearly as big as an integer. TINYINT goes up to 32767.
use tempdb;
GO
CREATE TABLE MyServices (
ServiceID INTEGER PRIMARY KEY
, id INTEGER
,ServiceDate DATE
, days_a_mat TINYINT
, rating TINYINT);
GO
INSERT INTO MyServices VALUES
(1, 12024, '2016-09-30', 5, 4),
(2, 11045, '2014-08-30', 2, 5),
(3, 10044, '2016-09-30', 5, 0),
(4, 10044, NULL, 2, 1),
(5, 1096, '2014-05-30', 4, 6);
October 29, 2016 at 7:07 pm
Thanks a lot from both of you!!!
Now, consider the adjusted table provided by you, could you pls give me some advice how to write my query?? 🙂
October 30, 2016 at 7:49 am
sanda.jan00 (10/29/2016)
This is my attempt but it doesn´t work :-/ :SELECT PRO.id, PRO.days_a_mat AS days_current, sub1.tot_max_days , sub2.tot_date
FROM PRODUCTS as PRO,
(SELECT id, max(days_a_mat) as tot_max_days FROM products AS PRO2 WHERE PRO2.ID IN(1,2,3,4,5,6) GROUP BY id) as sub1,
(SELECT id, date as tot_date FROM products AS PRO3 WHERE date=SUB1.date and PRO3.id IN(1,2,3,4,5,6) GROUP BY ID) as sub2
WHERE
date='2016_09_30'
AND PRO.id IN(1,2,3,4,5,6)
ORDER BY id ;
Thaks a lot for any help!!!!
Correct. It doesn't work. My recommendation would be to start out by fixing the obvious errors returned by SSMS when you try to run that code.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'date'.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "SUB1.date" could not be bound.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'date'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'date'.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2016 at 8:49 am
sanda.jan00 (10/29/2016)
Thanks a lot from both of you!!!Now, consider the adjusted table provided by you, could you pls give me some advice how to write my query?? 🙂
You still haven't provided expected results. It's sometimes hard to figure it out from the description, and it gives people a definite target.
My best guess based on your original query is:
;
WITH ms AS (
SELECT ms.*, MAX(ss.days_a_mat) OVER() AS tot_max_days, MAX(ms.ServiceDate) OVER(PARTITION BY ms.ServiceDate) AS tot_date
FROM MyServices ms
CROSS APPLY ( VALUES(ms.days_a_mat, ms.ServiceDate) ) ss
WHERE ms.ServiceID IN (1, 2, 3, 4, 5)
)
SELECT *
FROM ms
WHERE ms.ServiceDate = '2016-09-30'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 1, 2016 at 1:17 pm
drew.allen (10/31/2016)
sanda.jan00 (10/29/2016)
Thanks a lot from both of you!!!Now, consider the adjusted table provided by you, could you pls give me some advice how to write my query?? 🙂
You still haven't provided expected results. It's sometimes hard to figure it out from the description, and it gives people a definite target.
My best guess based on your original query is:
;
WITH ms AS (
SELECT ms.*, MAX(ss.days_a_mat) OVER() AS tot_max_days, MAX(ms.ServiceDate) OVER(PARTITION BY ms.ServiceDate) AS tot_date
FROM MyServices ms
CROSS APPLY ( VALUES(ms.days_a_mat, ms.ServiceDate) ) ss
WHERE ms.ServiceID IN (1, 2, 3, 4, 5)
)
SELECT *
FROM ms
WHERE ms.ServiceDate = '2016-09-30'
Drew
Thank you! I slightly modified your query and it gave me expected results. Next time I will try to ask in a more suitable way. 🙂
November 1, 2016 at 1:34 pm
sanda.jan00 (11/1/2016)
drew.allen (10/31/2016)
sanda.jan00 (10/29/2016)
Thanks a lot from both of you!!!Now, consider the adjusted table provided by you, could you pls give me some advice how to write my query?? 🙂
You still haven't provided expected results. It's sometimes hard to figure it out from the description, and it gives people a definite target.
My best guess based on your original query is:
;
WITH ms AS (
SELECT ms.*, MAX(ss.days_a_mat) OVER() AS tot_max_days, MAX(ms.ServiceDate) OVER(PARTITION BY ms.ServiceDate) AS tot_date
FROM MyServices ms
CROSS APPLY ( VALUES(ms.days_a_mat, ms.ServiceDate) ) ss
WHERE ms.ServiceID IN (1, 2, 3, 4, 5)
)
SELECT *
FROM ms
WHERE ms.ServiceDate = '2016-09-30'
Drew
Thank you! I slightly modified your query and it gave me expected results. Next time I will try to ask in a more suitable way. 🙂
Can you post with the code you eventually ended up with, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply