Difficult SQL query

[oda10]

Hi!
I have this table:
IDNR FOM TYP TOM
1000 2003-mar-31
1000 2003-mar-31 T 2003-apr-30
1000 2003-feb-11 2003-mar-30
1010 2003-mar-31
1020 2003-mar-31 T 2003-apr-30
1020 2003-mar-31 2003-apr-30
1030 2003-mar-31
1030 2003-feb-11 2003-apr-30

If I run the query on april 29 I want this result:
IDNR FOM TYP TOM
1000 2003-mar-31 T 2003-apr-30
1010 2003-mar-31
1020 2003-mar-31 T 2003-apr-30
1030 2003-feb-11 2003-apr-30

I want only one record per IDNR. Any idea anyone?
Thank you
/Fredrik

 

[MarcLodge]

Hi Fredrik,
Need to know a bit more about the construction of the table.

It appears from the data that you have given that you can have duplicate rows for a similar period for the same IDNR, but different TYPs. (see IDNR 1020) Is this correct?

Can you have duplicates for a similar period and identical TYP?

If duplicates are found, which do you wish to see?

Can there be more values (other than spaces and 'T') for the TYP. If so, when more than one is found, which do you wish to see?

Sorry to hit you with so many questions, but without the answers to these, it's going to be a bit difficult to sort out the SQL.

Marc

 

[oda10]

Hi Marc!
Yes, it is correct that I can have duplicate rows for the period but one row has TYP=T and the other one is blank. Then the row with the T should be returned. Only spaces and T's is in the column TYP.

There are never similar periods and identical TYP.

/Fredrik

 

[blom0344]

I do not see a solution with one SQL statement (hope I am proven wrong).
Solution with additional table:
Create a temp table T_TEMP:
SELECT IDNR AS KEY,COUNT(IDNR) AS FREQ FROM TABLE
GROUP BY IDNR.

Now run query over both tables:

Select IDNR,FOM,TYP,TOM
FROM TABLE,T_TEMP
WHERE TABLE.IDNR = T_TEMP.KEY AND
T_TEMP.FREQ = 1
UNION
Select IDNR,FOM,TYP,TOM
FROM TABLE,T_TEMP
WHERE TABLE.IDNR = T_TEMP.KEY AND
T_TEMP.FREQ = 2 AND TABLE.TYP IS NULL AND TABLE.TOM IS NOT NULL
UNION
Select IDNR,FOM,TYP,TOM
FROM TABLE,T_TEMP
WHERE TABLE.IDNR = T_TEMP.KEY AND
T_TEMP.FREQ = 3 AND TABLE.TYP IS NOT NULL
AND TABLE.TOM IS NOT NULL

assuming that there is a fixed structure in the number of IDNR that occur in the TABLE:

3x IDNR: select record with both fields filled
2X IDNR: select record with date field filled
1X IDNR: select the record (no choice possible)

I suspect that using a database view instead of a temporary table will not be succesfull




T. Blom
Information analyst
tbl@shimano-eu.com

 

[MarcLodge]

Fredrik,
The following appears to work, but it made a number of assumptions on the set up of your table. It's pretty horrid to look at and runs like a milkman's horse, but I present it as a theoretical answer to the problem, rather than the correct way of doing it!

SELECT A.NMBR, A.FROM_DATE, A.TYP, A.TO_DATE
FROM T_TABLE A
WHERE (A.TYP = 'T'
AND '29.04.2003' BETWEEN A.FROM_DATE AND A.TO_DATE)
OR ('29.04.2003' BETWEEN A.FROM_DATE AND A.TO_DATE
AND A.TYP = ' '
AND NOT EXISTS (SELECT ' '
FROM T_TABLE B
WHERE B.NMBR = A.NMBR
AND B.FROM_DATE = A.FROM_DATE
AND B.TYP = 'T') )
OR ('29.04.2003' > A.FROM_DATE
AND A.TO_DATE IS NULL
AND NOT EXISTS (SELECT ' '
FROM T_TABLE C
WHERE C.NMBR = A.NMBR
AND '29.04.2003' BETWEEN C.FROM_DATE AND C.TO_DATE) )

 

[oda10]

Hi!
Thanks a lot, it seems to work fine. It's not that slow and it dosn't matter that much since the query is only going to be run at night once a month.

Now I just have to put this query into a much bigger one....
Thanks again!
/Fredrik

 

[MarcLodge]

Hi Fredrik,
Great that it works. I'd give it a good testing if I was you, as I was only running on the data that you supplied, which I'm guessing is a subset of the actual data. I thought it was a bit shaky myself, so use it with care!!
Marc