Need help porting this query over to SQL Server

  • Thread starter Thread starter Drew
  • Start date Start date
D

Drew

I have an access query for a report and I need to make it into a view for
SQL Server. Here is the Access query,

SELECT CliEventTable.RegNo, CliCoreTable.CliDOB, CliCoreTable.CliSex,
CliEventTable.EventDate, Format([EventDate],"w") AS [Day], "Qtr" &
Format([EventDate],"q"", ""yyyy") AS Quarter, CliEventTable.EventEvent,
CliEventTable.EventSubcategory, CliEventTable.EventLocation,
CliEventTable.EventIllnessInjury, CliEventTable.Mobility,
CliEventTable.FallPrecaution, CliEventTable.RepeatFaller
FROM CliEventTable INNER JOIN CliCoreTable ON CliEventTable.RegNo =
CliCoreTable.RegNo
WHERE (((CliEventTable.EventEvent)="Aggressive Act"));

This query uses the FORMAT command 2 times, one for finding the day of the
week, the other for making a string with the quarter. I have gotten
everything to work except building the quarter string. How can I make a
string in SQL Server. It needs to be like this, Qtr1, 2004. Here is my SQL
query,

SELECT dbo.Events.RegNo, CliCore.dbo.tblClients.CliDOB,
CliCore.dbo.tblClients.CliSex, dbo.Events.EventDate, dbo.Events.EventEvent,
DATEPART(dw, dbo.Events.EventDate) AS [Day], DATEPART(q,
dbo.Events.EventDate) AS Quarter, dbo.Events.EventSubcategory,
dbo.Events.EventLocation,
dbo.Events.EventIllnessInjury, dbo.Events.Mobility,
dbo.Events.FallPrecaution, dbo.Events.RepeatFaller
FROM dbo.Events INNER JOIN
CliCore.dbo.tblClients ON dbo.Events.RegNo =
CliCore.dbo.tblClients.RegNo
WHERE (dbo.Events.EventEvent = 2)

Thanks,
Drew Laing
 
Enclose your string constant in single quotes and use the operator + :

.... 'Qtr' + DATEPART(q, dbo.Events.EventDate) AS Quarter ...

On SQL-Server, the single quote is used as the string delimiter, not the
double quote. However, with the correct option set for SQL-Server, the
double quote can also be used (but not in all occasions, so it is a moving
target). The & operator for string concatenation is also replaced with the
+ operator.

S. L.
 
Thanks! My other problem was that 'Qtr' is a string and
DATEPART(q,dbo.Events.EventDate) is numeric, so I used CAST to convert the
date to a string and then cocatenated them.

Thanks!
Drew

Sylvain Lafontaine said:
Enclose your string constant in single quotes and use the operator + :

.... 'Qtr' + DATEPART(q, dbo.Events.EventDate) AS Quarter ...

On SQL-Server, the single quote is used as the string delimiter, not the
double quote. However, with the correct option set for SQL-Server, the
double quote can also be used (but not in all occasions, so it is a moving
target). The & operator for string concatenation is also replaced with
the + operator.

S. L.

Drew said:
I have an access query for a report and I need to make it into a view for
SQL Server. Here is the Access query,

SELECT CliEventTable.RegNo, CliCoreTable.CliDOB, CliCoreTable.CliSex,
CliEventTable.EventDate, Format([EventDate],"w") AS [Day], "Qtr" &
Format([EventDate],"q"", ""yyyy") AS Quarter, CliEventTable.EventEvent,
CliEventTable.EventSubcategory, CliEventTable.EventLocation,
CliEventTable.EventIllnessInjury, CliEventTable.Mobility,
CliEventTable.FallPrecaution, CliEventTable.RepeatFaller
FROM CliEventTable INNER JOIN CliCoreTable ON CliEventTable.RegNo =
CliCoreTable.RegNo
WHERE (((CliEventTable.EventEvent)="Aggressive Act"));

This query uses the FORMAT command 2 times, one for finding the day of
the week, the other for making a string with the quarter. I have gotten
everything to work except building the quarter string. How can I make a
string in SQL Server. It needs to be like this, Qtr1, 2004. Here is my
SQL query,

SELECT dbo.Events.RegNo, CliCore.dbo.tblClients.CliDOB,
CliCore.dbo.tblClients.CliSex, dbo.Events.EventDate,
dbo.Events.EventEvent, DATEPART(dw, dbo.Events.EventDate) AS [Day],
DATEPART(q, dbo.Events.EventDate) AS Quarter,
dbo.Events.EventSubcategory, dbo.Events.EventLocation,
dbo.Events.EventIllnessInjury, dbo.Events.Mobility,
dbo.Events.FallPrecaution, dbo.Events.RepeatFaller
FROM dbo.Events INNER JOIN
CliCore.dbo.tblClients ON dbo.Events.RegNo =
CliCore.dbo.tblClients.RegNo
WHERE (dbo.Events.EventEvent = 2)

Thanks,
Drew Laing
Click to expand...
Click to expand...
 
Back
Top