March 8, 2017 at 2:06 am
Hi All,
Please i have a Tsql statement that i want to use to add extra rows to a set of rows based on supplied integer.
What i want to achieve is this:
CREATE TABLE [dbo].[SampleTable2](
[AdDate] [datetime] NULL,
[AdTime] [nvarchar](8) NULL,
[Stations] [nvarchar](30) NULL,
[Brand] [nvarchar](50) NULL,
[Identifier] [nvarchar](75) NULL,
[RN] [bigint] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70B00000000 AS DateTime), N'09:18:19', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 1)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70B00000000 AS DateTime), N'09:32:34', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 2)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70B00000000 AS DateTime), N'09:47:44', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 3)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70B00000000 AS DateTime), NULL, NULL, NULL, NULL, 4)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70B00000000 AS DateTime), NULL, NULL, NULL, NULL, 5)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70C00000000 AS DateTime), N'09:17:28', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 1)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70C00000000 AS DateTime), N'09:31:56', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 2)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70C00000000 AS DateTime), N'09:46:39', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 3)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70C00000000 AS DateTime), NULL, NULL, NULL, NULL, 4)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70C00000000 AS DateTime), NULL, NULL, NULL, NULL, 5)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70500000000 AS DateTime), NULL, NULL, NULL, NULL, 1)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70500000000 AS DateTime), NULL, NULL, NULL, NULL, 2)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70500000000 AS DateTime), NULL, NULL, NULL, NULL, 3)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70500000000 AS DateTime), NULL, NULL, NULL, NULL, 4)
INSERT [dbo].[SampleTable2] ([AdDate], [AdTime], [Stations], [Brand], [Identifier], [RN]) VALUES (CAST(0x0000A70500000000 AS DateTime), NULL, NULL, NULL, NULL, 5)
From this table:
CREATE TABLE [dbo].[SampleTable](
[AdDate] [datetime] NULL,
[AdTime] [nvarchar](8) NULL,
[Stations] [nvarchar](30) NULL,
[Brand] [nvarchar](50) NULL,
[Identifier] [nvarchar](75) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[SampleTable] ([AdDate], [AdTime], [Stations], [Brand], [Identifier]) VALUES (CAST(0x0000A70600000000 AS DateTime), N'09:28:19', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]')
INSERT [dbo].[SampleTable] ([AdDate], [AdTime], [Stations], [Brand], [Identifier]) VALUES (CAST(0x0000A70600000000 AS DateTime), N'09:32:19', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]')
INSERT [dbo].[SampleTable] ([AdDate], [AdTime], [Stations], [Brand], [Identifier]) VALUES (CAST(0x0000A70600000000 AS DateTime), N'09:47:19', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]')
INSERT [dbo].[SampleTable] ([AdDate], [AdTime], [Stations], [Brand], [Identifier]) VALUES (CAST(0x0000A70B00000000 AS DateTime), N'09:18:19', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]')
INSERT [dbo].[SampleTable] ([AdDate], [AdTime], [Stations], [Brand], [Identifier]) VALUES (CAST(0x0000A70B00000000 AS DateTime), N'09:32:34', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]')
INSERT [dbo].[SampleTable] ([AdDate], [AdTime], [Stations], [Brand], [Identifier]) VALUES (CAST(0x0000A70B00000000 AS DateTime), N'09:47:44', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]')
INSERT [dbo].[SampleTable] ([AdDate], [AdTime], [Stations], [Brand], [Identifier]) VALUES (CAST(0x0000A70C00000000 AS DateTime), N'09:17:28', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]')
INSERT [dbo].[SampleTable] ([AdDate], [AdTime], [Stations], [Brand], [Identifier]) VALUES (CAST(0x0000A70C00000000 AS DateTime), N'09:31:56', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]')
INSERT [dbo].[SampleTable] ([AdDate], [AdTime], [Stations], [Brand], [Identifier]) VALUES (CAST(0x0000A70C00000000 AS DateTime), N'09:46:39', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]')
This is my code:
Declare
@Scheduled int
set @Scheduled = 5;
WITH CTE AS
(
Select AdDate, AdTime, Stations,Brand,Identifier, Isnull(RN,0) as RN From
(SELECT AdDate
,AdTime
,Stations, Identifier
,Brand
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.SampleTable
WHERE AdDate in ('24 jan 2017', '30 jan 2017', '31 jan 2017')
) as d
Union All
Select AdDate,AdTime, Stations,Brand,Identifier, RN + 1 as RN from CTE
Where AdTime is null And RN < @Scheduled
--union All
--Select AdDate,AdTime, Stations,Brand,Identifier, 1 as RN from CTE
--Where AdTime is not null And RN < @Scheduled
)
Select AdDate, AdTime, Stations,Brand,Identifier, RN From CTE
--where not(RN = 0)
Thanks,
Please help
Tim
March 8, 2017 at 8:09 am
Here's a query that will do what you need. Recursion isn't really going to help you for this, as you're not dealing with a hierarchy or similar structure:-- ORIGINAL TABLE DATA
CREATE TABLE dbo.SampleTable(
AdDate datetime NULL,
AdTime nvarchar(8) NULL,
Stations nvarchar(30) NULL,
Brand nvarchar(50) NULL,
Identifier nvarchar(75) NULL
);
INSERT INTO dbo.SampleTable (AdDate, AdTime, Stations, Brand, Identifier)
SELECT AdDate, AdTime, Stations, Brand, Identifier
FROM (
VALUES (CAST(0x0000A70600000000 AS DateTime), N'09:28:19', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)T45E'),
(CAST(0x0000A70600000000 AS DateTime), N'09:32:19', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)T45E'),
(CAST(0x0000A70600000000 AS DateTime), N'09:47:19', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)T45E'),
(CAST(0x0000A70B00000000 AS DateTime), N'09:18:19', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)T45E'),
(CAST(0x0000A70B00000000 AS DateTime), N'09:32:34', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)T45E'),
(CAST(0x0000A70B00000000 AS DateTime), N'09:47:44', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)T45E'),
(CAST(0x0000A70C00000000 AS DateTime), N'09:17:28', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)T45E'),
(CAST(0x0000A70C00000000 AS DateTime), N'09:31:56', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)T45E'),
(CAST(0x0000A70C00000000 AS DateTime), N'09:46:39', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)T45E')
) AS X (AdDate, AdTime, Stations, Brand, Identifier);
SELECT *
FROM dbo.SampleTable;
--=============================================================================================
-- HERE'S THE QUERY:
-- We start with an additional variable to have the relevant dates appear in table form.
-- Then we use a CTE named E1 to represent 10 rows. Another CTE then uses the supplied
-- integer value to get the row numbers we'll need for the output. Another CTE gets us
-- the combination of all the row numbers with all the supplied dates, as a framework.
-- The final CTE assigns row numbers to the existing data, and the final SELECT just uses
-- a simple LEFT OUTER JOIN to marry the two together.
--=============================================================================================
DECLARE @Scheduled int = 5;
DECLARE @DATE_TABLE AS TABLE (
THE_DATE datetime
);
INSERT INTO @DATE_TABLE (THE_DATE)
SELECT THE_DATE
FROM (
VALUES ('24 jan 2017'),
('30 jan 2017'),
('31 jan 2017')
) AS X (THE_DATE);
WITH E1 AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
NUMBERS AS (
SELECT TOP (@Scheduled) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RN
FROM E1 AS A, E1 AS B
),
NUMS_DATES AS (
SELECT DT.THE_DATE, N.RN
FROM @DATE_TABLE AS DT
CROSS APPLY NUMBERS AS N
),
CTE AS (
SELECT ST.AdDate, ST.AdTime, ST.Stations, ST.Brand, ST.Identifier,
ROW_NUMBER() OVER(PARTITION BY ST.AdDate ORDER BY ST.AdTime) AS RN
FROM dbo.SampleTable AS ST
)
SELECT N.THE_DATE AS AdDate, C.AdTime, C.Stations, C.Brand, C.Identifier, N.RN
FROM NUMS_DATES AS N
LEFT OUTER JOIN CTE AS C
ON N.RN = C.RN
AND N.THE_DATE = C.AdDate
ORDER BY N.THE_DATE, N.RN;
DROP TABLE dbo.SampleTable;
--=============================================================================================
-- THIS QUERY JUST DISPLAYS THE DESIRED RESULTS, WHICH WERE REPRODUCED ABOVE.
--=============================================================================================
SELECT AdDate, AdTime, Stations, Brand, Identifier, RN
FROM (
VALUES (CAST(0x0000A70B00000000 AS DateTime), N'09:18:19', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 1),
(CAST(0x0000A70B00000000 AS DateTime), N'09:32:34', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 2),
(CAST(0x0000A70B00000000 AS DateTime), N'09:47:44', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 3),
(CAST(0x0000A70B00000000 AS DateTime), NULL, NULL, NULL, NULL, 4),
(CAST(0x0000A70B00000000 AS DateTime), NULL, NULL, NULL, NULL, 5),
(CAST(0x0000A70C00000000 AS DateTime), N'09:17:28', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 1),
(CAST(0x0000A70C00000000 AS DateTime), N'09:31:56', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 2),
(CAST(0x0000A70C00000000 AS DateTime), N'09:46:39', N'TV CONTINENTAL', N'MILO', N'MILO WHERE CHAMPIONS ARE MADE (DROGBA)[T45E]', 3),
(CAST(0x0000A70C00000000 AS DateTime), NULL, NULL, NULL, NULL, 4),
(CAST(0x0000A70C00000000 AS DateTime), NULL, NULL, NULL, NULL, 5),
(CAST(0x0000A70500000000 AS DateTime), NULL, NULL, NULL, NULL, 1),
(CAST(0x0000A70500000000 AS DateTime), NULL, NULL, NULL, NULL, 2),
(CAST(0x0000A70500000000 AS DateTime), NULL, NULL, NULL, NULL, 3),
(CAST(0x0000A70500000000 AS DateTime), NULL, NULL, NULL, NULL, 4),
(CAST(0x0000A70500000000 AS DateTime), NULL, NULL, NULL, NULL, 5)
) AS X (AdDate, AdTime, Stations, Brand, Identifier, RN)
ORDER BY AdDate, RN;
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
March 8, 2017 at 8:55 am
Hi Steve,
Thanks so much, you have saved me two days of babbling about.
Thanks so much again, it works very fine.
God bless you.
Tim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply