I have a table of addresses for residents in a housing authority, and these
addresses may change so I need to find the first move in date and subtract it
from today's date to get the length of residency. Here is my query:
select distinct a1.HouseholdNbr,
(SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
a1.householdNbr = a2.householdNbr) as MoveInDate,
DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
from tblResidentAddresses a1
where a1.householdnbr < 99000
order by HouseholdNbr
The length of years residency is correct except for years that are 2004 or
2005. Here is a sample result:
MoveinDate Residency
01/13/20014
08/08/200412
02/21/19978
02/19/198817
07/12/200410
01/17/19978
06/22/199411
06/27/19978
05/24/20046
01/20/20014
07/12/20046
06/01/200424
02/20/200413
01/01/200512
11/18/19978
04/19/20014
08/25/19996
03/22/200429
12/01/199411
Any suggestions why 2004 and 2005 produce wrong results? Thanks
Hi
Posting ddl and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006. I believe the problem is related
to your function dbo.dateString, try adding it to the datediff MIN()
statement as using datetimes is fine:
CREATE TABLE tblResidentAddresses ( householdnbr int not null
identity(1,1), MoveinDate datetime not null)
INSERT INTO tblResidentAddresses ( MoveinDate )
SELECT '20010113'
UNION ALL SELECT '20040808'
UNION ALL SELECT '19970221'
UNION ALL SELECT '19880219'
UNION ALL SELECT '20040712'
UNION ALL SELECT '19970117'
UNION ALL SELECT '19940622'
UNION ALL SELECT '19970527'
UNION ALL SELECT '20040524'
UNION ALL SELECT '20010120'
UNION ALL SELECT '20040712'
UNION ALL SELECT '20040601'
UNION ALL SELECT '20040220'
UNION ALL SELECT '20050101'
UNION ALL SELECT '19971118'
UNION ALL SELECT '20010419'
UNION ALL SELECT '19990825'
UNION ALL SELECT '20040322'
UNION ALL SELECT '19941201'
SELECT DISTINCT a1.householdnbr,
CONVERT(char(10),(SELECT MIN(MoveInDate) FROM tblResidentAddresses a2 WHERE
a1.householdnbr = a2.householdnbr),110) AS MoveInDate,
DATEDIFF(yy,(SELECT MIN(MoveInDate) FROM tblResidentAddresses a3 WHERE
a1.householdnbr = a3.householdnbr), GETDATE()) AS Residency
FROM tblResidentAddresses a1
WHERE a1.householdnbr < 99000
ORDER BY a1.householdnbr
Seems fine:
householdnbr MoveInDate Residency
-- -- --
1 01-13-2001 4
2 08-08-2004 1
3 02-21-1997 8
4 02-19-1988 17
5 07-12-2004 1
6 01-17-1997 8
7 06-22-1994 11
8 05-27-1997 8
9 05-24-2004 1
10 01-20-2001 4
11 07-12-2004 1
12 06-01-2004 1
13 02-20-2004 1
14 01-01-2005 0
15 11-18-1997 8
16 04-19-2001 4
17 08-25-1999 6
18 03-22-2004 1
19 12-01-1994 11
(19 row(s) affected)
John
"DLS" wrote:
> I have a table of addresses for residents in a housing authority, and these
> addresses may change so I need to find the first move in date and subtract it
> from today's date to get the length of residency. Here is my query:
> select distinct a1.HouseholdNbr,
> (SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
> a1.householdNbr = a2.householdNbr) as MoveInDate,
> DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
> a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
> from tblResidentAddresses a1
> where a1.householdnbr < 99000
> order by HouseholdNbr
> The length of years residency is correct except for years that are 2004 or
> 2005. Here is a sample result:
> MoveinDate Residency
> 01/13/20014
> 08/08/200412
> 02/21/19978
> 02/19/198817
> 07/12/200410
> 01/17/19978
> 06/22/199411
> 06/27/19978
> 05/24/20046
> 01/20/20014
> 07/12/20046
> 06/01/200424
> 02/20/200413
> 01/01/200512
> 11/18/19978
> 04/19/20014
> 08/25/19996
> 03/22/200429
> 12/01/199411
> Any suggestions why 2004 and 2005 produce wrong results? Thanks
Showing posts with label odd. Show all posts
Showing posts with label odd. Show all posts
Sunday, February 19, 2012
DateDiff returns odd result
I have a table of addresses for residents in a housing authority, and these
addresses may change so I need to find the first move in date and subtract it
from today's date to get the length of residency. Here is my query:
select distinct a1.HouseholdNbr,
(SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
a1.householdNbr = a2.householdNbr) as MoveInDate,
DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
from tblResidentAddresses a1
where a1.householdnbr < 99000
order by HouseholdNbr
The length of years residency is correct except for years that are 2004 or
2005. Here is a sample result:
MoveinDate Residency
01/13/2001 4
08/08/2004 12
02/21/1997 8
02/19/1988 17
07/12/2004 10
01/17/1997 8
06/22/1994 11
06/27/1997 8
05/24/2004 6
01/20/2001 4
07/12/2004 6
06/01/2004 24
02/20/2004 13
01/01/2005 12
11/18/1997 8
04/19/2001 4
08/25/1999 6
03/22/2004 29
12/01/1994 11
Any suggestions why 2004 and 2005 produce wrong results? ThanksHi
Posting ddl and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006. I believe the problem is related
to your function dbo.dateString, try adding it to the datediff MIN()
statement as using datetimes is fine:
CREATE TABLE tblResidentAddresses ( householdnbr int not null
identity(1,1), MoveinDate datetime not null)
INSERT INTO tblResidentAddresses ( MoveinDate )
SELECT '20010113'
UNION ALL SELECT '20040808'
UNION ALL SELECT '19970221'
UNION ALL SELECT '19880219'
UNION ALL SELECT '20040712'
UNION ALL SELECT '19970117'
UNION ALL SELECT '19940622'
UNION ALL SELECT '19970527'
UNION ALL SELECT '20040524'
UNION ALL SELECT '20010120'
UNION ALL SELECT '20040712'
UNION ALL SELECT '20040601'
UNION ALL SELECT '20040220'
UNION ALL SELECT '20050101'
UNION ALL SELECT '19971118'
UNION ALL SELECT '20010419'
UNION ALL SELECT '19990825'
UNION ALL SELECT '20040322'
UNION ALL SELECT '19941201'
SELECT DISTINCT a1.householdnbr,
CONVERT(char(10),(SELECT MIN(MoveInDate) FROM tblResidentAddresses a2 WHERE
a1.householdnbr = a2.householdnbr),110) AS MoveInDate,
DATEDIFF(yy,(SELECT MIN(MoveInDate) FROM tblResidentAddresses a3 WHERE
a1.householdnbr = a3.householdnbr), GETDATE()) AS Residency
FROM tblResidentAddresses a1
WHERE a1.householdnbr < 99000
ORDER BY a1.householdnbr
Seems fine:
householdnbr MoveInDate Residency
-- -- --
1 01-13-2001 4
2 08-08-2004 1
3 02-21-1997 8
4 02-19-1988 17
5 07-12-2004 1
6 01-17-1997 8
7 06-22-1994 11
8 05-27-1997 8
9 05-24-2004 1
10 01-20-2001 4
11 07-12-2004 1
12 06-01-2004 1
13 02-20-2004 1
14 01-01-2005 0
15 11-18-1997 8
16 04-19-2001 4
17 08-25-1999 6
18 03-22-2004 1
19 12-01-1994 11
(19 row(s) affected)
John
"DLS" wrote:
> I have a table of addresses for residents in a housing authority, and these
> addresses may change so I need to find the first move in date and subtract it
> from today's date to get the length of residency. Here is my query:
> select distinct a1.HouseholdNbr,
> (SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
> a1.householdNbr = a2.householdNbr) as MoveInDate,
> DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
> a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
> from tblResidentAddresses a1
> where a1.householdnbr < 99000
> order by HouseholdNbr
> The length of years residency is correct except for years that are 2004 or
> 2005. Here is a sample result:
> MoveinDate Residency
> 01/13/2001 4
> 08/08/2004 12
> 02/21/1997 8
> 02/19/1988 17
> 07/12/2004 10
> 01/17/1997 8
> 06/22/1994 11
> 06/27/1997 8
> 05/24/2004 6
> 01/20/2001 4
> 07/12/2004 6
> 06/01/2004 24
> 02/20/2004 13
> 01/01/2005 12
> 11/18/1997 8
> 04/19/2001 4
> 08/25/1999 6
> 03/22/2004 29
> 12/01/1994 11
> Any suggestions why 2004 and 2005 produce wrong results? Thanks
addresses may change so I need to find the first move in date and subtract it
from today's date to get the length of residency. Here is my query:
select distinct a1.HouseholdNbr,
(SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
a1.householdNbr = a2.householdNbr) as MoveInDate,
DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
from tblResidentAddresses a1
where a1.householdnbr < 99000
order by HouseholdNbr
The length of years residency is correct except for years that are 2004 or
2005. Here is a sample result:
MoveinDate Residency
01/13/2001 4
08/08/2004 12
02/21/1997 8
02/19/1988 17
07/12/2004 10
01/17/1997 8
06/22/1994 11
06/27/1997 8
05/24/2004 6
01/20/2001 4
07/12/2004 6
06/01/2004 24
02/20/2004 13
01/01/2005 12
11/18/1997 8
04/19/2001 4
08/25/1999 6
03/22/2004 29
12/01/1994 11
Any suggestions why 2004 and 2005 produce wrong results? ThanksHi
Posting ddl and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006. I believe the problem is related
to your function dbo.dateString, try adding it to the datediff MIN()
statement as using datetimes is fine:
CREATE TABLE tblResidentAddresses ( householdnbr int not null
identity(1,1), MoveinDate datetime not null)
INSERT INTO tblResidentAddresses ( MoveinDate )
SELECT '20010113'
UNION ALL SELECT '20040808'
UNION ALL SELECT '19970221'
UNION ALL SELECT '19880219'
UNION ALL SELECT '20040712'
UNION ALL SELECT '19970117'
UNION ALL SELECT '19940622'
UNION ALL SELECT '19970527'
UNION ALL SELECT '20040524'
UNION ALL SELECT '20010120'
UNION ALL SELECT '20040712'
UNION ALL SELECT '20040601'
UNION ALL SELECT '20040220'
UNION ALL SELECT '20050101'
UNION ALL SELECT '19971118'
UNION ALL SELECT '20010419'
UNION ALL SELECT '19990825'
UNION ALL SELECT '20040322'
UNION ALL SELECT '19941201'
SELECT DISTINCT a1.householdnbr,
CONVERT(char(10),(SELECT MIN(MoveInDate) FROM tblResidentAddresses a2 WHERE
a1.householdnbr = a2.householdnbr),110) AS MoveInDate,
DATEDIFF(yy,(SELECT MIN(MoveInDate) FROM tblResidentAddresses a3 WHERE
a1.householdnbr = a3.householdnbr), GETDATE()) AS Residency
FROM tblResidentAddresses a1
WHERE a1.householdnbr < 99000
ORDER BY a1.householdnbr
Seems fine:
householdnbr MoveInDate Residency
-- -- --
1 01-13-2001 4
2 08-08-2004 1
3 02-21-1997 8
4 02-19-1988 17
5 07-12-2004 1
6 01-17-1997 8
7 06-22-1994 11
8 05-27-1997 8
9 05-24-2004 1
10 01-20-2001 4
11 07-12-2004 1
12 06-01-2004 1
13 02-20-2004 1
14 01-01-2005 0
15 11-18-1997 8
16 04-19-2001 4
17 08-25-1999 6
18 03-22-2004 1
19 12-01-1994 11
(19 row(s) affected)
John
"DLS" wrote:
> I have a table of addresses for residents in a housing authority, and these
> addresses may change so I need to find the first move in date and subtract it
> from today's date to get the length of residency. Here is my query:
> select distinct a1.HouseholdNbr,
> (SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
> a1.householdNbr = a2.householdNbr) as MoveInDate,
> DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
> a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
> from tblResidentAddresses a1
> where a1.householdnbr < 99000
> order by HouseholdNbr
> The length of years residency is correct except for years that are 2004 or
> 2005. Here is a sample result:
> MoveinDate Residency
> 01/13/2001 4
> 08/08/2004 12
> 02/21/1997 8
> 02/19/1988 17
> 07/12/2004 10
> 01/17/1997 8
> 06/22/1994 11
> 06/27/1997 8
> 05/24/2004 6
> 01/20/2001 4
> 07/12/2004 6
> 06/01/2004 24
> 02/20/2004 13
> 01/01/2005 12
> 11/18/1997 8
> 04/19/2001 4
> 08/25/1999 6
> 03/22/2004 29
> 12/01/1994 11
> Any suggestions why 2004 and 2005 produce wrong results? Thanks
DateDiff returns odd result
I have a table of addresses for residents in a housing authority, and these
addresses may change so I need to find the first move in date and subtract i
t
from today's date to get the length of residency. Here is my query:
select distinct a1.HouseholdNbr,
(SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
a1.householdNbr = a2.householdNbr) as MoveInDate,
DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
from tblResidentAddresses a1
where a1.householdnbr < 99000
order by HouseholdNbr
The length of years residency is correct except for years that are 2004 or
2005. Here is a sample result:
MoveinDate Residency
01/13/2001 4
08/08/2004 12
02/21/1997 8
02/19/1988 17
07/12/2004 10
01/17/1997 8
06/22/1994 11
06/27/1997 8
05/24/2004 6
01/20/2001 4
07/12/2004 6
06/01/2004 24
02/20/2004 13
01/01/2005 12
11/18/1997 8
04/19/2001 4
08/25/1999 6
03/22/2004 29
12/01/1994 11
Any suggestions why 2004 and 2005 produce wrong results? ThanksHi
Posting ddl and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006. I believe the problem is relate
d
to your function dbo.dateString, try adding it to the datediff MIN()
statement as using datetimes is fine:
CREATE TABLE tblResidentAddresses ( householdnbr int not null
identity(1,1), MoveinDate datetime not null)
INSERT INTO tblResidentAddresses ( MoveinDate )
SELECT '20010113'
UNION ALL SELECT '20040808'
UNION ALL SELECT '19970221'
UNION ALL SELECT '19880219'
UNION ALL SELECT '20040712'
UNION ALL SELECT '19970117'
UNION ALL SELECT '19940622'
UNION ALL SELECT '19970527'
UNION ALL SELECT '20040524'
UNION ALL SELECT '20010120'
UNION ALL SELECT '20040712'
UNION ALL SELECT '20040601'
UNION ALL SELECT '20040220'
UNION ALL SELECT '20050101'
UNION ALL SELECT '19971118'
UNION ALL SELECT '20010419'
UNION ALL SELECT '19990825'
UNION ALL SELECT '20040322'
UNION ALL SELECT '19941201'
SELECT DISTINCT a1.householdnbr,
CONVERT(char(10),(SELECT MIN(MoveInDate) FROM tblResidentAddresses a2 WHERE
a1.householdnbr = a2.householdnbr),110) AS MoveInDate,
DATEDIFF(yy,(SELECT MIN(MoveInDate) FROM tblResidentAddresses a3 WHERE
a1.householdnbr = a3.householdnbr), GETDATE()) AS Residency
FROM tblResidentAddresses a1
WHERE a1.householdnbr < 99000
ORDER BY a1.householdnbr
Seems fine:
householdnbr MoveInDate Residency
-- -- --
1 01-13-2001 4
2 08-08-2004 1
3 02-21-1997 8
4 02-19-1988 17
5 07-12-2004 1
6 01-17-1997 8
7 06-22-1994 11
8 05-27-1997 8
9 05-24-2004 1
10 01-20-2001 4
11 07-12-2004 1
12 06-01-2004 1
13 02-20-2004 1
14 01-01-2005 0
15 11-18-1997 8
16 04-19-2001 4
17 08-25-1999 6
18 03-22-2004 1
19 12-01-1994 11
(19 row(s) affected)
John
"DLS" wrote:
> I have a table of addresses for residents in a housing authority, and thes
e
> addresses may change so I need to find the first move in date and subtract
it
> from today's date to get the length of residency. Here is my query:
> select distinct a1.HouseholdNbr,
> (SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
> a1.householdNbr = a2.householdNbr) as MoveInDate,
> DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
> a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
> from tblResidentAddresses a1
> where a1.householdnbr < 99000
> order by HouseholdNbr
> The length of years residency is correct except for years that are 2004 or
> 2005. Here is a sample result:
> MoveinDate Residency
> 01/13/2001 4
> 08/08/2004 12
> 02/21/1997 8
> 02/19/1988 17
> 07/12/2004 10
> 01/17/1997 8
> 06/22/1994 11
> 06/27/1997 8
> 05/24/2004 6
> 01/20/2001 4
> 07/12/2004 6
> 06/01/2004 24
> 02/20/2004 13
> 01/01/2005 12
> 11/18/1997 8
> 04/19/2001 4
> 08/25/1999 6
> 03/22/2004 29
> 12/01/1994 11
> Any suggestions why 2004 and 2005 produce wrong results? Thanks
addresses may change so I need to find the first move in date and subtract i
t
from today's date to get the length of residency. Here is my query:
select distinct a1.HouseholdNbr,
(SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
a1.householdNbr = a2.householdNbr) as MoveInDate,
DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
from tblResidentAddresses a1
where a1.householdnbr < 99000
order by HouseholdNbr
The length of years residency is correct except for years that are 2004 or
2005. Here is a sample result:
MoveinDate Residency
01/13/2001 4
08/08/2004 12
02/21/1997 8
02/19/1988 17
07/12/2004 10
01/17/1997 8
06/22/1994 11
06/27/1997 8
05/24/2004 6
01/20/2001 4
07/12/2004 6
06/01/2004 24
02/20/2004 13
01/01/2005 12
11/18/1997 8
04/19/2001 4
08/25/1999 6
03/22/2004 29
12/01/1994 11
Any suggestions why 2004 and 2005 produce wrong results? ThanksHi
Posting ddl and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006. I believe the problem is relate
d
to your function dbo.dateString, try adding it to the datediff MIN()
statement as using datetimes is fine:
CREATE TABLE tblResidentAddresses ( householdnbr int not null
identity(1,1), MoveinDate datetime not null)
INSERT INTO tblResidentAddresses ( MoveinDate )
SELECT '20010113'
UNION ALL SELECT '20040808'
UNION ALL SELECT '19970221'
UNION ALL SELECT '19880219'
UNION ALL SELECT '20040712'
UNION ALL SELECT '19970117'
UNION ALL SELECT '19940622'
UNION ALL SELECT '19970527'
UNION ALL SELECT '20040524'
UNION ALL SELECT '20010120'
UNION ALL SELECT '20040712'
UNION ALL SELECT '20040601'
UNION ALL SELECT '20040220'
UNION ALL SELECT '20050101'
UNION ALL SELECT '19971118'
UNION ALL SELECT '20010419'
UNION ALL SELECT '19990825'
UNION ALL SELECT '20040322'
UNION ALL SELECT '19941201'
SELECT DISTINCT a1.householdnbr,
CONVERT(char(10),(SELECT MIN(MoveInDate) FROM tblResidentAddresses a2 WHERE
a1.householdnbr = a2.householdnbr),110) AS MoveInDate,
DATEDIFF(yy,(SELECT MIN(MoveInDate) FROM tblResidentAddresses a3 WHERE
a1.householdnbr = a3.householdnbr), GETDATE()) AS Residency
FROM tblResidentAddresses a1
WHERE a1.householdnbr < 99000
ORDER BY a1.householdnbr
Seems fine:
householdnbr MoveInDate Residency
-- -- --
1 01-13-2001 4
2 08-08-2004 1
3 02-21-1997 8
4 02-19-1988 17
5 07-12-2004 1
6 01-17-1997 8
7 06-22-1994 11
8 05-27-1997 8
9 05-24-2004 1
10 01-20-2001 4
11 07-12-2004 1
12 06-01-2004 1
13 02-20-2004 1
14 01-01-2005 0
15 11-18-1997 8
16 04-19-2001 4
17 08-25-1999 6
18 03-22-2004 1
19 12-01-1994 11
(19 row(s) affected)
John
"DLS" wrote:
> I have a table of addresses for residents in a housing authority, and thes
e
> addresses may change so I need to find the first move in date and subtract
it
> from today's date to get the length of residency. Here is my query:
> select distinct a1.HouseholdNbr,
> (SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
> a1.householdNbr = a2.householdNbr) as MoveInDate,
> DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
> a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
> from tblResidentAddresses a1
> where a1.householdnbr < 99000
> order by HouseholdNbr
> The length of years residency is correct except for years that are 2004 or
> 2005. Here is a sample result:
> MoveinDate Residency
> 01/13/2001 4
> 08/08/2004 12
> 02/21/1997 8
> 02/19/1988 17
> 07/12/2004 10
> 01/17/1997 8
> 06/22/1994 11
> 06/27/1997 8
> 05/24/2004 6
> 01/20/2001 4
> 07/12/2004 6
> 06/01/2004 24
> 02/20/2004 13
> 01/01/2005 12
> 11/18/1997 8
> 04/19/2001 4
> 08/25/1999 6
> 03/22/2004 29
> 12/01/1994 11
> Any suggestions why 2004 and 2005 produce wrong results? Thanks
Tuesday, February 14, 2012
DATEADD returning odd count of records
Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qhDATEADD(m, -18, getdate()) is giving you 26th Jan 2004. That means
everything older than or equal to 26th Jan 2004. But your second query is
looking for data between 1st and 31st of Jan, which obviously is going to
have 5 days worth of additional data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1122371409.746888.44830@.g49g2000cwa.googlegroups.com...
Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qh|||Hello, Scott
You should notice the fact that "DATEADD(m, -18, getdate())"
returns January 26, 2004 and in your second query you are
comparing with January 31, 2004.
If you want to obtain the last day of the current month,
you can use the following:
SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
So your query might be:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
Razvan|||Hi Razvan,
Cheers for the reply, your solution sorted it. I didn't expect the
count to be over 200k for the days 26-31st Jan. I think it's myself
who doesn't know his own data!
;o)
Thanks
Scott
Razvan Socol wrote:
> Hello, Scott
> You should notice the fact that "DATEADD(m, -18, getdate())"
> returns January 26, 2004 and in your second query you are
> comparing with January 31, 2004.
> If you want to obtain the last day of the current month,
> you can use the following:
> SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
> So your query might be:
> select count(*) from MyTable
> where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
> Razvan
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qhDATEADD(m, -18, getdate()) is giving you 26th Jan 2004. That means
everything older than or equal to 26th Jan 2004. But your second query is
looking for data between 1st and 31st of Jan, which obviously is going to
have 5 days worth of additional data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1122371409.746888.44830@.g49g2000cwa.googlegroups.com...
Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qh|||Hello, Scott
You should notice the fact that "DATEADD(m, -18, getdate())"
returns January 26, 2004 and in your second query you are
comparing with January 31, 2004.
If you want to obtain the last day of the current month,
you can use the following:
SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
So your query might be:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
Razvan|||Hi Razvan,
Cheers for the reply, your solution sorted it. I didn't expect the
count to be over 200k for the days 26-31st Jan. I think it's myself
who doesn't know his own data!
;o)
Thanks
Scott
Razvan Socol wrote:
> Hello, Scott
> You should notice the fact that "DATEADD(m, -18, getdate())"
> returns January 26, 2004 and in your second query you are
> comparing with January 31, 2004.
> If you want to obtain the last day of the current month,
> you can use the following:
> SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
> So your query might be:
> select count(*) from MyTable
> where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
> Razvan
Subscribe to:
Posts (Atom)