11/22/2024 9:55:45 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
WITH ROLLUP and subqueries.
Posted: 22 Oct 09 8:18 AM
|
I have a query that the ROLLUP is working correctly with the count in my main queries, but it doesn't effect the subqueries. I hvae tried to add the GROUP BY x WITH ROLLUP to the subqueries but it throws back an error. Any way to get rollup totals in my subqueries?
SELECT ISNULL(CONVERT(VARCHAR, DT.dtime), 'Totals') AS OrderDate, COUNT(AP.ACCOUNTPRODUCTID) AS NumDeals2CS,
(SELECT COUNT(T.TICKETID) AS Expr1
FROM sysdba.TICKET AS T LEFT OUTER JOIN
sysdba.PICKLIST AS P ON T.STATUSCODE = P.ITEMID
WHERE (CAST(CAST(T.CREATEDATE AS varchar(11)) AS DATETIME) = DT.dtime) AND (P.TEXT LIKE 'Install%' OR
P.TEXT LIKE 'Completed%' OR
P.TEXT LIKE 'Dead%')) AS Num2Install,
(SELECT COUNT(DEALTYPE) AS Expr2
FROM sysdba.EQUIPMENT AS E
WHERE (CAST(CAST(CREATEDATE AS varchar(11)) AS DATETIME) = DT.dtime) AND (DEALTYPE LIKE 'NEW%')) AS NewDeal,
(SELECT COUNT(DEALTYPE) AS Expr3
FROM sysdba.EQUIPMENT AS E
WHERE (CAST(CAST(CREATEDATE AS varchar(11)) AS DATETIME) = DT.dtime) AND (DEALTYPE LIKE 'REPRO%')) AS REPRODeal,
(SELECT COUNT(DEALTYPE) AS Expr7
FROM sysdba.EQUIPMENT AS E
WHERE (CAST(CAST(CREATEDATE AS varchar(11)) AS DATETIME) = DT.dtime) AND (DEALTYPE LIKE 'USED%')) AS USEDDeal,
(SELECT COUNT(ACCOUNTPRODUCTID) AS Expr4
FROM sysdba.ACCOUNTPRODUCT AS AP
WHERE (CAST(CAST(INSTALLDATE AS varchar(11)) AS DATETIME) = DT.dtime)) AS NumInstalled,
(SELECT COUNT(AGENT2INSTALL) AS Expr5
FROM sysdba.ACCOUNTPRODUCT AS AP
WHERE (CAST(CAST(INSTALLDATE AS varchar(11)) AS DATETIME) = DT.dtime)) AS AgentToInstall,
(SELECT SUM(VOLUMEMERCH) AS Expr6
FROM sysdba.ACCOUNTPRODUCT AS AP
WHERE (CAST(CAST(CREATEDATE AS varchar(11)) AS DATETIME) = DT.dtime)) AS Volume
FROM dbo.udfDateTimes('10/1/2009', '10/30/2009', 1, 'day') AS DT LEFT OUTER JOIN
sysdba.ACCOUNTPRODUCT AS AP ON DT.dtime = CAST(CAST(AP.CREATEDATE AS varchar(11)) AS DATETIME)
GROUP BY DT.dtime WITH ROLLUP |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|