What a numpty.

Thanks

I've not designed the database or the table. I'm simply trying to get a report out of it

Hi,

The key here is the DatePaid field. This is the date that drives the graph.

The graph gets the Financial Year and Month from a date table.

The plots on the...

Legend.

Thanks. Does what I need it to do!!!

Thanks guys - as always appreciate the help.

Hi,

So I ended up using the following expressions in my report in order to default dates in SSRS

Work out first monday of the previous month.

=IIF(Weekday(DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)))=2,

DateAdd("m",...

That works Chris, and makes sense.

Thanks

Worked it out -

select officer_code

,department

,eff_date

,CASE WHEN LEAD (eff_date-1, 1, 0) OVER (PARTITION BY officer_code ORDER BY eff_date) = '1900-01-01 00:00:00.000'...

Hi,

Changing tact on this one. The supplier of the software has now supplied a officer history table.

So I am now using a new tsql as below -

select ...

A Change to the audit script

--Audit Script--

select CONCAT([Forename],' ' ,[Surname]) AS 'StaffName', date_of_change as 'changedate', [Job Title Before], [Job Title After]

from co_audit_trail2

cross apply (select Item AS 'Job Title...

This is the issue I think I have - I only have the Staff Name in both tables that's unique.

So the only way I can think is to join from...

Morning,

Ok – After the mess I did explaining myself – lets start from scratch -

To create the audit table.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[co_audit_trail2](

[user_name] [varchar](60)...

ignore last post - think I've worked it out - will post all code again shortly.

Sorry

Thank you Jason.

INSERT INTO [dbo].[co_audit_trail2]

(user_name, date_of_change, time_of_change, bi, ai, modified_table)

SELECT 'ryank','Feb 25 2015 9:08AM','09:07:58:913','01ªRC1ªMrªRªCottissª ª4231ª0ª0ª0ª0ª1ª0ª0ªCustomer Experienceª0ªRickyª ªricky.cottiss@mh.org.ukª0ª0ª0ª ª0ª ª ª ª...

Sorry this is proving a nightmare.

I now get this when I try and insert using this code -

INSERT INTO [dbo].[co_audit_trail2]

(user_name, date_of_change,...