Creating Tables and automatically inserting data-HELP!!

[Sparrow]

I have created a table with the following columns...
Date(datetime),Actual (Int),Planned (Int)

I need to insert weekending dates starting from 23/04/04 looping
thru'for the next 52weeks automatically into the date column.

Then in the actual and planned colums, I need to insert a count of
some records in the table.

I will appreciate help on a SQL query to achieve this!

[Hugo Kornelis]

On 19 Apr 2004 11:14:00 -0700, Sparrow wrote:

I have created a table with the following columns...
Date(datetime),Actual (Int),Planned (Int)

I need to insert weekending dates starting from 23/04/04 looping
thru'for the next 52weeks automatically into the date column.

Hi Sparrow,

For inserting the dates:

DECLARE @LoopDate datetime
DECLARE @LoopCount int
SET @LoopDate = '20040423' -- yyyymmdd is allways recognised,
-- regardless of locale setting
SET @LoopCount = 0
WHILE @LoopCount <= 52
BEGIN
INSERT YourTable([Date])
VALUES (@LoopDate)
SET @LoopDate = DATEADD(day, 7, @LoopDate)
SET @LoopCount = @LoopCount + 1
END

(untested)

Then in the actual and planned colums, I need to insert a count of
some records in the table.

I will appreciate help on a SQL query to achieve this!

I can only help if you tell more precisely what you want - a count of
"some" records in the table (what table? the one you just inserted the
next 53 fridays in?) is not concise enough.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Sparro Chi]

Thanks for ur help Hugo!..

The count for the actual will be derived from the a table where I can do
a count of the actual records where the date is <= the date I have put
in the table.
And for the planned where date = the date in the table at those
intervals within a year!

I hope this makes sense.

Regards,

Sparro

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Hugo Kornelis]

On 20 Apr 2004 08:27:05 GMT, Sparro Chi wrote:

Thanks for ur help Hugo!..

The count for the actual will be derived from the a table where I can do
a count of the actual records where the date is <= the date I have put
in the table.
Hi Sparro,

This part is easy:

UPDATE YourTable
SET Actual = (SELECT COUNT(*)
FROM YourTable AS A
WHERE A.[Date] <= YourTable.[Date])

(untested)

And for the planned where date = the date in the table at those
intervals within a year!

I hope this makes sense.

Actually, it doesn't. It would help if you could post:
* the actual DDL used to declare the table (and any other tables
relevant to the question),
* illustrative examples of data in other tables relevant to the
question,
* desired output.

The preferred way to describe table structure is DDL (create table
statements), including all relevant constraints and indexes.
The preferred way to describe sample data is in the form of insert
statements.
If DDL ans inserts are posted, I'll almost allways copy and paste it
into Query Analyzer and test the queries I suggest. Without DDL and
sample data, I can only provide untested suggestions.

The preferred way to describe desired output is in a tabular format.
Make sure it's aligned properly when viewed with an unproportional
font.
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Sparro Chi]

Hugo,

This is the structure of the table:

CREATE TABLE ActualVPlanned (
AVP_ID int NULL ,
AVP_DATE datetime NULL ,
ACTUAL int NULL ,
PLANNED int NULL
)
GO

AVP_ID is an Auto increasing field,
Then,
AVP_date is a field populated with weekending dates for the next 52
weeks
ACTUAL is calculated using count (taskactual)where date <= the
corresponding row in the AVP_Date column
PLANNED is calculated using count (taskplanned)where date <= the
corresponding row in the AVP_Date column

I hope this gives a better view.

Thanks for ur help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Hugo Kornelis]

On 20 Apr 2004 13:27:13 GMT, Sparro Chi wrote:

Hugo,

This is the structure of the table:

CREATE TABLE ActualVPlanned (
AVP_ID int NULL ,
AVP_DATE datetime NULL ,
ACTUAL int NULL ,
PLANNED int NULL
)
GO

AVP_ID is an Auto increasing field,
Then,
AVP_date is a field populated with weekending dates for the next 52
weeks
ACTUAL is calculated using count (taskactual)where date <= the
corresponding row in the AVP_Date column
PLANNED is calculated using count (taskplanned)where date <= the
corresponding row in the AVP_Date column

I hope this gives a better view.

Hi Sparrow,

I still don't know what (taskactual) and (taskplanned) are, but for
now I'll assume they are columns in one or two other tables.

Step 1: Fill all required values for AVP_DATE in ActualVPlanned, using
the technique I outlined in my first reply to you.

Step 2: Calculate ACTUAL and PLANNED:

BEGIN TRANSACTION
UPDATE ActualVPlanned
SET ACTUAL = (SELECT COUNT(*)
FROM taskactual
WHERE taskactual.[date] <= ActualVPlanned.AVP_DATE),
PLANNED = (SELECT COUNT(*)
FROM taskplanned
WHERE taskplanned.[date] <= ActualVPlanned.AVP_DATE)

Step 3: Inspect results and rollback or commit as required.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)