| Author |
 Topic  |
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
 Posted - 11/18/2012 : 18:56:41
|
Hi
In a query to retreive all records assigned to a person in that particular month i see that my query is not returning few records because it has a different time stamp
My Query
SELECT SR.roc_id, convert(varchar(20),SR.prodmth,101) as prodmth, RD.rptdesctext,C.first_name + space(1) + C.Last_name as AnalystName,SR.opa_id FROM RENEWALS SR INNER JOIN REPORT RD ON SR.rptdesc = RD.rptdesc Inner join Contact C on SR.opa_id = C.contact_id
WHERE (SR.opa_id = 1 and YEAR(SR.prodmth) = YEAR(getdate()) AND MONTH(SR.prodmth) = MONTH(getdate()))
Query not returning the below record though it is for the current month
148857 2012-11-20 00:00:00.000
Query returning the below records
148858 2012-11-20 12:00:00.000
148859 2012-11-20 12:00:00.000
148860 2012-11-20 12:00:00.000
148861 2012-11-20 12:00:00.000
148862 2012-11-20 12:00:00.000
How do i modify my query so that it does not take the timestamp into consideration.All it should take is the month and year
Thanks
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/18/2012 : 19:15:13
|
| Have you tried with DATEPART Function? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 11/19/2012 : 01:29:38
|
just use condition like
...
SR.prodmth>=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND SR.prodmth < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
..
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 11/19/2012 : 08:55:57
|
I have not used Datepart function.How do i use it herequote:
Originally posted by sodeep
Have you tried with DATEPART Function?
|
|
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 11/19/2012 : 09:00:32
|
Hi Visakh
i changed my query like below and still does not get all records
SELECT SR.roc_id, convert(varchar(20),SR.prodmth,101) as prodmth, RD.rptdesctext,C.first_name + space(1) + C.Last_name as AnalystName,SR.opa_id FROM RENEWALS SR INNER JOIN REPORT RD ON SR.rptdesc = RD.rptdesc Inner join Contact C on SR.opa_id = C.contact_id
WHERE (SR.opa_id = 1 and SR.prodmth>=DATEADD(mm,DATEDIFF(year,0,GETDATE()),0)
AND SR.prodmth < DATEADD(year,DATEDIFF(year,0,GETDATE())+1,0) AND SR.prodmth>=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND SR.prodmth < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))
Thanks
quote:
Originally posted by visakh16
just use condition like
...
SR.prodmth>=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND SR.prodmth < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
..
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 09:09:56
|
Jim, your original query and the query Visakh posted should have worked and returned the row where prodmth = '2012-11-20 00:00:00.000'. If it is not returning that, possible reasons are:
a) that row is eliminated due to other filters in the where clause or join conditions - for example, check if that row has an opa_id other than 1, or whether rptdesc is null for that row.
b) if the data type of that column is not date time, it can produce unexpected results. You can check the data type using the following query:SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE COLUMN_NAME = 'prodmth' AND TABLE_NAME = 'RENEWALS'; |
|
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 11/19/2012 : 09:27:05
|
Thank You for directing me in the right direction.I see that rptdesc is NULL and this happening after an update query
quote:
Originally posted by sunitabeck
Jim, your original query and the query Visakh posted should have worked and returned the row where prodmth = '2012-11-20 00:00:00.000'. If it is not returning that, possible reasons are:
a) that row is eliminated due to other filters in the where clause or join conditions - for example, check if that row has an opa_id other than 1, or whether rptdesc is null for that row.
b) if the data type of that column is not date time, it can produce unexpected results. You can check the data type using the following query:SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE COLUMN_NAME = 'prodmth' AND TABLE_NAME = 'RENEWALS';
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 09:34:54
|
You are quite welcome :)
Couple of comments, once you work out the reasons for the null join:
1. The query Visakh posted can be more efficient than the one you were using. The reason is that his query avoids the per-row function evaluation.
2. Usually people recommend that you don't join on columns such as description (which can be less than precise). I am only guessing from the column name ("rptdesc") that it is some sort of description. If you have a description key or something of that nature, it would be better to join on such key. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 11/20/2012 : 01:44:53
|
quote:
Originally posted by jim_jim
Hi Visakh
i changed my query like below and still does not get all records
SELECT SR.roc_id, convert(varchar(20),SR.prodmth,101) as prodmth, RD.rptdesctext,C.first_name + space(1) + C.Last_name as AnalystName,SR.opa_id FROM RENEWALS SR INNER JOIN REPORT RD ON SR.rptdesc = RD.rptdesc Inner join Contact C on SR.opa_id = C.contact_id
WHERE (SR.opa_id = 1 and SR.prodmth>=DATEADD(mm,DATEDIFF(year,0,GETDATE()),0)
AND SR.prodmth < DATEADD(year,DATEDIFF(year,0,GETDATE())+1,0) AND SR.prodmth>=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND SR.prodmth < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))
Thanks
quote:
Originally posted by visakh16
just use condition like
...
SR.prodmth>=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND SR.prodmth < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
..
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
the year check is redundant here as you're already having a more restrictive filter based on month
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
unable to capture few records because of timestamp
