I'm building an application in access for tracking projects. Like status, costs and how many hours are spent for each project. I've come a long way but I've stumbled upon a problem.
The data that I store in SQL is stored as follows:
ID | EmployeeID | Date | Hours | Projectnumber | Remarks
---| ---------- | ---------- | ----- | ------------- | ----------
1 | 1 | 2016-07-18 | 6 | 123456 | Many calls
2 | 2 | 2016-07-17 | 2 | 654321 |
Now I'd like my co-workers to see this data on a week to week basis, the most popular view is to have columns from Monday to Sunday. This means if somebody works the whole week on a specific project, you only get to see one line for this project.
In Access I managed to create this via a Crosstab query, but it is rather slow. I know that things speed up when using a stored procedure in SQL and so I tried to accomplish the same thing but with an SQL stored procedure + a pass-through query.
This is my code in MS ACCESS VBA:
"TRANSFORM Sum(U.hours) AS Hours " & _
"SELECT U.Projectnumber AS Project, W.Description as Description, F.Client As Client" & _
",(W.Adress & "" te "" & W.Town) as Adress, U.Remarks AS Remarks" & _
"FROM ((tblhours As U LEFT JOIN tblprojects As W ON U.Projectnumber = W.Projectnumber) " & _
"LEFT JOIN tblClients As F ON W.ClientID = F.ClientID) " & _
"WHERE (U.EmployeeID Like " & Me.cboEmployee & " " & _
"AND DatePart('ww',[U.Date],2,1) Like " & Me.cboWeek & " " & _
"AND Datepart('yyyy',[U.Date],2,1) like " & Me.cboYear & ") " & _
"GROUP BY U.Projectnumber, U.Remarks, W.Description, F.Client, W.Adress,W.Town " & _
"ORDER BY U.Date ASC PIVOT U.Date"
I have searched the internet for quite some time and ultimately put together this stored procedure in SQL Server 2016:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[usp_myprocedurename]
@EmployeeID int,
@week int,
@year int
AS
BEGIN
SET NOCOUNT ON
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
DECLARE @prevquery as NVARCHAR(MAX)
SELECT @prevquery = '(SELECT
Sum(U.Hours) as Hours, U.Projectnumber AS Project,
W.Description as Description, F.Client As Client,
(W.Adress + '' te '' + W.Town) as Adress, U.Remarks AS Remarks,
U.Date as Date
FROM (tblHours As U LEFT JOIN tblProjects As W
ON U.Projectnumber = W.Projectnumber)
LEFT JOIN tblClients As F ON W.ClientID = F.ClientID
WHERE U.EmployeeID Like ' + CONVERT(varchar(10), @EmployeeID) + ' AND DatePart(ww,U.Date) Like '
+ CONVERT(varchar(10), @week) + ' AND Datepart(yyyy,U.Date) like ' + CONVERT(varchar(10), @Year) +
'GROUP BY U.Projectnumber, U.Remarks, W.Description, F.Client, W.Adress,W.Town, u.Date)'
SELECT @cols = STUFF((SELECT distinct
',' +
QUOTENAME(left(convert(nvarchar(30),Date),10))
FROM tblHours
WHERE EmployeeID Like CONVERT(varchar(10), @EmployeeID)
AND DatePart(ww,Date) Like CONVERT(varchar(10), @week) AND Datepart(yyyy,Date)
like CONVERT(varchar(10), @Year)
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
SET @query = ' SELECT Project, Description, Client, Adress, Remarks, ' + @cols + '
FROM
(
' + @prevquery + '
) t
PIVOT
(
SUM(Hours)
FOR Date IN( ' + @cols + ')
) p ';
Execute(@query)
End
I hope it isn't all too messy, haha. Anyways, I have a picture of how this looks like in management studio with some random variables:
I thought, well this is great now I can continue building other things, but Access wasn't very happy with the change.
When I replace the current query in Access by a pass-through query which executes with the right variables it first gives me an ODBC error:
[Microsoft][SQL Server Native Client 11.0][SQL Server]SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexed on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial indes operations. (#1934)
I click modify on my stored procedure and added the following:
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SET ANSI_WARNINGS OFF
GO
When I run the query to alter the stored procedure and I restart Access the warning is gone. What is weird is that I have done this three times already. After the modification I click modify again to see nothing has actually changed?? Any ideas on that?
Anyways the error message is gone, hooray. But now for the real problem:
As you can probably see in the picture, my decimal separator is a dot and the sum calculation below doesn't add up. I live in Holland where we use the comma as decimal separator, that's why the addition fails.
My other stored procedures work just fine, but with those I declare a table variable (no ODBC error). VBA also gives an error saying data type mismatch when summing those values.
How can I get access to see that the decimals in hours should be separated by a comma? Is it fixed by telling SQL which datatype the output data should be? If yes, please help me on how to achieve this. The hours are decimal(4,2) datatype in SQL by the way.
If there is something you can recommend me or if there's something you think me and others can profit from, please feel free to share this piece of information. I just recently started using SQL server, so any help is welcome.
==========
UPDATE
I think I've got it now;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[usp_myprocedurename]
@EmployeeID int, @week int, @year int AS BEGIN
SET NOCOUNT ON
DECLARE @cols nvarchar(max), @cols2 nvarchar(max), @Create nvarchar(max), @prevquery nvarchar(max), @query nvarchar(max)
SELECT @cols = (SELECT distinct ',' + QUOTENAME(left(convert(nvarchar(30),Date),10)) + ' numeric(4,2)' as [text()]
FROM tblHours WHERE employeeID Like CONVERT(varchar(10), @EmployeeID) AND DatePart(ww,date) Like CONVERT(varchar(10), @week) AND Datepart(yyyy,date) like CONVERT(varchar(10), @Year)
FOR XML PATH(''));
SELECT @cols2 = (SELECT distinct ',' + QUOTENAME(left(convert(nvarchar(30),Date),10)) as [text()]
FROM tblHours WHERE EmployeeID Like CONVERT(varchar(10), @EmployeeID) AND DatePart(ww,date) Like CONVERT(varchar(10), @week) AND Datepart(yyyy,Date) like CONVERT(varchar(10), @Year)
FOR XML PATH(''))
SELECT @prevquery = '(SELECT Sum(U.Hours) as Hours, U.Projectnumber AS Project,
W.fldOmschrijving as Omschrijving, F.fldNaam As Opdrachtgever,
(W.fldAdres + '''' te '''' + W.fldPlaats) as Adres, U.Remarks AS Remarks, U.Date as Date FROM (tblHours As U
LEFT JOIN tblProjects As W ON U.Projectnumber = W.Projectnumber)
LEFT JOIN tblClients As F ON W.ClientID = F.ClientID
WHERE U.Employeeid Like ' + CONVERT(varchar(10), @EmployeeID) + ' AND DatePart(ww,U.Date) Like ' + CONVERT(varchar(10), @week) + ' AND Datepart(yyyy,U.Date) like ' + CONVERT(varchar(10), @Year) + '
GROUP BY U.Projectnumber, U.Remarks, W.Description, F.Client, W.Adress,W.Town, u.Date)'
SET @query = ' SELECT Project, Description, Client, Adress,
Remarks' + @cols2 + ' FROM (' + @prevquery + ') t
PIVOT(SUM(Hours) FOR Date IN( ' + STUFF(@cols2,1,1,'') + ')) p ';
EXEC('DECLARE @mytable table(Project int, Description nvarchar(255),
Client nvarchar(255), Adress nvarchar(255), Remarks nvarchar(max)' + @cols + ')
Insert Into @mytable(project, Description, Client,
adress, remark' + @cols2 + ')
Execute(''' + @query + ''') SELECT * FROM @mytable')
End
It may not be the most efficient piece of SQL you've seen, but it seems to work now. Any comments on how to improve it are welcome ofcourse :)
