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()))

 

[Olaf Doschke]

The view, your query of the first post?
This query gave information for March, in March.
Today it gives information for April, up to today. 20 would still be false for that.

The query is always working for the current month, as it makes use of GETDATE(), which simplpy speaking is now, GETDATE returns the current datetime.

So let me summarize:
So far we've seen the different function definitions, not all so far, but enough to see they were once written to cover 2011/2012 specific holidays and need an upgrade.
Your view is making use of these function with two timespans: a) from 1st of month to today as ElapsedDays and from 1st to last of the month as TotalDays.
djj55 has cut that out and explained it in the first answer already.

For April you will get 22 days, as wee have 4 weekends, 30-8 =22 as TotalDays

Bye, Olaf.

 

[Cpreston]

Hi

Indeed in the view it is using elapsed days; makedate and bankholidays and the looking at now so i believe the view is not correct. This is not my view by the way i am investigating why it gives incorrect figures. What it should do is look at March only. I wll investigate it more Monday and update the thread.

Thank you all for your good answers.

 

[Olaf Doschke]

Again: This view query looks at the current month. Not last month, and certainly it's not tied to march.
But you can get the march figures, if you replace every occurrence of MONTH(GETDATE()) with 3. Watch out you don't replace one bracket too much, though.

Bye, Olaf.

 

[Cpreston]

Hi

Ok in the sql query I changed MONTH(GETDATE()) with 3 and then I got February totals.

I also tried MONTH(GETDATE())-1 and this also gave February's figures.
I have also tried to change the dates in bank holidays, and elapsed dates but this had no effect on the result at all no matter what I changed the dates to.

I have another query (which I made) which if I put in MONTH(GETDATE())-1 I get the correct figures for March, but this does not have the details of the query I am working with.

So my question really is how can I make the initial query look at last month always. I don't think the elapsed days and total days is important.
I will keep trying to amend a copy of the query but if anyone as further good advise it would be welcome.

Thanks

 

[Olaf Doschke]

3 is March and should work, the query will always work for the current month, not the last month, from the perspecitve of the dates the [148-MakeDate] procedure creates.
It can'T be seen what the query does effective from all you gave so far, there stil are many more tables and functions involved, eg [148-YearFinancials] and [148-vwSalesData]
From what you say the query should give you march data unchanged. If it doesn'T, there's something wrong with data, data is not present for march or whatever else.

As I already said someone, who should help you with this would need hands on. Directing you from the forum with questions and answers will need to continue and be unbearable.

At this point I am off from this thread, sorry.

Bye, Olaf.