Need Help with the view - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Help with the view

Author

Topic

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2007-02-02 : 20:50:38

Hey Guys,
I am trying to get 5 days back date and in my table the following 3 records are there but my query is not pulling. Could you please tell me what is wrong with my query? Thanks

select * from
dbo.tblTransaction
WHERE Performance_dm >= dateadd(day, datediff(day, 0, getdate()), - 5) AND Performance_dm < dateadd(day, datediff(day, 0, getdate()), -4)
order by Performance_dm

These are the records in the table but it is not pulling. How should i pull these records please?
2007-01-28 00:01:00.000
2007-01-28 00:01:00.000
2007-01-28 00:01:00.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-02 : 21:00:19

Your query is correct. It should return the 3 records


declare @tblTransaction table
(
	Performance_dm	datetime
)

insert into @tblTransaction 
select	'2007-01-28 00:01:00.000'	union all
select	'2007-01-28 00:01:00.000'	union all
select	'2007-01-28 00:01:00.000'

select *
from	@tblTransaction
where	Performance_dm >= dateadd(day, datediff(day, 0, '20070202'), - 5)
and	Performance_dm <  dateadd(day, datediff(day, 0, '20070202'), - 4)

/*

Performance_dm                                         
------------------------------------------------------ 
2007-01-28 00:01:00.000
2007-01-28 00:01:00.000
2007-01-28 00:01:00.000
*/

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2007-02-03 : 00:03:47

Hello,
First of all i would like to thank you.Yes it did work now but in my office it didn't work because Performance_dm in my database is storing GMT date so that is why i guess it was not pulling.
Now my question is i want to make sure that it gives right records so should i use some variables storing time ='00.00.00.000' and 23.59.59.999 or no? If yes then can you please guide me in the right path how should i create variables for RateReview and Performance Column in a view. Your answer will be greatly appreciated. Thanks

CREATE VIEW dbo.viewTransactionSolicitation_Profile
AS
SELECT CAST(a.Transaction_GUID AS varchar(36)) AS Transaction_GUID, CAST(a.Customer_GUID AS varchar(36)) AS Customer_GUID, a.Film_id AS MovieId,
a.First_nm AS FirstName, a.Last_nm AS LastName, a.Email_nm AS EmailAddress, a.Title AS MovieName, a.ImagePath_tx AS MoviePosterUrl,
a.Email_nm
FROM (SELECT MIN(CAST(customer_guid AS varchar(36))) AS Customer_GUID, Transaction_GUID
FROM tblTransactionSolicitation(nolock)
WHERE RateReview_dm >= dateadd(day, datediff(day, 0, getdate()), - 1) AND RateReview_dm < dateadd(day, datediff(day, 0, getdate()), 0) AND
Terminate_dm IS NULL AND customer_guid NOT IN
(SELECT CAST(customer_guid AS varchar(36)) AS Customer_GUID
FROM tblTransactionSolicitation(nolock)
WHERE Performance_dm < dateadd(day, datediff(day, 0, getdate()), - 5))
GROUP BY transaction_guid, customer_guid) z INNER JOIN
dbo.tblTransactionSolicitation a ON z.Transaction_GUID = a.Transaction_GUID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources