SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 unable to capture few records because of timestamp
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 11/18/2012 :  18:56:41  Show Profile  Reply with Quote
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
7174 Posts

Posted - 11/18/2012 :  19:15:13  Show Profile  Reply with Quote
Have you tried with DATEPART Function?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/19/2012 :  01:29:38  Show Profile  Reply with Quote
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/

Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 11/19/2012 :  08:55:57  Show Profile  Reply with Quote
I have not used Datepart function.How do i use it here
quote:
Originally posted by sodeep

Have you tried with DATEPART Function?

Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 11/19/2012 :  09:00:32  Show Profile  Reply with Quote
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/



Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  09:09:56  Show Profile  Reply with Quote
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';
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 11/19/2012 :  09:27:05  Show Profile  Reply with Quote
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';


Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  09:34:54  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/20/2012 :  01:44:53  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000