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

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-02-18 : 11:02:50

Hallo,

Please could anyone convert this select query which was written in access database to microsoft sql standard. Considering all the required tables and queries are available.



SELECT tblProjectScheduleEv.ProjectPracticeID, Format([tblprojectPractices].[PracticeGPID],"000000") 
AS GPID, tblODSPractices.MainContact, Format([EvBFDate],"d mmm yy") AS 
[BF Date], lkupEvTypes.EvType, Format([RemLetterGenDate],"d mmm yy") AS [Rem Lttr], tblProjectScheduleEv.EvAccount AS Comment
FROM tblODSPractices INNER JOIN (qryRecentScheduledEvents INNER JOIN 
((tblProjectScheduleEv INNER JOIN tblProjectPractices ON 
tblProjectScheduleEv.ProjectPracticeID = tblProjectPractices.ProjectPracticeID) 
INNER JOIN lkupEvTypes ON tblProjectScheduleEv.EvTypeID = lkupEvTypes.EvTypeID) ON 
(qryRecentScheduledEvents.ProjectPracticeID = 
tblProjectScheduleEv.ProjectPracticeID) AND (qryRecentScheduledEvents.MaxOfEvBFDate = tblProjectScheduleEv.EvBFDate)) ON 
tblODSPractices.PracticeEID = tblProjectPractices.PracticeEID
WHERE (((tblProjectScheduleEv.EvBFDate)<=Date()) AND ((tblProjectPractices.ProjectID)=[forms]![frmNoticeBoard]![lstProjects]))
ORDER BY Format([tblprojectPractices].[PracticeGPID],"000000");

Thank you very much

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-18 : 11:10:48

Something like this..

SELECT   tblProjectScheduleEv.ProjectPracticeID, 
         RIGHT ('000000'+ CAST(dbo.tblProjectPractices.PracticeGPID AS varchar(10)),6) AS GPID, 
         tblODSPractices.MainContact, 
         convert(varchar(20),[EvBFDate],5)                      AS [BF Date], 
         lkupEvTypes.EvType, 
         convert(varchar(20),[RemLetterGenDate],5)             AS [Rem Lttr], 
         tblProjectScheduleEv.EvAccount                        AS Comment 
FROM     tblODSPractices 
         INNER JOIN (qryRecentScheduledEvents 
                     INNER JOIN ((tblProjectScheduleEv 
                                  INNER JOIN tblProjectPractices 
                                    ON tblProjectScheduleEv.ProjectPracticeID = tblProjectPractices.ProjectPracticeID) 
                                 INNER JOIN lkupEvTypes 
                                   ON tblProjectScheduleEv.EvTypeID = lkupEvTypes.EvTypeID) 
                       ON (qryRecentScheduledEvents.ProjectPracticeID = tblProjectScheduleEv.ProjectPracticeID) 
                          AND (qryRecentScheduledEvents.MaxOfEvBFDate = tblProjectScheduleEv.EvBFDate)) 
           ON tblODSPractices.PracticeEID = tblProjectPractices.PracticeEID 
WHERE    (((tblProjectScheduleEv.EvBFDate) <= getdate()) 
          AND ((tblProjectPractices.ProjectID) = [forms]![frmNoticeBoard]![lstProjects])) 
ORDER BY GPID

I'm not sure what [forms]![frmNoticeBoard]![lstProjects]...you need to take care of that part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 11:12:35

quote:

Originally posted by vijayisonly

Something like this..

SELECT   tblProjectScheduleEv.ProjectPracticeID, 
         RIGHT ('000000'+ CAST(dbo.tblProjectPractices.PracticeGPID AS varchar(10)),6) AS GPID, 
         tblODSPractices.MainContact, 
         convert(varchar(20),[EvBFDate],5)                      AS [BF Date], 
         lkupEvTypes.EvType, 
         convert(varchar(20),[RemLetterGenDate],5)             AS [Rem Lttr], 
         tblProjectScheduleEv.EvAccount                        AS Comment 
FROM     tblODSPractices 
         INNER JOIN (qryRecentScheduledEvents 
                     INNER JOIN ((tblProjectScheduleEv 
                                  INNER JOIN tblProjectPractices 
                                    ON tblProjectScheduleEv.ProjectPracticeID = tblProjectPractices.ProjectPracticeID) 
                                 INNER JOIN lkupEvTypes 
                                   ON tblProjectScheduleEv.EvTypeID = lkupEvTypes.EvTypeID) 
                       ON (qryRecentScheduledEvents.ProjectPracticeID = tblProjectScheduleEv.ProjectPracticeID) 
                          AND (qryRecentScheduledEvents.MaxOfEvBFDate = tblProjectScheduleEv.EvBFDate)) 
           ON tblODSPractices.PracticeEID = tblProjectPractices.PracticeEID 
WHERE    (((tblProjectScheduleEv.EvBFDate) <= getdate()) 
          AND ((tblProjectPractices.ProjectID) = [forms]![frmNoticeBoard]![lstProjects])) 
ORDER BY GPID

I'm not sure what [forms]![frmNoticeBoard]![lstProjects]...you need to take care of that part.

I think its parameter that gets passed from form

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-02-18 : 11:25:15

Thanks you all, worked well

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 11:31:06

gr8

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-02-18 : 11:33:23

the lstprojects onclick event in access had the folowing select statement

SELECT tblProjects.ProjectID, [SEAGProtocol] & " - " & [ProjectDesc] AS ProjectName FROM tblProjects WHERE (((tblProjects.ProjectStatus)=0)) GROUP BY tblProjects.ProjectID, [SEAGProtocol] & " - " & [ProjectDesc] ORDER BY [SEAGProtocol] & " - " & [ProjectDesc];

Again considering that we have all the tables as shown above. Please can anyone change this for me in sql and then I'll call it on click event in my form in VB.net

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-18 : 11:36:03

replace "&" with "+" and double quotes with single quote.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-02-18 : 12:19:47

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-18 : 12:21:44

You're welcome.