Help with date formats 1

[Cpreston]

Hi

I am creating a view that needs to look back from todays date to the last 2 months. I am only ointerested in working dates and also need to inculde a holiday tabloe fo dates

So I need this months working days, Decembers and Novembers. I need it to always look look at the current month and the last 2 full months including the holiday days to be taken away as well as weekends.
So in Febuary i would currrent month Feb and then january and December.

In the view I have a table with caledarmonth and calenderyear fields.
I also ahve a table called calender that as one column with the dates on for many years ahead, for each day.

Could someone give me some ideas how to create this and also take away the hoildaydays from the total working days.

not sure if I have expalined myself very well

Thanks

 

[SkipVought]

Hi,

I've worked in the aerospace manufacturing industry for over 30 years in IT. All these companies have manufacturing calendars (tables defining workdays, accounting related days and holidays.)

So I'd suggest either getting a copy of your company's calendar, or getting access to their calendar table. But you need a table: dates for the significant past and dates for the significant future.

A typical calendar has EVERY DATE IN THE YEAR, WorkDays as sequential numbers so that for a typical week, Friday, Saturday & Sunday would be WorkDay n and Monday would be WorkDay n + 1. It might also have week numbers, accounting week, month, quarter, year indicators.

This should not be done with formulas. Maybe you use formulas to get the date data set up before you carefully check it, but formulas ought not be used to run your business, IMNSHO.

With such a table, the ambiguities are gone. It's very useful! It is abundantly reusable.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Cpreston]

Hi

We do have a calendar table and we also have a holidays table.

What I cannot work out is how to code this in my view, so it looks at todays date and goes back to end of November, then in March to end of December etc....
Also take out dates in the Holiday table and then give me a count of the working days.

Holiday Table consists of 2 fields called Holiday and Holidaydate

Holiday : Boxing Day BH Holidaydate: 2015-12-128

Calendar Table consists of 1 field called CalendarDate

Calendardate : 2015-12-27 00:00:00.000

Any ideas how I can get what I need into my view please. I shall continue to Google and try in the meantime

Thanks

 

[fredericofonseca]

look at

https://www.mssqltips.com/sqlserver...te-dimension-or-calendar-table-in-sql-server/

for an example of a calendar table - adapt as needed to your country.
Note that in this case it was decided that weekend would be day 1 and day 7 - this should be changed to match what your company/country normally defines the week.


The code above allows for the most common holidays but another table for "holidays" should always be created - not only to hold the holidays but to hold days that require specific processing.
such table would as a minimum contain the following

field       datatype       description
date        date           --date
process     varchar(20)    --process type
description varchar(200)   -description

where process is the driver for this table and allows you to define dates that hold particular interest for your company such as dates for Direct Debit Processing or dates for Commissions processing etc.


using the table is then as easy as

retrieve all working days from date table

select *
from dbo.DateDimension dd
where dd.IsWeekend = 0 -- only monday to friday
and dd.IsHoliday = 0 -- only non holiday days

expand to include holidays table

select *
from dbo.DateDimension dd
left outer join dbo.Holidays h
   on h.date = dd.date
   and h.process = 'holidays'
where dd.IsWeekend = 0 -- only monday to friday
and dd.IsHoliday = 0 -- only non holiday days
and h.date is null

expand to your desired period

select *
from dbo.DateDimension dd
left outer join dbo.Holidays h
   on h.date = dd.date
   and h.process = 'holidays'
where dd.IsWeekend = 0 -- only monday to friday
and dd.IsHoliday = 0 -- only non holiday days
and h.date is null
and dd.Month between month(getdate()) and month(getdate()) - 2

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Thanks, I will try and apply the code to our tables and get back with the results

Thank you

 

[Cpreston]

Hi

I have started looking at this today and have ran into a minor issue.

I have ran the code from your link

https://www.mssqltips.com/sqlservertip/4054/creati...

for

It worked ok, but a lot of the fields were not required and some public holidays dates etc... were not UK accurate. So I decided to delete the table then logged out of sql and then back in and start again.

The first 2 parts of the link worked but when I went to populate data into the datedimension table using the code from the link I keep getting

Msg 208, Level 16, State 0, Line 1
Invalid object name '#dim'.

How do I check if the #dim is there (the code to create this ran ok and resulted as ok).
How do I clear everything out so I can sue the code again.

Thanks

 

[Cpreston]

Hi

Got this working again in our test database.

Just tying out the code, not having much luck at the moment, will try abot further and then no doubt will have some questions

Thanks

 

[Cpreston]

Ok this is where I am

The table datedimensions is set and as all dates from 2005 to the future and takes out Christmas day, new years day and should have easter holidays.

I have another table called 148-HolidayCP with 32 fields

field datatype description
date date --date
Holiday nvarchar(15) --process type

In this table is all our other holidays.

Currently if I run the code

select *
from dbo.DateDimension dd
where dd.IsWeekend = 0 -- only monday to friday
and dd.IsHoliday = 0 -- only non holiday days
and DATE between '2015-12-01' and '06-01-2016'

it comes up with all dates except 2015-12-25 and 2016-01-01, which is correct. I think now we need to take this a stage at a time (sorry my SQL skills are not so hot)

I now want to join it with the Holiday table so it then also does not show the dates listed in the holidaycp tables in the result. How do I do this. I have doen the below but it still shows the dates that are in the HolidayCP table. Thanks

select *
from dbo.DateDimension dd
Left outer join dbo."148-Holidaycp" h
on h.date = dd.date
and h.holiday = 'holidays'
where dd.IsWeekend = 0 -- only monday to friday
and dd.IsHoliday = 0 -- only non holiday days
and h.date is null

 

[Cpreston]

Also when I run this one you gave

select *
from dbo.DateDimension dd
left outer join dbo."148-holidaycp" h
on h.date = dd.date
and h.holiday = 'holidays'
where dd.IsWeekend = 0 -- only monday to friday
and dd.IsHoliday = 0 -- only non holiday days
and h.date is null
and dd.MONTH between month(getdate()) and month(getdate()) - 2


It is returning no data at all, brings the header in but no data , any ideas please

Many thanks

 

[Cpreston]

Any ideas on this please anyone. Can seem to get it working. Thankd

 

[fredericofonseca]

give us the output of the following sql's


select top 10 *
from dbo.DateDimension dd
where year(dd.date) = 2016'

select top 10 *
from dbo."148-Holidaycp" h
where year(h.date) = 2016'

Regarding the month sql - I had it wrong and I am sorry for that.

change
and dd.MONTH between month(getdate()) and month(getdate()) - 2
to
and dd.date between dateadd(mm, -2, convert(date, left(convert(char(8), getdate(), 112), 6) + '01', 112))
and dateadd(dd, -1, convert(date, left(convert(char(8), dateadd(mm, 1, getdate()), 112), 6) + '01', 112))


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

I will try them tomorrow when I have access to our Sql Servers and gt back to you with the result.

many thanks for the help.

 

[Cpreston]

Regarding the first query attached file is the result (sorry not sure how to paste results onto here)

 

[Cpreston]

Answer to second query attached

 

[Cpreston]

I have also added in the code

and dd.date between dateadd(mm, -2, convert(date, left(convert(char(8), getdate(), 112), 6) + '01', 112))
and dateadd(dd, -1, convert(date, left(convert(char(8), dateadd(mm, 1, getdate()), 112), 6) + '01', 112))

This gives me back results start of November to end of January. However it still includes the days on the holidaycp table.

Ideally I would like the dates in Holidaycp table not to be included in the result.
Also the query to run from todays date (the date it is run) and not to the end of Janaury, is this possible.

many thanks

 

[fredericofonseca]

your original post stated "So I need this months working days, Decembers and Novembers." and I missed the fact that you were stating "back from todays date to the last 2 months" and implicitly asking only for dates up to todays date.

for the above change

and dd.date between dateadd(mm, -2, convert(date, left(convert(char(8), getdate(), 112), 6) + '01', 112))
                and dateadd(dd, -1, convert(date, left(convert(char(8), dateadd(mm, 1, getdate()), 112), 6) + '01', 112))

to

and dd.date between dateadd(mm, -2, convert(date, left(convert(char(8), getdate(), 112), 6) + '01', 112))
                and convert(date,  getdate())

for the holidays - as you didn't create that table as I suggested you can't obviously use the same criteria that I gave you can you?
so remove the line that has "and h.holiday = 'holidays'"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Thanks for the code thus far and it is very appreciated. I have looked at my view and think I will have issues applying the calendar due to the fact the only table with any dates as a Year And Month field only. So Year as 2015 and 2016 and Month as 1,2,3 etc.....

I can sue the information you have kindly supplied for future querys where date fields are evident, but how can I use a year and Month fields so they do the same, go from todays date and back to the start of November, and then of course when it becomes February go back to start of December.

Currently we have something like below, but this does not calculate working days correctly (from current date to start of November taking out the days listed in Holdaycp table as well)

CalendarYear = DATEPART(year, GETDATE()) OR = DATEPART(YEAR, DATEADD(month, - 1, GETDATE()))
CalnedarMonth = DATEPART(MONTH, GETDATE()) OR = DATEPART(MONTH, DATEADD(month, - 1, GETDATE()))



Thanks

 

[Cpreston]

Hi in fact I though I would include the whole SQL (please let me know if I need to open a new thread as the initial requirement as changed somewhat)

Current working days from today back to November 1st excluding the days given in the dbo.[148-Holiday].Holidaydate table it is using (all dates are in the table)
should = 54. If I run the below SQL it gives me 32 working days.

I know the code is not looking back to the 1st November and also I feel it is not taking out Holidaydate listed ion the holiday table. I have tried to change the code but cannot get it to get anywhere near the 54 working days expected. Any ideas on what in I need to change tog et the dates right. (again sorry if this should be in anew thread) Thanks as always

SELECT dbo.ProductAnalysis.ProductID,
(SELECT (DATEDIFF(dd, DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0),
(SELECT InvoiceDate
FROM dbo.LastInvoiceDate)) + 1) - DATEDIFF(wk, DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0),
(SELECT InvoiceDate
FROM dbo.LastInvoiceDate AS LastInvoiceDate_3)) * 2 - (CASE WHEN DATENAME(dw, (DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)))
= 'Sunday' THEN 1 ELSE 0 END) - (CASE WHEN DATENAME(dw,
(SELECT InvoiceDate
FROM LastInvoiceDate)) = 'Saturday' THEN 1 ELSE 0 END) -
(SELECT COUNT(*) AS Expr1
FROM dbo.[148-Holiday]
WHERE (HolidayDate >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)) AND (HolidayDate <
(SELECT InvoiceDate
FROM dbo.LastInvoiceDate AS LastInvoiceDate_2))) AS WorkingDaysL1) AS WorkingDaysL1,
CASE WHEN [m3 Used] = 0 THEN 0.0001 ELSE [m3 Used] END AS VolumeUsed, dbo.Product.ProductCode
FROM dbo.ProductAnalysis INNER JOIN
dbo.Product ON dbo.ProductAnalysis.ProductID = dbo.Product.ProductID INNER JOIN
dbo.ProductGroup ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID INNER JOIN
dbo.[148-vwQuantityUsedCheck_Usage_MDF3] ON dbo.ProductAnalysis.ProductID = dbo.[148-vwQuantityUsedCheck_Usage_MDF3].ProductID CROSS JOIN
dbo.LastInvoiceDate AS LastInvoiceDate_1
WHERE (dbo.ProductAnalysis.CalendarYear = DATEPART(year, GETDATE()) OR
dbo.ProductAnalysis.CalendarYear = DATEPART(YEAR, DATEADD(month, - 1, GETDATE()))) AND (dbo.ProductAnalysis.CalendarMonth = DATEPART(MONTH,
GETDATE()) OR
dbo.ProductAnalysis.CalendarMonth = DATEPART(MONTH, DATEADD(month, - 1, GETDATE())))
GROUP BY dbo.ProductAnalysis.BranchID, dbo.ProductAnalysis.ProductID, dbo.ProductGroup.Level1ID, LastInvoiceDate_1.InvoiceDate,
CASE WHEN [m3 Used] = 0 THEN 0.0001 ELSE [m3 Used] END, dbo.Product.ProductCode
HAVING (dbo.ProductAnalysis.BranchID IN (1, 9)) AND (dbo.ProductGroup.Level1ID = 893) AND (CASE WHEN [m3 Used] = 0 THEN 0.0001 ELSE [m3 Used] END > 0.0001) OR
(dbo.ProductAnalysis.BranchID IN (1, 9)) AND (dbo.ProductGroup.Level1ID = 893) AND (CASE WHEN [m3 Used] = 0 THEN 0.0001 ELSE [m3 Used] END > 0.0001) OR
(dbo.ProductAnalysis.BranchID IN (1, 9)) AND (dbo.ProductGroup.Level1ID = 893) AND (CASE WHEN [m3 Used] = 0 THEN 0.0001 ELSE [m3 Used] END > 0.0001)

 

[fredericofonseca]

hum... few things first.

1 - give your tables alias and then use them - not too long of an alias.
2 - if particular values are repetitive throughout the code place them on a outer apply - specially useful if functions are involved
3 - fully qualify your columns with their corresponding table alias
4 - format your code


and when posting here use the tags to to format it as code e.g. add [ignore]

[/ignore] and

before and after your code (remove the space I added here)

here is the code above reworked for the above aspects - a lot easier to follow in my opinion

SELECT pa.ProductID
     , (SELECT DATEDIFF(dd, bd.start_previous_month , lid.InvoiceDate) + 1
             - DATEDIFF(wk, bd.start_previous_month , lid.InvoiceDate) * 2
       - CASE WHEN DATENAME(dw, bd.start_previous_month) = 'Sunday' THEN 1 ELSE 0 END
       - CASE WHEN DATENAME(dw, lid.InvoiceDate) = 'Saturday' THEN 1 ELSE 0 END
       - (SELECT COUNT(*) AS Expr1
          FROM dbo.[148-Holiday] as hol
          WHERE hol.HolidayDate >= bd.start_previous_month) 
             AND hol.HolidayDate < lid.InvoiceDate) AS WorkingDaysL1
       ) AS WorkingDaysL1
    , CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END AS VolumeUsed
    , pr.ProductCode
FROM dbo.ProductAnalysis as pa
INNER JOIN dbo.Product as pr
   ON pa.ProductID = pr.ProductID
INNER JOIN dbo.ProductGroup as pg
   ON pr.ProductGroupID = pg.ProductGroupID
INNER JOIN dbo.[148-vwQuantityUsedCheck_Usage_MDF3] as qucu
   ON pa.ProductID = qucu.ProductID
CROSS JOIN dbo.LastInvoiceDate AS lid
outer apply (select DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) as start_previous_month) as bd
WHERE (pa.CalendarYear = datepart(year, GETDATE()) OR pa.CalendarYear = datepart(YEAR, DATEADD(month, - 1, GETDATE())))
AND (pa.CalendarMonth = datepart(MONTH, GETDATE()) OR pa.CalendarMonth = datepart(MONTH, DATEADD(month, - 1, GETDATE())))
GROUP BY pa.BranchID
       , pa.ProductID
       , pg.Level1ID
       , lid.InvoiceDate
       , CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END
       , pr.ProductCode
HAVING pa.BranchID IN (1, 9)
   AND pg.Level1ID = 893
   AND CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END > 0.0001

-- code below looks duplicated
   OR pa.BranchID IN (1, 9)
   AND pg.Level1ID = 893
   AND CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END > 0.0001
   OR pa.BranchID IN (1, 9)
   AND pg.Level1ID = 893
   AND CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END > 0.0001

Regarding the question of how to query a table when a date is stored as its individual parts
the answer is you join them and form a string/value/date that can then be used in a proper compare.
example

select *
     , y + m + d as valid_date_format_112
     , convert(date, right('00000000' + convert(varchar(8), y + m + d), 8)) as true_date
     , convert(date, getdate()) as currentdate
     , convert(int, convert(varchar(6), GETDATE(), 112)) as year_month -- convert to format 112 gives format yyyymmdd, as result is restricted to varchar(6) only first 6 numbers are used, and then convert to a int gives 201601
from (
select year(getdate()) * 10000 as y
     , month(getdate()) * 100 as m
     , day(getdate()) as d
     ) t

looking at your code above table ProductAnalysis contains columns year and month. Maybe also day but as you didn't use it it may not exist/be applicable.

so to get all records from the above table where the records fall within a period you have several options.
WHERE (pa.CalendarYear = datepart(year, GETDATE()) OR pa.CalendarYear = datepart(YEAR, DATEADD(month, - 1, GETDATE())))
AND (pa.CalendarMonth = datepart(MONTH, GETDATE()) OR pa.CalendarMonth = datepart(MONTH, DATEADD(month, - 1, GETDATE())))

select *
from dbo.ProductAnalysis as pa
where pa.CalendarYear * 10000 + pa.CalendarMonth * 100 
      between convert(int, convert(varchar(6), DATEADD(month, - 1, GETDATE()), 112)) -- get previous month - result could be previous year
          and convert(int, convert(varchar(6), GETDATE(), 112))

 
select *
from dbo.ProductAnalysis as pa
where pa.CalendarYear * 10000 + pa.CalendarMonth * 100 
      between datepart(year, DATEADD(month, - 1, GETDATE())) * 10000 + datepart(MONTH, DATEADD(month, - 1, GETDATE())) * 100 -- get previous month - result could be previous year
          and datepart(year, getdate()) * 10000 + datepart(month, getdate()) * 100

other ways of doing it but all comes down to requirements and table definitions and data

regarding how you add the holidays I don't see what you need there as you already have some code for it, and the joins do not seem to require that - but maybe I am wrong on which case more information is required

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Thanks, it will take me some time to run through this, I quickly tried you main code and I get this

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AND'.
Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'as'.

Also could you advise a training course for this sort of thing. I am fine with a lot of SQL but just cant seem to get dates and conversions into my head.

Thanks