January 3, 2008 at 12:15 pm
Original SQL below, the issue is with the MONTH part where it is not returning a 12 for DEC but a 0! Basically just trying to get it to look back one month in time to include the previous month and this month. I did try adding some additional logic as well which is in the second statment.... any help is greatly appreciated.
select x.personnum, sum(CASE when x.paycodeid='151' Then x.timeinseconds else Null end)
,x.laborlevelname1,x.laborlevelname2,x.laborlevelname3,x.laborlevelname4,x.laborlevelname5,x.applydate,
sum(CASE when x.paycodeid='201' Then x.timeinseconds else Null end),
sum(CASE when x.paycodeid='202' Then x.timeinseconds else Null end),
sum(CASE when x.paycodeid='203' Then x.timeinseconds else Null end),case when x.timeinseconds=0 then 0 else ((x.wageamount)/(x.timeinseconds))*3600 end,
e.homelaborlevelnm1,e.homelaborlevelnm2,e.homelaborlevelnm3,e.homelaborlevelnm4,e.homelaborlevelnm5,t.startdtm,e.basewagehourlyamt,e.payrulename
from vp_totals x, vp_employeev42 e,vp_timesheetitmv42 t
where x.employeeid=e.personid and x.paycodeid<>'204' and x.timesheetitemid=t.timesheetitemid and MONTH(x.applydate)>=(MONTH(Getdate())-1) and x.applydate<=getdate()
group by x.personnum,x.laborlevelname1,x.laborlevelname2,x.laborlevelname3,x.laborlevelname4,x.laborlevelname5,x.applydate,case when x.timeinseconds=0 then 0 else ((x.wageamount)/(x.timeinseconds))*3600 end,e.basewagehourlyamt,
e.homelaborlevelnm1,e.homelaborlevelnm2,e.homelaborlevelnm3,e.homelaborlevelnm4,e.homelaborlevelnm5,t.startdtm,e.payrulename
order by x.personnum,x.applydate
SECOND STATEMTENT WITH ADDITIONAL CHANGES:
select x.personnum, sum(CASE when x.paycodeid='151' Then x.timeinseconds else Null end)
,x.laborlevelname1,x.laborlevelname2,x.laborlevelname3,x.laborlevelname4,x.laborlevelname5,x.applydate,
sum(CASE when x.paycodeid='201' Then x.timeinseconds else Null end),
sum(CASE when x.paycodeid='202' Then x.timeinseconds else Null end),
sum(CASE when x.paycodeid='203' Then x.timeinseconds else Null end),case when x.timeinseconds=0 then 0 else ((x.wageamount)/(x.timeinseconds))*3600 end,
e.homelaborlevelnm1,e.homelaborlevelnm2,e.homelaborlevelnm3,e.homelaborlevelnm4,e.homelaborlevelnm5,t.startdtm,e.basewagehourlyamt,e.payrulename
from vp_totals x, vp_employeev42 e,vp_timesheetitmv42 t
where x.employeeid=e.personid and x.paycodeid<>'204' and x.timesheetitemid=t.timesheetitemid and MONTH(x.applydate)>=('month'=CASE when (select month(getdate())-1) = 0 then 12
else (select month(getdate())-1)
end
and x.applydate<=getdate()
group by x.personnum,x.laborlevelname1,x.laborlevelname2,x.laborlevelname3,x.laborlevelname4,x.laborlevelname5,x.applydate,case when x.timeinseconds=0 then 0 else ((x.wageamount)/(x.timeinseconds))*3600 end,e.basewagehourlyamt,
e.homelaborlevelnm1,e.homelaborlevelnm2,e.homelaborlevelnm3,e.homelaborlevelnm4,e.homelaborlevelnm5,t.startdtm,e.payrulename
order by x.personnum,x.applydate
January 3, 2008 at 12:18 pm
Issue is because of the change from 2007 and 2008.. where the MONTH returns a 0 and there for ALL applydates are greater than or equal to 0 so we get data for the entire 2007 year up until today. We only want to capture two months ( current and previous ) which worked fine the rest of the year because the month being returned was say 10 and it would only send data with an apply date greater than or equal to 10...
help!
January 3, 2008 at 12:26 pm
actually need to add the YEAR value in there to where it only goes back two months... as this year was the first on the DB so in 2008 when the month is greater than a month it will grab all last year data.. so I need to incorporate the YEAR into this to make sure I only get the last two months of data..
January 3, 2008 at 12:40 pm
Grab the datemonthstarts function from here[/url] and use that instead of MONTH.
Since you'll then be comparing datetimes, 1 Dec 2007 is < 1 Jan 2008
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2008 at 12:43 pm
You should not use the month function becuase you will always run into anomalies that will break your code. Use the datetime fields as they are intended to be used.
declare this at the top of you query
DECLARE @PrevBOM DATETIME, @CurEOM DATETIME
SET @PrevBOM = DateAdd(month, DateDiff(month, 0, DATEADD(MM,-1,GETDATE())), 0)
SET @CurEOM = DateAdd(second, -1, DateAdd(month, DateDiff(month, 0, GETDATE())+1, 0))
Now in your where clause use this:
x.applydate BETWEEN @PrevBOM AND @CurEOM
This sets a criteria of everything >= 2007-12-01 00:00:00.000 and <= 2008-01-31 23:59:59.000
January 3, 2008 at 12:46 pm
Looks like Gail beat me too the punch 😉
January 3, 2008 at 12:58 pm
forgive my ignornace but will that work through out the year where it will only grab the previous montn and the current month of data?
Thanks again
Rob
January 3, 2008 at 1:11 pm
forgive my ignornace but will that work through out the year where it will only grab the previous montn and the current month of data?
Thanks again
Rob
Yes.
If you look at the declaration of how I got the variable values you can see I am using the getdate() function to dynamically determine the previous beginning of month and the current end of month.
January 3, 2008 at 1:13 pm
Mine or Adam's?
The DateMonthStarts I pointed you at will take a date and return the 1st day of that month.
Write the code into a user defined function, so that it's easily available
So instead of WHERE ... MONTH(x.applydate)>=(MONTH(Getdate())-1) which, as you've noticed, breaks around december every year ;), use
WHERE ... dbo.DateMonthStarts(x.applydate) >= dbo.DateMonthStarts(DATEADD(mm,-1,getdate()))
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2008 at 1:23 pm
Gail,
We are using different methods but achieving the same goal. You are talking about using a UDF and I am using a simple declaration of variables to house the data.
Rob should be able to use either of our code, as the concept is the same. We are both finding the previous beginning of month and the current end of month the grabbing all data inbetween (this could be substuted for a GETDATE() if Rob does not want data beyond the current date).
-Adam
January 3, 2008 at 1:28 pm
Rob,
If you do not want the current date range to exceed the current date then change the curEOM to
SET @CurEOM = GETDATE()
E.g. If you run this query on 1/3/2008 and you only want data between 12/1/2007 and 1/3/2008 and not between 12/1/2007 and 1/31/2008, then change the @CurEOM to the above statement.
January 3, 2008 at 1:40 pm
thank you both... will have to test and confirm the results! But THANKYOU again for the info... greatly appreciated
January 4, 2008 at 11:30 am
well it appears that neither are working....
With Adams, It appears to run but I get no results. I put the DECLARES at the top and then the Where part in.
DECLARE @PrevBOM DATETIME, @CurEOM DATETIME
SET @PrevBOM = DateAdd(month, DateDiff(month, 0, DATEADD(MM,-1,GETDATE())), 0)
SET @CurEOM = DateAdd(second, -1, DateAdd(month, DateDiff(month, 0, GETDATE())+1, 0))
select x.personnum, sum(CASE when x.paycodeid='151' Then x.timeinseconds else Null end)
,x.laborlevelname1,x.laborlevelname2,x.laborlevelname3,x.laborlevelname4,x.laborlevelname5,x.applydate,
sum(CASE when x.paycodeid='201' Then x.timeinseconds else Null end),
sum(CASE when x.paycodeid='202' Then x.timeinseconds else Null end),
sum(CASE when x.paycodeid='203' Then x.timeinseconds else Null end),case when x.timeinseconds=0 then 0 else ((x.wageamount)/(x.timeinseconds))*3600 end,
e.homelaborlevelnm1,e.homelaborlevelnm2,e.homelaborlevelnm3,e.homelaborlevelnm4,e.homelaborlevelnm5,t.startdtm,e.basewagehourlyamt,e.payrulename
from vp_totals x, vp_employeev42 e,vp_timesheetitmv42 t
where x.employeeid=e.personid and x.paycodeid<>'204' and x.timesheetitemid=t.timesheetitemid and x.applydate BETWEEN @PrevBOM AND @CurEOM and x.applydate<=getdate()
group by x.personnum,x.laborlevelname1,x.laborlevelname2,x.laborlevelname3,x.laborlevelname4,x.laborlevelname5,x.applydate,case when x.timeinseconds=0 then 0 else ((x.wageamount)/(x.timeinseconds))*3600 end,e.basewagehourlyamt,
e.homelaborlevelnm1,e.homelaborlevelnm2,e.homelaborlevelnm3,e.homelaborlevelnm4,e.homelaborlevelnm5,t.startdtm,e.payrulename
order by x.personnum,x.applydate
and wil Gails, I took out the declare and just added her where statement that she provided.
NOTE: not sure if this matters but I am not 100% this is server 2005 but didnt think that would matter with just a sql statment....
January 4, 2008 at 11:41 am
Neither appear to be working.
Adams appears to return no data.. no errors, but no data
with GAils I put her where statement but it was erroring out on the DBO.
thoughts?
January 5, 2008 at 7:35 am
With mine, the error is because you didn't create the function DateMonthStarts. It's not an inbuilt SQL function. The contents of the function can be found on the page I linked to.
The only difference between Adam's and mine is that I suggested creating a udf so that you can use it in any query you need, while Adam's has the calculation in the stored proc itself.
Now, if you take the portion of the where that refers to dates (and x.applydate BETWEEN @PrevBOM AND @CurEOM and x.applydate<=getdate()) and run the query, how many rows do you get and in what range are the applydates?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply