Need help using Case Statement

Transact-SQL
#1

I've table and data as following
/*
CREATE TABLE [dbo].[DataTable](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[SCHL_SDTE] nvarchar NULL,
[LOAN_SDTE] nvarchar NULL,
CONSTRAINT [PK_DataTable] PRIMARY KEY CLUSTERED
(
[UniqueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[DataTable] ON
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (1, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (2, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (3, N'198904', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (4, N'198801', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (5, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (6, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (7, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (8, N'0', N'197905')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (9, N'0', N'198006')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (10, N'198401', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (11, N'0', N'199408')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (12, N'198508', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (13, N'0', N'198410')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (14, N'0', N'198405')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (15, N'0', N'198801')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (16, N'0', N'198610')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (17, N'0', N'198607')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (18, N'0', N'198607')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (19, N'0', N'198607')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (20, N'0', N'198707')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (21, N'0', N'198807')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (22, N'198909', N'199201')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (23, N'0', N'198907')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (24, N'0', N'198907')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (25, N'0', N'198907')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (26, N'0', N'198907')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (27, N'0', N'199007')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (28, N'0', N'199007')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (29, N'0', N'199007')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (30, N'0', N'199110')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (31, N'0', N'199107')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (32, N'0', N'199107')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (33, N'0', N'199107')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (34, N'0', N'199207')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (35, N'0', N'199207')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (36, N'0', N'199207')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (37, N'0', N'199212')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (38, N'199407', N'199508')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (39, N'199307', N'199507')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (40, N'0', N'199401')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (41, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (42, N'0', N'199307')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (43, N'0', N'199410')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (44, N'0', N'199510')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (45, N'0', N'199407')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (46, N'0', N'199407')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (47, N'0', N'199407')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (48, N'199507', N'199710')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (49, N'199507', N'199706')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (50, N'0', N'199412')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (51, N'0', N'199507')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (52, N'0', N'199607')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (53, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (54, N'0', N'199607')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (55, N'0', N'199801')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (56, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (57, N'0', N'199906')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (58, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (59, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (60, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (61, N'197609', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (62, N'197709', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (63, N'198001', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (64, N'198108', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (65, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (66, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (67, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (68, N'0', N'199706')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (69, N'0', N'199706')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (70, N'0', N'200106')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (71, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (72, N'0', N'200007')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (73, N'0', N'199906')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (74, N'199806', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (75, N'199806', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (76, N'199806', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (77, N'199806', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (78, N'199806', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (79, N'199811', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (80, N'0', N'199901')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (81, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (82, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (83, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (84, N'0', N'199901')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (85, N'0', N'199812')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (86, N'0', N'199811')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (87, N'199906', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (88, N'0', N'199906')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (89, N'199906', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (90, N'0', N'199906')
GO
SET IDENTITY_INSERT [dbo].[DataTable] OFF
GO

*/

/*
CREATE TABLE [dbo].[simulateTable](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[SCHL_SDTE] [date] NULL,
[LOAN_SDTE] [date] NULL,
CONSTRAINT [PK_simulateTable] PRIMARY KEY CLUSTERED
(
[UniqueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

*/

I need to Insert DataTable into simulateTable

If DataTable(SCHL_SDTE)=0 and DataTable(LOAN_SDTE)=0, please insert NULL

If DataTable(SCHL_SDTE) =0, please choose DataTable(LOAN_SDTE) to insert. If DataTable(LOAN_SDTE)=0, please insert NULL

If DataTable(LOAN_SDTE) =0, please choose DataTable(SCHL_SDTE) to insert. If DataTable(SCHL_SDTE)=0, please insert NULL

Please help

#2

hi

i am trying to do this !!!

this is very easy to do !!!

I am not clear on what you want

Can you please explain in a better way !!!
:slight_smile:

I fixed your script ... there were some errors

Please click arrow to the left for Drop Create Data 
drop table [dbo].[DataTable]
go 

CREATE TABLE [dbo].[DataTable](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[SCHL_SDTE] nvarchar(100) NULL,
[LOAN_SDTE] nvarchar(100) NULL,
CONSTRAINT [PK_DataTable] PRIMARY KEY CLUSTERED
(
[UniqueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[DataTable] ON
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (1, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (2, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (3, N'198904', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (4, N'198801', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (5, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (6, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (7, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (8, N'0', N'197905')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (9, N'0', N'198006')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (10, N'198401', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (11, N'0', N'199408')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (12, N'198508', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (13, N'0', N'198410')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (14, N'0', N'198405')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (15, N'0', N'198801')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (16, N'0', N'198610')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (17, N'0', N'198607')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (18, N'0', N'198607')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (19, N'0', N'198607')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (20, N'0', N'198707')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (21, N'0', N'198807')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (22, N'198909', N'199201')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (23, N'0', N'198907')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (24, N'0', N'198907')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (25, N'0', N'198907')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (26, N'0', N'198907')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (27, N'0', N'199007')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (28, N'0', N'199007')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (29, N'0', N'199007')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (30, N'0', N'199110')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (31, N'0', N'199107')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (32, N'0', N'199107')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (33, N'0', N'199107')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (34, N'0', N'199207')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (35, N'0', N'199207')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (36, N'0', N'199207')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (37, N'0', N'199212')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (38, N'199407', N'199508')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (39, N'199307', N'199507')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (40, N'0', N'199401')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (41, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (42, N'0', N'199307')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (43, N'0', N'199410')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (44, N'0', N'199510')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (45, N'0', N'199407')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (46, N'0', N'199407')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (47, N'0', N'199407')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (48, N'199507', N'199710')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (49, N'199507', N'199706')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (50, N'0', N'199412')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (51, N'0', N'199507')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (52, N'0', N'199607')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (53, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (54, N'0', N'199607')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (55, N'0', N'199801')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (56, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (57, N'0', N'199906')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (58, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (59, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (60, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (61, N'197609', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (62, N'197709', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (63, N'198001', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (64, N'198108', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (65, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (66, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (67, N'0', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (68, N'0', N'199706')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (69, N'0', N'199706')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (70, N'0', N'200106')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (71, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (72, N'0', N'200007')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (73, N'0', N'199906')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (74, N'199806', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (75, N'199806', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (76, N'199806', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (77, N'199806', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (78, N'199806', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (79, N'199811', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (80, N'0', N'199901')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (81, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (82, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (83, N'0', N'199806')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (84, N'0', N'199901')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (85, N'0', N'199812')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (86, N'0', N'199811')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (87, N'199906', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (88, N'0', N'199906')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (89, N'199906', N'0')
GO
INSERT [dbo].[DataTable] ([UniqueId], [SCHL_SDTE], [LOAN_SDTE]) VALUES (90, N'0', N'199906')
GO
SET IDENTITY_INSERT [dbo].[DataTable] OFF
GO

select * from  [dbo].[DataTable]

drop table [dbo].[simulateTable]
go 

CREATE TABLE [dbo].[simulateTable](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[SCHL_SDTE] [date] NULL,
[LOAN_SDTE] [date] NULL,
CONSTRAINT [PK_simulateTable] PRIMARY KEY CLUSTERED
(
[UniqueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
#3

I think my [dbo].[simulateTable] is a mistake. Here the correction

/*
CREATE TABLE [dbo].[simulateTable](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[cleanDate] [date] NULL
CONSTRAINT [PK_simulateTable] PRIMARY KEY CLUSTERED
(
[UniqueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

*/

I need to Insert DataTable into simulateTable

(1) If DataTable(SCHL_SDTE)=0 and DataTable(LOAN_SDTE)=0, please insert [cleanDate] = NULL

(2) If DataTable(SCHL_SDTE) =0 and DataTable(LOAN_SDTE)!=0, please insert [cleanDate] = LOAN_SDTE

(3) If DataTable(LOAN_SDTE) =0, and DataTable(SCHL_SDTE)!=0, please insert [cleanDate] = SCHL_SDTE

(4) Else '20010101'

One more thing, how to insert my script properly using this Forum?

#4

hi

here is the insert script .....Its giving errors

simulateTable column cleandate is DATE datatype ...

the data from main table is nvarchar
the data is like this ... 199906 this is only year and month
but cleandate DATE will expect day also

like this yyyymmdd where yyyy is year mm is month dd is day
example .. 20011019

what to do ???? put 01 as day for all data

Please click arrow to the left for Insert Script 
INSERT INTO [dbo].[simulateTable]
select 
CASE WHEN SCHL_SDTE= 0 and LOAN_SDTE = 0 then NULL
     WHEN SCHL_SDTE= 0 and LOAN_SDTE <> 0 then LOAN_SDTE
     WHEN SCHL_SDTE <> 0 and LOAN_SDTE = 0 then SCHL_SDTE 
     ELSE '20010101'
END as   [cleanDate]   
 FRoM [dbo].[DataTable]

If you click on this .. you can insert script properly

image
image1441×731 45.2 KB

#5

Yes sir. put 01 as day for all handicap data

#6

Here is your insert script

INSERT INTO [dbo].[simulateTable]
select 
CAST(
CASE WHEN SCHL_SDTE= 0 and LOAN_SDTE = 0 then NULL
     WHEN SCHL_SDTE= 0 and LOAN_SDTE <> 0 then LOAN_SDTE+'01'
     WHEN SCHL_SDTE <> 0 and LOAN_SDTE = 0 then SCHL_SDTE+'01' 
     ELSE '20010101'
END 
 as DATE) as   [cleanDate]   
 FRoM [dbo].[DataTable]

Data looks this this

image
image562×992 7.85 KB

1 Like
#7

Thank you Mister