| |
|
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.
|
 |
|
02-03-10, 15:46
|
|
Registered User
|
|
Join Date: Feb 2010
Posts: 3
|
|
|
Challange to all Experts
|
|
Hello Please Solve my Problem
i have one table in Sql Server with Col
TransectionID,CustomerID,Date,Time,DebitAmout
1 1 1/1/2009 24,000
2 1 4/1/2009 36,000
3 2 1/1/2009 10,000
i need to extract data with debit amount base records mean if customer told me please show my 50,000 rang invoices then return as
TransectionID,CustomerID,Date,Time,DebitAmout
1 1 1/1/2009 24,000
2 1 4/1/2009 26,000
mean extract data with value range return all invoice up to deposit Amount
please suggest solution
thanks in advance
|
|
02-04-10, 04:55
|
|
Registered User
|
|
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 343
|
|
The solution to your problem is an academic class in RDBMS theory and application. :-)))
|
|
02-04-10, 05:04
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
|
|
This is pretty tricky. Can you supply DML & DDL please?
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
02-04-10, 08:43
|
|
Registered User
|
|
Join Date: Jan 2003
Location: Nottinghamshire, UK
Posts: 364
|
|
Clarify what is
'show my 50,000 rang invoices'
'up to deposit Amount'
This may be your solution
Quote:
SELECT
tbl.*
FROM tbl
JOIN
(
SELECT
CustomerID,
SUM(DebitAmout)/COUNT(*) DebitMean
FROM tbl
GROUPBY CustomerID
) tbl_2
ON tbl.CustomerID = tbl_2.CustomerID
-- AND tbl.DebitAmout <= tbl_2.DebitMean
WHERE tbl.DebitAmout <= tbl_2.DebitMean
|
__________________
"Everything should be made as simple as possible, but not simpler." - Albert Einstein
"Everything should be made as complex as possible, so I look Cleverer." - Application Developer
|
|
02-04-10, 09:47
|
|
Registered User
|
|
Join Date: Feb 2010
Posts: 3
|
|
|
Please clear it that is not requried query
problem is that we need to extract all Invoices those payments are not received yet i mean if customer have 4 invoices and invoices data is
in other word we need to extract recovery data
TransectionID,CustomerID,Date,DebitAmount,CreditAm ount
1, 1,1/1/2009, 24,000,0
2, 1,4/1/2009, 22,000,0
3, 1,4/1/2009, 16,000,0
4, 1,4/1/2009, 36,000,0
4, 1,4/1/2009, 0, 50000
and Customer Deposit only 50000
then data show only those Invoices with out 50000 Recovered Amount in FIFO
mean
TransectionID,CustomerID,Date,DebitAmount,CreditAm ount, RecoveryBalance
3, 1,4/1/2009, 12,000,0
4, 1,4/1/2009, 36,000,0
Please give us fruitful guide line
|
Last edited by ksaddique; 02-04-10 at 09:57.
Reason: save with out deciencee
|
02-04-10, 10:32
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,448
|
|
|
Last edited by n_i; 02-04-10 at 10:35.
|
02-04-10, 11:29
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,448
|
|
Something like this, may be?
Code:
select
transactionid
,customerid
,date
,debitamount
from
(select
transactionid
,customerid
,date
,debitamount
,(select sum(debitamount)
from yourtable
where customerid = t1.customerid and date <= t1.date ) runningtotal
from
(select
transactionid
,customerid
,date
,debitamount
from yourtable) t1
) t2
where
runningtotal > (
select sum(creditamount)
from yourtable
where customerid = t2.customerid
)
order by customerid, date
|
|
02-04-10, 11:36
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Not to poop on anyone's parade, because achieving this in SQL is no mean feat, however if this needs to scale well then your best bet would be to use CLR or even (horror of horrors) a cursor. I don't have the Itzik Ben Gan link to hand proving this but can dig it up if required.
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
02-04-10, 11:48
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,448
|
|
Quote:
Originally Posted by pootle flump
if this needs to scale well then your best bet would be to use CLR or even (horror of horrors) a cursor. I don't have the Itzik Ben Gan link to hand proving this but can dig it up if required.
|
I could agree that in some circumstances that might be the case, however, I highly doubt that anyone can prove that procedures (CLR or T-SQL) generally perform (or scale) better than moderately complex queries. I'd be interested in checking the link you've mentioned.
|
|
02-04-10, 12:59
|
|
Registered User
|
|
Join Date: Feb 2010
Posts: 3
|
|
|
Hello n_i that is not correct result
Dear we need only pending Amounts against deposited Amount,but that provide all.
if Data is
TransectionID,CustomerID,Date,DebitAmount,CreditAm ount
1, 1,1/1/2009, 24,000,0
2, 1,4/1/2009, 22,000,0
3, 1,4/1/2009, 16,000,0
4, 1,4/1/2009, 36,000,0
4, 1,4/1/2009, 0, 50000
then Correct Result is
TransectionID,CustomerID,Date,DebitAmount,CreditAm ount
3, 1,4/1/2009, 12,000,0
4, 1,4/1/2009, 36,000,0
but that result is not comming
|
|
02-04-10, 13:10
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,448
|
|
Quote:
Originally Posted by ksaddique
Dear we need only pending Amounts against deposited Amount,but that provide all.
|
I'm sure you can figure out subtraction yourself. If not, I will be happy to do your job in exchange for your paycheck.
|
|
02-04-10, 20:39
|
|
Registered User
|
|
Join Date: Feb 2009
Posts: 48
|
|
Quote:
Originally Posted by ksaddique
Dear we need only pending Amounts against deposited Amount,but that provide all.
if Data is
TransectionID,CustomerID,Date,DebitAmount,CreditAm ount
1, 1,1/1/2009, 24,000,0
2, 1,4/1/2009, 22,000,0
3, 1,4/1/2009, 16,000,0
4, 1,4/1/2009, 36,000,0
4, 1,4/1/2009, 0, 50000
then Correct Result is
TransectionID,CustomerID,Date,DebitAmount,CreditAm ount
3, 1,4/1/2009, 12,000,0
4, 1,4/1/2009, 36,000,0
but that result is not comming
|
oops you selected wrong invoices..
in FIFO it should be
1, 1,1/1/2009, 24,000,0
2, 1,4/1/2009, 22,000,0
and apply the remaining 4000 to
3, 1,4/1/2009, 16,000,0
it will become
3, 1,4/1/2009, 12,000,4000
simply you need a good database design and inquiry routine
nothing complex to achive
__________________
.......
She drank beer with Coke Cola
and that's the way I like it...
.......
|
|
02-05-10, 03:52
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Quote:
Originally Posted by n_i
I could agree that in some circumstances that might be the case, however, I highly doubt that anyone can prove that procedures (CLR or T-SQL) generally perform (or scale) better than moderately complex queries. I'd be interested in checking the link you've mentioned.
|
I'll dig up the link. The key is in the Theta Join. As the number of rows in the base tables increase so the number of rows processed in the query (and therefore the cost) increases exponentially. When using iterative code the increase is linear. The power of set based processing in 99% of circumstances is the cost increases logarithmically to the number of rows in the base tables.
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
02-05-10, 04:01
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
|
|
02-05-10, 08:20
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,448
|
|
Interesting reading, and they are making some good points, but still I doubt that one can directly translate algorithmic complexity into query performance metrics. Also, their benchmark results, without explanation of how they've been achieved, look rather like global warming diagrams...
|
|
| 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
>
Challange to all Experts
