August 22, 2011 at 9:57 am
Hi
I'm trying to add a column called field date but i cant seem to get it to work . Its meant to add a date to the the field date column by abstracting the month from the capture date column.
Can anyone tell me the reason why??
Select 'Fcst' as DataType
,CaptureDate
,Item
,ShipTo
,DateAdd(m,-1, Cast(Right(CaptureDate,4) + '/' +Left(CaptureDate,2) +'/01' as Date )AS FieldDate - NOT WORKING
INTO VerticalTable
FROM HorizontalTable
-- Create the table
CREATE TABLE [dbo].[January](
[Item] [varchar](32) NOT NULL,
[Company] [varchar](3) NOT NULL,
[Division] [varchar](8) NOT NULL,
[Corporation] [varchar](12) NOT NULL,
[SoldTo] [varchar](8) NOT NULL,
[Department] [varchar](3) NOT NULL,
[ShipTo] [varchar](12) NOT NULL,
[Class1] [varchar](20) NOT NULL,
[Class2] [varchar](20) NOT NULL,
[Class3] [varchar](20) NOT NULL,
[Class4] [varchar](20) NOT NULL,
[SysFcst#1] [int] NULL,
[SysFcst#2] [int] NULL,
[SysFcst#3] [int] NULL,
[SysFcst#4] [int] NULL,
[SysFcst#5] [int] NULL,
[SysFcst#6] [int] NULL,
[SysFcst#7] [int] NULL,
[SysFcst#8] [int] NULL,
[SysFcst#9] [int] NULL,
[SysFcst#10] [int] NULL,
[SysFcst#11] [int] NULL,
[SysFcst#12] [int] NULL,
[AdjFcst#1] [int] NULL,
[AdjFcst#2] [int] NULL,
[AdjFcst#3] [int] NULL,
[AdjFcst#4] [int] NULL,
[AdjFcst#5] [int] NULL,
[AdjFcst#6] [int] NULL,
[AdjFcst#7] [int] NULL,
[AdjFcst#8] [int] NULL,
[AdjFcst#9] [int] NULL,
[AdjFcst#10] [int] NULL,
[AdjFcst#11] [int] NULL,
[AdjFcst#12] [int] NULL
)ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-- Inserting data into the table
BULK INSERT Test.dbo.January FROM 'C:\KeyFields5.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
I've attached a file called Keyfields5
-- Complete script
Part 1
select Item as SysDate
into SystemDateTable
from January
where item = '01 2011'
-- Part 2
select SysDate as CaptureDate,item,Company,Division,Corporation,SoldTo,Department,Class1,ShipTo,Class2,Class3,Class4,SysFcst#1,SysFcst#2
,SysFcst#3,SysFcst#4,SysFcst#5,SysFcst#6,SysFcst#7,SysFcst#8,SysFcst#9,SysFcst#10,SysFcst#11
,SysFcst#12,AdjFcst#1 as Fcst01,AdjFcst#2 as Fcst02,AdjFcst#3 as Fcst03,AdjFcst#4 as Fcst04,AdjFcst#5 as Fcst05,AdjFcst#6 as Fcst06,AdjFcst#7 as Fcst07,AdjFcst#8 as Fcst08
,AdjFcst#9 as Fcst09,AdjFcst#10 as Fcst10,AdjFcst#11 as Fcst11,AdjFcst#12 as Fcst12
into HorizontalTable
from January Cross Join SystemDateTable
--- Part 3 - Problem with this script
Select 'Fcst' as DataType
,CaptureDate
,Item
,ShipTo
,DateAdd(m,-1, Cast(Right(CaptureDate,4) + '/' +Left(CaptureDate,2) +'/01' as Date )AS FieldDate
INTO VerticalTable
FROM HorizontalTable
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
August 22, 2011 at 10:02 am
Thanks for the very detailed post. It's really helpful to helps us help you.
Now the only missing part is 1-2 sample date + required output.
Right now I don't understand what you need.
August 22, 2011 at 12:07 pm
Hi
does this help?
SELECT
DATEADD (MONTH, -1, (CAST ( Right(CaptureDate,4) + '/' + Left(CaptureDate,2) + '/01' as DATE))) AS FieldDate
FROM dbo.HorizontalTable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 22, 2011 at 4:11 pm
@j-2 Livingston SQL many thanks for the script that is exactly what i needed.
@ Ninja's_RGR'us want I wanted is to subtract 1 month from the capture date which is placed in the field date.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply