I have the following data
| Item | Value | Date |
------------------------------
| 1 | 10 | 01.01.2010
| 1 | 20 | 02.01.2010
| 1 | 30 | 03.01.2010
| 1 | 40 | 04.01.2010
| 1 | 50 | 05.01.2010
| 1 | 80 | 10.01.2010
| 2 | 30 | 04.01.2010
| 2 | 60 | 06.01.2010
| 2 | 70 | 07.01.2010
| 2 | 80 | 08.01.2010
| 2 | 100 | 09.01.2010
And the following statement
SELECT Item, Value, MIN(Date) OVER (PARTITION BY Item)
FROM Data
WHERE Value >= 50
And I get the following result
| Item | Value | Date |
------------------------------
| 1 | 50 | 05.01.2010
| 1 | 80 | 05.01.2010
| 2 | 60 | 06.01.2010
| 2 | 70 | 06.01.2010
| 2 | 80 | 06.01.2010
| 2 | 100 | 06.01.2010
But what I need is this
| Item | Value | Date |
------------------------------
| 1 | 10 | 05.01.2010
| 1 | 20 | 05.01.2010
| 1 | 30 | 05.01.2010
| 1 | 40 | 05.01.2010
| 1 | 50 | 05.01.2010
| 1 | 80 | 05.01.2010
| 2 | 30 | 06.01.2010
| 2 | 60 | 06.01.2010
| 2 | 70 | 06.01.2010
| 2 | 80 | 06.01.2010
| 2 | 100 | 06.01.2010
Is there any quick solution to get this with one statment without a self-join?
Thank you :)
without a self join, try this:
DECLARE @YourTable table (item int,value int, Date datetime)
INSERT @YourTable VALUES (1 , 10 , '01/01/2010')
INSERT @YourTable VALUES (1 , 20 , '02/01/2010')
INSERT @YourTable VALUES (1 , 30 , '03/01/2010')
INSERT @YourTable VALUES (1 , 40 , '04/01/2010')
INSERT @YourTable VALUES (1 , 50 , '05/01/2010')
INSERT @YourTable VALUES (1 , 80 , '10/01/2010')
INSERT @YourTable VALUES (2 , 30 , '04/01/2010')
INSERT @YourTable VALUES (2 , 60 , '06/01/2010')
INSERT @YourTable VALUES (2 , 70 , '07/01/2010')
INSERT @YourTable VALUES (2 , 80 , '08/01/2010')
INSERT @YourTable VALUES (2 , 100 , '09/01/2010')
SELECT Item, Value, MIN(CASE WHEN Value >= 50 THEN Date ELSE NULL END) OVER (PARTITION BY Item)
FROM @YourTable
OUTPUT:
Item Value
----------- ----------- -----------------------
1 10 2010-05-01 00:00:00.000
1 20 2010-05-01 00:00:00.000
1 30 2010-05-01 00:00:00.000
1 40 2010-05-01 00:00:00.000
1 50 2010-05-01 00:00:00.000
1 80 2010-05-01 00:00:00.000
2 30 2010-06-01 00:00:00.000
2 60 2010-06-01 00:00:00.000
2 70 2010-06-01 00:00:00.000
2 80 2010-06-01 00:00:00.000
2 100 2010-06-01 00:00:00.000
Warning: Null value is eliminated by an aggregate or other SET operation.
(11 row(s) affected)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With