I'm trying to figure out how to split a single query into two separate queries. The following two queries is what I want:
Query #1:
Label Value
TOTALSTUFF 100
Query #2:
Label Value
NONSTUFF 50
Currently the query displays like this:
TOTALSTUFF NONSTUFF
100 50
If someone would be so kind as to help me out regarding the pattern(s) I should look for when reading/reconstructing these types of queries it would be awesome, since there will be more I'd need to do this with. That may or may not be possible but I'm giving everything a shot at this point.
Here's the original query:
SELECT FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0')
AS 'TOTALSTUFF' ,FORMAT(SUM(c.countworkorders -
countpositivecontact),'##,##0') AS 'NONSTUFF'
FROM (SELECT DISTINCT
dpt.dateperiodtypeid
,wo.workordercode
, 1 AS countworkorders
, wo.successfulcompletionind AS countpositivecontact
FROM (SELECT 'CALYEAR' AS dateperiodtypeid
UNION ALL
SELECT 'CALMONTH'
UNION ALL
SELECT 'ROLLBCKPM24MBYMAM') ddpt
JOIN wh.cpy_dateperiodtype_dim dpt
ON dpt.dateperiodtypeid = ddpt.dateperiodtypeid
JOIN wh.cpy_dateperiod_dim dp
ON dp.dateperiodtypekey = dpt.dateperiodtypekey
JOIN wh.mwm_workorder_fact wo
ON wo.createdatetime >= dp.startdate
AND cast(wo.createdatetime as Date) <= dp.enddate
JOIN wh.cpy_employee_dim e
ON e.employeekey = wo.assignedtokey
JOIN wh.cpy_employee_dim mgr
ON mgr.employeekey = e.managerkey
JOIN wh.cpy_org_dim o
ON o.orgid = e.orgid
WHERE wo.dnpind = 1) c
WHERE c.dateperiodtypeid = 'CALMONTH';
Best Answer
Without knowing the logic in the query or the underlying dataset, the easiest (but not necessarily the best) way to do what you're asking is to use these 2 queries:
and
This is just using Column aliases as described here - https://msdn.microsoft.com/en-GB/library/ms176104.aspx
Edited to add a better explanation:
The change is in the columns section of the query. In your original you're requesting that SQL Server returns:
which is 2 columns, one named TOTALSTUFF and one named NONSTUFF. In the first of my reworked queries we request:
First we request a dummy column 'TOTALSTUFF' and alias it as 'Label'. Next we pull back the value that was aliased as TOTALSTUFF in your original query and this time alias it as 'Value'
The logic behind your 2 values TOTALSTUFF and NONSTUFF is the same, so I could just split out the columns in the column list.