I have a query which I was running in Microsoft SQL server and it worked just fine. It made use of CTEs because I joined three tables. The first two in an inner query then an outer one. However when I tried this on my web server it only has MySQL installed and I could not run the query. Is there any way to run a query with that structure? Here is a sample:
WITH CTE_DatesTable
AS
(
SELECT CAST('20160401' as date) AS [date]
UNION ALL
SELECT DATE_ADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATE_ADD(dd, 1, [date]) <= '20170331'
) Select A.[date],
(SELECT CASE DATEPART(WEEKDAY,A.[date])
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY' End) WkDay,
Case When Isnull(C.Hdate,'')<>'' then C.Descr
When (DATEPART(WK,A.[Date])%2)=1 And B.WkOff=1 Then 'AlterWeekoff'
When B.WkOff=0 Then 'Weekoff'Else 'WD' End As WorkDay,
Case When (DATEPART(WK,A.[Date])%2)=1 And B.WkOff=1 or Isnull(C.Hdate,'')<>'' Then 'Holiday'
When B.WkOff=0 Then 'Holiday' Else 'WeekDay' End As WorkDescr
From CTE_DatesTable A Left Join BPC_WDD1 B on DATEPART(dw,A.[Date])=B.WDay
Left Join BPC_HCL1 C on A.date=C.Hdate
OPTION (MAXRECURSION 0);