Query Problem

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
RickyHi 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.|||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
"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
>
>|||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]
)|||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
>
>|||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)
"Uri Dimant" <urid@.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...
>|||Thanks everyone, for your contributions, will go through these and compose
something, will let you guys know, how I get on!
Kind Regards
Ricky
"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
>
>sql