Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Sixstring]

Blimey, Jamie........you fluent in upper mongolian too???


I understand none of this!!

[cem_devecioglu]

for calculating exact values I need another table
which defines for a product from which price how many has been sold to design the query..

I have experienced same problem in the past so I hold
the sold numbers and their prices in another seperate table.. :-/

as actually for a same product if the price is different its something different ..

otherwise it will be approximative..

but if not available  I can use some averaging methods..

[cem_devecioglu]

ok checking another method..first in first out-fifo

[cem_devecioglu]

as the invoice id is an incremental number
we can use that.. but you will also need to code in vb6..preparing..

[cem_devecioglu]

Here is    

ps: all connections must be opened simultaneously
like
dim adoconnection1 As New ADODB.Connection
dim adoconnection2 As New ADODB.Connection
dim adoconnection3 As New ADODB.Connection

dim rsl As New ADODB.Recordset
dim rs2 As New ADODB.Recordset
dim rs3 As New ADODB.Recordset

cp1 = "Provider=sqloledb;uid=sa;pwd=password;server=servername;database=dbname ;"

    Set adoconnection1 = New ADODB.Connection
    adoconnection1.CommandTimeout = 2000
    adoconnection1.ConnectionTimeout = 10
    adoconnection1.Open cp1
   

    Set adoconnection2 = New ADODB.Connection
    adoconnection2.CommandTimeout = 2000
    adoconnection2.ConnectionTimeout = 10
    adoconnection2.Open cp1
   
   
    Set adoconnection3 = New ADODB.Connection
    adoconnection3.CommandTimeout = 2000
    adoconnection3.ConnectionTimeout = 10
    adoconnection3.Open cp1



gridrow=0

txt1="select ProductID,Description,RetailPrice,CostPrice from Products order by ProductID" 'read every product id

Set rs1 = New ADODB.Recordset
rs1.Open txt1, adoconnection1


While rs1.EOF = False   

  txt2="select cumstockforprod=sum(UnitsInStock) from StockLevel where ProductID=" & rs1!ProductID
  Set rs2 = New ADODB.Recordset
  rs2.Open txt2, adoconnection2
  cumstockforprod=rs2!cumstockforprod ' get cumulative stock for that product

  suminvoicecount=0
  sumactualcost=0.0

  txt3="select Quantity,CostPrice from Invoices where ProductID=" & rs1!ProductID & " order by InvoiceID desc"  ' this part is critical

  Set rs3 = New ADODB.Recordset
  rs3.Open txt3, adoconnection3

  while rs3.eof and suminvoicecount<cumstockforprod
    if suminvoicecount+rs3!Quantity <=cumstockforprod then

       suminvoicecount=suminvoicecount + rs3!Quantity
       sumactualcost=sumactualcost + rs3!quantity*rs3!CostPrice
   
    else

       suminvoicecount=suminvoicecount+(cumstockforprod-suminvoicecount) '
       sumactualcost=sumactualcost + (cumstockforprod-suminvoicecount)*rs3!CostPrice

    end if
      
    rs3.movenext
  Wend

   '  now write to grid or print
 
   
    gridrow=gridrow+1  '

    grid.rows=gridrow+1

 
   grid(gridrow,1).text=rs1!Description
   grid(gridrow,2).text=sumactualcost
   
   

 rs1.MoveNext

Wend

[jereboam]

I'm awfully glad I didn't respond to the original post.  It's not really an SQL problem.

Fairly simple to solve in PL/SQL, but you haven't got Oracle.  Pity about that.

[cem_devecioglu]

SQL 2000 - 2005 also have extra properties that I did not use..and remember stored procedures are not allowed..

[Omegatoy]

got halfway through this post and fell asleep!!!
 if you fellas have to work this out daily then i dont envy any of you, my life is much simpler even with all the car problems we get!! all i do is undo nuts and bolts and repair and paint body panels, life is for enjoying!! lolol clever chaps on here!!!

[cem_devecioglu]

got halfway through this post and fell asleep!!!
 if you fellas have to work this out daily then i dont envy any of you, my life is much simpler even with all the car problems we get!! all i do is undo nuts and bolts and repair and paint body panels, life is for enjoying!! lolol clever chaps on here!!!

unfortunately yes.. and sometimes you are expected to write more than a thousand lines per day during development period..

and agree your idea.. life is for enjoying

for my next life I hope I can remember that

[Omegatoy]

got halfway through this post and fell asleep!!!
 if you fellas have to work this out daily then i dont envy any of you, my life is much simpler even with all the car problems we get!! all i do is undo nuts and bolts and repair and paint body panels, life is for enjoying!! lolol clever chaps on here!!!

unfortunately yes.. and sometimes you are expected to write more than a thousand lines per day during development period..

and agree your idea.. life is for enjoying

for my next life I hope I can remember that

hope so to Cem, what you do would give me a major headache everyday, I take my hat off to you

[cem_devecioglu]

Jaime , in sql 2005 this transposition or pivoting is a bit easier.. 

http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx

[cem_devecioglu]

got halfway through this post and fell asleep!!!
 if you fellas have to work this out daily then i dont envy any of you, my life is much simpler even with all the car problems we get!! all i do is undo nuts and bolts and repair and paint body panels, life is for enjoying!! lolol clever chaps on here!!!

unfortunately yes.. and sometimes you are expected to write more than a thousand lines per day during development period..

and agree your idea.. life is for enjoying

for my next life I hope I can remember that

hope so to Cem, what you do would give me a major headache everyday, I take my hat off to you

Thanks Omegatoy

[TheBoy]

Cem - as there is replication going on, we cannot guarantee that InvoiceID is in order. There is a date field in Invoices though.

Actually, I may have given you misinformation about tables....


Invoice:
InvoiceID
InvoiceDate

InvoiceDetails:
InvoiceDetailID
InvoiceID
ProductID
CostPrice
Quantity

(tables have other stuff, doubt relevent though)


We can assume proper stock rotation, so that those left in stock are the most recently invoiced.

[TheBoy]

Jaime , in sql 2005 this transposition or pivoting is a bit easier.. 

http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx

Sadly, this also has to work in Jet/Access, so unlikely I can use SQL2K5 specific functions

[cem_devecioglu]

a bit busy day sorry,  will arrange the code accordingly the first time I'm available