September 26, 2008 at 7:29 am
Hi,
I picked up this script on the web which does part of what i need. I need some help to be able to achieve the other part.
DECLARE @LowerBoundDate DATETIME
DECLARE @UpperBoundDate DATETIME
--set the initial dates
SET @LowerBoundDate = '1/1/1980';
SET @UpperBoundDate = '1/1/2040';
--create our temporary table
DECLARE @TempDateDimension TABLE
(
Date DATETIME
, [Year] INT
, [Month] INT
, [Day] INT
);
--use CTE to create our date iterator
WITH CalculatedDate AS
(
SELECT
@LowerBoundDate Date
UNION ALL
SELECT
(Date + 1)
FROM
CalculatedDate
WHERE
((Date + 1) <= @UpperBoundDate)
)
--insert each date to our temporay dimension table
INSERT INTO @TempDateDimension
SELECT
Date
, Year(Date) AS [Year]
, Month(Date) AS [Month]
, Day(Date) AS Day
FROM
CalculatedDate
OPTION (MAXRECURSION 0)
--Insert the dates to our permanent Dimension table
INSERT INTO dbo.DateDimension
SELECT
Date
, [Year]
, [Month]
, [Day]
FROM
@TempDateDimension
--View the contents of the table
SELECT
Date
, [Year]
, [Month]
, [Day]
FROM
DateDimension WITH(NOLOCK)
I want to extend the code so that i can add another column called MachineID. This will be gotten from a table with 20 rows.
Basically i want to add the column but get the data from another table.
For every machineid present repeat the process until completed.
Any help would be apprecited
September 26, 2008 at 8:55 am
Hi,
When you say you want to add machineID is that so there is a row for each day/machine combination?
If so, you should be able to do something like:
--create our temporary table
DECLARE @TempDateDimension TABLE
(
Date DATETIME
, [Year] INT
, [Month] INT
, [Day] INT
, [MachineName] VARCHAR(20)
);
INSERT INTO dbo.DateDimension
SELECT
Date
, [Year]
, [Month]
, [Day]
, [MachineName]
FROM
@TempDateDimension
CROSS JOIN myMachineNameTable
September 26, 2008 at 3:01 pm
Adrian Nichols (9/26/2008)
Hi,When you say you want to add machineID is that so there is a row for each day/machine combination?
If so, you should be able to do something like:
--create our temporary table
DECLARE @TempDateDimension TABLE
(
Date DATETIME
, [Year] INT
, [Month] INT
, [Day] INT
, [MachineName] VARCHAR(20)
);
INSERT INTO dbo.DateDimension
SELECT
Date
, [Year]
, [Month]
, [Day]
, [MachineName]
FROM
@TempDateDimension
CROSS JOIN myMachineNameTable
Thanks for the changes... Yes that's exactly what i'm trying to create. But i get an error saying
Insert Error: Column name or number of supplied values does not match table definition.
I set it to machineID but the error came up. The tblMachines has 22 rows data.
Any ideas appreciate the help very much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply