March 2, 2017 at 10:27 pm
Hi Experts,
Need TSQL query help. I have a table 2 columns and some sample data.
Requirement is : User will give date. (say getdate() or getdate()-1 or a specific date ...)
From the input date value, need to extract the day(i.e Friday) and Time part(i.e say 10:00 AM) of PatchTime column which is nvarchar datatype
Now bussiness rule is, get/extract the starting time part of the PatchTime column value, and then add+2 hours to it
--For example : If my Input date is something like this
Input date: Mar 3 2017 10:00:00:000AM and assume that it is a Friday and time part is 10:00 AM
Expected output should be , we need exclude the next cycle patch record.
Next cycle will b, the input time (time +2 hours) of that specific day(Friday).
In our example we should exclude "SQLPatch - Friday 12:00 PM - 2:00 PM PST" record(rowid=3 record) and display remaining records in the output.
How can we do it. Need some help here. Is this do-able?...
Below is some sample data
create table test
(id int not null identity(1,1),
PatchTime nvarchar(600)
)
insert into test(PatchTime)
SELECT 'SQLPatch - Friday 10:00 AM - 12:00 PM PST'
UNION ALL
SELECT 'SQLPatch - Friday 12:00 AM - 2:00 AM PST'
UNION ALL
SELECT 'SQLPatch - Friday 12:00 PM - 2:00 PM PST'
UNION ALL
SELECT 'SQLPatch - Friday 2:00 AM - 4:00 AM PST'
UNION ALL
SELECT 'SQLPatch - Friday 2:00 PM - 4:00 PM PST'
UNION ALL
SELECT 'SQLPatch - Friday 4:00 AM - 6:00 AM PST'
UNION ALL
SELECT 'SQLPatch - Friday 4:00 PM - 6:00 PM PST'
UNION ALL
SELECT 'SQLPatch - Friday 6:00 AM - 8:00 AM PST'
UNION ALL
SELECT 'SQLPatch - Friday 6:00 PM - 8:00 PM PST'
UNION ALL
SELECT 'SQLPatch - Friday 8:00 AM - 10:00 AM PST'
UNION ALL
SELECT 'SQLPatch - Friday 8:00 PM - 10:00 PM PST'
SELECT * FROM TEST
-- queries to get the weekday and time in 12hour format
select DATENAME(weekday,getdate()) --Friday -- basically we need extract the weekday part of the given date
select convert(varchar(100),getdate(),109) as InputDate
-- Mar 3 2017 10:00:00:000AM
Input date: Mar 3 2017 10:00:00:000AM
Expected result : exclude rowid 3 and display remaining 10 rows in the output
id PatchTime
1 SQLPatch - Friday 10:00 AM - 12:00 PM PST
2 SQLPatch - Friday 12:00 AM - 2:00 AM PST
4 SQLPatch - Friday 2:00 AM - 4:00 AM PST
5 SQLPatch - Friday 2:00 PM - 4:00 PM PST
6 SQLPatch - Friday 4:00 AM - 6:00 AM PST
7 SQLPatch - Friday 4:00 PM - 6:00 PM PST
8 SQLPatch - Friday 6:00 AM - 8:00 AM PST
9 SQLPatch - Friday 6:00 PM - 8:00 PM PST
10 SQLPatch - Friday 8:00 AM - 10:00 AM PST
11 SQLPatch - Friday 8:00 PM - 10:00 PM PST
Thanks,
Sam
March 3, 2017 at 12:47 am
What did you try? As stated, it almost sounds like homework.
"PatchTime column which is nvarchar datatype" Why? Because storing it as a date with time would be too difficult? and then you could use normal functions to return the weekday? Makes perfect sense.
March 3, 2017 at 2:08 am
I would firstly get your data into something of a usable format. I've used a WITH statement, but I advise actually updating your table structure. I'm also assumed that the format always follows the set up '[Patch Type] - [Patch Day] [Start Time (h:mm PM/AM)] - [End Time (h:mm PM/AM)] [Timezone]'. If it doesn't, the below will fail:
DECLARE @CurrDateTime Datetime;
SET @CurrDateTime = '20170303 10:00:00.000';
WITH ProperFormats AS(
SELECT id,
PatchTime AS PatchDescriptor,
LEFT(PatchTime, CHARINDEX(' ', PatchTime)-1) AS PatchType,
SUBSTRING(PatchTime,CHARINDEX(' ', PatchTime)+3, CHARINDEX(' ', PatchTime,CHARINDEX(' ', PatchTime)+3) - (CHARINDEX(' ', PatchTime)+3)) As PatchDay,
CAST(LTRIM(SUBSTRING(PatchTime,CHARINDEX(' ', PatchTime,CHARINDEX(' ', PatchTime)+3) ,9)) AS time) AS PatchStartTime,
CAST(LTRIM(SUBSTRING(PatchTime, CHARINDEX('-',PatchTime,CHARINDEX('-', Patchtime)+1)+1,9)) AS time) AS PatchEndTime
FROM test)
SELECT *
FROM ProperFormats PF
WHERE PF.PatchDay = DATENAME(WEEKDAY, @CurrDateTime)
AND PF.PatchStartTime != CAST(DATEADD(HOUR, 2, @CurrDateTime) AS time);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 3, 2017 at 10:08 am
Awesome Sir! Thank you very very much. This one is quite a daunting task for me.
Good day.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply