Cascade Inserts/Clone a tree

I am creating a small project management application using SQL Server. There
are three main tables (prjProjects, prjTasks, prjDetails). A project can
have 0 to many tasks and a tasks can have 0 to many details.
Many of our projects are very similar in that they would consist of
basically the same task and detail lists/records. I would like to be able to
"clone" a project using a stored procedure. I will be accessing the tables
from multiple front ends such as MS Access, ColdFusion, and ASP. A stored
procedure would allow me to create a new, cloned project from any of these
front ends.
I am not sure where to start with this. I have searched Google and news
groups without finding much useful information. Do I need to create a cursor
to loop through records and perform inserts or is there a more efficient
method? The tables, significant fields, and records are created below. There
are additional fields for descriptions, dates, etc but if I get the basic
framework, I should be able to include the other fields. I would like to
duplicate the "TITLE" field values in the new records.
Hope I have provided enough info that someone can point me in the right
direction.
Duane Hookom
MS Access MVP
CREATE TABLE [dbo].[prjProjects]
(
[PR_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PR_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[prjTasks] (
[TS_ID] [int] IDENTITY (1, 1) NOT NULL ,
[TS_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TS_PR_ID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[prjDetails]
(
[DT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DT_TITLE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DT_TS_ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjProjects] WITH NOCHECK ADD
CONSTRAINT [PK_prjProjects] PRIMARY KEY CLUSTERED
(
[PR_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjTasks] WITH NOCHECK ADD
CONSTRAINT [PK_prjTasks] PRIMARY KEY CLUSTERED
(
[TS_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjDetails] WITH NOCHECK ADD
CONSTRAINT [PK_prjDetails] PRIMARY KEY CLUSTERED
(
[DT_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjTasks] ADD
CONSTRAINT [FK_prjTasks_prjProjects] FOREIGN KEY
(
[TS_PR_ID]
) REFERENCES [dbo].[prjProjects] (
[PR_ID]
)
GO
ALTER TABLE [dbo].[prjDetails] ADD
CONSTRAINT [FK_prjDetails_prjTasks] FOREIGN KEY
(
[DT_TS_ID]
) REFERENCES [dbo].[prjTasks] (
[TS_ID]
)
GO
INSERT INTO prjProjects (PR_TITLE) Values('Project to Clone')
GO
INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task One', 1)
GO
INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task Two', 1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 3 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
Two',2)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
Two',2)
GO"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I am creating a small project management application using SQL Server.
>There are three main tables (prjProjects, prjTasks, prjDetails). A project
>can have 0 to many tasks and a tasks can have 0 to many details.
> Many of our projects are very similar in that they would consist of
> basically the same task and detail lists/records. I would like to be able
> to "clone" a project using a stored procedure. I will be accessing the
> tables from multiple front ends such as MS Access, ColdFusion, and ASP. A
> stored procedure would allow me to create a new, cloned project from any
> of these front ends.
> I am not sure where to start with this. I have searched Google and news
> groups without finding much useful information. Do I need to create a
> cursor to loop through records and perform inserts or is there a more
> efficient method? The tables, significant fields, and records are created
> below. There are additional fields for descriptions, dates, etc but if I
> get the basic framework, I should be able to include the other fields. I
> would like to duplicate the "TITLE" field values in the new records.
> Hope I have provided enough info that someone can point me in the right
> direction.
> --
> Duane Hookom
> MS Access MVP
>
First you should be to add some important constraints. These are my
assumptions of course but the keys will be important here.
ALTER TABLE dbo.prjProjects ALTER COLUMN PR_TITLE NVARCHAR(50) NOT NULL;
ALTER TABLE dbo.prjTasks ALTER COLUMN TS_TITLE NVARCHAR(50) NOT NULL;
ALTER TABLE dbo.prjProjects ADD CONSTRAINT AK1_prjProjects
UNIQUE (PR_TITLE);
ALTER TABLE dbo.prjTasks ADD CONSTRAINT AK1_prjTasks
UNIQUE (TS_TITLE, TS_PR_ID);
ALTER TABLE dbo.prjDetails ADD CONSTRAINT AK1_prjDetails
UNIQUE (DT_TITLE, DT_TS_ID);
Now create a proc something like this (error handling omitted for brevity):
CREATE PROC dbo.usp_project_copy
(
@.pr_id INT,
@.pr_title NVARCHAR(50),
@.new_pr_id INT OUTPUT
)
AS
BEGIN
INSERT INTO dbo.prjProjects (PR_TITLE)
VALUES (@.pr_title) ;
SET @.new_pr_id = SCOPE_IDENTITY();
INSERT INTO dbo.prjTasks (TS_TITLE, TS_PR_ID)
SELECT TS_TITLE, @.new_pr_id
FROM dbo.prjTasks
WHERE TS_PR_ID = @.pr_id ;
INSERT INTO dbo.prjDetails (DT_TITLE, DT_TS_ID)
SELECT D1.DT_TITLE, T2.TS_ID
FROM dbo.prjDetails AS D1
JOIN dbo.prjTasks AS T1
ON D1.DT_TS_ID = T1.TS_ID
AND T1.TS_PR_ID = @.pr_id
JOIN dbo.prjTasks AS T2
ON T1.TS_TITLE = T2.TS_TITLE
AND T2.TS_PR_ID = @.new_pr_id
AND T1.TS_TITLE = T2.TS_TITLE ;
RETURN
END
GO
DECLARE @.pr_id INT
EXEC dbo.usp_project_copy
@.pr_id = 1,
@.pr_title = 'New Project',
@.new_pr_id = @.pr_id OUTPUT ;
SELECT *
FROM prjProjects
WHERE PR_ID = @.pr_id ;
Thanks for including the DDL. Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You could create a script file, very similar to the DDL/DML you offered,
adding additional elements (as David indicated), and then execute the script
file using OSQL (for SQL 2000) or SQLcmd (for SQL 2005)
If you were to use any variables in the script file, remember that when a GO
is encountered, the variable goes out of scope, so you would have to
re-create and initialize it again.
If you want to automate the process, the script file can be executed from a
SQL Agent Job, or even from a stored procedure.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I am creating a small project management application using SQL Server.
>There are three main tables (prjProjects, prjTasks, prjDetails). A project
>can have 0 to many tasks and a tasks can have 0 to many details.
> Many of our projects are very similar in that they would consist of
> basically the same task and detail lists/records. I would like to be able
> to "clone" a project using a stored procedure. I will be accessing the
> tables from multiple front ends such as MS Access, ColdFusion, and ASP. A
> stored procedure would allow me to create a new, cloned project from any
> of these front ends.
> I am not sure where to start with this. I have searched Google and news
> groups without finding much useful information. Do I need to create a
> cursor to loop through records and perform inserts or is there a more
> efficient method? The tables, significant fields, and records are created
> below. There are additional fields for descriptions, dates, etc but if I
> get the basic framework, I should be able to include the other fields. I
> would like to duplicate the "TITLE" field values in the new records.
> Hope I have provided enough info that someone can point me in the right
> direction.
> --
> Duane Hookom
> MS Access MVP
>
> CREATE TABLE [dbo].[prjProjects]
> (
> [PR_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [PR_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[prjTasks] (
> [TS_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [TS_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TS_PR_ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[prjDetails]
> (
> [DT_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [DT_TITLE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DT_TS_ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjProjects] WITH NOCHECK ADD
> CONSTRAINT [PK_prjProjects] PRIMARY KEY CLUSTERED
> (
> [PR_ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[prjTasks] WITH NOCHECK ADD
> CONSTRAINT [PK_prjTasks] PRIMARY KEY CLUSTERED
> (
> [TS_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjDetails] WITH NOCHECK ADD
> CONSTRAINT [PK_prjDetails] PRIMARY KEY CLUSTERED
> (
> [DT_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjTasks] ADD
> CONSTRAINT [FK_prjTasks_prjProjects] FOREIGN KEY
> (
> [TS_PR_ID]
> ) REFERENCES [dbo].[prjProjects] (
> [PR_ID]
> )
> GO
> ALTER TABLE [dbo].[prjDetails] ADD
> CONSTRAINT [FK_prjDetails_prjTasks] FOREIGN KEY
> (
> [DT_TS_ID]
> ) REFERENCES [dbo].[prjTasks] (
> [TS_ID]
> )
> GO
> INSERT INTO prjProjects (PR_TITLE) Values('Project to Clone')
> GO
> INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task One', 1)
> GO
> INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task Two', 1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
> One',1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
> One',1)
> GO
>
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 3 of Task
> One',1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
> Two',2)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
> Two',2)
> GO
>|||David and Arnie,
Thanks much. This should provide a good starting point for me. All of my
projects, tasks, and details have start and expected dates associated so I
will need to include them. I'm fairly sure I can get this on my own. If not,
I'll be back.
Duane Hookom
MS Access MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OHES$RsmGHA.3796@.TK2MSFTNGP05.phx.gbl...
> "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
> news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> First you should be to add some important constraints. These are my
> assumptions of course but the keys will be important here.
> ALTER TABLE dbo.prjProjects ALTER COLUMN PR_TITLE NVARCHAR(50) NOT NULL;
> ALTER TABLE dbo.prjTasks ALTER COLUMN TS_TITLE NVARCHAR(50) NOT NULL;
> ALTER TABLE dbo.prjProjects ADD CONSTRAINT AK1_prjProjects
> UNIQUE (PR_TITLE);
> ALTER TABLE dbo.prjTasks ADD CONSTRAINT AK1_prjTasks
> UNIQUE (TS_TITLE, TS_PR_ID);
> ALTER TABLE dbo.prjDetails ADD CONSTRAINT AK1_prjDetails
> UNIQUE (DT_TITLE, DT_TS_ID);
>
> Now create a proc something like this (error handling omitted for
> brevity):
> CREATE PROC dbo.usp_project_copy
> (
> @.pr_id INT,
> @.pr_title NVARCHAR(50),
> @.new_pr_id INT OUTPUT
> )
> AS
> BEGIN
> INSERT INTO dbo.prjProjects (PR_TITLE)
> VALUES (@.pr_title) ;
> SET @.new_pr_id = SCOPE_IDENTITY();
> INSERT INTO dbo.prjTasks (TS_TITLE, TS_PR_ID)
> SELECT TS_TITLE, @.new_pr_id
> FROM dbo.prjTasks
> WHERE TS_PR_ID = @.pr_id ;
> INSERT INTO dbo.prjDetails (DT_TITLE, DT_TS_ID)
> SELECT D1.DT_TITLE, T2.TS_ID
> FROM dbo.prjDetails AS D1
> JOIN dbo.prjTasks AS T1
> ON D1.DT_TS_ID = T1.TS_ID
> AND T1.TS_PR_ID = @.pr_id
> JOIN dbo.prjTasks AS T2
> ON T1.TS_TITLE = T2.TS_TITLE
> AND T2.TS_PR_ID = @.new_pr_id
> AND T1.TS_TITLE = T2.TS_TITLE ;
> RETURN
> END
> GO
> DECLARE @.pr_id INT
> EXEC dbo.usp_project_copy
> @.pr_id = 1,
> @.pr_title = 'New Project',
> @.new_pr_id = @.pr_id OUTPUT ;
> SELECT *
> FROM prjProjects
> WHERE PR_ID = @.pr_id ;
> Thanks for including the DDL. Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||As an additional piece of information.
I would have a stored procedure creates the script file. By so doing, then I
could pass in the changable pieces of data, the stored procedure could
re-create all variables after encountering a GO in the script, and the end
result is a comprehensive script file that does not require me to do a
'search and destroy' (I mean search are replace) with the chance of missing
some location where a value needed to be replaced.
I've done it with two different processes. One, just use PRINT statements
line by line, then copy the generated output into a new QA window and save
(or execute the script). Or, two, using OSQL, output the generated script to
a file. I find the first choice gives me greater control on formating,
readibility, etc.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:etEkjismGHA.4164@.TK2MSFTNGP03.phx.gbl...
> You could create a script file, very similar to the DDL/DML you offered,
> adding additional elements (as David indicated), and then execute the
> script file using OSQL (for SQL 2000) or SQLcmd (for SQL 2005)
> If you were to use any variables in the script file, remember that when a
> GO is encountered, the variable goes out of scope, so you would have to
> re-create and initialize it again.
> If you want to automate the process, the script file can be executed from
> a SQL Agent Job, or even from a stored procedure.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
> news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>

CASCADE !

I am creating two tables category_group & category.
category references category_group.
When a category group is deleted i want to delete all associated categories
also.
Is what i have below the correct way to do this?
/* Tables to manage categories; Applies to users and events*/
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_group_name VARCHAR(30))
CREATE TABLE category
(category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_name VARCHAR(30),
category_group INT NOT NULL REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
Help Appreciated!!
AJ
Hi
Defining a cascading Foreign Key (as you have) will do this for you. If you
want to know more see
the subject "Creating and Modifying FOREIGN KEY Constraints" in Books
online.
I prefer to name the keys so my DDL and example data would be like:
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) CONSTRAINT PK_Category_Group PRIMARY
KEY CLUSTERED,
category_group_name VARCHAR(30))
CREATE TABLE category
(category_id INT IDENTITY(1,1) CONSTRAINT PK_Category PRIMARY KEY
CLUSTERED,
category_name VARCHAR(30),
category_group INT NOT NULL CONSTRAINT FK_Category_Category_Group
REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 1' )
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 2' )
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 3' )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 1' , 1 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 2' , 2 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 3' , 3 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 4' , 2 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 5' , 1 )
BEGIN TRANSACTION
SELECT * FROM Category_Group
SELECT * FROM Category
DELETE FROM Category_Group WHERE category_group_id = 1
SELECT * FROM Category_Group
SELECT * FROM Category
DELETE FROM Category_Group WHERE category_group_id = 2
SELECT * FROM Category_Group
SELECT * FROM Category
ROLLBACK TRANSACTION
John
CREATE TABLE category
> (category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_name VARCHAR(30),
> category_group INT NOT NULL REFERENCES
category_group(category_group_id)
> ON DELETE CASCADE)
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:uYAHCV4oEHA.3876@.TK2MSFTNGP15.phx.gbl...
> I am creating two tables category_group & category.
> category references category_group.
> When a category group is deleted i want to delete all associated
categories
> also.
> Is what i have below the correct way to do this?
>
> /* Tables to manage categories; Applies to users and events*/
> CREATE TABLE category_group
> (category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_group_name VARCHAR(30))
> CREATE TABLE category
> (category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_name VARCHAR(30),
> category_group INT NOT NULL REFERENCES
category_group(category_group_id)
> ON DELETE CASCADE)
> Help Appreciated!!
> AJ
>
|||In addition to John's reply, don't forget to declare proper keys. You have
nullable columns and no natural key in either table:
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_group_name VARCHAR(30) NOT NULL UNIQUE)
CREATE TABLE category
(category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_name VARCHAR(30) NOT NULL UNIQUE,
category_group INT NOT NULL REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
David Portas
SQL Server MVP

CASCADE !

I am creating two tables category_group & category.
category references category_group.
When a category group is deleted i want to delete all associated categories
also.
Is what i have below the correct way to do this?
/* Tables to manage categories; Applies to users and events*/
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_group_name VARCHAR(30))
CREATE TABLE category
(category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_name VARCHAR(30),
category_group INT NOT NULL REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
Help Appreciated!!
AJHi
Defining a cascading Foreign Key (as you have) will do this for you. If you
want to know more see
the subject "Creating and Modifying FOREIGN KEY Constraints" in Books
online.
I prefer to name the keys so my DDL and example data would be like:
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) CONSTRAINT PK_Category_Group PRIMARY
KEY CLUSTERED,
category_group_name VARCHAR(30))
CREATE TABLE category
(category_id INT IDENTITY(1,1) CONSTRAINT PK_Category PRIMARY KEY
CLUSTERED,
category_name VARCHAR(30),
category_group INT NOT NULL CONSTRAINT FK_Category_Category_Group
REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 1' )
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 2' )
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 3' )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 1' , 1 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 2' , 2 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 3' , 3 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 4' , 2 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 5' , 1 )
BEGIN TRANSACTION
SELECT * FROM Category_Group
SELECT * FROM Category
DELETE FROM Category_Group WHERE category_group_id = 1
SELECT * FROM Category_Group
SELECT * FROM Category
DELETE FROM Category_Group WHERE category_group_id = 2
SELECT * FROM Category_Group
SELECT * FROM Category
ROLLBACK TRANSACTION
John
CREATE TABLE category
> (category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_name VARCHAR(30),
> category_group INT NOT NULL REFERENCES
category_group(category_group_id)
> ON DELETE CASCADE)
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:uYAHCV4oEHA.3876@.TK2MSFTNGP15.phx.gbl...
> I am creating two tables category_group & category.
> category references category_group.
> When a category group is deleted i want to delete all associated
categories
> also.
> Is what i have below the correct way to do this?
>
> /* Tables to manage categories; Applies to users and events*/
> CREATE TABLE category_group
> (category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_group_name VARCHAR(30))
> CREATE TABLE category
> (category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_name VARCHAR(30),
> category_group INT NOT NULL REFERENCES
category_group(category_group_id)
> ON DELETE CASCADE)
> Help Appreciated!!
> AJ
>|||In addition to John's reply, don't forget to declare proper keys. You have
nullable columns and no natural key in either table:
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_group_name VARCHAR(30) NOT NULL UNIQUE)
CREATE TABLE category
(category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_name VARCHAR(30) NOT NULL UNIQUE,
category_group INT NOT NULL REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
--
David Portas
SQL Server MVP
--

Carry Forward Balance

Hello All,
I'm creating a "stock ledger" using Crystal Reports 8.0. This report is printed monthly and requires that the previous month's closing balance become current month's opening balance. How do I do this?
Regards,
Rajmathihi.........

i dont know wts ur tables structure is.................but u can apply condition in ur query as

select sum(AmountField) from TableName where Month(TableName.DateField)=Month(TableName.DateField)-1

Best of Luck|||Hello "Silly Star"

Thanks for the reply, but the opening balance stock quantity and stock value are to be calculated as follows:

Every time new stock is received, the new quantity and its value gets added to the existing stock and a new rate-per-unit is calculated. suppose say I have received two consignments of a particular item on different dates, one of 100 units at 1 Re. each and the other also of 100 units, but at a cost of Rs. 1.10 each then the total stock with me is worth 210.

The same concept is applicable when stock is issued. Now when I have to issue an item I have to issue at Rs. 1.05 each.

Therefore directly considering the total of the quantity recieved and quantity issued can give me info on the quantity used, but there is huge variation in the cost (value) factor. There is fluctuation in the value and the RPU gets changed everytime new stock is received.

The report that I have designed could be used to get stock across months (say Apr - Oct). The closing balance of Apr should be taken as opening balance for May and so on.

It seems in D2K there is separate option using which this can be performed. I just wanted to know if there is a way out in Crystal Reports too

Regards,
Rajmathi|||RajMathi,
I am not sure about this.
I think you can use formula field having the code

currencyvar opbal;
whilprintingrecords;
if month{datefiled}="April" then
opbal:=opbal+{ClosingBalField};

Use other formula to check whether the month is may, if so
add opbal value to openingbalance value of May|||Hello madhi,

Well, I missed out one thing. There are several items in stock and the grouping is on items, so opening balance is separate for each item.

Regards|||RajMathi,
Ok then use other formula named @.Reset having this code

currencyvar opbal;
EhilePrintingRecords;
opbal:=0;

and place this in Group header and other formula in Dectails Section

Capturing value of identity column for use later?

This doesn't work because the first INSERT is creating multiple
records for multiple projects. @.@.IDENTITY, then, contains the Identity
column value for the last tblWeekReportedLine record inserted.

Consequently, all the hours records are then associated with
that last value.

The source work table, #EstimateLines, is a pivoted representation
with a Begin/End date and some Hours for each of six periods - a line
per project that gets pushed up to the DB by some VB code.
Definition below the sample coding.

The "@.WeekReportedID" value was successfully captured when
previous coding inserted six records into that table: one for
each date range (i.e. column in the UI screen)

Sounds like I'm approaching this wrong.

Suggestions on the right way to go about it?

-------
INSERT INTO tblWeekReportedLine
(
WeekReportedID,
RelativeLineNumber,
ProjectID
)
SELECT
@.WeekReportedID1,
#EstimateLines.RelativeLineNumber,
#EstimateLines.ProjectID
FROM#EstimateLines;

SET@.CurWeekReportedLineID = @.@.IDENTITY;

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
@.WeekReportedID1,
@.CurWeekReportedLineID,
@.BeginDate1,
Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM#EstimateLines;
--------

The #Temp table create via VB:
--------

1030 .CommandText = "CREATE TABLE #EstimateLines " & _
" ( " & _
" PersonID int, " & _
" ProjectID int, " & _
" RelativeLineNumber int, " & _
" Available1 decimal(5,2) Default 0, Estimate1
decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _
" Available2 decimal(5,2) Default 0, Estimate2
decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _
" Available3 decimal(5,2) Default 0, Estimate3
decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _
" Available4 decimal(5,2) Default 0, Estimate4
decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _
" Available5 decimal(5,2) Default 0, Estimate5
decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _
" Available6 decimal(5,2) Default 0, Estimate6
decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _
" );"
--------

--
PeteCresswell"(Pete Cresswell)" <x@.y.z> wrote in message
news:dhmtov0kgau16c4opg7ktc2d69agtieh0t@.4ax.com...
> This doesn't work because the first INSERT is creating multiple
> records for multiple projects. @.@.IDENTITY, then, contains the Identity
> column value for the last tblWeekReportedLine record inserted.
> Consequently, all the hours records are then associated with
> that last value.

<snip
Without full DDL (including keys) and knowing how you populate your
variables, this is a guess, but it may be along the right lines - you can
join onto the tblWeekReportedLine table to get the identity values:

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
w.WeekReportedID,
w.IdentityColumn,
@.BeginDate1,
e.Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM #EstimateLines e
join tblWeekReportedLine w
on e.ProjectID = w.ProjectID and
e.RelativeLineNumber = w.RelativeLineNumber
WHERE w.WeekReportedID = @.WeekReportedID1;

Simon|||RE/
>this is a guess,

Pretty good guess!

I'm still an SQL novice, and haven't learned to stop thinking sequential
processing yet...

Thanks. I may make my Monday deadline yet....

--
PeteCresswell