Calling a function with input parameter problem :

1

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

2

Three's a DOT of some sort between your two ISNULL statements, probably the useless MarkDown that this forum uses ... please re-edit your post and format the SQL so that it isn't mangled by the forum software,.

    ```sql

    ... your code here ...

    ```

You don't need ISNULL around your SELECT COUNT() surely? SELECT COUNT() will always return a value ... but that's obviously not related to your question.

Beware that scalar functions like this execute notoriously slowly :frowning:

Do you want your function to match the wildcard? Right now it will only match an exact CustomerID code/value.

3

Thanks Kristen. This was just a preliminary approach to what I need, I am revising the plan, and will post to the threat once I clearly know what I am aiming for.

4

Here's what i really want to do is group all the customerids and add up the instances that these statuses occur, but here I can't past the "cant use an aggregate function on a subquery that contains an aggregate" error:

select coalesce (customerid,'GRANDTOTAL') AS CUSTID,
TRANSIT = sum(case when o.status in ('100', '200') AND orderarrived IS NULL) then 1 else 0 end) + sum(select count(*) from orders o2
INNER JOIN members me
ON o2.codes = me.location AND o2.status = '55'
AND me.CustomerID = o2CustomerID)

from orders
group by rollup (customerid)

Thanks for help
So