Date problem in View

[ramsfield]

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?

 

[SQLSister]

I'd probably add a case statement to handle the case of Jan.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[ramsfield]

Could you provide an example? I am not real familiar with cases

 

[SQLDenis]

when i run this select DATEADD(dd, 15, DATEADD(mm, - 1, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 121)
+ '01'))) , DATEADD(dd, 14, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 121) + '01'))
I get back
2005-12-16 00:00:00.000,2006-01-15 00:00:00.000
This seems correct right?
Exept it's from the 16th of last month not the 15th

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ramsfield]

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.

Thanks for everyones help