Mar 12 2024 03:13 PM
I have a query in MS Access that uses a crosstab. I need to be able to run that query in SQL Server. I'm looking at using the Pivot command but not sure how to do this.
My Original query was
transform count(b.field1) as cnt
select a.name
from database.dbo.table1 a
inner join database.dbo.table2 b on a.pk = b.fk
where a.field2 in ('11','aa','bb') and b.field3 between startdate() and enddate()
group by a.field1
Pivot b.field2
Thats from MS Access 2010 I tried using the syntax from SQL Server books online but keep getting syntax errors and I have no clue what I'm doing wrong.
The SQL server version looks like this
Select a.name
from database.dbo.table1 a
inner join database.dbo.table2 b on a.pk = b.fk
where a.field2 in ('11','aa','bb') and b.field3 between startdate() and enddate()
group by a.field1
pivot
count(b.field1)
for a.field4 as pvt
Mar 13 2024 10:51 PM
but keep getting syntax errors
@RayMilhon , and what for an exact error message do you get; you haven't mentioned it in any way.
between startdate() and enddate()
In T-SQL there are no build-in functions like "startdate() and enddate()"; so UDF's?
Mar 14 2024 06:58 AM
Yes those are UDF Startdate() returns the first of the month from 12 months ago. so if run today it returns the 1st of March 2023 Enddate() returns the last of the previous month. So today it would return the 29th of Feb 2024. The function would return those date on any day the query is run during the current month. Also I use the same function in several other queries with no issues. I can post the content of those functions if you want it.
Mar 14 2024 07:08 AM