August 15, 2008 at 12:18 pm
DECLARE @MyVersions TABLE
(
RowKeyINT,
RowDateDATETIME,
VersionVARCHAR(32),
CapacityINT
)
SELECT 1, '01/01/2008', '1.0', 1UNION
SELECT 1, '01/01/2008', '1.0', 2UNION
SELECT 2, '01/03/2008', '1.0', 1UNION
SELECT 2, '01/03/2008', '1.0', 2UNION
SELECT 3, '01/04/2008', '1.0', 1UNION
SELECT 3, '01/04/2008', '1.0', 2UNION
SELECT 4, '01/05/2008', '1.0', 1UNION
SELECT 4, '01/05/2008', '1.0', 2UNION
SELECT 4, '01/05/2008', '1.0', 1.5UNION
SELECT 5, '01/06/2008', '1.0', 1UNION
SELECT 5, '01/06/2008', '1.0', 2UNION
SELECT 5, '01/06/2008', '1.0', 1.5UNION
/* looks like a mistake. went, but we need this in the results */
SELECT 6, '01/08/2008', '1.0', 1UNION
SELECT 6, '01/08/2008', '1.0', 2UNION
SELECT 7, '01/09/2008', '1.0', 1UNION
SELECT 7, '01/09/2008', '1.0', 2UNION
SELECT 7, '01/09/2008', '1.0', 2UNION
SELECT 9, '01/11/2008', '1.0', 1UNION
SELECT 9, '01/11/2008', '1.0', 2UNION
SELECT 9, '01/11/2008', '1.0', 2UNION
SELECT 10, '01/12/2008', '1.0', 1UNION
SELECT 10, '01/12/2008', '1.0', 2UNION
SELECT 10, '01/12/2008', '1.0', 1.5UNION
SELECT 11, '01/13/2008', '1.0', 1UNION
SELECT 11, '01/13/2008', '1.0', 2UNION
SELECT 11, '01/13/2008', '1.0', 1.5UNION
SELECT 12, '01/14/2008', '1.0', 1UNION
SELECT 12, '01/14/2008', '1.0', 2UNION
SELECT 12, '01/14/2008', '1.0', 1.5UNION
SELECT 13, '01/16/2008', '1.0', 1UNION
SELECT 13, '01/16/2008', '1.0', 4UNION
SELECT 14, '01/17/2008', '1.0', 1UNION
SELECT 14, '01/17/2008', '1.0', 4UNION
SELECT 15, '01/18/2008', '1.0', 1UNION
SELECT 15, '01/18/2008', '1.0', 4UNION
SELECT 16, '01/20/2008', '1.0', 1UNION
SELECT 16, '01/20/2008', '1.0', 4UNION
SELECT 17, '01/21/2008', '1.0', 1UNION
SELECT 17, '01/21/2008', '1.0', 3UNION
SELECT 17, '01/21/2008', '2.0', 1UNION
SELECT 18, '01/22/2008', '1.0', 1UNION
SELECT 18, '01/22/2008', '1.0', 3UNION
SELECT 18, '01/22/2008', '2.0', 1UNION
SELECT 19, '01/23/2008', '1.0', 1UNION
SELECT 19, '01/23/2008', '1.0', 3UNION
SELECT 19, '01/23/2008', '2.0', 1UNION
SELECT 20, '01/24/2008', '1.0', 1UNION
SELECT 20, '01/24/2008', '2.0', 3UNION
SELECT 20, '01/24/2008', '3.0', 1UNION
SELECT 21, '01/26/2008', '1.0', 1UNION
SELECT 21, '01/26/2008', '2.0', 3UNION
SELECT 21, '01/26/2008', '3.0', 1UNION
SELECT 22, '01/27/2008', '1.0', 1UNION
SELECT 22, '01/27/2008', '2.0', 3UNION
SELECT 22, '01/27/2008', '3.0', 1UNION
SELECT 23, '01/28/2008', '1.0', 1UNION
SELECT 23, '01/28/2008', '2.0', 3UNION
SELECT 23, '01/28/2008', '3.0', 1UNION
SELECT 23, '01/29/2008', '1.0', 1UNION
SELECT 23, '01/29/2008', '3.0', 4UNION
SELECT 23, '01/30/2008', '1.0', 1UNION
SELECT 23, '01/30/2008', '3.0', 4
/*
Expected result is to have data for each group when it is started.
Example '01/01/2008' and '01/04/2008' are same data we need only one row with lowest
date
Result set should be the results like this query.
SELECT * FROM @MyVersions WHERE RowKey IN (1, 4, 6, 7, 10, 13, 17, 20, 23)
/*
Regards,
gova
August 16, 2008 at 3:17 pm
I believe this would give you the results you require
SELECT Rowkey, Min(RowDate), Version, Capacity FROM Mytable GROUP BY Rowkey,Version,Capacity
For rowkey 23 it will return
RowkeyDate Version Capacity
232008-01-28 00:00:00.0001.01
232008-01-28 00:00:00.0002.03
232008-01-28 00:00:00.0003.01
232008-01-29 00:00:00.0003.04
Where the data is
RowkeyRowDate Version Capacity My comments
232008-01-28 00:00:00.0001.01 Lowest date
232008-01-28 00:00:00.0002.03
232008-01-28 00:00:00.0003.01
232008-01-29 00:00:00.0001.01 Higher date
232008-01-29 00:00:00.0003.04
232008-01-30 00:00:00.0001.01 Even higher date
232008-01-30 00:00:00.0003.04
August 17, 2008 at 7:48 am
Thanks bitbucket
The results needed is exactly like SELECT * FROM @MyVersions WHERE RowKey IN (1, 4, 6, 7, 10, 13, 17, 20, 23)
But we have to find (1, 4, 6, 7, 10, 13, 17, 20, 23) in program logic(i.e. each row key is a set. Row keys are in order but not continues. if a set varies from previous set we need that set.)
Regards,
gova
August 17, 2008 at 8:59 am
Gova sorry but I am having difficulty completely understanding your question
Expected result is to have data for each group when it is started.
Example '01/01/2008' and '01/04/2008' are same data we need only one row with lowest date
The results needed is exactly like SELECT * FROM @MyVersions WHERE RowKey IN (1, 4, 6, 7, 10, 13, 17, 20, 23)
Using SELECT rowkey,rowdate,version,capacity FROM MyVersions
WHERE RowKey in (1,4,5,7,10,13,17,20,23)
rowkey rowdate version capacity
----------- ----------------------- -------------------------------- -----------
1 2008-01-01 00:00:00.000 1.0 1
1 2008-01-01 00:00:00.000 1.0 2
Original data
2 2008-01-03 00:00:00.000 1.0 1
2 2008-01-03 00:00:00.000 1.0 2
3 2008-01-04 00:00:00.000 1.0 1
3 2008-01-04 00:00:00.000 1.0 2
4 2008-01-05 00:00:00.000 1.0 1
4 2008-01-05 00:00:00.000 1.0 1
4 2008-01-05 00:00:00.000 1.0 2
I can not understand why you want Rowkeys 1 and 4 and NOT 2 and 3. The pattern of data for 1, 2 and 3 are identical. While 4 has an apparent duplicate row where version = 1 and capacity = 1 other than that its pattern is the same as 1,2 and 3.
Can you be explicit in what differentiates these rows, so we can be of some assistance or conversely only confuse the issue even more.
August 18, 2008 at 8:58 am
bitbucket
I am sorry if I did not express well.
Each row key is a set of data. We get the same data almost every day. But there will be changes in the set. The objective is to get the start of each set.
I can not understand why you want Rowkeys 1 and 4 and NOT 2 and 3. The pattern of data for 1, 2 and 3 are identical
The objective is to get the first of the each continues idetical sets.
Row key 1 came first - we need it
Row key 2 came after - It is same as row key 1 we don't need it
Row key 3 came after - It is same as row key 2 we don't need it
Row key 4 came after - It is different from row key 3 we need it
go like this to the end.
FYI the rowkeys I gave in the sample data are continues. In real data the row key are in increasing order but not continues.
The results needed is exactly like SELECT * FROM @MyVersions WHERE RowKey IN (1, 4, 6, 7, 10, 13, 17, 20, 23)
The final result set I am expecting would be the same as the result set returned from the above query.
Regards,
gova
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply