| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-30-03, 05:12
|
|
The SQL Apostle
|
|
Join Date: Jul 2003
Location: The Dark Planet
Posts: 1,395
|
|
|
Stumped -- Need a set based solution
|
|
Guys ,
Here's my problem
Table Structure
Code:
create table holding (
id1 int,
id2 int,
datefield datetime,
holding int,
quantity int
)
go
insert into holding
select 1,1,'20031210',10,null
union
select 1,1,'20031211',30,null
union
select 1,1,'20031212',70,null
union
select 1,1,'20031213',60,null
union
select 1,2,'20031210',100,null
union
select 1,3,'20031210',100,null
union
select 1,3,'20031210',10,null
go
Quantity is based on Holding for the day - Holding for the previous transaction involving same id1 ,id2.
i.e result should look like this
Code:
id1 id2 datefield holding quantity
----------- ----------- ------------------------------------------------------ ----------- -----------
1 1 2003-12-10 00:00:00.000 10 10
1 1 2003-12-11 00:00:00.000 30 20
1 1 2003-12-12 00:00:00.000 70 40
1 1 2003-12-13 00:00:00.000 60 -10
1 2 2003-12-10 00:00:00.000 100 100
1 3 2003-12-10 00:00:00.000 100 100
1 3 2003-12-11 00:00:00.000 10 -90
__________________
Get yourself a copy of the The Holy Book
order has no physical Brett in The meaning of a Kaiser . -database data
|
Last edited by Enigma; 12-30-03 at 05:15.
|
12-30-03, 06:52
|
|
Registered User
|
|
Join Date: Sep 2003
Location: The extremely Royal borough of Kensington, London
Posts: 778
|
|
select h.id1, h.id2, h.datefield, h.holding,
h.holding -
(CASE WHEN EXPR IS NULL THEN 0 ELSE EXPR END) AS "RESULT"
from holding h;
EXPR =
(select holding from holding where id1 = h.id1 AND id2 = h.id2 AND
datefield = (select max(datefield) from holding where datefield < h.datefield))
__________________
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.
|
Last edited by r123456; 12-30-03 at 07:15.
|
12-30-03, 09:39
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
|
|
Is this what you're suggesting?
Code:
SELECT h.id1
, h.id2
, h.datefield
, h.holding
, h.holding - (CASE WHEN (SELECT holding
FROM holding
WHERE id1 = h.id1
AND id2 = h.id2
AND datefield = (SELECT MAX(datefield)
FROM holding
WHERE datefield < h.datefield)
) IS NULL
THEN 0
ELSE
(SELECT holding
FROM holding
WHERE id1 = h.id1
AND id2 = h.id2
AND datefield = (SELECT MAX(datefield)
FROM holding
WHERE datefield < h.datefield)
END) AS Quantity
FROM holding h;
Can't get it to work...
|
|
12-30-03, 09:44
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
This worked...very nice....
Code:
SELECT h.id1
, h.id2
, h.datefield
, h.holding
, h.holding - ISNULL((SELECT ISNULL(holding,0)
FROM holding
WHERE id1 = h.id1
AND id2 = h.id2
AND datefield = (SELECT MAX(datefield)
FROM holding
WHERE datefield < h.datefield)
),0) AS New_Quantity
FROM holding h;
|
|
12-31-03, 02:01
|
|
The SQL Apostle
|
|
Join Date: Jul 2003
Location: The Dark Planet
Posts: 1,395
|
|
Thanks Brett and r123456 ... you saved my year !!!!
__________________
Get yourself a copy of the The Holy Book
order has no physical Brett in The meaning of a Kaiser . -database data
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Stumped -- Need a set based solution
