March 14, 2017 at 5:54 am
I two tables one is master and other one is detail, its not structured yet, Please see sample data
Master Table:
Ticket_Num Date Schedule
102 03/13/2017 M-S 8:15, 11:15, 14:15, 17:10, 20:15, 23:40
Detail Table
Ticket Data_executed Count
102 03/13/2017 08:15:12 AM 10
102 03/13/2017 11:16:58 AM 0
102 03/13/2017 02:18:44 PM 1
102 03/13/2017 05:10:24 PM 2
102 03/13/2017 08:17:24 PM 0
102 03/13/2017 11:44:37 PM 0
As u can see ticket 102 has to execute every day 6times only on Monday- Saturday, Can you please help me out best way to create date and time, fact tables, End goal is track job whether it is executed or not. There are tickets should execute Monthly as well. Thanks for help!
March 14, 2017 at 6:03 am
This is untested, as you didn't provide DDL and consumable data, but:SELECT *
FROM #Master M
CROSS APPLY dbo.DelimitedSplit8K(M.Schedule, ',') DS
LEFT JOIN #Detail D ON M.Ticket_num = D.Ticket_num
AND M.[Date] = cast(D.Data_executed AS date)
AND CAST(D.data_executed AS time) BETWEEN DS.item AND DATEADD(minute, 5, DS.item);
I've guessed that you've used a delimited string (you should really change that), for the scehdule, so made use of the DelimitedSplit8K function, which you can find on this website.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 14, 2017 at 6:12 am
Yes, I have to change schedule, Could you please let me know what is the best way to store data in table. Im planning to use SSAS cube in future.thanks!
March 14, 2017 at 6:56 am
koti.raavi - Tuesday, March 14, 2017 6:12 AMCould you please let me know what is the best way to store data in table.
That is a very open ended question. Could you be more specific?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 14, 2017 at 9:00 am
yea sure, i have diffenet tickets running on differnt schedule for example one can run monday to friday 3 times a day and other can run on saturday to sunday 1time in a day. currently we dont have data in tables, we want to store the data in normalized format so that it will help us to identity to check which process is not executed or delayed..i have provided sample data above pls use that for reference.. if u have any other questions pls do let me know..thank you for your help..Much appreciated.
March 14, 2017 at 9:33 am
koti.raavi - Tuesday, March 14, 2017 9:00 AMyea sure, i have diffenet tickets running on differnt schedule for example one can run monday to friday 3 times a day and other can run on saturday to sunday 1time in a day. currently we dont have data in tables, we want to store the data in normalized format so that it will help us to identity to check which process is not executed or delayed..i have provided sample data above pls use that for reference.. if u have any other questions pls do let me know..thank you for your help..Much appreciated.
Are you asking how to store the above data in a normalised form? Can you provide DDL and consumable data then please.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 14, 2017 at 10:29 am
Hi, Please find the DDL, let me know if any questions, thank you.
create table dbo.Master_Data
(
Ticket_Num Bigint,
Title varchar(100),
Schedue_time varchar(500)
)
Insert into dbo.Master_Data (Ticket_Num,Title,Schedule) Values (1010,'Exe1',
'Mon-Sun 08:15, 11:15, 14:15, 17:10, 20:15, 23:40')
Insert into dbo.Master_Data (Ticket_Num,Title,Schedule) Values (1011,'Exe2',
'Mon-Tue 09:00, 11:00 Sat-Sun 14:00, 20:50')
Insert into dbo.Master_Data (Ticket_Num,Title,Schedule) Values (1012,'Exe3',
'Mon-Thu 08:00 Sun 14:00')
Create table dbo.Detail_Data
(
Ticket_Num Bigint,
Executed_Date varchar(100),
Count Int
)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'03/13/2017 08:15:12 AM',10)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'03/13/2017 11:16:58 AM',10)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'03/13/2017 02:18:44 PM',20)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'03/13/2017 05:10:24 PM',40)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'03/13/2017 08:17:24 PM',50)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'03/13/2017 11:44:37 PM',60)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1011,'03/13/2017 09:10:12 AM',10)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1011,'03/13/2017 11:10:58 AM',30)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1012,'03/13/2017 08:12 AM',10)
March 14, 2017 at 10:41 am
Did you test that code? It doesn't run:
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Schedule'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'Schedule'.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'Schedule'.
Also, you should supply your dates in a format that isn't reliant on language. 03/13/2017, for mean, means the 13th month. I would have been happy to change that bit, but as your SQL isn't correct anyway, I think it's important for you to fix it as well. Try using the format 'yyyyMMdd'.
Yes, I know how to fix the above error, but it's crucial you supply correct and working SQL, as otherwise how can I, or other users, trust it's correct.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 14, 2017 at 10:48 am
Sorry, I left system in office and prepared code from mobile 03 is month and 13th is date, so 13th march 2017
March 14, 2017 at 10:57 am
I have modified, please check it once,sorry can't test
create table dbo.Master_Data
(
Ticket_Num Bigint,
Title varchar(100),
Schedue_time varchar(500)
)
Insert into dbo.Master_Data (Ticket_Num,Title,Schedue_time ) Values (1010,'Exe1',
'Mon-Sun 08:15, 11:15, 14:15, 17:10, 20:15, 23:40')
Insert into dbo.Master_Data (Ticket_Num,Title,Schedue_time ) Values (1011,'Exe2',
'Mon-Tue 09:00, 11:00 Sat-Sun 14:00, 20:50')
Insert into dbo.Master_Data (Ticket_Num,Title,Schedue_time ) Values (1012,'Exe3',
'Mon-Thu 08:00 Sun 14:00')
Create table dbo.Detail_Data
(
Ticket_Num Bigint,
Executed_Date varchar(100),
Count Int
)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'20170313 08:15:12 AM',10)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'20170313 11:16:58 AM',10)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'20170313 02:18:44 PM',20)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'20170313 05:10:24 PM',40)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'20170313 08:17:24 PM',50)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1010,'20170313 11:44:37 PM',60)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1011,'20170313 09:10:12 AM',10)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1011,'20170313 11:10:58 AM',30)
Insert into dbo.Detail_Data (Ticket_Num,Executed_date,Count)
values(1012,'20170313 08:12 AM',10)
March 14, 2017 at 12:47 pm
Just looking but I'm not sure what you are expecting based on the sample data and DDL you provided.
March 15, 2017 at 3:45 am
Ok, that took me far longer than I'd wanted, there might be a better way, but it's how i got to where I wanted, so if others want to add anything, please do.
The below, however, normalises that awful data you have:WITH Weekdays AS (
SELECT *
FROM (VALUES (1, 'Mon'),
(2, 'Tue'),
(3, 'Wed'),
(4, 'Thu'),
(5, 'Fri'),
(6, 'Sat'),
(7, 'Sun'))
AS w (WeekDayNum, WeekDayName)),
Tidy AS (
SELECT MD.*,
1 AS DayNumber,
REPLACE(REPLACE(MD.Schedue_time,',',''),' ' + WD.WeekDayName,', ' + WD.WeekDayName) AS Schedule_time
FROM Master_Data MD
--CROSS APPLY dbo.DelimitedSplit8K (MD.Schedue_time, ',') DS
JOIN WeekDays WD ON WD.WeekDayNum = 1
UNION ALL
SELECT T.Ticket_Num,
T.Title,
T.Schedue_time,
T.DayNumber + 1,
REPLACE(T.Schedule_time,' ' + WD.WeekDayName,', ' + WD.WeekDayName) AS Schedule_time
FROM Tidy T
JOIN WeekDays WD ON T.DayNumber + 1 = WD.WeekDayNum
),
Split AS
(SELECT T.Ticket_Num,
T.Title,
T.Schedule_time,
LTRIM(DS.Item) AS Item,
DS.ItemNumber,
W.WeekDayNum
FROM Tidy T
CROSS APPLY dbo.DelimitedSplit8K (T.Schedule_time, ',') DS
LEFT JOIN Weekdays W ON PATINDEX('%' + W.WeekDayName + '%', DS.Item) > 0
WHERE T.DayNumber = (SELECT MAX(sq.DayNumber) FROM Tidy sq WHERE T.Schedue_time = sq.Schedue_time)),
RemoveDays AS (
SELECT S.Ticket_Num, S.Title,
S.Item,
REPLACE(REPLACE(S.Item,'-',''), WD.WeekdayName, '') AS Times,
S.ItemNumber,
S.WeekDayNum,
1 AS WeekdayLoop,
MIN(S.WeekDayNum) OVER (PARTITION BY S.Item) AS StartDay,
MAX(S.WeekDayNum) OVER (PARTITION BY S.Item) AS EndDay
FROM Split S
JOIN WeekDays WD ON WD.WeekDayNum = 1
UNION ALL
SELECT RD.Ticket_Num, RD.Title,
RD.Item,
REPLACE(REPLACE(RD.Times,'-',''), WD.WeekdayName, '') AS Times,
RD.ItemNumber,
RD.WeekdayNum,
WD.WeekDayNum As WeekdayLoop,
RD.StartDay,
RD.EndDay
FROM RemoveDays RD
JOIN Weekdays WD ON RD.WeekdayLoop + 1 = WD.WeekDayNum)
SELECT DISTINCT
RD.Ticket_Num, RD.Title,
WD.WeekDayNum AS ScheduleDayNum,
WD.WeekDayName AS ScheduleDayName,
CAST(DS.Item AS time) AS ScheduleTime
FROM RemoveDays RD
CROSS APPLY DelimitedSplit8K(ltrim(rtrim(RD.Times)), ' ') DS
JOIN Weekdays WD ON WD.WeekDayNum BETWEEN RD.StartDay AND RD.EndDay
WHERE RD.WeekdayLoop = (SELECT MAX(sq.WeekdayLoop) FROM RemoveDays sq WHERE sq.Item = RD.Item)
ORDER BY RD.Ticket_Num,
RD.Title,
WD.WeekDayNum,
ScheduleTime;
Still annoying that my formatting is lost between pasting from SSMS/Notepad and Firefox... 🙁
You should more than easily be able to achieve what you want now on your detail table. I would strongly suggest update your schedule data to something better and usable.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 15, 2017 at 7:52 am
Code looks awesome, Thank you so much for your help! Much appreciated. I'm have started validation so far looks good, I will let you know if any questions! thanks for your time, great work!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply