Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ramsfield
Starting Member
9 Posts |
 Posted - 2006-01-13 : 13:22:28
|
| 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? |
|
|
ramsfield
Starting Member
9 Posts |
Posted - 2006-01-13 : 13:34:26
|
| 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. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Date problem in View