| Author |
 Topic  |
|
|
Pinto
Aged Yak Warrior
United Kingdom
590 Posts |
 Posted - 01/18/2007 : 10:46:26
|
Here's my sp which doesn't do what I want 
I want it to only list records with LastDateIn 10 days prior to today's date.
CREATE Procedure [dbo].[spRMU_NoFilesBookedInByDay]
AS
SELECT Convert(nvarchar, LastDateIn,103) AS Expr1, COUNT(Status) AS Expr2
FROM tblFiles
where Convert(datetime, LastDateIn,103) < dateadd(day, -10, getdate())
GROUP BY Convert(nvarchar, LastDateIn,103)
ORDER BY Convert(nvarchar, LastDateIn,103) DESC
GO |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/18/2007 : 10:52:07
|
select dateadd(day, datediff(day, 0, getdate()), 0), count(*) from tblFiles
where lastdatein < dateadd(day, datediff(day, 10, getdate()), 0)
group by dateadd(day, datediff(day, 0, getdate()), 0)
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 01/18/2007 10:54:00 |
 |
|
|
Pinto
Aged Yak Warrior
United Kingdom
590 Posts |
Posted - 01/18/2007 : 11:01:55
|
I am getting the error below when I check the syntax of the sp
Error 164 : GROUP BY expressions must refer to column names that appear in the select list. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/18/2007 : 11:09:45
|
My bad
select dateadd(day, datediff(day, 0, lastdatein), 0), count(*) from tblFiles
where lastdatein < dateadd(day, datediff(day, 10, getdate()), 0)
group by dateadd(day, datediff(day, 0, lastdatein), 0)
Peter Larsson
Helsingborg, Sweden |
|
|
|
Pinto
Aged Yak Warrior
United Kingdom
590 Posts |
Posted - 01/18/2007 : 11:33:22
|
Thank you. I have amended your sql a bit as I what I wanted was the last 10 days from now - sorry I did not explain myself properly.
This is what I have now and it works fine.
CREATE Procedure [dbo].[spRMU_NoFilesBookedInByDay]
AS
select dateadd(day, datediff(day, 0, lastdatein), 0), count(*) from tblFiles
where lastdatein > dateadd(day, datediff(day, 10, getdate()), 0)
group by dateadd(day, datediff(day, 0, lastdatein), 0)
order by dateadd(day, datediff(day, 0, lastdatein), 0) desc
GO |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/18/2007 : 11:49:20
|
>= will the best choice.
Thank you.
Peter Larsson
Helsingborg, Sweden |
|
|
| |
Topic |
|
|
|
Using DateAdd and Convert
