timestamp 2 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

timestamp 2

Author

Topic

wawansur
Starting Member

44 Posts

Posted - 2009-07-27 : 01:53:36

This the real problem
I have SP to check the rest of stock in warehouse.
I Used FSeqtime(Timestamp) to sort data from the first entry to the last one.
Sometimes it work,but sometime the result is wrong value.
This databse was have more 100000 transaction, if I change data type I afraid it will brake the data.
Any suggestion for this problem?

As Note :
@pKdStok is refer to Stock Code
@pKdGdg is refer to Warehouse Code
@pSatuan is refer to Unit Code
@pTglTrans is refer to Transaction Date

the SP :

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE [dbo].[SP_CHECKSISASTOCK_O]
(@pKdStok VARCHAR(30), @pKdGdg VARCHAR(30), @pSatuan VARCHAR(30),
@pQty MONEY, @pTglTrans DATETIME)
--WITH ENCRYPTION
AS
DECLARE @pSaldoQty Money, @pTglTransLast datetime

SET @pTglTransLast =
CAST (YEAR (@pTglTrans) AS varchar (4)) + '-' +
CAST (MONTH (@pTglTrans) AS varchar (2)) + '-' +
CAST (DAY (@pTglTrans) AS varchar (2)) + ' 23:59:59'

SET @pSaldoQty =
ISNULL((SELECT SUM (FQtyMain) FROM StckMsk
WHERE FKStck = @pKdStok AND FKGdg = @pKdGdg AND
FSatMain = @pSatuan AND FTgl <= @pTglTransLast), 0) -
ISNULL((SELECT SUM (FQtyMain) FROM StckKlr
WHERE FKStck = @pKdStok AND FKGdg = @pKdGdg AND
FSatMain = @pSatuan AND FTgl <= @pTglTransLast), 0)
- @pQty
IF SIGN (@pSaldoQty) <> -1
BEGIN

DECLARE @QtyIO money
DECLARE Cur cursor
LOCAL FOR
SELECT FQtyMain
FROM
(
SELECT dbo.f_DateFirst (FTgl) AS FTgl, FQtyMain, FSeqTime, FSeqT
FROM StckMsk
WHERE (FKStck = @pKdStok) AND (FTgl > @pTglTrans)
AND (FKGdg = @pKdGdg) AND (FSatMain = @pSatuan)
UNION ALL
SELECT dbo.f_DateFirst (FTgl) AS FTgl, -FQtyMain, FSeqTime, FSeqT
FROM StckKlr
WHERE (FKStck = @pKdStok) AND (FTgl > @pTglTrans)
AND (FKGdg = @pKdGdg) AND (FSatMain = @pSatuan)
)X
ORDER BY FTgl, FSeqT, FSeqTime
OPEN Cur
FETCH NEXT FROM Cur INTO @QtyIO
WHILE (@@FETCH_STATUS = 0) AND (SIGN (@pSaldoQty) >= 0)
BEGIN
SET @pSaldoQty = @pSaldoQty + @QtyIO
FETCH NEXT FROM Cur INTO @QtyIO
END
CLOSE Cur
DEALLOCATE Cur

END

SELECT Qty = CASE SIGN(@pSaldoQty) WHEN -1 THEN @pSaldoQty ELSE 0 END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

the table :

CREATE TABLE [StckMsk] (
[FNJ] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FNTrans] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FTgl] [datetime] NULL ,
[FKGdg] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FKStck] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FSat] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FQty] [money] NULL ,
[FHrgSat] [money] NULL ,
[FTotal] [money] NULL ,
[FPDisc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FNDisc] [money] NULL ,
[FNominal] [money] NULL ,
[FKet] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FCOACredit] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FUserID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FTglInput] [datetime] NULL ,
[FPostFrom] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FTpTr] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FPostId] [datetime] NULL ,
[FSeqTime] [timestamp] NULL ,
[FSeqT] [bigint] NULL ,
[FQtyMain] [money] NULL ,
[FSatMain] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FKDept] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [StckKlr] (
[FNJ] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FNTrans] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FTgl] [datetime] NULL ,
[FKGdg] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FKStck] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FSat] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FQty] [money] NULL ,
[FHrgSat] [money] NULL ,
[FTotal] [money] NULL ,
[FPDisc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FNDisc] [money] NULL ,
[FNominal] [money] NULL ,
[FHPP] [money] NULL ,
[FKet] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FCOADebet] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FUserID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FTglInput] [datetime] NULL ,
[FPostFrom] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FTpTr] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FPostId] [datetime] NULL ,
[FSeqTime] [timestamp] NULL ,
[FSeqT] [bigint] NULL ,
[FQtyMain] [money] NULL ,
[FSatMain] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FKDept] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

and the Function :

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION dbo.f_DateFirst (@Tanggal datetime)
RETURNS datetime
AS
BEGIN
RETURN (
CAST (YEAR (@Tanggal) AS varchar (4)) + '-' +
CAST (MONTH (@Tanggal) AS varchar (2)) + '-' +
CAST (DAY (@Tanggal) AS varchar (2)) +
' 00:00:00')
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Rgds

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 02:19:19

I didn't look through your query in detail . . but what is your usage of FSeqTime ?

for timestamp data type, from BOL

quote:
The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated

What is your usage of FSeqTime ?
Do you need FSeqTime to be unique ?
Do you want to change FSeqTime when the record is being updated ?

KH
[spoiler]Time is always against us[/spoiler]

wawansur
Starting Member

44 Posts

Posted - 2009-07-27 : 02:27:38

i want to get unique data when inserted and i can use as identity to sort data. I don't want this value change when the data is updated.
But i have usedthe timestamp type.
how to solve this problem?
data in my customer database is get big.
i afraid if i change data type will crash the data

Rgds

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 03:23:33

quote:
i want to get unique data when inserted and i can use as identity to sort data. I don't want this value change when the data is updated.
But i have usedthe timestamp type.

The behavior of timestamp data type is it will change when the row is updated.

quote:
how to solve this problem?
data in my customer database is get big.
i afraid if i change data type will crash the data

Looks like you need the date & time when the row is inserted ?
Create a new column, default it to getdate() or via your application that create the record there or via trigger. Use this new column instead of the timestamp comlumn

KH
[spoiler]Time is always against us[/spoiler]

wawansur
Starting Member

44 Posts

Posted - 2009-07-27 : 03:30:25

thx khatan