Query Problem

12 Jan 2006 12:13 PM

Ricky

Hi

I'm sure there is simple answer to thsi trivial problem, but I am completely
stuck.

I have got a table which contains 1000's of transactions of Claims, however
I need to run a query of all closed claims.

Below is a sample for one claim.

Status Key is: 1=Opened, 2=Re-Opened, 4=Settled/Closed.

How do extract data for a given date range, of all Currently Settled/Closed
claims?

For e.g, Data range 01/JAN/2005 TO 01/DEC/2005.

Bearing in mind, you cannot just put in the WHERE clause, Status=4, since it
was re-opened several months later again.

CLAIM NO TRANS     DATE CLAIMS                     STATUS

--------------------------     -----------------------------------         -
-------------------------

SOU/05/00007489     2005-01-13 00:00:00.000         1

SOU/05/00007489     2005-06-07 00:00:00.000         4

SOU/05/00007489     2005-11-07 00:00:00.000         2

Any ideas? I'm sure I need to use MAX dat somewhere, I have tried to use
MAX(Date Claims) and then Status = 4, but this doesn't work?

Kind Regards

Ricky

12 Jan 2006 12:23 PM

Jens

Hi RIcky !

Try this one here:

SELECT
    *
FROM SomeTable OuterTable
WHERE
    STATUS = 4 AND
    [DATE CLAIMS] BETWEEN '20050101' AND '20061231' AND
    NOT EXISTS
        (
            SELECT
                *
            FROM SomeTable InnerTable
            WHERE
                                                Status = 1 AND
                                                InnerTable.[DATE
CLAIMS] >= OuterTable.[DATE CLAIMS]
        )

HTH, jens Suessmeyer.

12 Jan 2006 12:25 PM

Jens

Sorry, missed something:

SELECT
    *
FROM SomeTable OuterTable
WHERE
    STATUS = 4 AND
    [DATE CLAIMS] BETWEEN '20050101' AND '20061231' AND
    NOT EXISTS
        (
            SELECT
                *
            FROM SomeTable InnerTable
            WHERE
                                                Status = 1 AND
                                                InnerTable.[DATE
CLAIMS] >= OuterTable.[DATE CLAIMS] AND
                                                InnerTable.[CLAIM NO
TRANS] = OuterTable.[CLAIM NO TRANS]

        )

12 Jan 2006 12:23 PM

Uwe Ricken

Hi Ricky,

this should work

>Status Key is: 1=Opened, 2=Re-Opened, 4=Settled/Closed.

SELECT * FROM dbo.foo
WHERE Status & 4 = 4

See more details about bitwise AND in BOL

HTH ;-)

--
Gruß, Uwe Ricken
MCP for SQL Server 2000 Database Implementation

GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
____________________________________________________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm

Show quote

"Ricky" <MSN.MSN.com> schrieb im Newsbeitrag
news:uPN06F3FGHA.208@tk2msftngp13.phx.gbl...
> Hi
>
> I'm sure there is simple answer to thsi trivial problem, but I am
> completely
> stuck.
>
> I have got a table which contains 1000's of transactions of Claims,
> however
> I need to run a query of all closed claims.
>
> Below is a sample for one claim.
>
> Status Key is: 1=Opened, 2=Re-Opened, 4=Settled/Closed.
>
> How do extract data for a given date range, of all Currently
> Settled/Closed
> claims?
>
> For e.g, Data range 01/JAN/2005 TO 01/DEC/2005.
>
> Bearing in mind, you cannot just put in the WHERE clause, Status=4, since
> it
> was re-opened several months later again.
>
> CLAIM NO TRANS     DATE CLAIMS                     STATUS
>
> --------------------------     -----------------------------------
> -
> -------------------------
>
> SOU/05/00007489     2005-01-13 00:00:00.000         1
>
> SOU/05/00007489     2005-06-07 00:00:00.000         4
>
> SOU/05/00007489     2005-11-07 00:00:00.000         2
>
>
>
> Any ideas? I'm sure I need to use MAX dat somewhere, I have tried to use
> MAX(Date Claims) and then Status = 4, but this doesn't work?
>
> Kind Regards
>
> Ricky
>
>
>
>

12 Jan 2006 12:42 PM

Uri Dimant

See if this helps you

CREATE TABLE #Test
(
rowid INT NOT NULL,
dt DATETIME NOT NULL,
status INT
)
INSERT INTO #Test VALUES (1,'20010101',1)
INSERT INTO #Test VALUES (1,'20010102',4)
INSERT INTO #Test VALUES (2,'20010103',1)
INSERT INTO #Test VALUES (2,'20010104',4)
INSERT INTO #Test VALUES (3,'20010105',1)
INSERT INTO #Test VALUES (3,'20010106',3)
INSERT INTO #Test VALUES (3,'20010107',4)
INSERT INTO #Test VALUES (4,'20010108',4)
INSERT INTO #Test VALUES (4,'20010109',2)
INSERT INTO #Test VALUES (4,'20010110',1)
INSERT INTO #Test VALUES (5,'20010108',4)
INSERT INTO #Test VALUES (5,'20010109',2)
INSERT INTO #Test VALUES (5,'20010110',1)

SELECT * FROM
(
SELECT * FROM #Test
WHERE dt=(SELECT MAX(dt) FROM #Test T WHERE
t.rowid=#Test.rowid)
) as Der WHERE status=4

Show quote

"Ricky" <MSN.MSN.com> wrote in message
news:uPN06F3FGHA.208@tk2msftngp13.phx.gbl...
> Hi
>
> I'm sure there is simple answer to thsi trivial problem, but I am
> completely
> stuck.
>
> I have got a table which contains 1000's of transactions of Claims,
> however
> I need to run a query of all closed claims.
>
> Below is a sample for one claim.
>
> Status Key is: 1=Opened, 2=Re-Opened, 4=Settled/Closed.
>
> How do extract data for a given date range, of all Currently
> Settled/Closed
> claims?
>
> For e.g, Data range 01/JAN/2005 TO 01/DEC/2005.
>
> Bearing in mind, you cannot just put in the WHERE clause, Status=4, since
> it
> was re-opened several months later again.
>
> CLAIM NO TRANS     DATE CLAIMS                     STATUS
>
> --------------------------     -----------------------------------
> -
> -------------------------
>
> SOU/05/00007489     2005-01-13 00:00:00.000         1
>
> SOU/05/00007489     2005-06-07 00:00:00.000         4
>
> SOU/05/00007489     2005-11-07 00:00:00.000         2
>
>
>
> Any ideas? I'm sure I need to use MAX dat somewhere, I have tried to use
> MAX(Date Claims) and then Status = 4, but this doesn't work?
>
> Kind Regards
>
> Ricky
>
>
>
>

12 Jan 2006 1:01 PM

Uri Dimant

Correction , range dates

SELECT *
FROM(
SELECT * FROM #Test
WHERE dt BETWEEN '20010101' AND '20010105'
) AS der WHERE dt=(SELECT MAX(dt)
FROM #Test T WHERE T.dt
BETWEEN '20010101' AND '20010105' AND t.rowid=der.rowid AND der.status=4)

Show quote

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:u$T5vW3FGHA.2012@TK2MSFTNGP14.phx.gbl...
> See if this helps you
>
> CREATE TABLE #Test
> (
> rowid INT NOT NULL,
> dt DATETIME NOT NULL,
> status INT
> )
> INSERT INTO #Test VALUES (1,'20010101',1)
> INSERT INTO #Test VALUES (1,'20010102',4)
> INSERT INTO #Test VALUES (2,'20010103',1)
> INSERT INTO #Test VALUES (2,'20010104',4)
> INSERT INTO #Test VALUES (3,'20010105',1)
> INSERT INTO #Test VALUES (3,'20010106',3)
> INSERT INTO #Test VALUES (3,'20010107',4)
> INSERT INTO #Test VALUES (4,'20010108',4)
> INSERT INTO #Test VALUES (4,'20010109',2)
> INSERT INTO #Test VALUES (4,'20010110',1)
> INSERT INTO #Test VALUES (5,'20010108',4)
> INSERT INTO #Test VALUES (5,'20010109',2)
> INSERT INTO #Test VALUES (5,'20010110',1)
>
>
> SELECT * FROM
> (
> SELECT * FROM #Test
> WHERE dt=(SELECT MAX(dt) FROM #Test T WHERE
>          t.rowid=#Test.rowid)
> ) as Der WHERE status=4
>
>
>
>
>
>
>
> "Ricky" <MSN.MSN.com> wrote in message
> news:uPN06F3FGHA.208@tk2msftngp13.phx.gbl...
>> Hi
>>
>> I'm sure there is simple answer to thsi trivial problem, but I am
>> completely
>> stuck.
>>
>> I have got a table which contains 1000's of transactions of Claims,
>> however
>> I need to run a query of all closed claims.
>>
>> Below is a sample for one claim.
>>
>> Status Key is: 1=Opened, 2=Re-Opened, 4=Settled/Closed.
>>
>> How do extract data for a given date range, of all Currently
>> Settled/Closed
>> claims?
>>
>> For e.g, Data range 01/JAN/2005 TO 01/DEC/2005.
>>
>> Bearing in mind, you cannot just put in the WHERE clause, Status=4, since
>> it
>> was re-opened several months later again.
>>
>> CLAIM NO TRANS     DATE CLAIMS                     STATUS
>>
>> --------------------------     -----------------------------------
>>   -
>> -------------------------
>>
>> SOU/05/00007489     2005-01-13 00:00:00.000         1
>>
>> SOU/05/00007489     2005-06-07 00:00:00.000         4
>>
>> SOU/05/00007489     2005-11-07 00:00:00.000         2
>>
>>
>>
>> Any ideas? I'm sure I need to use MAX dat somewhere, I have tried to use
>> MAX(Date Claims) and then Status = 4, but this doesn't work?
>>
>> Kind Regards
>>
>> Ricky
>>
>>
>>
>>
>
>

12 Jan 2006 3:53 PM

Ricky

Thanks everyone, for your contributions, will go through these and compose
something, will let you guys know, how I get on!

Kind Regards

Ricky

Show quote

"Ricky" <MSN.MSN.com> wrote in message
news:uPN06F3FGHA.208@tk2msftngp13.phx.gbl...
> Hi
>
> I'm sure there is simple answer to thsi trivial problem, but I am
completely
> stuck.
>
> I have got a table which contains 1000's of transactions of Claims,
however
> I need to run a query of all closed claims.
>
> Below is a sample for one claim.
>
> Status Key is: 1=Opened, 2=Re-Opened, 4=Settled/Closed.
>
> How do extract data for a given date range, of all Currently
Settled/Closed
> claims?
>
> For e.g, Data range 01/JAN/2005 TO 01/DEC/2005.
>
> Bearing in mind, you cannot just put in the WHERE clause, Status=4, since
it
> was re-opened several months later again.
>
> CLAIM NO TRANS     DATE CLAIMS                     STATUS
>
> --------------------------     -----------------------------------
-
> -------------------------
>
> SOU/05/00007489     2005-01-13 00:00:00.000         1
>
> SOU/05/00007489     2005-06-07 00:00:00.000         4
>
> SOU/05/00007489     2005-11-07 00:00:00.000         2
>
>
>
> Any ideas? I'm sure I need to use MAX dat somewhere, I have tried to use
> MAX(Date Claims) and then Status = 4, but this doesn't work?
>
> Kind Regards
>
> Ricky
>
>
>
>