I have a stored procedure that takes customerids inside my Orders table and it sums up every instance per customerid of a status for an order. I have built a userdefined scalar function to get the correct mode of customerids that i need for this report:
.:
TABLE dbo.orders --o
(
OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
sourceid (varchar(12) NOT NULL),
Status varchar(50) NULL,
ordertype varchar(20) NULL,
ordershipped datetime NULL,
orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);
TABLE dbo.codes --cod
(
codeID (pk uniqueidentifier NOT NULL),
sourceid (varchar(12) NOT NULL),
confirmed datetime NULL,
code (varchar(5) NULL),
CONSTRAINT PK_codeID PRIMARY KEY CLUSTERED (codeID ASC)
);
table dbo.members --me
(
meID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
location varchar(20) NULL,
zone varchar(10),
CONSTRAINT PK_meID PRIMARY KEY CLUSTERED (meID ASC)
)
create function [dbo].[countstatuses]
(
@CustomerID varchar(5)
)
RETURNS decimal(10,0)
AS
BEGIN
declare @counts decimal(10,0)
SET @counts =
ISNULL((select count(*) as counts from orders
WHERE status IN ('100', '200') AND orderarrived IS NULL
AND CustomerID = @CustomerID),0)
ISNULL((select count(*) as counts
from orders o
INNER JOIN members me
ON o.zone = me.location AND o.status = '55'
AND me.CustomerID = @CustomerID),0)
RETURN @counts
END
select coalesce (customerid,'GRANDTOTAL') AS CUSTID,
TRANSIT = SELECT countstatuses(*)
from orders
group by rollup (customerid)
....where I am now lost is having the 80 or so customerids inside the signature parenthesis (right now i have denoted it with a wildcard * ) customerids tally correctly for the TRANSIT derived column
?
Thanks in advance