please help - 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)

please help

Author

Topic

dpais
Yak Posting Veteran

60 Posts

Posted - 2007-12-13 : 13:08:36

HI : PLEASE HELP -

I HAVE 4 COLUMS

DATE = MONDAY OF EVERY WEEK LOOKING FORWARD.
DEMAND = DATA IS GIVEN ...
FIRST PREVIOUS_BALANCE IS GIVEN .... I.E 8000

RELEASE = IF PREVIOUS_BALANCE > RELEASE THEN RELEASE = 0
ELSE IF PREVIOUS_BALANCE < RELEASE THEN RELEASE = RELEASE - PREVIOUS_BLANCE
BALANCE IS ALWAYS EQUAL TO PREVIOUS_BALANCE - DEMAND + RELEASE.

DATE______________DEMAND______RELEASE________BALANCE
FIRST BALANCe________________________________8000
12/10/2007________5000________0______________3000
12/17/2007________500_________0______________2500
12/24/2007________7770________5270___________0
12/31/2007________1000________1000___________0

thnaks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-13 : 19:25:25

What is your question ?

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

dpais
Yak Posting Veteran

60 Posts

Posted - 2007-12-14 : 07:12:48

hi : the question is how do i code the calculation of the reelase and balance column to be calculated automatically and inserted into the table by SQl.

thanks.

quote:
Originally posted by khtan

What is your question ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-14 : 08:04:19

can you post your table DDL and sample data also ?

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

dpais
Yak Posting Veteran

60 Posts

Posted - 2007-12-14 : 11:32:52

HERE YOU GO SIR

-- CREATE TABLE

CREATE TABLE [test] (
[Date] [datetime] NULL ,
[Demand] [int] NULL ,
[Release] [int] NULL ,
[Balance] [int] NULL
) ON [PRIMARY]
GO

-- INSERT SAMPLE DATA

INSERT INTO [test]([Date], [Demand], [Release], [Balance])
VALUES ('2007-12-10',5000,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-12-17',500,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-12-24',7770,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-12-31',1000,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-01-07',500,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-01-14',400,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-01-21',6000,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-01-28',2500,NULL,NULL)

PLEASE USE A VARIABLE FOR THE OPENING BALANCE OF 8000 PCS.

THANKS FOR THE HELP.

Friznost
Starting Member

20 Posts

Posted - 2007-12-17 : 11:59:53

Was a little confused by the business rules and your example because they don't match. This should be close to what you are looking for....

CREATE TABLE [test] (
[Date] [datetime] NULL ,
[Demand] [int] NULL ,
[Release] [int] NULL ,
[Balance] [int] NULL,
[PrimaryKey] INT Primary Key IDENTITY (1, 1) 
) ON [PRIMARY]
GO

--INSERT SAMPLE DATA

INSERT INTO [test]([Date], [Demand], [Release], [Balance])
VALUES ('2007-12-10',5000,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-12-17',500,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-12-24',7770,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-12-31',1000,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2008-01-07',500,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2008-01-14',400,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2008-01-21',6000,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2008-01-28',2500,NULL,NULL)




DECLARE @Initial_Balance INT
SET @Initial_Balance = 8000


DECLARE TestCursor CURSOR FOR
        SELECT Demand, Release, Balance
        FROM Test
        ORDER BY [Date]



DECLARE @Current_Demand AS Int,
        @Previous_Demand AS Int,
        @Current_Release AS Int,
        @Previous_Release AS Int,
        @Current_Balance AS Int,
        @Previous_Balance AS Int

SET @Previous_Balance = @Initial_Balance

OPEN TestCursor

FETCH NEXT FROM TestCursor INTO @Current_Demand, @Current_Release, @Current_Balance

SET @Current_Release = CASE WHEN @Current_Release IS NULL THEN 0 ELSE @Current_Release END
SET @Current_Balance = CASE WHEN @Current_Balance IS NULL THEN 0 ELSE @Current_Balance END

WHILE (@@Fetch_Status = 0)
       BEGIN
             UPDATE Test
                     SET
                         Release = CASE WHEN @Current_Release IS NULL THEN 0
                                        WHEN @Previous_Balance > @Current_Release THEN 0
                                        WHEN @Previous_Balance <= @Current_Release THEN @Current_Release - @Previous_Balance END,

                         Balance = @Previous_Balance - @Current_Demand + (CASE WHEN @Current_Release IS NULL THEN 0
                                                                               WHEN @Previous_Balance > @Current_Release THEN 0
                                                                               WHEN @Previous_Balance <= @Current_Release THEN @Current_Release - @Previous_Balance END)
                     WHERE CURRENT OF TestCursor
             SET @Previous_Balance = @Previous_Balance - @Current_Demand + (CASE WHEN @Current_Release IS NULL THEN 0
                                                                                 WHEN @Previous_Balance > @Current_Release THEN 0
                                                                                 WHEN @Previous_Balance <= @Current_Release THEN @Current_Release - @Previous_Balance END)      
             FETCH NEXT FROM TestCursor INTO @Current_Demand, @Current_Release, @Current_Balance
             SET @Current_Release = CASE WHEN @Current_Release IS NULL THEN 0 ELSE @Current_Release END
             SET @Current_Balance = CASE WHEN @Current_Balance IS NULL THEN 0 ELSE @Current_Balance END
       END

CLOSE TestCursor
DEALLOCATE TestCursor


SELECT [Date], Demand, Release, Balance 
FROM Test 
Order BY [Date]

dpais
Yak Posting Veteran

60 Posts

Posted - 2007-12-18 : 10:52:13

hi friznost : i seem to have tweaked your code to make it work for me - thanks for the direction - works perfectly for me -

please see following -----------------------------------------

-- please use this part carefully - it drops any table named test if it exists
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test] -- like i said be careful of running this
GO

-- create the table called test

CREATE TABLE [test] (
[Date] [datetime] NULL ,
[Demand] [int] NULL ,
[Release] [int] NULL ,
[Balance] [int] NULL,
[PrimaryKey] INT Primary Key IDENTITY (1, 1)
) ON [PRIMARY]
GO

--INSERT SAMPLE DATA

INSERT INTO [test]([Date], [Demand], [Release], [Balance])
VALUES ('2007-12-10',5000,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-12-17',500,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-12-24',7770,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2007-12-31',1000,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2008-01-07',500,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2008-01-14',400,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2008-01-21',6000,NULL,NULL)
INSERT INTO [test]([Date], [Demand], [Release], [Balance])
values ('2008-01-28',2500,NULL,NULL)

-- declare the opening balance
DECLARE @Initial_Balance INT
SET @Initial_Balance = 8000

-- decalre the cursor.
DECLARE TestCursor CURSOR FOR
SELECT Demand, Release, Balance
FROM Test
ORDER BY [Date]

-- declare the control variables
DECLARE @Current_Demand AS Int,
@Previous_Demand AS Int,
@Current_Release AS Int,
@Previous_Release AS Int,
@Current_Balance AS Int,
@Previous_Balance AS Int

-- set the variable @previous balance to the opening initial balance.
SET @Previous_Balance = @Initial_Balance

-- process cursor
OPEN TestCursor

FETCH NEXT FROM TestCursor INTO @Current_Demand, @Current_Release, @Current_Balance

SET @Current_Release = CASE WHEN @Current_Release IS NULL THEN 0 ELSE @Current_Release END
SET @Current_Balance = CASE WHEN @Current_Balance IS NULL THEN 0 ELSE @Current_Balance END

-- logic used is :
-- if the previous balance - current demand is greater than 0
-- then current release = 0
-- else current release = current demand - previous blaance

WHILE (@@Fetch_Status = 0)
BEGIN
UPDATE Test
SET
Release = CASE WHEN @Current_Release IS NULL THEN 0
WHEN @Previous_Balance- @Current_Demand > @Current_Release THEN 0
WHEN @Previous_Balance- @Current_Demand <= @Current_Release THEN @Current_demand - @Previous_Balance END,

Balance = @Previous_Balance - @Current_Demand + (CASE WHEN @Current_Release IS NULL THEN 0
WHEN @Previous_Balance- @Current_Demand > @Current_Release THEN 0
WHEN @Previous_Balance- @Current_Demand <= @Current_Release THEN @Current_demand - @Previous_Balance END)
WHERE CURRENT OF TestCursor
SET @Previous_Balance = @Previous_Balance - @Current_Demand + (CASE WHEN @Current_Release IS NULL THEN 0
WHEN @Previous_Balance- @Current_Demand > @Current_Release THEN 0
WHEN @Previous_Balance- @Current_Demand <= @Current_Release THEN @Current_demand - @Previous_Balance END)
FETCH NEXT FROM TestCursor INTO @Current_Demand, @Current_Release, @Current_Balance
SET @Current_Release = CASE WHEN @Current_Release IS NULL THEN 0 ELSE @Current_Release END
SET @Current_Balance = CASE WHEN @Current_Balance IS NULL THEN 0 ELSE @Current_Balance END
END

CLOSE TestCursor
DEALLOCATE TestCursor

-- diplay the output.
SELECT [Date], Demand, Release, Balance
FROM Test
Order BY [Date]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources