Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2006
    Posts
    18

    Unanswered: Selection problem

    this is a close question to the prior post but different
    Code:
    pn                   Amount                date          billingamt   idc         
    -------------------- --------------------- ------------- ------------ ----------- 
    a7447                .0000                 2005-12-28    150.0        10
    a7447                .0000                 2006-01-09    150.0        11
    a7447                .0000                 2006-01-10    150.0        12
    a7447                50.0000               2006-03-30    150.0        1
    a7447                100.0000              2006-04-09    150.0        2
    a7447                150.0000              2006-04-16    150.0        3
    a7447                150.0000              2006-05-09    150.0        7
    a7447                150.0000              2006-05-17    150.0        8
    a7447                10.0000               2006-06-16    150.0        15
    a7447                150.0000              2006-09-06    150.0        9
    a7474                .0000                 2006-04-06    185.0        4
    a7474                .0000                 2006-04-22    185.0        5
    a7474                25.0000               2006-04-24    185.0        6
    a7474                10.0000               2006-06-16    185.0        16
    a7474                50.0000               2006-08-22    185.0        13
    a7474                100.0000              2006-09-20    185.0        14
    Ok now i need to do is i need to calculate a sum of amount field which equals or less then billingamt for each permit ordered by the date desc

    meaning result items should be
    Code:
    a7447                150.0000              2006-09-06    150.0        9
    
    a7474                25.0000               2006-04-24    185.0        6
    a7474                10.0000               2006-06-16    185.0        16
    a7474                50.0000               2006-08-22    185.0        13
    a7474                100.0000              2006-09-20    185.0        14
    I can deal with just a count too
    as in resulting
    a7447 1
    a7474 4

    I hope the question make sense
    i couldnt find any way to do this with out of a cursor any ideas
    Thanks

  2. #2
    Join Date
    Jul 2006
    Posts
    18
    Here is another example to explain it a little better

    Code:
    pn                   Amount                date          billingamt   idc         
    -------------------- --------------------- ------------- ------------ ----------- 
    a7374                .0000                 2006-04-06    185.0        4
    a7374                .0000                 2006-04-22    185.0        5
    a7374                25.0000               2006-04-24    185.0        6
    a7374                10.0000               2006-06-16    185.0        16
    a7374                50.0000               2006-08-22    185.0        13
    a7374                75.0000               2006-08-29    185.0        50
    a7374                100.0000              2006-09-20    185.0        14
    result should be like
    Code:
    a7374                75.0000               2006-08-29    185.0        50
    a7374                100.0000              2006-09-20    185.0        14
    now the total is 175 and it is less then 185 if i add it next one 50 that would be to much 225 and passes 185
    i felt like there should be a way to do this with out using a cursor but not sure if it is possible. Any ideas

  3. #3
    Join Date
    Jul 2006
    Posts
    18
    cursor solution is here
    assume the data is in the temp table and i create a temp2 table and it puts what i need in there but of course its a cursor so its probably much slower then selects updates and so on


    Code:
    drop table #temp2
    create table #temp2
    (
    	pn varchar(20), 
    	Amount money, 
    	date datetime, 
    	billingamt money, 
    	idc int
    )
    
    declare @priorpn varchar(20), @pn varchar(20), @Amount money, @date datetime, @billingamt money, @idc int
    declare @skippn as bit
    set @skippn = 0
    
    Declare CR cursor scroll for
    	select * from #temp where amount <> 0
    		order by pn, date desc
    
    
    Open CR
    
    -- Fetch next from CiteCursor into @Agency, @Citeid, @Apply_year, @Apply_Month, @Received_Amt, @CDID
    Fetch next from CR into @pn, @Amount, @date, @billingamt, @idc
    
    While @@Fetch_Status = 0
    	Begin
    if @priorpn <> @pn 
    	begin
    		set @skippn = 0 
    	end
    set @priorpn = @pn
    if (select isnull(sum(amount), 0) + @amount  from #temp2 where pn = @pn) > @billingamt
    	begin
    		set @skippn = 1
    	end
    if (select isnull(sum(amount), 0) + @amount  from #temp2 where pn = @pn) <= @billingamt and @skippn = 0 
    	begin
    		insert into #temp2 select @pn, @Amount, @date, @billingamt, @idc
    	end
    
    		Fetch next from CR into @pn, @Amount, @date, @billingamt, @idc
    	End
    Close CR
    deallocate CR
    
    
    
    select * from #temp2

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No cursors, please...
    Code:
    select	A.pn,
    	A.Amount,
    	A.date,
    	A.billingamt,
    	A.idc,
    	sum(B.Amount) as RunningTotal,
    	count(*) as RecordCount
    from	[YourTable] A
    	inner join [YourTable] B
    		on A.pn = B.pn
    		and A.date >= B.date
    group by A.pn,
    	A.Amount,
    	A.date,
    	A.billingamt,
    	A.idc
    having	sum(B.Amount) <= A.billingamt
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2006
    Posts
    18
    Hi on some values the code didnt return correctly it repeated on a level
    data is below
    Code:
    pn                   Amount                date           billingamt    idc         
    -------------------- --------------------- -------------- ------------- ----------- 
    A1120                .0000                 2000-04-30     310.0         1
    A1120                .0000                 2000-04-30     310.0         2
    A1120                50.0000               2000-05-01     310.0         3
    A1120                25.0000               2000-05-01     310.0         4
    A1120                .0000                 2001-08-05     310.0         5
    A1120                .0000                 2001-08-15     310.0         6
    A1120                25.0000               2002-04-15     310.0         7
    A1120                .0000                 2002-08-31     310.0         8
    A1120                25.0000               2005-05-28     310.0         9
    A1120                50.0000               2005-05-30     310.0         10
    A1120                100.0000              2005-06-05     310.0         11
    A1120                150.0000              2005-07-08     310.0         12
    A1120                150.0000              2005-08-06     310.0         13
    A1120                150.0000              2005-08-06     310.0         14
    A1120                10.0000               2006-06-16     310.0         15
    
    (15 row(s) affected)
    and it returns
    Code:
    pn                   Amount                date                        billingamt                                            idc         RunningTotal          RecordCount 
    -------------------- --------------------- --------------------------- ----------------------------------------------------- ----------- --------------------- ----------- 
    A1120                25.0000               2000-05-01 00:00:00.000     310.0                                                 4           75.0000               4
    A1120                25.0000               2002-04-15 00:00:00.000     310.0                                                 7           100.0000              7
    A1120                25.0000               2005-05-28 00:00:00.000     310.0                                                 9           125.0000              9
    A1120                50.0000               2000-05-01 00:00:00.000     310.0                                                 3           75.0000               4
    A1120                50.0000               2005-05-30 00:00:00.000     310.0                                                 10          175.0000              10
    A1120                100.0000              2005-06-05 00:00:00.000     310.0                                                 11          275.0000              11
    
    (6 row(s) affected)
    and should have returned like
    Code:
    pn                   Amount                date                        billingamt            idc         
    -------------------- --------------------- --------------------------- --------------------- ----------- 
    A1120                10.0000               2006-06-16 00:00:00.000     310.0000              15
    A1120                150.0000              2005-08-06 00:00:00.000     310.0000              13
    A1120                150.0000              2005-08-06 00:00:00.000     310.0000              14
    
    (3 row(s) affected)
    i couldnt find the mistake but there is few instances it does that i need to check it out too . Ill let you know if i find any solutions. Thanks

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Run this code to check for duplicate pn/date combinations in your data:
    Code:
    select pn, date, count(*) from [YourTable] group by pn, date having count(*) > 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2006
    Posts
    18
    Here are the duplicates But they are legit data. the same action happened on the same day thats it.
    Code:
    pn                   date                                    
    -------------------- --------------------------- ----------- 
    A1120                2000-04-30 00:00:00.000     2
    A1120                2000-05-01 00:00:00.000     2
    A1120                2005-08-06 00:00:00.000     2

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You have to have some way of uniquely order the data, or you will wind up with either duplicates or inconsistent results.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jul 2006
    Posts
    18
    Is it possible to use the idc field thats a unique identifier of these items,
    and i really dont care if they are on the same day which one is used as long as it gets the totals right. I am using my cursor and it is getting the correct result.
    I like your query, i actually did start writting the same thing before hand but it was getting a little complicated. Ill try to modify the selection so it will work but i dont have time to do it now since im on a deadline. But when i get the time to go back ill change the cursor.
    Luckly the data isnt so big so i can use the cursor. But i do need to do this on other db's which is much bigger.
    Thanks for the help.
    Ill post my updated query when i get back to it. (DONT KNOW WHEN THO)

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should care. Having two different amount values on the same day can result in different running totals, depending upon which one is summed first.
    But if idc is sufficient, then try this code:
    Code:
    select	A.pn,
    	A.Amount,
    	A.date,
    	A.billingamt,
    	A.idc,
    	sum(B.Amount) as RunningTotal,
    	count(*) as RecordCount
    from	[YourTable] A
    	inner join [YourTable] B
    		on A.pn = B.pn
    		and (A.date >= B.date
    			or (A.date = B.date and A.idc >= B.idc))
    group by A.pn,
    	A.Amount,
    	A.date,
    	A.billingamt,
    	A.idc
    having	sum(B.Amount) <= A.billingamt
    I'll warn you that in certain circumstances a cursor can be faster than the above method. It all depends upon the nature of your data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jul 2006
    Posts
    18
    No it didnt work brought back some wierd stuff. I used the cursor for now since i needed the data hours ago. Thanks for the help ill probably have some time following month when we generate the real report if we do. Then i ll take look at this one more time. Thanks tho.

  12. #12
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by drewex
    ...I used the cursor for now since i needed the data...
    Your cursor solution does not produce the results you asked for in any of your posts
    I think part of your problem lies between the keyboard and the chair.
    Code:
    set dateformat ymd
    create table #t1 (pn varchar(5),Amount decimal(15,4),date datetime,billingamt decimal(15,1),idc int)
    insert into #t1 select 
    'a7447',       0.000,            '2005-12-28',         150.0    ,10 union all select 
    'a7447',       .0000            ,'2006-01-09',         150.0    ,11 union all select 
    'a7447',       .0000            ,'2006-01-10',         150.0    ,12 union all select 
    'a7447',       50.0000          ,'2006-03-30',         150.0    ,1  union all select 
    'a7447',       100.0000         ,'2006-04-09',         150.0    ,2  union all select 
    'a7447',       150.0000         ,'2006-04-16',         150.0    ,3  union all select 
    'a7447',       150.0000         ,'2006-05-09',         150.0    ,7  union all select 
    'a7447',       150.0000         ,'2006-05-17',         150.0    ,8  union all select 
    'a7447',       10.0000          ,'2006-06-16',         150.0    ,15 union all select 
    'a7447',       150.0000         ,'2006-09-06',         150.0    ,9  union all select 
    'a7474',       .0000            ,'2006-04-06',         185.0    ,4  union all select 
    'a7474',       .0000            ,'2006-04-22',         185.0    ,5  union all select 
    'a7474',       25.0000          ,'2006-04-24',         185.0    ,6  union all select 
    'a7474',       10.0000          ,'2006-06-16',         185.0    ,16 union all select 
    'a7474',       50.0000          ,'2006-08-22',         185.0    ,13 union all select 
    'a7474',       100.0000         ,'2006-09-20',         185.0    ,14 union all select 
    'a7374',       .0000            ,'2006-04-06',         185.0    ,4  union all select
    'a7374',       .0000            ,'2006-04-22',         185.0    ,5  union all select
    'a7374',       25.0000          ,'2006-04-24',         185.0    ,6  union all select
    'a7374',       10.0000          ,'2006-06-16',         185.0    ,16 union all select
    'a7374',       50.0000          ,'2006-08-22',         185.0    ,13 union all select
    'a7374',       75.0000          ,'2006-08-29',         185.0    ,50 union all select
    'a7374',       100.0000         ,'2006-09-20',         185.0    ,14 union all select
    'A1120',       .0000            ,'2000-04-30',         310.0    ,1  union all select 
    'A1120',       .0000            ,'2000-04-30',         310.0    ,2  union all select 
    'A1120',       50.0000          ,'2000-05-01',         310.0    ,3  union all select 
    'A1120',       25.0000          ,'2000-05-01',         310.0    ,4  union all select 
    'A1120',       .0000            ,'2001-08-05',         310.0    ,5  union all select 
    'A1120',       .0000            ,'2001-08-15',         310.0    ,6  union all select 
    'A1120',       25.0000          ,'2002-04-15',         310.0    ,7  union all select 
    'A1120',       .0000            ,'2002-08-31',         310.0    ,8  union all select 
    'A1120',       25.0000          ,'2005-05-28',         310.0    ,9  union all select 
    'A1120',       50.0000          ,'2005-05-30',         310.0    ,10 union all select 
    'A1120',       100.0000         ,'2005-06-05',         310.0    ,11 union all select 
    'A1120',       150.0000         ,'2005-07-08',         310.0    ,12 union all select 
    'A1120',       150.0000         ,'2005-08-06',         310.0    ,13 union all select 
    'A1120',       150.0000         ,'2005-08-06',         310.0    ,14 union all select 
    'A1120',       10.0000          ,'2006-06-16',         310.0    ,15 
    
    select * into #temp2 from #t1 where 1=2
    
    declare CR cursor for select pn, Amount, date, billingamt, idc from #t1
    Open CR
    declare @pn varchar(10), @Amount decimal(15,2), @date datetime, @billingamt decimal(15,2), @idc int
    , @skippn int,@priorpn varchar(10)
    -- Fetch next from CiteCursor into @Agency, @Citeid, @Apply_year, @Apply_Month, @Received_Amt, @CDID
    Fetch next from CR into @pn, @Amount, @date, @billingamt, @idc
    set @priorpn=''
    While @@Fetch_Status = 0
    	Begin
    if @priorpn <> @pn 
    	begin
    		set @skippn = 0 
    	end
    set @priorpn = @pn
    if (select isnull(sum(amount), 0) + @amount  from #temp2 where pn = @pn) > @billingamt
    	begin
    		set @skippn = 1
    	end
    if (select isnull(sum(amount), 0) + @amount  from #temp2 where pn = @pn) <= @billingamt and @skippn = 0 
    	begin
    		insert into #temp2 select @pn, @Amount, @date, @billingamt, @idc
    	end
    
    		Fetch next from CR into @pn, @Amount, @date, @billingamt, @idc
    	End
    Close CR
    deallocate CR
    
    select * from #temp2 order by pn,date,idc 
    
    pn    Amount            date                    billingamt        idc         
    ----- ----------------- ----------------------- ----------------- ----------- 
    A1120             .0000 2000-04-30 00:00:00.000             310.0           1 
    A1120             .0000 2000-04-30 00:00:00.000             310.0           2 
    A1120           50.0000 2000-05-01 00:00:00.000             310.0           3 
    A1120           25.0000 2000-05-01 00:00:00.000             310.0           4 
    A1120             .0000 2001-08-05 00:00:00.000             310.0           5 
    A1120             .0000 2001-08-15 00:00:00.000             310.0           6 
    A1120           25.0000 2002-04-15 00:00:00.000             310.0           7 
    A1120             .0000 2002-08-31 00:00:00.000             310.0           8 
    A1120           25.0000 2005-05-28 00:00:00.000             310.0           9 
    A1120           50.0000 2005-05-30 00:00:00.000             310.0          10 
    A1120          100.0000 2005-06-05 00:00:00.000             310.0          11 
    a7374             .0000 2006-04-06 00:00:00.000             185.0           4 
    a7374             .0000 2006-04-22 00:00:00.000             185.0           5 
    a7374           25.0000 2006-04-24 00:00:00.000             185.0           6 
    a7374           10.0000 2006-06-16 00:00:00.000             185.0          16 
    a7374           50.0000 2006-08-22 00:00:00.000             185.0          13 
    a7374           75.0000 2006-08-29 00:00:00.000             185.0          50 
    a7447             .0000 2005-12-28 00:00:00.000             150.0          10 
    a7447             .0000 2006-01-09 00:00:00.000             150.0          11 
    a7447             .0000 2006-01-10 00:00:00.000             150.0          12 
    a7447           50.0000 2006-03-30 00:00:00.000             150.0           1 
    a7447          100.0000 2006-04-09 00:00:00.000             150.0           2 
    a7474             .0000 2006-04-06 00:00:00.000             185.0           4 
    a7474             .0000 2006-04-22 00:00:00.000             185.0           5 
    a7474           25.0000 2006-04-24 00:00:00.000             185.0           6 
    a7474           10.0000 2006-06-16 00:00:00.000             185.0          16 
    a7474           50.0000 2006-08-22 00:00:00.000             185.0          13 
    a7474          100.0000 2006-09-20 00:00:00.000             185.0          14 
    
    (28 row(s) affected)
    
    drop table #temp2
    But in post#13 you asked for
    Code:
    pn                   Amount                date                        billingamt            idc         
    -------------------- --------------------- --------------------------- --------------------- ----------- 
    A1120                10.0000               2006-06-16 00:00:00.000     310.0000              15
    A1120                150.0000              2005-08-06 00:00:00.000     310.0000              13
    A1120                150.0000              2005-08-06 00:00:00.000     310.0000              14
    Post #2 asked for
    Code:
    a7374                75.0000               2006-08-29    185.0        50
    a7374                100.0000              2006-09-20    185.0        14
    and post #1 for
    Code:
    a7447                150.0000              2006-09-06    150.0        9
    
    a7474                25.0000               2006-04-24    185.0        6
    a7474                10.0000               2006-06-16    185.0        16
    a7474                50.0000               2006-08-22    185.0        13
    a7474                100.0000              2006-09-20    185.0        14

  13. #13
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Fixed
    Code:
    select	A.pn,
    	A.Amount,
    	A.date, 
    	A.billingamt,
    	A.idc,
    	sum(B.Amount) as RunningTotal, 
    	count(*) as RecordCount
    from	#t1 A
    	inner join #t1 B
    		on A.pn = B.pn
    		and (A.date > B.date
    			or (A.date = B.date and A.idc >= B.idc))
    group by A.pn,
    	A.Amount,
    	A.date,
    	A.billingamt,
    	A.idc
    having	sum(B.Amount) <= A.billingamt
    order by a.pn, a.date ,a.idc
    
    pn    Amount            date                    billingamt        idc         RunningTotal RecordCount 
    ----- ----------------- ----------------------- ----------------- ----------- ------------ ----------- 
    A1120             .0000 2000-04-30 00:00:00.000             310.0           1        .0000           1 
    A1120             .0000 2000-04-30 00:00:00.000             310.0           2        .0000           2 
    A1120           50.0000 2000-05-01 00:00:00.000             310.0           3      50.0000           3 
    A1120           25.0000 2000-05-01 00:00:00.000             310.0           4      75.0000           4 
    A1120             .0000 2001-08-05 00:00:00.000             310.0           5      75.0000           5 
    A1120             .0000 2001-08-15 00:00:00.000             310.0           6      75.0000           6 
    A1120           25.0000 2002-04-15 00:00:00.000             310.0           7     100.0000           7 
    A1120             .0000 2002-08-31 00:00:00.000             310.0           8     100.0000           8 
    A1120           25.0000 2005-05-28 00:00:00.000             310.0           9     125.0000           9 
    A1120           50.0000 2005-05-30 00:00:00.000             310.0          10     175.0000          10 
    A1120          100.0000 2005-06-05 00:00:00.000             310.0          11     275.0000          11 
    a7374             .0000 2006-04-06 00:00:00.000             185.0           4        .0000           1 
    a7374             .0000 2006-04-22 00:00:00.000             185.0           5        .0000           2 
    a7374           25.0000 2006-04-24 00:00:00.000             185.0           6      25.0000           3 
    a7374           10.0000 2006-06-16 00:00:00.000             185.0          16      35.0000           4 
    a7374           50.0000 2006-08-22 00:00:00.000             185.0          13      85.0000           5 
    a7374           75.0000 2006-08-29 00:00:00.000             185.0          50     160.0000           6 
    a7447             .0000 2005-12-28 00:00:00.000             150.0          10        .0000           1 
    a7447             .0000 2006-01-09 00:00:00.000             150.0          11        .0000           2 
    a7447             .0000 2006-01-10 00:00:00.000             150.0          12        .0000           3 
    a7447           50.0000 2006-03-30 00:00:00.000             150.0           1      50.0000           4 
    a7447          100.0000 2006-04-09 00:00:00.000             150.0           2     150.0000           5 
    a7474             .0000 2006-04-06 00:00:00.000             185.0           4        .0000           1 
    a7474             .0000 2006-04-22 00:00:00.000             185.0           5        .0000           2 
    a7474           25.0000 2006-04-24 00:00:00.000             185.0           6      25.0000           3 
    a7474           10.0000 2006-06-16 00:00:00.000             185.0          16      35.0000           4 
    a7474           50.0000 2006-08-22 00:00:00.000             185.0          13      85.0000           5 
    a7474          100.0000 2006-09-20 00:00:00.000             185.0          14     185.0000           6 
    
    (28 row(s) affected)
    
    drop table #t1

Posting Permissions

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