Query Help Please.

[Mat]

The code below returns the number of Service Visits logged to Each
Property.

What I'd like to do is summarise this data by displaying the number of
Properties with 1,2,3,4,n Visits.

I assume I need to nest the first sproc ?

Ideas please ?????

---------------------------------------------------------------------------

Alter Procedure rsp_ServicesToCompletion

@CONTRACT VARCHAR(3)

As

DECLARE @YEARSTART DATETIME

SELECT @YEARSTART = (SELECT DATEADD(YY, CONTRACTYEAR, CONTRACTSTART)
FROM TBL_CONTRACT WHERE (CONTRACT = @CONTRACT))

SELECT COUNT(PROPREF) FROM (SELECT COUNT(TBL_SERVICE.SERVICEDATE) AS
VISITS, TBL_SERVICE.PROPREF
FROM TBL_PROPERTY INNER JOIN TBL_SERVICE ON TBL_PROPERTY.PROPREF =
TBL_SERVICE.PROPREF
WHERE (TBL_PROPERTY.SERVICESTATUS = N'SERVICED') AND
(TBL_PROPERTY.CONTRACT = @CONTRACT) AND
(TBL_SERVICE.SERVICEDATE BETWEEN @YEARSTART AND GETDATE())
GROUP BY TBL_SERVICE.PROPREF) GROUP BY VISITS

[Simon Hayes]

"Mat" <MA*@OLDHALL.TELE2.CO.UK> wrote in message
news:a2**************************@posting.google.c om...

The code below returns the number of Service Visits logged to Each
Property.

What I'd like to do is summarise this data by displaying the number of
Properties with 1,2,3,4,n Visits.

I assume I need to nest the first sproc ?

Ideas please ?????

-------------------------------------------------------------------------- -
Alter Procedure rsp_ServicesToCompletion

@CONTRACT VARCHAR(3)

As

DECLARE @YEARSTART DATETIME

SELECT @YEARSTART = (SELECT DATEADD(YY, CONTRACTYEAR, CONTRACTSTART)
FROM TBL_CONTRACT WHERE (CONTRACT = @CONTRACT))

SELECT COUNT(PROPREF) FROM (SELECT COUNT(TBL_SERVICE.SERVICEDATE) AS
VISITS, TBL_SERVICE.PROPREF
FROM TBL_PROPERTY INNER JOIN TBL_SERVICE ON TBL_PROPERTY.PROPREF =
TBL_SERVICE.PROPREF
WHERE (TBL_PROPERTY.SERVICESTATUS = N'SERVICED') AND
(TBL_PROPERTY.CONTRACT = @CONTRACT) AND
(TBL_SERVICE.SERVICEDATE BETWEEN @YEARSTART AND GETDATE())
GROUP BY TBL_SERVICE.PROPREF) GROUP BY VISITS

Without more details, this is a guess, but it may be close enough to help
you:

SELECT
COUNT(PROPREF)
FROM (
SELECT
COUNT(TS.SERVICEDATE) AS VISITS,
TS.PROPREF
FROM
TBL_PROPERTY TP
INNER JOIN TBL_SERVICE TS
ON TP.PROPREF = TS.PROPREF
INNER JOIN (
select CONTRACT, DATEADD(YY, CONTRACTYEAR, CONTRACTSTART) as YEARSTART
FROM TBL_CONTRACT ) YS
ON TP.CONTRACT = YS.CONTRACT
WHERE
TP.CONTRACT = @CONTRACT
TP.SERVICESTATUS = N'SERVICED' AND
TS.SERVICEDATE BETWEEN YS.YEARSTART AND GETDATE()
GROUP BY
TS.PROPREF) DT
GROUP BY VISITS
Instead of using your procedure, you can join on a view or derived table (YS
in my example), which has the YEARSTART for each contract. If this isn't
helpful, please consider posting CREATE TABLE scripts, sample data, and your
expected results.

Simon