April 17, 2017 at 11:06 pm
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,
zipcode varchar(10),
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.zipcode = 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
April 18, 2017 at 7:04 am
Zososql - Monday, April 17, 2017 11:06 PMI 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,
zipcode varchar(10),
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.zipcode = 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
You have very weird decisions in your code. I'll explain and then go to you problem.
Two of your tables have an integer primary key, while the third one has a uniqueidentifier. That's a weird decision because UIDs should be avoided when possible due to their size and lack of sequence for their generation.
You're joining orders and members with the zipcode. The column is not a primary key or unique key on any of the tables, so this will create duplicates.
You're getting your customers from the orders table, which would be a great waste of resources compared to reading the customers table.
Scalar functions are slow and will execute row by row, prevent parallelism and hide many performance problems.
You're returning a value with the decimal data type when an integer should be used.
Here's a better version of what you have, changing the scalar function into an inline table-valued function.
create function [dbo].[countstatuses]
(
@CustomerID varchar(5)
)
RETURNS table
AS
RETURN
SELECT SUM( counts) AS Counts
FROM (select count(*) as counts
from orders
WHERE status IN ('100', '200')
AND orderarrived IS NULL
AND CustomerID = @CustomerID
UNION ALL
select count(*) as counts
from orders o
INNER JOIN members me ON o.zipcode = me.location
WHERE o.status = '55'
AND me.CustomerID = @CustomerID)x;
GO
----
select coalesce (customerid,'GRANDTOTAL') AS CUSTID,
TRANSIT = SUM(Counts)
from orders
CROSS APPLY dbo.[countstatuses](customerid) cnt
group by rollup (customerid);
April 18, 2017 at 11:04 pm
Thanks for your insights and help Luis.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
April 19, 2017 at 7:04 am
Zososql - Tuesday, April 18, 2017 11:04 PMThanks for your insights and help Luis.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
And what's the problem with the query that I showed you? If you don't want to use a function, then a derived table can work.
SELECT COALESCE (customerid,'GRANDTOTAL') AS CUSTID,
TRANSIT = SUM(Counts)
FROM (
SELECT CustomerID AS CustomerID, COUNT(*) Counts
FROM orders
WHERE o.status in ('100', '200')
AND orderarrived IS NULL
UNION ALL
SELECT o2.CustomerID, COUNT(*)
FROM orders o2
INNER JOIN members me ON o2.codes = me.location
WHERE o2.status = '55'
AND me.CustomerID = o2.CustomerID)x
GROUP BY ROLLUP (customerid);
This can also be re-written as a CTE
WITH cteCounts AS(
SELECT CustomerID AS CustomerID, COUNT(*) Counts
FROM orders
WHERE o.status in ('100', '200')
AND orderarrived IS NULL
UNION ALL
SELECT o2.CustomerID, COUNT(*)
FROM orders o2
INNER JOIN members me ON o2.codes = me.location
WHERE o2.status = '55'
AND me.CustomerID = o2.CustomerID
)
SELECT COALESCE (customerid,'GRANDTOTAL') AS CUSTID,
TRANSIT = SUM(Counts)
FROM cteCounts
GROUP BY ROLLUP (customerid);
These queries are equivalent, but some people prefer to read them as CTEs and others as derived tables.
It's following the same logic that you had. It counts by customer with one condition, then by other condition and finally it sums up both counts.
April 19, 2017 at 7:53 am
OK, thank you for this- if it can still exist as a derived column format (and the CTE can be recognized as a "column"), then yes, thats the desired format.
Zo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply