Select Statement - 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

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-07-26 : 06:41:36

Hi,

This is a select statement presented from access database please could you convert it to sql server 2005 standards . Thanks

SELECT tblProjectScheduleEv.ProjectPracticeID
FROM (tblProjects INNER JOIN tblProjectPractices ON tblProjects.ProjectID = tblProjectPractices.ProjectID) INNER JOIN tblProjectScheduleEv ON tblProjectPractices.ProjectPracticeID = tblProjectScheduleEv.ProjectPracticeID
WHERE (((tblProjectScheduleEv.ProjectPracticeID) Not In (SELECT tblProjectScheduleEv.ProjectPracticeID
FROM tblProjects INNER JOIN (tblProjectPractices INNER JOIN tblProjectScheduleEv ON tblProjectPractices.ProjectPracticeID = tblProjectScheduleEv.ProjectPracticeID) ON tblProjects.ProjectID = tblProjectPractices.ProjectID
WHERE (((tblProjects.ProjectStatus)=0) AND ((tblProjectScheduleEv.EvBFDate)>Date()));)) AND ((tblProjects.ProjectStatus)=0) AND ((tblProjectScheduleEv.EvBFDate)<=Date()));

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 06:46:11

I dont see anything that can be changed except that you have to change Date() to Getdate().

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-07-26 : 06:50:43

Thanks, is this code below fine;

SELECT dbo.tblProjectScheduleEv.ProjectPracticeID
FROM dbo.tblProjects INNER JOIN
dbo.tblProjectPractices ON dbo.tblProjects.ProjectID = dbo.tblProjectPractices.ProjectID INNER JOIN
dbo.tblProjectScheduleEv ON dbo.tblProjectPractices.ProjectPracticeID = dbo.tblProjectScheduleEv.ProjectPracticeID
WHERE (dbo.tblProjectScheduleEv.ProjectPracticeID NOT IN
(SELECT tblProjectScheduleEv_1.ProjectPracticeID
FROM dbo.tblProjects AS tblProjects_1 INNER JOIN
dbo.tblProjectPractices AS tblProjectPractices_1 INNER JOIN
dbo.tblProjectScheduleEv AS tblProjectScheduleEv_1 ON
tblProjectPractices_1.ProjectPracticeID = tblProjectScheduleEv_1.ProjectPracticeID ON
tblProjects_1.ProjectID = tblProjectPractices_1.ProjectID
WHERE (tblProjects_1.ProjectStatus = '0') AND (tblProjectScheduleEv_1.EvBFDate > GETDATE()))) AND (dbo.tblProjects.ProjectStatus = '0')
AND (dbo.tblProjectScheduleEv.EvBFDate <= GETDATE())

Why does it generate - tblProjectScheduleEv_1, tblProjects_1, tblProjects_1. Does it mean the query still is ok?

thanks for any response

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 06:55:42

What do you mean by

"generate - tblProjectScheduleEv_1, tblProjects_1, tblProjects_1'

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-07-26 : 07:04:17

when i run in query it never had the "_1" but when I saved it as a view and excuted the select statement it showed the _1 but worked ok ..

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 07:19:01

quote:
Originally posted by dr223

when i run in query it never had the "_1" but when I saved it as a view and excuted the select statement it showed the _1 but worked ok ..

I have no clue.
But one more thing.If my guess is right the Access DATE() function will return only the date while Getdate()in SQL will return the date part along with time.
So replace your getdate() with this
dateadd(dd,datediff(dd,0,getdate()),0) so that only the date part comes into picture and is compared.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-07-26 : 08:02:13

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources