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

    Unanswered: Set based solution for ....

    Code:
    CREATE TABLE [Cube_fact_table] (
    	[ISIN_ID] [bigint] NOT NULL ,
    	[CLIMSTID] [bigint] NOT NULL ,
    	[HOLDID] [bigint] NOT NULL ,
    	[Quantity] [bigint] NULL ,
    	[Holding] [numeric](15, 0) NOT NULL ,
    	[Cost] [numeric](18, 3) NOT NULL ,
    	[Close_Price] [numeric](18, 3) NOT NULL ,
    	[Tran_Value] [bigint] NULL ,
    	[Date] [datetime] NULL ,
    	[UCOA] [bigint] NULL ,
    	[COA] [bigint] NULL 
    ) ON [PRIMARY]
    GO
    
    
    
    
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('1', '1', '1', '2000', 2000, 170.100, 170.100, '340200', '11/28/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('1', '1', '3', '-1000', 1000, 185.500, 185.500, '-185500', '12/12/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '5', '48600', 48600, 225.000, 225.000, '10935000', '11/27/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '8', '-48575', 25, 243.000, 243.000, '-11803725', '12/4/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '9', '12575', 12600, 254.000, 254.000, '3194050', '12/5/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '10', '-12120', 480, 232.000, 232.000, '-2811840', '12/5/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '11', '12120', 12600, 219.000, 219.000, '2654280', '12/6/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '12', '-12120', 480, 265.000, 265.000, '-3211800', '12/6/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '13', '4261', 4741, 224.000, 224.000, '954464', '12/8/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '14', '9059', 13800, 223.000, 223.000, '2020157', '12/11/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '15', '10200', 24000, 233.000, 233.000, '2376600', '12/12/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '16', '-12188', 11812, 243.000, 243.000, '-2961684', '12/12/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '17', '12188', 24000, 234.000, 234.000, '2851992', '12/13/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '18', '-12188', 11812, 234.000, 234.000, '-2851992', '12/13/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '19', '788', 12600, 254.000, 254.000, '200152', '12/16/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '20', '8000', 20600, 223.000, 223.000, '1784000', '12/17/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '21', '-13754', 6846, 231.000, 231.000, '-3177174', '12/17/2003 12:00:00 AM', NULL, NULL)
    INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '22', '10554', 17400, 245.000, 245.000, '2585730', '12/19/2003 12:00:00 AM', NULL, NULL)

    Now what i want to do is this ...

    UCOA is to be updated with the average of all previous transactions where the climstid and isin_id are the same.

    example

    take case where holdid(transaction no) in the sample data is 14.
    What i need is a query which will sum up the data in the tran_value column upto holdid 5.

    Again I need a set based solution. Gurus .. please help
    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
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    select 
    			HOLDID, 
    			(
    				select 
    					sum(tran_value) 
    				from 
    					cube_fact_table 
    				where 
    					holdid <= b.holdid 
    					and isin_id = b.isin_id 
    					and climstid = b.climstid
    			)/holding "UCOA"
    		from 
    			cube_fact_table b

    Did find it ... this worked
    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    does sql server allow you to update the same table you're selecting from? i can't remember, and i don't have sql server to test on

    why do you say "... a query which will sum up the data in the tran_value column upto holdid 5" -- is that because you are going "up" the list, i.e. backwards? (i would call that down, but never mind)

    what sequence should be used, holdid itself, or the date?

    and how come the tran_value column values are in quotes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by r937
    does sql server allow you to update the same table you're selecting from? i can't remember, and i don't have sql server to test on
    Yeah ... it does allow that ...

    Originally posted by r937
    why do you say "... a query which will sum up the data in the tran_value column upto holdid 5" -- is that because you are going "up" the list, i.e. backwards? (i would call that down, but never mind)
    yes .. i need to backtrack the transactions on this table

    Originally posted by r937
    what sequence should be used, holdid itself, or the date?
    holdid can be used as its distinct for all rows

    Originally posted by r937
    and how come the tran_value column values are in quotes?
    Dont know actually ... generated the insert statements using a free software called sqlwebadmin from Microsoft site .... allows you to generate insert statements for data. a very useful tool to have
    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
  •