January 13, 2006 at 11:24 am
I have the following query(view), the problem is during the month of January it does not return anything for January, just December. This only occurs during January.
It is suppose to return the from the 15th of the previous month to the 14th of the current month.
SELECT TOP 100 PERCENT dbo.vw_employee_days.EmpCode, dbo.vw_employee_days.calendar_days, SUM(dbo.tblTime.Hours) AS Sum_of_hours
FROM dbo.vw_employee_days LEFT OUTER JOIN
dbo.tblTime ON dbo.vw_employee_days.EmpCode = dbo.tblTime.EmpCode AND dbo.vw_employee_days.calendar_days = dbo.tblTime.[Date]
WHERE (dbo.vw_employee_days.calendar_days BETWEEN DATEADD(dd, 15, DATEADD(mm, - 1, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 121)
+ '01'))) AND DATEADD(dd, 14, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 121) + '01')))
GROUP BY dbo.vw_employee_days.EmpCode, dbo.vw_employee_days.calendar_days
HAVING (dbo.vw_employee_days.EmpCode = 'ARC100')
ORDER BY dbo.vw_employee_days.calendar_days
Any ideas to make this work for all month, including when the previous month is in a prior year?
January 13, 2006 at 11:30 am
What is dbo.vw_employee_days.calendar_days ?
We don't have the view definition, and it's tough to know what to make of a WHERE that appears to compare something that is a number of days? with a true date.
January 13, 2006 at 11:34 am
After further review I realized the problem is caused by certain dates missing from the dbo.tblTime.[Date] table.
This was a mistake on my part. The statement works as expected...as long as all the related tables are populated correctly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply