Database

22-Jul-07 0:07

hi,

I have two tables reciept and issue.
Reciept have the foloowing details

partno date rquantity rrate
a 10/1/2005 50 15
a 15/4/2005 10 22
a 11/7/2005 40 20

Issue have the foloowing details

partno date iquantity irate value
a 14/1/2005 10 10
a 15/5/2005 20 22
a 1/8/2005 20 22

what i have to do is
take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calucated result in "Value" column
iquantity(irate-rrate)
example, first we have to subtract
10 from the 50, the remainder is 40 ,store 10(10)+10(10-15)=50
20 from the 40, the remainder is 20,store 20(22)+20(22-22)=440
20 from the 20, the remainder is 0,,store 20(20)+20(22-20)=440

Can anyone tell to solve this?

Chaitra N

Joe22-Jul-07 21:21

Joe

22-Jul-07 21:21

n_gchaitra wrote:
take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calucated result in "Value" column

How about trying to itrate this from Front-End and thereby updating the column in the DB accordingly?

n_gchaitra23-Jul-07 1:53

23-Jul-07 1:53

I thought of using asp.net with vb script. But I think then also we need to write appropriate sql statements only.

Chaitra N

jonathan1523-Jul-07 5:34

jonathan15

23-Jul-07 5:34

What you are looking for is a cursor (at least if you are using sql server).
cursors allow you to iterate through a set of results returned by a select statement and perform any number of sql actions on each iteration. Be careful though as SQL is designed for Set based actions not iterative procedures so the performance overhead against a properly designed query (if one would be possible for what you want to do) is huge.

Just google for 'SQL Cursor' and you should find what you want.

Jonathan

n_gchaitra23-Jul-07 17:31

Update clause using if condition

23-Jul-07 17:31

I dont know whether i have to use cursor or simply a stored prcedure.
This is FIFO inventory system. Recieved and Issue items will be in different table say, RECIEVE & ISSUE. Each item may be recieved and issued many times. Each time may be with different rate,quantiy and date. For example let us considere the following tables,

RECIEVE TABLE

PARTNO RDATE RRATE/UNIT rQUANTITY

A 10/10/2006 15 20

A 10/10/2007 20 50

A 1/11/2006 18 10

B 10/12/2006 15 30

B 13/2/2007 25 15

ISSUE TABLE

PARTNO IDATE IRATE/UNIT iQUANTITY

A 10/5/2007 18 18

A 1/12/2007 20 45

A 13/2/2007 20 15

B 13/2/2007 20 20

B 1/5/2007 20 25

Ultimately i have to get the following table

PARTNO IDATE IRATE/UNIT iQUANTITY ShortTerm LongTerm

Here I want to find the LongTerm Value and Shorttem value depending on the difference in date between idate and rdate of each item. This is to find the gain or loss of the item in that particular date. The formula is iquatity(irate)+iquatity(irate-rRate),if the date difference between recieve and issue is >365 day then the calculated value should be place in LongTerm value column else in the Short term value column

First we take A, the first issue quanitity is 18, but the no of quantity recieved was 20 so the value will be 18(18)+18(18-15). Now the remaining no of items present in first recieved is 2.The difference between issue date 10/5 /2007 and recieve date 10/10/2006 is less than 365 days so has to be placed in ShortTerm column.

So when calculating the amount of second issue first we have issue remaining 2, then go for second recieve. Here issue quantity is 45. so, the value must be (2(20)+2(20-15)). The difference between issue date 1/12/2007 and recieve date 10/10/2006 is more than 365 days so has to be placed in LongTerm column. Then remaining 43 items, the value has to be (43(20)+(43(20-20). The difference between issue date 1/12/2007 and recieve date 1/12/2007 is less than 365 days so has to be placed in ShortTerm column. Now the remaining items in Recieve table for A item is 7+10.

Now we have to calculate the next A item as we did for second. i.e. first issuing 7 items from the remaining RECIEVE and then another 8. So we are left with 2 recieved items which will be there until next issue.

This has to be repeated for the Item B and so on.

Chaitra N

n_gchaitra21-Jul-07 22:05

Re: Update clause using if condition

21-Jul-07 22:05

I have written following sqlstatements to update a column based on the no of days between two dates. but it is giving the error,
Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

In what other way i can get the expected result

if (select datediff(dd,transactiondate, Issuedate) from receipt)>365
begin
update receipt set longvalue=datediff(dd,transactiondate, Issuedate)
end
else
begin
update receipt set shortvalue=datediff(dd,transactiondate, Issuedate)
end

Chaitra N

Colin Angus Mackay21-Jul-07 22:48

Colin Angus Mackay

21-Jul-07 22:48

Use a WHERE in the UPDATE

UPDATE receipt set longvalue=datediff(dd,transactiondate, Issuedate)
WHERE datediff(dd,transactiondate, Issuedate) >365
 
UPDATE receipt set shortvalue=datediff(dd,transactiondate, Issuedate)
WHERE datediff(dd,transactiondate, Issuedate) <= 365

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

My website

datedifference from two different tables

n_gchaitra21-Jul-07 21:01

Re: datedifference from two different tables

21-Jul-07 21:01

Hi,

I want to find the date difference of date1 and date2. But these two colums are present in different tables. I have used joins but not getting proper results because the no of rows i am getting is the cartesian product of the two tables. I am includeing two tables and the result i have got after using joins. Please tell the right way to do this.

Tran_type Partno Description Transactiondate Quantity Rate Balqty
--------- ------ ----------- ------------------- ------------ --------- -------
R ABC NULL 2005-01-01 00:00:00 15.0 20.0 15.0
R ABC NULL 2005-04-02 00:00:00 15.0 30.0 15.0
R ABC NULL 2005-09-01 00:00:00 50.0 30.0 50.0

(3 row(s) affected)

Tran_type Partno Description Transactiondate Quantity Rate Balqty
---------- ------ ----------- ------------------- ----------- --------- --------
S ABC NULL 2005-06-03 00:00:00 5.0 25.0 NULL
S ABC NULL 2005-07-01 00:00:00 5.0 10.0 NULL
S ABC NULL 2006-06-01 00:00:00 15.0 50.0 NULL

(3 row(s) affected)

tran_type transactiondate transactiondate tran_type Date diff
--------- --------------------------- --------------------------- ---------- -----------
R 2005-01-01 00:00:00 2005-06-03 00:00:00 S 153
R 2005-04-02 00:00:00 2005-06-03 00:00:00 S 62
R 2005-09-01 00:00:00 2005-06-03 00:00:00 S -90
R 2005-01-01 00:00:00 2005-07-01 00:00:00 S 181
R 2005-04-02 00:00:00 2005-07-01 00:00:00 S 90
R 2005-09-01 00:00:00 2005-07-01 00:00:00 S -62
R 2005-01-01 00:00:00 2006-06-01 00:00:00 S 516
R 2005-04-02 00:00:00 2006-06-01 00:00:00 S 425
R 2005-09-01 00:00:00 2006-06-01 00:00:00 S 273

(9 row(s) affected)

Chaitra N

N a v a n e e t h22-Jul-07 22:51

N a v a n e e t h

22-Jul-07 22:51

Could you show the query ?

My Website | Ask smart questions

Re: datedifference from two different tables

n_gchaitra23-Jul-07 1:51

Access to one table in quick succession cause problem.

23-Jul-07 1:51

select 'Difference in Date'=datediff(dd,r.rdate,i.idate) from receive r,issue i where r.partno=i.partno

Chaitra N

cosma21721-Jul-07 11:04

cosma217

21-Jul-07 11:04

Hallo

for the overview I only copied the code wich is necessary to, I hope, solve my problem.

1. With the following command line "retError = dbExtDel.DeleteSQL(sql)" I am deleting one recordset from my table. [Succsessful]

2. With the following command line "arrMedExtension = ReadExtensions(retError)" I import the data from my table with the before deleted record again. [Unuccsessful]

Even the record has been deleted in the first step, in the second step I still can see the deleted record. If I insert the command line " System.Threading.Thread.Sleep(1000)" an wait for a litle while sometimes it is Succsessful but not ever. It seems that it is a timing problem but I cant find the cause. Can anybody assist me to solve my problem ?

Thanks in advance.

The sql string I used

sql = "DELETE Audio_Extension.ID, Audio_Extension.AudioExtension FROM " + _
"Audio_Extension WHERE (((Audio_Extension.AudioExtension)='" + ExtDel + "'));"
______________________________________________________________________________________
If diagReceive = 2 Then
retError = dbExtDel.DeleteSQL(sql)
If retError = True Then Exit Sub

arrMedExtension = dbReadExt.ReadFieldsFromTable("Audio_Extension", "AudioExtension", retError)
If retError = True Then Exit Sub

....

End if
_____________________________________________________________________________________
Public Function DeleteSQL(ByVal _sql As String) As Boolean
Dim retVal As Integer
Dim AbortApp As Boolean
Dim cmdStr As String = _sql
Dim con As New OleDb.OleDbConnection(ConString)
Dim cmd As New OleDb.OleDbCommand(cmdStr, con)

Try

con.Open() connection .
retVal = cmd.ExecuteNonQuery

If retVal = 0 Then
MsgBox(gResErr.GetString("dbErr6"), MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, gResErr.GetString("dbErrHead1"))
AbortApp = True

End If

Catch ex As Exception
MsgBox(gResErr.GetString("dbErr1") + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErr3") + vbCrLf + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErr3Sol1") + vbCrLf + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErrMes") + vbCrLf + ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Mod: DeleteSQL >" + mod_GlobalVar.gResErr.GetString("dbErrHead1"))

AbortApp = True
Finally
Try
con.Close()
con = Nothing

Catch ex As Exception
'No further error mesage.

End Try
End Try

Return AbortApp

End Function
___________________________________________________________________________________

Public Function ReadFieldsFromTable(ByVal _tableName As String, ByVal _FieldName As String, ByRef AbortApp As Boolean) As ArrayList

Dim rs As Object = CreateObject("ADODB.Recordset")
Dim FieldContent As String = ""
Dim arr_Fileds As New ArrayList

Try

rs.Open(_tableName, ConString, 3, 3)

If rs.RecordCount > 0 Then
Do Until rs.EOF
arr_Fileds.Add(rs.Fields(_FieldName).Value)
rs.MoveNext()

Loop
End If

Catch ex As Exception

MsgBox(mod_GlobalVar.gResErr.GetString("dbErr1") + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErr2") + vbCrLf + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErr3Sol1") + vbCrLf + vbCrLf + mod_GlobalVar.gResErr.GetString("dbErrMes") + vbCrLf + ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Mod: ReadFieldsFromTable >" + mod_GlobalVar.gResErr.GetString("dbErrHead1"))

AbortApp = True

Finally
Try
rs.Close()
rs = Nothing

Catch ex As Exception
'No further error mesage.

End Try
End Try

Return arr_Fileds 'return value.

End Function

Report genaration: Data exchange to MS-Excel

Ravisubrahmanya21-Jul-07 3:11

Re: Report genaration: Data exchange to MS-Excel

21-Jul-07 3:11

Hii,

I have a MSDE or SQL sever, it is logging the data from an instrument continuously. I want to genarate an Report on Excel sheet on daily basis and separate Monthly basis, which should happen automatically.

In this case,

For, Daily report,each hour data has to be reported in MS-Excel sheet by row wise in the perticular column. 00:00 hrs to 24:00 hrs data should be updated frm SQL sever or MSDE to Excel sheet. In another column of Excel, 01:00-00:00(subtraction) hr data should be updated as a subtracted result, 02:00-00:00 hr data likewise for 24:00 hr, 24:00-00:00 hr data to be updated (From each hour 00:00 hr's data to be subtract), and below side of the excel sheet, all these 24 subtracted result should be added and this is the Result. All this things happen automatically.This is the daily report.

In the monthly Reoprt, all the result of the daily report update in another excel sheet row wise for each day and after the complition of month, all data's to be added and it will be the result in that report.

So please give me the solution regarding this. This Reports should genarate automatically.

With regards,
Ravisubrahmnaya

Rob Graham21-Jul-07 4:07

Rob Graham

21-Jul-07 4:07

Sounds like you have the start of a nice spec for a reporting application including a scheduler. It could be implemented in C# using the System.Data.SqlClient classes to read the SQL data and System.Data.Oledb classes to write the Excel spreadsheet.

Another alternative would be to use SQL Server reporting services, since this could also provide the scheduling aspect.

Re: Report genaration: Data exchange to MS-Excel

Ravisubrahmanya21-Jul-07 6:54

Re: Report genaration: Data exchange to MS-Excel

21-Jul-07 6:54

Dear Mr.Rob Graham,

Thanks for ur suggestions. If i have further doubt i will ask ur help.

with regards,
Ravi

With regards,
Ravisubrahmnaya

Ravisubrahmanya21-Jul-07 7:26

How to restore MSDE server data into SQL server database?

21-Jul-07 7:26

Dear Rob Graham,

Can i just illustrate me how it will be done using C# and by SQL Server reporting services. Just for day's report genaration can u illustrate me how it will be done using programmng?. if u have a time and if my help is nt seems to be wrong, pls reply me.

With regards,
Ravisubrahmnaya

Ravisubrahmanya21-Jul-07 2:45