// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('routing_calls'[Date Filter])),
'routing_calls'[Date Filter] = 1
)
VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('routing_calls'[Program])),
AND(
'routing_calls'[Program] IN {"HARDSURF",
"CARPET"},
'routing_calls'[Program] IN {"CARPET",
"HARDSURF"}
)
)
VAR __DS0FilterTable3 =
FILTER(KEEPFILTERS(VALUES('routing_calls'[IFFormula])), 'routing_calls'[IFFormula] = 1)
VAR __DS0Core =
CALCULATETABLE(
SUMMARIZE(
'routing_calls',
'routing_calls'[Program],
'routing_calls'[Store],
'routing_calls'[Work Order],
'routing_calls'[Billing Type],
'routing_calls'[ActivityDateTime],
'routing_calls'[Attention],
'routing_calls'[PhoneNo],
'routing_calls'[Email],
'routing_calls'[Routing Call Made],
'routing_calls'[Entered By],
'routing_calls'[Notes],
'routing_calls'[Store Code]
),
KEEPFILTERS(__DS0FilterTable),
KEEPFILTERS(__DS0FilterTable2),
KEEPFILTERS(__DS0FilterTable3)
)
VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0Core,
'routing_calls'[PhoneNo],
1,
'routing_calls'[Program],
1,
'routing_calls'[Store],
1,
'routing_calls'[Work Order],
1,
'routing_calls'[Billing Type],
1,
'routing_calls'[ActivityDateTime],
1,
'routing_calls'[Attention],
1,
'routing_calls'[Email],
1,
'routing_calls'[Routing Call Made],
1,
'routing_calls'[Entered By],
1,
'routing_calls'[Notes],
1,
'routing_calls'[Store Code],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
'routing_calls'[PhoneNo],
'routing_calls'[Program],
'routing_calls'[Store],
'routing_calls'[Work Order],
'routing_calls'[Billing Type],
'routing_calls'[ActivityDateTime],
'routing_calls'[Attention],
'routing_calls'[Email],
'routing_calls'[Routing Call Made],
'routing_calls'[Entered By],
'routing_calls'[Notes],
'routing_calls'[Store Code]
// Direct Query
SELECT
TOP (501) [c1],[c2],[c3],[c4],[c6],[c8],[c9],[c10],[c11],[c12],[c13],[c23]
FROM
(
SELECT [t0].[ActivityDateTime] AS [c1],[t0].[Attention] AS [c2],[t0].[BillingType] AS [c3],[t0].[Program] AS [c4],[t0].[WoDisp] AS [c6],[t0].[RcallMade] AS [c8],[t0].[Notes] AS [c9],[t0].[CustID] AS [c10],[t0].[PhoneNo] AS [c11],[t0].[Email] AS [c12],[t0].[Store Code] AS [c13],[t0].[Date Filter] AS [c15],[t0].[IFFormula] AS [c20],[t0].[Entered By] AS [c23],[t0].[ActivityDateTime] AS [o5]
FROM
(
SELECT [c1] AS [ActivityDateTime],[c2] AS [Attention],[c3] AS [BillingType],[c4] AS [Program],[c6] AS [WoDisp],[c8] AS [RcallMade],[c9] AS [Notes],[c10] AS [CustID],[c11] AS [PhoneNo],[c12] AS [Email],[c13] AS [Store Code],[c15] AS [Date Filter],
(
CASE
WHEN
(
(
NOT(
(
[c16] IS NULL
)
)
AND
([c16] = 5)
)
AND
(COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
)
THEN 1
WHEN
(
(
NOT(
(
[c16] IS NULL
)
)
AND
([c16] = 4)
)
AND
(COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
)
THEN 1
WHEN
(
(
NOT(
(
[c16] IS NULL
)
)
AND
([c16] = 3)
)
AND
(COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
)
THEN 1
WHEN
(
(
NOT(
(
[c16] IS NULL
)
)
AND
([c16] = 2)
)
AND
(COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
)
THEN 1
WHEN
(
(
NOT(
(
[c16] IS NULL
)
)
AND
([c16] = 1)
)
AND
(COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
)
THEN 1
WHEN
(
(
NOT(
(
[c16] IS NULL
)
)
AND
([c16] = 6)
)
AND
(COALESCE([c17], CAST(-2 AS datetime)) >= COALESCE([c18], CAST(-2 AS datetime)))
)
THEN 1
WHEN
(
(
NOT(
(
[c16] IS NULL
)
)
AND
([c16] = 7)
)
AND
(COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
)
THEN 1
WHEN
(
(
NOT(
(
[c16] IS NULL
)
)
AND
([c16] = 7)
)
AND
(COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c21], CAST(-2 AS datetime)))
)
THEN 1
ELSE 0
END
)
AS [IFFormula],[c23] AS [Entered By]
FROM
(
SELECT [c1],[c2],[c3],[c4],[c6],[c8],[c9],[c10],[c11],[c12],[c13],
(
CASE
WHEN
(
NOT(
(
[c14] IS NULL
)
)
AND
([c14] = CAST( '20241004 00:00:00' AS datetime))
)
THEN 0
ELSE 1
END
)
AS [c15],DATEPART(WEEKDAY, [c19]) AS [c16],CAST(CAST(
CAST([c14] AS VARCHAR(4000))
AS DATE) AS DATETIME) AS [c17],CAST(CAST(
CAST(
(SELECT COALESCE(Op1 + Op2, Op1, CAST(Op2 - 2 AS DATETIME)) FROM (SELECT [c19] AS Op1, 1 AS Op2) AS AuxTable)
AS VARCHAR(4000))
AS DATE) AS DATETIME) AS [c18],CAST(CAST(
CAST(
(SELECT COALESCE(Op1 + Op2, Op1, CAST(Op2 - 2 AS DATETIME)) FROM (SELECT [c19] AS Op1, 2 AS Op2) AS AuxTable)
AS VARCHAR(4000))
AS DATE) AS DATETIME) AS [c21],[c23]
FROM
(
SELECT [t0].[ActivityDateTime] AS [c1],[t0].[Attention] AS [c2],[t0].[BillingType] AS [c3],[t0].[Program] AS [c4],[t0].[WoDisp] AS [c6],[t0].[RcallMade] AS [c8],[t0].[Notes] AS [c9],[t0].[CustID] AS [c10],[t0].[PhoneNo] AS [c11],[t0].[Email] AS [c12],RIGHT([t0].[OfficeLocation], 1) AS [c13],CAST(CAST(
CAST([t0].[ActivityDateTime] AS VARCHAR(4000))
AS DATE) AS DATETIME) AS [c14],CAST( '20241004 00:00:00' AS datetime) AS [c19],[t0].[Entered By] AS [c23]
FROM
(
(
select [_].[ActivityDateTime] as [ActivityDateTime],
[_].[Attention] as [Attention],
[_].[BillingType] as [BillingType],
[_].[SummaryProgram] as [Program],
[_].[OfficeLocation] as [OfficeLocation],
[_].[WoDisp] as [WoDisp],
[_].[RcallResults] as [RcallResults],
[_].[RcallMade] as [RcallMade],
[_].[EnteredDateTime] as [Entered Date],
[_].[EnteredBy] as [Entered By],
[_].[Notes] as [Notes],
[_].[CustID] as [CustID],
[_].[PhoneNo] as [PhoneNo],
[_].[Email] as [Email]
from [dbo].[trav_tblCR2Alert_RoutingCallResults_View] as [_]
)
)
AS [t0]
)
AS [t0]
)
AS [t0]
)
AS [t0]
)
AS [t0]
WHERE
(
(
(
[c15] = 1
)
AND
(
[c20] = 1
)
)
AND
(
([c4] IN (N'HARDSURF',N'CARPET'))
)
)
GROUP BY [c1],[c2],[c3],[c4],[c6],[c8],[c9],[c10],[c11],[c12],[c13],[c23],[o5]
ORDER BY [c11]
ASC
,[c4]
ASC
,[c10]
ASC
,[c6]
ASC
,[c3]
ASC
,
COALESCE([o5], CAST(-2 AS datetime))
ASC
, (CASE WHEN [o5] IS NULL THEN 0 ELSE 1 END)
ASC
,[c2]
ASC
,[c12]
ASC
,[c8]
ASC
,[c23]
ASC
,[c9]
ASC
,[c13]
ASC
