# Board index » delphi » Re: Calculation within a Query/Cyril Damm

## Re: Calculation within a Query/Cyril Damm

2004-02-24 04:20:46 PM
delphi110
"Cyril Damm \(charax\)" <XXXX@XXXXX.COM>writes:
##### Quote
Hi,

What's the database you are using ?

Here is a solution for Oracle

select qt2.Quantity - qt1.Quantity

from (select Quantity from tblShareBalance where Date=01.01.2004) qt1

(select Quantity from tblShareBalance where Date=31.01.2004) qt2

Mfg-cd

RE: for Cyril Damm,
I'm using MS SQL Server 2000
##### Quote

"Shahzard" <XXXX@XXXXX.COM>schrieb im Newsbeitrag
news:403991f2\$XXXX@XXXXX.COM...
>
>I got the following tables:
>
>01.tblShareBalance:
>(CompID*,Date*,ShareHolderID*,SecurityCode,Quantity)
>02.tblShareHolder(ShareHolderID*,GroupID,Name)
>03.tblCompany(CompID*, Name)
>04.tblSecurity(SecurityCode*,SecurityName)
>05.tblGroup(GroupID*,GroupName)
>* indicates the Primary Key for each of the above tables.
>
>I want to Query the tblShareBalance for 2 specific date and their quantity
for respective dates and finally get the Differance between Quantity between
that respective date.
>
>Lets Say, one queries the tblShareBalance for 01/01/2004 as "From Date"
and gets the quantity, then he Selects the Quantity for 31/01/2004 as "To
Date" and finally Gets the a differance between quantity, which means:
>
>Difference = (Quantity for 31/01/2004) -
>(Quantity for 01/01/2004);
>
>How can I write a Query for this, and how can I calculate the differance
within this query. Is it possible? or do I have to make any changes.
>

## Re: Calculation within a Query/Cyril Damm

Hi,
ok then try this
select qt2.Quantity - qt1.Quantity
from tblShareBalance qt1, tblShareBalance qt2
where t1.Date = '01.01.2004'
and t2.Date = '31.01.2004'
and t1.CompID = xxxxxxx
and t1.ShareHolderID = xxxxxxx
and t1.CompID=t2.CompID
and t1.ShareHolderID=t2.ShareHolderID
dont forget to replace the "xxxxxxxxx" by whatever
mfg-cd
"Shahzard" <XXXX@XXXXX.COM>schrieb im Newsbeitrag
##### Quote

"Cyril Damm \(charax\)" <XXXX@XXXXX.COM>writes:
>Hi,
>
>What's the database you are using ?
>
>Here is a solution for Oracle
>
>select qt2.Quantity - qt1.Quantity
>
>from (select Quantity from tblShareBalance where Date=01.01.2004) qt1
>
>(select Quantity from tblShareBalance where Date=31.01.2004) qt2
>
>Mfg-cd
>
>
RE: for Cyril Damm,
I'm using MS SQL Server 2000
>
>"Shahzard" <XXXX@XXXXX.COM>schrieb im Newsbeitrag
>news:403991f2\$XXXX@XXXXX.COM...
>>
>>I got the following tables:
>>
>>01.tblShareBalance:
>>(CompID*,Date*,ShareHolderID*,SecurityCode,Quantity)
>>02.tblShareHolder(ShareHolderID*,GroupID,Name)
>>03.tblCompany(CompID*, Name)
>>04.tblSecurity(SecurityCode*,SecurityName)
>>05.tblGroup(GroupID*,GroupName)
>>* indicates the Primary Key for each of the above tables.
>>
>>I want to Query the tblShareBalance for 2 specific date and their
quantity
>for respective dates and finally get the Differance between Quantity
between
>that respective date.
>>
>>Lets Say, one queries the tblShareBalance for 01/01/2004 as "From Date"
>and gets the quantity, then he Selects the Quantity for 31/01/2004 as "To
>Date" and finally Gets the a differance between quantity, which means:
>>
>>Difference = (Quantity for 31/01/2004) -
>>(Quantity for 01/01/2004);
>>
>>How can I write a Query for this, and how can I calculate the
differance
>within this query. Is it possible? or do I have to make any changes.
>>
>
>

## Re: Calculation within a Query/Cyril Damm

Please replace all "t1" in where clause by "qt1" and all "t2" by "qt2"
mfg-cd
"Cyril Damm (charax)" <XXXX@XXXXX.COM>schrieb im Newsbeitrag
##### Quote
Hi,

ok then try this

select qt2.Quantity - qt1.Quantity
from tblShareBalance qt1, tblShareBalance qt2
where t1.Date = '01.01.2004'
and t2.Date = '31.01.2004'
and t1.CompID = xxxxxxx
and t1.ShareHolderID = xxxxxxx
and t1.CompID=t2.CompID
and t1.ShareHolderID=t2.ShareHolderID

dont forget to replace the "xxxxxxxxx" by whatever

mfg-cd

"Shahzard" <XXXX@XXXXX.COM>schrieb im Newsbeitrag
news:403b095e\$XXXX@XXXXX.COM...
>
>"Cyril Damm \(charax\)" <XXXX@XXXXX.COM>writes:
>>Hi,
>>
>>What's the database you are using ?
>>
>>Here is a solution for Oracle
>>
>>select qt2.Quantity - qt1.Quantity
>>
>>from (select Quantity from tblShareBalance where Date=01.01.2004) qt1
>>
>>(select Quantity from tblShareBalance where Date=31.01.2004) qt2
>>
>>Mfg-cd
>>
>>
>RE: for Cyril Damm,
>I'm using MS SQL Server 2000
>>
>>"Shahzard" <XXXX@XXXXX.COM>schrieb im Newsbeitrag
>>news:403991f2\$XXXX@XXXXX.COM...
>>>
>>>I got the following tables:
>>>
>>>01.tblShareBalance:
>>>(CompID*,Date*,ShareHolderID*,SecurityCode,Quantity)
>>>02.tblShareHolder(ShareHolderID*,GroupID,Name)
>>>03.tblCompany(CompID*, Name)
>>>04.tblSecurity(SecurityCode*,SecurityName)
>>>05.tblGroup(GroupID*,GroupName)
>>>* indicates the Primary Key for each of the above tables.
>>>
>>>I want to Query the tblShareBalance for 2 specific date and their
quantity
>>for respective dates and finally get the Differance between Quantity
between
>>that respective date.
>>>
>>>Lets Say, one queries the tblShareBalance for 01/01/2004 as "From
Date"
>>and gets the quantity, then he Selects the Quantity for 31/01/2004 as
"To
>>Date" and finally Gets the a differance between quantity, which means:
>>>
>>>Difference = (Quantity for 31/01/2004) -
>>>(Quantity for 01/01/2004);
>>>
>>>How can I write a Query for this, and how can I calculate the
differance
>>within this query. Is it possible? or do I have to make any changes.
>>>
>>
>>
>