serviceaellis (8/18/2015)

---

Please see attached.

The worksheet is the result that needs to be produced with the highlighted row.

in the Access file the two tables are:

1. PersonMembership - with all the membership...

You're welcome. Sample data, a...

Sure - try this:

SELECT

h.[hours],

a.ActType,

Activities = COUNT(a.ActType),

Points = COUNT(*) * x.points

FROM #tblActivities a

CROSS APPLY (

SELECT

[hours] = CASE

WHEN a.ActType = 'Motion' THEN DATEPART(HOUR, a.isoTimeStamp)

WHEN a.ActType =...

Here's a halfway point:

SELECT

a.*,

x.[hours],

Points = COUNT(*) OVER(PARTITION BY a.ActType, x.[hours]) * x.points

FROM #tblActivities a

CROSS APPLY (

SELECT

[hours] = CASE

WHEN a.ActType = 'Motion' THEN DATEPART(HOUR, a.isoTimeStamp)

WHEN...

Excellent job on putting together sample data. Can you post up what you expect your output to look like? Cheers.

serviceaellis (8/18/2015)

---

It is showing but showing 2014 and 2015.

It should show 2015 AND a null row like ClubNo 8004

So neither solutions are showing ClubNo 5305 null row like ClubNo 8004.

Only...

serviceaellis (8/18/2015)

---

Thank you both ...

however ...

ChrisM@Work,

yours is showing 2014.

and both are not showing ClubNo 5305 with a null membership info like ClubNo 8004.

NOTE: 5305 has memberships in 2015 and prior...

Affairs

What datatype is te.TripEndTime?

Try this:

SELECT

TE.JobID,

TotalDistanceTravelled as Distance,-- what table is this from?

TotalTripFare AS TotalFare -- what table is this from?

FROM [sqlcdscluster\sqlcds].cdsbusiness.dbo.Tbljobbooking MW

INNER remote JOIN dbo.tbltripenddetail TE

ON MW.JobID = TE.jobID

AND...

You say "@Process table data,...

In...

born2achieve (8/18/2015)

---

Hi,

Sample data to play with,

declare @Process table(ProcessId int identity(1,1) primary key,ProcessName varchar(100),ParentStep int, ChildStep int);

Insert into @Process(ProcessName,ParentStep,ChildStep)

select 'Process1',1,2 union all

select 'Process2',1,3 union all

select 'Process3',1,6 union all

select 'Process4',4,2 union all

select...

-- This has become more or less the gold standard method

-- for avoiding the division by zero error:

SELECT

i.CPSP35,

i.VINT35,

ISNULL(i.CPSP35 / NULLIF(i.VINT35,0),0) AS High

FROM dbo.SSCCWorderExtract e

INNER JOIN DEV.dbo.INP35 i

ON...

-- Note the use of aliases to reduce noise and make your query more clear

-- avoid right joins, most humans can't read them

-- don't put a column from an outer-joined...