sql days of week plus time formula

Hello all hope you can help

i have data as
invoice.datetime_from       invoive.datetime_to
2014-12-06 20:00:00.000      2014-12-07 07:59:00.000
2015-01-13 10:15:00.000      2015-01-13 19:59:00.000
2015-01-14 20:00:00.000      2015-01-15 07:59:00.000
2015-01-16 08:00:00.000      2015-01-16 13:59:00.000
2015-01-17 20:00:00.000      2015-01-18 07:59:00.000
2015-01-18 20:00:00.000      2015-01-19 07:59:00.000
2015-01-18 20:00:00.000      2015-01-19 07:59:00.000


i need to some how
Days (Monday to Friday between the hours of 08.00am to 08.00pm inclusive, excluding Bank Holidays (table called Bank_Holidays)
Nights (Monday to Friday between the hours of 08.01pm and 07.59am inclusive, excluding Bank Holidays (table called Bank_Holidays)  Saturdays (between the hours of midnight and 11.59pm)
Sundays and Bank Holidays (between the hours of midnight and 11.59pm).(table called Bank_Holidays)

here is code SELECT     id.datetime_from AS invoice_datetime_from, id.datetime_to AS invoice_datetime_from
FROM         rate_sheet AS rs INNER JOIN
                      public_holidays AS ph ON rs.rate_id = ph.rate_id INNER JOIN
                      main_order AS mo ON rs.rate_id = mo.rate_id INNER JOIN
                      invoice_detail AS id ON mo.order_id = id.order_id
LVL 31
James MurrellProduct SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's a date (no time) start --> SQL Server Calendar Table

>table called Bank_Holidays
You'll need to give us a data mockup of this table so we can see how days, nights, holidays, Sundays are handled, as it looks like your main table cuts across multiple days.
0
James MurrellProduct SpecialistAuthor Commented:
the public holiday table hold date type same as invoice but it is held as

2015-04-03 00:00:00.000      2015-04-03 23:59:00.000
2015-04-06 00:00:00.000      2015-04-06 23:59:00.000
2015-05-04 00:00:00.000      2015-05-04 23:59:00.000
2015-05-25 00:00:00.000      2015-05-25 23:59:00.000
2015-08-31 00:00:00.000      2015-08-31 23:59:00.000
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Okay, so ... what's the question?  Specifically, give us a data mockup of what you hope to generate.

Given an invoice.date_from and date_to where rows span more than one day, and a Bank_Holidays table where rows can either be a single day (Days, Holidays, Sundays, Bank Holidays) or multiple days (Nights), I want to ____ ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

arnoldCommented:
There are date functions.

Ref https://msdn.microsoft.com/en-us/library/ms186724.aspx

You could convert your holiday days to year,day of year  for easier comparison.
Day(of date column) will return day of week?

Hour() will return the hour where you can check whether it is more than equal 8 or less than 20
0
James MurrellProduct SpecialistAuthor Commented:
Ok i need another need column (not bothered by name) which will notify/marked as

day - if between mon and fri and times between 8am and 8 pm (not a BH)
Night - if between mon and fri and times between 8pm and 8 am or if sat all day (not a BH)
Sunday & Bank Holiday - if sunday or bank holiday and between all day

so if it was 2015-09-05 :0900: it would show Night as it is a Saturday
if it was 2015-09-04 :1500 it would be DAY as that is friday at 3pm
so id 2015-12-25 it would be Sunday & Public Holiday as it is in PH table

Thanks for helping
0
arnoldCommented:
Actually you are complicating matters, is stored procedure an option/consideration?

Do you need a query that identifies which day is which or do you

When do you need this data on the inser or on a report?
0
James MurrellProduct SpecialistAuthor Commented:
it is part of a much larger sql export

A SP is out (sorry )

as mentioned need it to say what type it is based on rules

so far i have managed

to get type sort of but not bh  with case

  case DATENAME(weekday, id.datetime_from) 
  when  'Sunday' then 'Weekend'  
  when  'Saturday' then 'Weekend' 
  else 'Weekday' end  + ' ' +   DATENAME(weekday, id.datetime_from) + ' ' +
  
  CASE WHEN CAST( CONVERT(CHAR(2), id.datetime_from, 108) AS INT) < 20 THEN 'Day'
       WHEN CAST( CONVERT(CHAR(2), id.datetime_from, 108) AS INT) < 20 THEN 'Evening' ELSE 'Night' END as 'Type ' ,     

Open in new window

0
arnoldCommented:
Having a sample data and your desired output would be helpful to understand what you are after.


Let's try this. You gave a table that defines the banking holidays for each year.
You gave what seems a transactional table
That has from date/time and to date time.

You want to use a query calculating the pay ?
0
James MurrellProduct SpecialistAuthor Commented:
no just the type of day in the rules mention twice now...

day - if between mon and fri and times between 8am and 8 pm (not a BH)
Night - if between mon and fri and times between 8pm and 8 am or if sat all day (not a BH)
Sunday & Bank Holiday - if sunday or bank holiday and between all day

so if it was 2015-09-05 :0900: it would show Night as it is a Saturday
if it was 2015-09-04 :1500 it would be DAY as that is friday at 3pm
so id 2015-12-25 it would be Sunday & Public Holiday as it is in PH table
0
arnoldCommented:
You can as you demonstrated use the case statement to output the week day, and time of day
The difficulty that you want the data combined.
One option would be 20150831 09:00 would show up as Monday, day,bh versus Monday & bh,day

as you mentioned, you could add a column or outer join on bank_holiday.
Current your query does not include/mark any record as being a bank holiday.
Using isnull(bank_holiday.descr) this will only have value... If from date is 20150830 22:00:00:00 to 20150831 09:00:00
Would it be Sunday, night or this scenario is not part of your .......
1
PortletPaulfreelancerCommented:
I'm a bit concerned with the accuracy of what is being discussed here.

For example
from                                          to
2015-04-03 00:00:00.000      2015-04-03 23:59:00.000

that is an inaccurate way to define a start and finish for a holiday (it is short by 1 minute)

Similary I would describe 'day' this way, and I would completely avoid the use of "between"
DAY
Monday to Friday, excluding holidays
>= 08:00:00
< 20:00:00

This covers an exactly 12 hour period starting at 8am (08:00:00)

Your existing query. If you use only INNER JOINS you would only get a few rows that do exist in common across all the tables used. In your case it would only list invoice details on public holidays.

I URGE you to provide some sample data, from each table.
and, then
provide the "expected result"
0
PortletPaulfreelancerCommented:
With sample data, and knowing what the expected result is usually allows us to preare an accurate solution without a lot of clarification.

using this data
CREATE TABLE Holidays
    ([Hdate] date)
;
    
INSERT INTO Holidays
    ([Hdate])
VALUES
    ('2015-01-01 00:00:00'),
    ('2015-01-17 00:00:00'), --<< had to add this for match to sample invoice data
    ('2015-08-31 00:00:00'),
    ('2015-05-25 00:00:00'),
    ('2015-05-04 00:00:00'),
    ('2015-04-06 00:00:00'),
    ('2015-04-03 00:00:00'),
    ('2015-12-25 00:00:00'),
    ('2015-12-28 00:00:00'),
    ('2016-01-01 00:00:00'),
    ('2016-03-25 00:00:00'),
    ('2016-03-28 00:00:00'),
    ('2016-05-02 00:00:00'),
    ('2016-05-30 00:00:00'),
    ('2016-08-29 00:00:00'),
    ('2016-12-26 00:00:00'),
    ('2016-12-27 00:00:00')
;


CREATE TABLE invoice_detail 
    ([datetime_from] datetime, [datetime_to] datetime)
;
    
INSERT INTO invoice_detail 
    ([datetime_from], [datetime_to])
VALUES
    ('2014-12-06 20:00:00', '2014-12-07 07:59:00'),
    ('2015-01-13 10:15:00', '2015-01-13 19:59:00'),
    ('2015-01-14 20:00:00', '2015-01-15 07:59:00'),
    ('2015-01-16 08:00:00', '2015-01-16 13:59:00'),
    ('2015-01-17 20:00:00', '2015-01-18 07:59:00'),
    ('2015-01-18 20:00:00', '2015-01-19 07:59:00'),
    ('2015-01-18 20:00:00', '2015-01-19 07:59:00')
;

Open in new window

I can match to holidays
|              datetime_from |                datetime_to |      Hdate |
|----------------------------|----------------------------|------------|
| December, 06 2014 20:00:00 | December, 07 2014 07:59:00 |     (null) |
|  January, 13 2015 10:15:00 |  January, 13 2015 19:59:00 |     (null) |
|  January, 14 2015 20:00:00 |  January, 15 2015 07:59:00 |     (null) |
|  January, 16 2015 08:00:00 |  January, 16 2015 13:59:00 |     (null) |
|  January, 17 2015 20:00:00 |  January, 18 2015 07:59:00 | 2015-01-17 |
|  January, 18 2015 20:00:00 |  January, 19 2015 07:59:00 |     (null) |
|  January, 18 2015 20:00:00 |  January, 19 2015 07:59:00 |     (null) |

Open in new window

using this query
select
    invd.*, h.*
from invoice_detail invd
left outer join Holidays h on ( invd.datetime_from >= h.hdate and invd.datetime_from < dateadd(day,1,h.hdate) )
                           or ( invd.datetime_to >= h.hdate and invd.datetime_to < dateadd(day,1,h.hdate) )

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnoldCommented:
You need to establish clearer rules you can incorporate the Paul's outer join with yours to use the case statements to output the  day of week and day or night......
0
PortletPaulfreelancerCommented:
I had not intended comment ID: 40964563 to be an answer, what I was trying to communicate is that with sample data and an expected result we can aid you find a solution. As arnold points out you still need the case expressions for "day" "night" etc. and for these I remain concerned about the accuracy of the rules.

tip
Please do NOT use "between" for date or time ranges, always use a combination of >= with <
see: "Beware of Between"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.