Thanks all.

Divine Flame, yours seems the best to follow, but I get this;

Msg 537, Level 16, State 3, Line 9

Invalid length parameter passed to the LEFT or SUBSTRING function.

Not all...

Thanks for the input guys. I have managed to sort this out;

USEInpro

DECLARE@FROMDATEAS datetime

DECLARE@TODATEAS datetime

SET@FROMDATE= { TS '2012-01-01 00:00:00.000' }

SET@TODATE= { TS '2012-03-31 23:59:59.997' }

SELECTCOALESCE(C.CASECOUNT, 0) CASECOUNT, N1.FIRSTNAME + ' '...

Any thoughts guys?

Mark Fitzgerald-331224 (5/4/2012)Given the sample data :

CASEIDNAMETYPENAMENO

4382EMP2272

4382SIG2272

4383EMP-93

4385EMP-93

4385SIG2272

4387EMP-93

4388EMP2272

4388SIG2272

What should the result be?

Reasoning : The cases 4382, 4385 and 4388 have a SIG so should be allocated to 2272. ...

Thanks Mark, but that doesn't return the correct data.

As before, if the NAMENO is -93 (Record Department), I want the join to be made on the SIG nametype, therefore the...

NAME table;

NAMENOSTAFFNAME

-93Records Department

2272Joe Bloggs

CASENAME table;

CASEIDNAMETYPENAMENO

4382EMP2272

4382SIG2272

4383EMP-93

4385EMP-93

4385SIG2272

4387EMP-93

4388EMP2272

4388SIG2272

CASEEVENT TABLE;

CASEIDEVENTNOEVENTDATE

4382-162007-11-15 00:00:00.000

4383-162007-11-20 00:00:00.000

4385-162007-11-13 00:00:00.000

4387-162007-11-20 00:00:00.000

4388-162007-11-13 00:00:00.000

CASES table;

CASEID

4382

4383

4385

4387

4388

OK, this is current SQL with the output;

SELECTCOUNT(CA.CASEID) CASESCOUNT, CASE WHEN N.NAMENO = 2272 THEN 'Joe Bloggs' ELSE N.FIRSTNAME + ' ' + N.NAME END STAFFNAME

FROMCASES CA JOIN

CASEEVENT CE ON...

Mark Fitzgerald-331224 (5/3/2012)

---

PLease show all DDL (create table etc), the sample data (INSERT INTO...) and the required output. This requirement is illdefined at present and we need to know...

I have added another table, CASES.

What I want to do is count all the cases a user has. At the moment it (correctly) shows the number of cases for each...

I agree 100% Lynn, however it's a 3rd party we're talking about here and its permissions are granted per user. The app also maintains it own 'name' list (as in...

D'oh. It's Monday!

D'oh. Of course. -0.5700 is 57 minutes short of 0.00 (midnight), which is where 23:03:00 comes from.

But that is not what I need. The value in the numeric column is...

Not quite. It converted my numeric value from -0.5700 to 23:03:00.

That does indeed work;

CREATE SYNONUM synName

FOR

[linkedServerName].[databaseName].[dbo].[tableName]

Thanks!

Thanks for this! They gave me the platform to, I think, solve the problem;

WITH cte_Clockings AS

(

SELECTE.EMP_Forename, E.EMP_Surname,

ISNULL(CASE C.CLK_Type

WHEN 'I' THEN 'In'

WHEN 'B' THEN 'Out (Break)'

WHEN 'R' THEN...