Hi,
I am working on a query that was written in MS Access by someone else. I am trying to convert that query to SQL Server as we will be running this query in sql server.
There are few functions in the query like “FIRST”,”LAST” AND “RIGHT” that is only recognized by MS Access and not SQL Server.
We were trying to grab the first and last record in the group by clause for the field mentioned in the query below. How can we achieve the same thing in SQL server. Are there are any alternative functions in sql server that does the same thing like “FIRST”,”LAST” AND “RIGHT” in MS Access query below.
Also, can we have the joins in sql server the same way it is joined MS Access.
Will appreciate any help.
Below is the query. I have cut it short to accomodate in the window.
SELECT
dbo_Agents.Agent,
dbo_Agents.Agent, dbo_Agents.AgentName,
dbo_SirconAgreement.ManagerLevelID,
dbo_SirconFirm.NationalProducerID,
First(DateDiff("d",dbo_SirconAgreement.begindate,Now())) AS Days,
First(DateDiff("d",dbo_SirconIndividual.filedate,Now())) AS IndividualDays,
First(DateDiff("d",dbo_SirconFirm.filedate,Now())) AS FirmDays,
First(dbo_SirconAgreement.FileDate) AS FirstOfFileDate,
Last(dbo_SirconIndividual.Action) AS LastOfAction1
FROM
((dbo_SirconAgreement RIGHT JOIN (dbo_Agents LEFT JOIN dbo_SirconIndividual ON dbo_Agents.Agent = dbo_SirconIndividual.NationalProducerID)
ON dbo_SirconAgreement.ID = dbo_Agents.Agent)
LEFT JOIN dbo_SirconFirm ON dbo_SirconAgreement.ExternalID = dbo_SirconFirm.NationalProducerID)
LEFT JOIN dbo_SirconAppointment ON dbo_SirconAgreement.KeyInternalID = dbo_SirconAppointment.KeyInternalID
WHERE (((Right([dbo_Agents].[Agent],3))<>"DEL"))
GROUP BY
dbo_Agents.Agent,
dbo_Agents.AgentName,
dbo_SirconAgreement.ManagerLevelID,
dbo_SirconFirm.NationalProducerID
I am working on a query that was written in MS Access by someone else. I am trying to convert that query to SQL Server as we will be running this query in sql server.
There are few functions in the query like “FIRST”,”LAST” AND “RIGHT” that is only recognized by MS Access and not SQL Server.
We were trying to grab the first and last record in the group by clause for the field mentioned in the query below. How can we achieve the same thing in SQL server. Are there are any alternative functions in sql server that does the same thing like “FIRST”,”LAST” AND “RIGHT” in MS Access query below.
Also, can we have the joins in sql server the same way it is joined MS Access.
Will appreciate any help.
Below is the query. I have cut it short to accomodate in the window.
SELECT
dbo_Agents.Agent,
dbo_Agents.Agent, dbo_Agents.AgentName,
dbo_SirconAgreement.ManagerLevelID,
dbo_SirconFirm.NationalProducerID,
First(DateDiff("d",dbo_SirconAgreement.begindate,Now())) AS Days,
First(DateDiff("d",dbo_SirconIndividual.filedate,Now())) AS IndividualDays,
First(DateDiff("d",dbo_SirconFirm.filedate,Now())) AS FirmDays,
First(dbo_SirconAgreement.FileDate) AS FirstOfFileDate,
Last(dbo_SirconIndividual.Action) AS LastOfAction1
FROM
((dbo_SirconAgreement RIGHT JOIN (dbo_Agents LEFT JOIN dbo_SirconIndividual ON dbo_Agents.Agent = dbo_SirconIndividual.NationalProducerID)
ON dbo_SirconAgreement.ID = dbo_Agents.Agent)
LEFT JOIN dbo_SirconFirm ON dbo_SirconAgreement.ExternalID = dbo_SirconFirm.NationalProducerID)
LEFT JOIN dbo_SirconAppointment ON dbo_SirconAgreement.KeyInternalID = dbo_SirconAppointment.KeyInternalID
WHERE (((Right([dbo_Agents].[Agent],3))<>"DEL"))
GROUP BY
dbo_Agents.Agent,
dbo_Agents.AgentName,
dbo_SirconAgreement.ManagerLevelID,
dbo_SirconFirm.NationalProducerID