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 |
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2012-11-18 : 18:56:41
|
HiIn 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 stampMy 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 month148857 2012-11-20 00:00:00.000Query returning the below records 148858 2012-11-20 12:00:00.000148859 2012-11-20 12:00:00.000148860 2012-11-20 12:00:00.000148861 2012-11-20 12:00:00.000148862 2012-11-20 12:00:00.000How do i modify my query so that it does not take the timestamp into consideration.All it should take is the month and yearThanks |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-18 : 19:15:13
|
Have you tried with DATEPART Function? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-19 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2012-11-19 : 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
306 Posts |
Posted - 2012-11-19 : 09:00:32
|
Hi Visakhi changed my query like below and still does not get all recordsSELECT 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)) Thanksquote: 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 MVPhttp://visakhm.blogspot.com/
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-19 : 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
306 Posts |
Posted - 2012-11-19 : 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-19 : 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
52326 Posts |
Posted - 2012-11-20 : 01:44:53
|
quote: Originally posted by jim_jim Hi Visakhi changed my query like below and still does not get all recordsSELECT 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)) Thanksquote: 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 MVPhttp://visakhm.blogspot.com/
the year check is redundant here as you're already having a more restrictive filter based on month------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|