Someone left a table that some how is complicated for me to query the data I want to resemble the output. struggled with the transform then the cross tab, not much luck, could not do it. I need a query that from the input con_tbl table i get the output table.
input con_tbl table::
all fields are of type text.
assuming datex3 > datex2 > datex1, for a given MyId select the row if its col1 = '2' with highest date within the same Col1 setting otherwise if Col1='2' does not exist, then, for that MyId select row with col1 = '1' instead still with highest date within the same COl1 setting. Aim is to catch the rows with col1 = '2' and if not exist catch the row with col1='1'. One row per each distinct MyId.
The output will look like:
input con_tbl table::
all fields are of type text.
Code:
(general
format)
ID MyId Col1 Col2 mydate Col3
----------------------------------------------------------
1 aaa1 1 xa datex1 aa
2 aaa1 2 xb datex2 aa
3 aaa1 2 xc datex3 bb
4 bbb1 1 xc datex1 cc
5 ccc1 1 xd datex1 dd
5 ccc1 1 xe datex2 ee
6 ddd1 2 xf datex1 ff
7 eee1 1 xg datex1 gg
8 eee1 2 xh datex1 hh
.
.
assuming datex3 > datex2 > datex1, for a given MyId select the row if its col1 = '2' with highest date within the same Col1 setting otherwise if Col1='2' does not exist, then, for that MyId select row with col1 = '1' instead still with highest date within the same COl1 setting. Aim is to catch the rows with col1 = '2' and if not exist catch the row with col1='1'. One row per each distinct MyId.
The output will look like:
Code:
MyId Col1 Col2 mydate Col3
---------------------------------------------------------------------------------------
aaa1 2 xc datex3 bb
bbb1 1 xc datex1 cc
ccc1 1 xe datex2 ee
ddd1 2 xf datex1 ff
eee1 2 xh datex1 hh
.
.