Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: 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
    Last edited by Enigma; 12-30-03 at 06:15.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    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))
    Last edited by r123456; 12-30-03 at 08:15.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    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;
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •