topic Re: SQL Explain in QlikView

SQL Explain

Chanty4u — Wed, 25 Nov 2020 16:16:04 GMT

Hi all

can anyone explain what below code does?

WITH Quarters AS (

SELECT Q = 'Q1', MonthBegin = 11, MonthEnd = 12 UNION

SELECT Q = 'Q1', MonthBegin = 1, MonthEnd = 1 UNION

SELECT Q = 'Q2', MonthBegin = 2, MonthEnd = 4 UNION

SELECT Q = 'Q3', MonthBegin = 5, MonthEnd = 7 UNION

SELECT Q = 'Q4', MonthBegin = 8, MonthEnd = 10

)

left(Datename(month,cast(S.ReportedMonth+'01' as date)),3) AS 'ReportedMonth',

(q.Q+'FY'+right(S.reportedYear,2)) AS 'Fiscal_Quarter',

4 .

INNER JOIN Quarters q ON ( right(S.ReportedMonth,2) >= q.MonthBegin AND right(S.ReportedMonth,2) <= q.MonthEnd )

where S.businessunit = 'SW' and S.ReportedYear = '2018'

PrashantSangle — Wed, 25 Jul 2018 10:08:36 GMT

1: Your 1st sql query create temp table in memory. which store data like

Q, monthBegin, monthEnd

1, 11,12

2,1,1

3,2,4

4,5,7

5,8.10

Regards,

dapostolopoylos — Wed, 25 Jul 2018 10:09:59 GMT

This code is kind of weird, can you share with us the full script?

The CTE in bullet 1 seems to be wrong, Q1 isn't from month 1 to month 3?

jyothish8807 — Wed, 25 Jul 2018 10:10:25 GMT

Hi Chanty,

1. "WITH" is used to set a name to the sub query block. You can refer it in many places in main sql query.

The Union statement is combining all the results of the statements.

Br,

Chanty4u — Wed, 25 Jul 2018 10:27:50 GMT

thanks all. im pretty new to Sql queries. Let me check some of the codes.