What part of this sql codes is eleting last month 1

[Cpreston]

Hi

I have copied a View that gives information from last month March and also for this year. I cannot figure out which part of the code is giving the result for last month. Could someone please let me know which part of the code is doing this. ( I need to alter the view but as soon as I take a filed out it is looking at all months) Thanks in advance

SELECT dbo.[148-vwSalesData].FinancialYear AS Year, dbo.[148-vwSalesData].Half, dbo.[148-vwSalesData].Quarter AS Qtr, dbo.[148-vwSalesData].MonthName AS Month, dbo.[148-vwSalesData].Quay, dbo.[148-vwSalesData].Product, dbo.[148-vwSalesData].OriginalRepArea AS [Rep Area], dbo.[148-vwSalesData].[Cust Code], dbo.[148-vwSalesData].[Cust Name], dbo.[148-vwSalesData].[Buying Group], dbo.[148-vwSalesData].Value, dbo.[148-vwSalesData].m3, dbo.[148-vwSalesData].[Sub-group], dbo.[148-vwSalesData].[Group], dbo.[148-ElapsedWorkingDays](dbo.[148-MakeDate](1, MONTH(GETDATE()), YEAR(GETDATE())), dbo.[148-MakeDate](DAY(GETDATE()), MONTH(GETDATE()), YEAR(GETDATE()))) AS ElapsedDays, dbo.[148-ElapsedWorkingDays](dbo.[148-MakeDate](1, MONTH(GETDATE()), YEAR(GETDATE())), dbo.[148-MakeDate](dbo.[148-LastDayInMonth](YEAR(GETDATE()), MONTH(GETDATE())), MONTH(GETDATE()), YEAR(GETDATE()))) AS TotalDays, dbo.[148-YearFinancials].Year AS Y, dbo.[148-YearFinancials].Month AS M, dbo.[148-vwSalesData].Rep_Corrected AS Rep

FROM dbo.[148-vwSalesData] WITH (NOLOCK) INNER JOIN dbo.[148-YearFinancials] WITH (NOLOCK) ON dbo.[148-vwSalesData].MonthName = dbo.[148-YearFinancials].PrevMonthName AND dbo.[148-vwSalesData].FinancialYear = dbo.[148-YearFinancials].PrevMonthFinancialYear

WHERE (dbo.[148-YearFinancials].Year = YEAR(GETDATE())) AND (dbo.[148-YearFinancials].Month = MONTH(GETDATE()))

 

[djj55]

    dbo.[148-ElapsedWorkingDays](dbo.[148-MakeDate](1, MONTH(GETDATE()), YEAR(GETDATE())), 
    dbo.[148-MakeDate](DAY(GETDATE()), MONTH(GETDATE()), YEAR(GETDATE()))) AS ElapsedDays, 
    dbo.[148-ElapsedWorkingDays](dbo.[148-MakeDate](1, MONTH(GETDATE()), YEAR(GETDATE())), 
    dbo.[148-MakeDate](dbo.[148-LastDayInMonth](YEAR(GETDATE()), MONTH(GETDATE())), MONTH(GETDATE()), YEAR(GETDATE()))) AS TotalDays,

You have two functions [148-ElapsedWorkingDays] and [148-MakeDate] which give your information

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Cpreston]

I thought it was them but I cannot locate them on the sql server in the database in tables, views or SP.
The 148 means they have been added specifically

 

[Olaf Doschke]

You may only have been granted execution permmission to these SPs, but not reading/modifying.

Bye, Olaf.

 

[Cpreston]

Hi

What the issue is I cannot find the functions under tables, views or SP's

[148-ElapsedWorkingDays] and [148-MakeDate]

So how can the view call them if they are not there?

Thanks

 

[Cpreston]

I have found Elapsedworkingdays and also Makedate. However they do not make a lot of sense to me unfortunately.

If I run the script as it is above the ElapsedDays = 16 and the Totaldays = 20. The month and year is correct. So where does it get the 16 and 20 total (remembering this is for March, last month). I will past the code in from the both of them, could someone please explain what they are coding and how it calculated the dates, thanks

Makedate
)
RETURNS datetime
AS
BEGIN
DECLARE @Result datetime

SET @Result = convert(datetime,convert(varchar(4),@Year) + '-' + convert(varchar(2),@Month) + '-' + convert(varchar(2),@Day))

RETURN @Result

END

Elapsedworkingdays
USE [HTI Live]
GO
/****** Object: UserDefinedFunction [dbo].[148-ElapsedWorkingDays] Script Date: 04/24/2015 14:13:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[148-ElapsedWorkingDays] (
@StartDate datetime,
@EndDate datetimeElapsedworkingd
)

RETURNS int

AS
BEGIN
DECLARE @EWD int
DECLARE @Y int
DECLARE @BH int

-- get weekdays
SET @EWD =
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

-- now check bank holidays
SET @Y = Year(@StartDate)
SELECT @BH = Count(BH) FROM dbo.[148-BankHolidays] (@Y) where BH Between @StartDate AND @EndDate

-- now check for extra holidays
IF DATEPART(month, @Startdate) = 12 AND DATEPART(day, @Enddate)
= dbo.[148-LastDayInMonth](DATEPART(year, @Enddate),12)
SET @BH = @BH + CASE WHEN DATEPART(year, @StartDate) = 2011 THEN 2 ELSE CASE WHEN DATEPART(year, @StartDate) = 2012 THEN 3 ELSE 0 END END

RETURN @EWD - @BH
END

 

[Cpreston]

Also is there anyway I can see an output using query.

I have tried all sorts and currently have this

select dbo.[148-ElapsedWorkingDays] as MyResult

but I am getting this

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.148-ElapsedWorkingDays" could not be bound.

 

[Olaf Doschke]

In regard to your first question:

The first function MakeDate simply generates a date type from year,month, day (not datetime, a pure date)
The second function in short just caluclates the working days between two dates. It includes bankholidays and the last part is only specified for 2011 and 2012.

These procedures take parameters, you have to call them with values, eg

SELECT dbo.[148-ElapsedWorkingDays](dbo.[148-MakeDate](1,3,2015),dbo.[148-MakeDate](31,3,2015)) should give number of march 2015 working days, maybe with an error de to the "extra holidays" part.

Bye, Olaf.

 

[Cpreston]

If the dates are 2011 and 2012 then should these not be this year, or does other calculations work this out?

Thanks for the help, some good information thanks you

 

[djj55]

To run [148-ElapsedWorkingDays] you need to add the parameters. the code you presented does not look correct as the original post had three parameters (1, month, year) where as the function presented is looking for startdate, enddate, and something else (Elapsedworkingd???)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Olaf Doschke]

I actually don't understand the logic of the extra holidays. The code is adding 2 for 2011 start dates (any dates throughout the whole year) and 3 for 2012 start dates. It doesn't make sense to me, seems like a patch by adding an offset. You'd alos need to look into [148-BankHolidays] to what this does. Maybe it also has code specific for 2011/12 only.

This whole function could have a validity only somewhere between 2011 and 2012 and should be reviewed or totally rewritten, if you don't get the correct results from them.

Bye, Olaf.

 

[Cpreston]

I am sorry I did not understand, I am not very good with SQl code indepth.
Are you saying the initial code is incorrect for Elapsed Working Days, if so how should it be.
It is used in a View and as this format;

Elapsed Days
dbo.[148-ElapsedWorkingDays] (dbo.[148-MakeDate] (1, MONTH(GETDATE()), YEAR(GETDATE())), dbo.[148-MakeDate] (DAY(GETDATE()), MONTH(GETDATE()), YEAR(GETDATE())))
And this gives the result of 16 under elapsed days column

then we have Total days
dbo.[148-ElapsedWorkingDays] (dbo.[148-MakeDate] (1, MONTH(GETDATE()), YEAR(GETDATE())), dbo.[148-MakeDate] (dbo.[148-LastDayInMonth] (YEAR(GETDATE()), MONTH(GETDATE())), MONTH(GETDATE()), YEAR(GETDATE())))

And this gives us the result of 20

This is supposed to show last months figures for March and there were 22 days last month?

Thanks

 

[Olaf Doschke]

It's not easy to mend this, as holidays are varying with the years. The essential error would be in code you didn't yet show, in [148-BankHolidays]

Bye, Olaf.

 

[Cpreston]

Hi

yes looks like the dates are still back in 2012, Bank holiday code below

USE [HTI Live]
GO
/****** Object: UserDefinedFunction [dbo].[148-BankHolidays] Script Date: 04/24/2015 16:18:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[148-BankHolidays]
(
@Year int
)
RETURNS
@BankHolidays TABLE
(
BH DATETIME
)
AS
BEGIN
DECLARE @CD DATETIME
DECLARE @Day int

-- EXTRA DAYS FOR 2012 - see later for WHIT
IF @Year = 2012
BEGIN
INSERT INTO @BankHolidays
SELECT convert(datetime,'2012-06-04')

INSERT INTO @BankHolidays
SELECT convert(datetime,'2012-06-05')
END

-- New Years Day
SET @Day = dbo.[148-FirstMonday] (@Year, 1)

INSERT INTO @BankHolidays
SELECT convert(datetime,convert(varchar(4),@Year)+'-01-'+convert(varchar(2),@Day))

-- Good Friday
SET @CD = DATEADD(dd,-2,dbo.[148-EasterSundayByYear] (@Year))

INSERT INTO @BankHolidays
SELECT @CD

-- Easter Monday
SET @CD = DATEADD(dd,3,@CD)

INSERT INTO @BankHolidays
SELECT @CD

-- May Day
SET @Day = dbo.[148-FirstMonday] (@Year, 5)

INSERT INTO @BankHolidays
SELECT convert(datetime,convert(varchar(4),@Year)+'-05-'+convert(varchar(2),@Day))

-- Whit
/*SET @Day = dbo.[148-LastMonday] (@Year, 5)

INSERT INTO @BankHolidays
SELECT convert(datetime,convert(varchar(4),@Year)+'-05-'+convert(varchar(2),@Day))
*/

-- August
SET @Day = dbo.[148-LastMonday] (@Year, 8)

INSERT INTO @BankHolidays
SELECT convert(datetime,convert(varchar(4),@Year)+'-08-'+convert(varchar(2),@Day))

-- Christmas
SET @Day = DATEPART(dw,convert(datetime,convert(varchar(4),@year)+'-12-25'))

IF @Day = 7 OR @Day = 1
-- weekend
SET @Day = 27
ELSE
SET @Day = 25

INSERT INTO @BankHolidays
SELECT convert(datetime,convert(varchar(4),@Year)+'-12-'+convert(varchar(2),@Day))

-- Boxing Day
SET @Day = DATEPART(dw,convert(datetime,convert(varchar(4),@year)+'-12-26'))

IF @Day = 7
-- saturday
SET @Day = 28
ELSE
IF @Day = 1
-- sunday
SET @Day = 29
ELSE
SET @Day = 26

INSERT INTO @BankHolidays
SELECT convert(datetime,convert(varchar(4),@Year)+'-12-'+convert(varchar(2),@Day))

RETURN
END

 

[Olaf Doschke]

20 or 22 could be good, remember it's computing the number of working days. weekends and holidays are subtracted.

Bye, Olaf.

 

[Cpreston]

yes but we had 22 in March last month and also using the code supplied earlie it gives 22
SELECT dbo.[148-ElapsedWorkingDays](dbo.[148-MakeDate](1,3,2015),dbo.[148-MakeDate](31,3,2015))

 

[Cpreston]

Any ideas how the dates need to be changed and where.

Also how can I look in 148-Bank holidays to see what data it holds if possible. it is under stored procedures/programmability/Functions/Tabled value functions, is it possible with a select statement in query?

Thanks for the help

 

[Olaf Doschke]

If 22 is correct then that's fine, isn't it. You're distracted by the name of the function. If you don't feed in 31 for last day of march, but any other day, it computes working days elapsed from 1st march to that day.

Besides you already posted /****** Object: UserDefinedFunction [dbo].[148-BankHolidays] Script Date: 04/24/2015 16:18:20 ******/ now.

There are some dates specific to 2012, as you saw yourself. There is yet another function called from there: dbo.[148-EasterSundayByYear](), seems to work correct for any year.

Anyway, if someone should mend this, he'd need hands on, this goes too far for a forum thread.

Bye, Olaf.

 

[Cpreston]

So basically do i need to change the dates like 2012 to 2015. I dont expect code written for jus pointing in right direction

Thanks

 

[Cpreston]

On the view it is also bringing back 20 not 22 so this is incorrect

Thanks