Help adding to existing SQL statement, date greater than or equal to

M

mrackley

New Member
Joined
Aug 16, 2021
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
I'm trying to insert a filter into an existing SQL statement that I did not create, and frankly do not really understand. Where in this series can I add a filter for date >= 01/01/2017?

SELECT [Case Name],
[Case Status Name] AS [Case Status],

CASE
WHEN SUBSTRING([Log], CHARINDEX('[', [Log],CHARINDEX(CHAR(13) + CHAR(10) + 'Status ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'Status ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'Status ', [Log])) - 1) = 'Null'
THEN ''
ELSE SUBSTRING([Log], CHARINDEX('[', [Log],CHARINDEX(CHAR(13) + CHAR(10) + 'Status ', [Log])) + 1,
CHARINDEX(']', [Log],CHARINDEX(CHAR(13) + CHAR(10) + 'Status ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'Status ', [Log])) - 1)
END AS [Status],

CASE
WHEN ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) - 1)) = 1
THEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) - 1)
WHEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) - 1) LIKE '%Null%'
THEN CAST(CONVERT(varchar, (SELECT DateCreated FROM udqCasesAll WHERE CaseId = Transactions.CaseID), 101) AS datetime)
ELSE CAST(CONVERT(varchar, TransactionDate, 101) AS datetime)
END AS [Status Date],

CASE
WHEN ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) - 1)) = 1
AND ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) - 1)) = 1
THEN DATEDIFF(day, SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) - 1),
SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) - 1))
WHEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log])) - 1) LIKE '%Null%'
AND ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) - 1)) = 1
THEN DATEDIFF(day, (SELECT DateCreated FROM udqCasesAll WHERE CaseId = Transactions.CaseID),
SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusDate ', [Log]))) - 1))
ELSE 0
END AS [Days in Status],
CASE
WHEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusNote ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusNote ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusNote ', [Log])) - 1) = 'Null'
THEN ''
ELSE CAST(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusNote ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusNote ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'StatusNote ', [Log])) - 1) AS nvarchar(max))
END AS [StatusNote],
Attorney AS Atty,
[Support 1] AS [2nd Atty],
[Support 2] AS [2nd LA],
[Support 3] AS [LA],
[Case Stage] AS [DoP],

Transactions.CaseID
FROM Transactions WITH(NOLOCK)
INNER JOIN udqCases WITH(NOLOCK) ON udqCases.CaseId = Transactions.CaseID
WHERE TransactionComments = 'CaseTable' AND [Log] LIKE '%' + CHAR(13) + CHAR(10) + 'Status %'

UNION

SELECT [Case Name],
[Case Status Name],

CASE
WHEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1 ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1 ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1 ', [Log])) - 1) = 'Null'
THEN ''
ELSE SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1 ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1 ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1 ', [Log])) - 1)
END AS [Status],

CASE
WHEN ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) - 1)) = 1
THEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) - 1)
WHEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) - 1) LIKE '%Null%'
THEN CAST(CONVERT(varchar, (SELECT DateCreated FROM udqCasesAll WHERE CaseId = Transactions.CaseID), 101) AS datetime)
ELSE CAST(CONVERT(varchar, TransactionDate, 101) AS datetime)
END AS [Status Date],

CASE
WHEN ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) - 1)) = 1
AND ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) - 1)) = 1
THEN DATEDIFF(day, SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) - 1),
SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) - 1))
WHEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log])) - 1) LIKE '%Null%'
AND ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) - 1)) = 1
THEN DATEDIFF(day, (SELECT DateCreated FROM udqCasesAll WHERE CaseId = Transactions.CaseID),
SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Date ', [Log]))) - 1))
ELSE 0
END AS [Days in Status],
CASE
WHEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Note ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Note ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Note ', [Log])) - 1) = 'Null'
THEN ''
ELSE CAST(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Note ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Note ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus1Note ', [Log])) - 1) AS nvarchar(max))
END AS [StatusNote],
Attorney AS Atty,
[Support 1] AS [2nd Atty],
[Support 2] AS [2nd LA],
[Support 3] AS [LA],
[Case Stage] AS [DoP],
Transactions.CaseID
FROM Transactions WITH(NOLOCK)
INNER JOIN udqCases WITH(NOLOCK) ON udqCases.CaseId = Transactions.CaseID
WHERE TransactionComments = 'CaseTable' AND [Log] LIKE '%' + CHAR(13) + CHAR(10) + 'SubStatus1 %'
UNION

SELECT [Case Name],
[Case Status Name],

CASE
WHEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2 ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2 ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2 ', [Log])) - 1) = 'Null'
THEN ''
ELSE SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2 ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2 ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2 ', [Log])) - 1)
END AS [Status],

CASE
WHEN ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) - 1)) = 1
THEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) - 1)
WHEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) - 1) LIKE '%Null%'
THEN CAST(CONVERT(varchar, (SELECT DateCreated FROM udqCasesAll WHERE CaseId = Transactions.CaseID), 101) AS datetime)
ELSE CAST(CONVERT(varchar, TransactionDate, 101) AS datetime)
END AS [Status Date],

CASE
WHEN ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) - 1)) = 1
AND ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) - 1)) = 1
THEN DATEDIFF(day, SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) - 1),
SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) - 1))
WHEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log])) - 1) LIKE '%Null%'
AND ISDATE(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) - 1)) = 1
THEN DATEDIFF(day, (SELECT DateCreated FROM udqCasesAll WHERE CaseId = Transactions.CaseID),
SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) + 1,
CHARINDEX(']', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) -
CHARINDEX('[', [Log], CHARINDEX('->', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Date ', [Log]))) - 1))
ELSE 0
END AS [Days in Status],

CASE
WHEN SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Note ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Note ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Note ', [Log])) - 1) = 'Null'
THEN ''
ELSE CAST(SUBSTRING([Log], CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Note ', [Log])) + 1,
CHARINDEX(']', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Note ', [Log])) -
CHARINDEX('[', [Log], CHARINDEX(CHAR(13) + CHAR(10) + 'SubStatus2Note ', [Log])) - 1) AS nvarchar(max))
END AS [StatusNote],
Attorney AS Atty,
[Support 1] AS [2nd Atty],
[Support 2] AS [2nd LA],
[Support 3] AS [LA],
[Case Stage] AS [DoP],
Transactions.CaseID
FROM Transactions WITH(NOLOCK)
INNER JOIN udqCases WITH(NOLOCK) ON udqCases.CaseId = Transactions.CaseID
WHERE TransactionComments = 'CaseTable' AND [Log] LIKE '%' + CHAR(13) + CHAR(10) + 'SubStatus2 %'

UNION

SELECT [Case Name],
[Case Status Name],
[Status],
CAST(CONVERT(varchar, [StatusDate], 101) AS datetime),
DATEDIFF(day, [StatusDate], GETDATE()),
CAST(StatusNote AS nvarchar(Max)) AS StatusNote,
[Atty-Div] AS Atty,
CaseSupervisor AS [2nd Atty],
Atty AS [2nd LA],
LegalSecretary AS [LA],
[CaseStage] AS [DoP],
CaseId
FROM udqCasesAll
WHERE [Status] IS NOT NULL AND StatusDate IS NOT NULL

UNION

SELECT [Case Name],
[Case Status Name],
SubStatus1,
CAST(CONVERT(varchar, SubStatus1Date, 101) AS datetime),
DATEDIFF(day, SubStatus1Date, GETDATE()),
CAST(SubStatus1Note AS nvarchar(Max)) AS SubStatus1Note,
[Atty-Div] AS Atty,
CaseSupervisor AS [2nd Atty],
Atty AS [2nd LA],
LegalSecretary AS [LA],
[CaseStage] AS [DoP],

CaseId
FROM udqCasesAll
WHERE SubStatus1 IS NOT NULL AND SubStatus1Date IS NOT NULL

UNION

SELECT [Case Name],
[Case Status Name],
SubStatus2,
CAST(CONVERT(varchar, SubStatus2Date, 101) AS datetime),
DATEDIFF(day, SubStatus2Date, GETDATE()),
CAST(SubStatus2Note AS nvarchar(Max)) AS SubStatus12Note,
[Atty-Div] AS Atty,
CaseSupervisor AS [2nd Atty],
Atty AS [2nd LA],
LegalSecretary AS [LA],
[CaseStage] AS [DoP],
CaseId
FROM udqCasesAll
WHERE SubStatus2 IS NOT NULL AND SubStatus2Date IS NOT NULL

ORDER BY [Case Name], [Status Date]
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You must log in or register to reply here.

Similar threads

A
  • Question Question
Have SQL code refer to cells in worksheet
Replies
0
Views
796
All2Cheesy
A
R
  • Question Question
With Selection.QueryTable -** Please help**
Replies
0
Views
917
Ramnaidu
R

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top