Thank you Jeremy. Your example is clear and it works.
Now what if I need to chain this with another CTE? For example
WITH C1 AS (
...
),
C2 AS (
SELECT C1.*, se = ROW_NUMBER() OVER (PARTITION BY [MEMBER_ID], [PARENT_GID],
[TYPE] ORDER BY ts, Type DESC)
FROM C1
)
SELECT * FROM [C2]
On Monday, June 10, 2019 at 9:23:07 PM UTC-7, Jeremy Evans wrote:
>
> On Monday, June 10, 2019 at 5:19:58 PM UTC-7, lkfken wrote:
>>
>> Hi,
>>
>> I would like to convert the SQL code below to Sequel. Hopefully, after I
>> see the conversion, I will have a better understand on how Sequel handle
>> CROSS APPLY and VALUES and CTE
>>
>> WITH C1 AS (
>> SELECT
>> MEMBER_ID, PARENT_GID,
>> ts,
>> Type,
>> e = CASE Type
>> WHEN 1
>> THEN NULL
>> ELSE ROW_NUMBER()
>> OVER (PARTITION BY MEMBER_ID, PARENT_GID, Type
>> ORDER BY END_DATE) END,
>> s = CASE Type
>> WHEN -1
>> THEN NULL
>> ELSE ROW_NUMBER()
>> OVER (PARTITION BY MEMBER_ID, PARENT_GID, Type
>> ORDER BY START_DATE) END
>> FROM Elig
>> CROSS APPLY (VALUES (1, START_DATE), (-1, END_DATE)) a(Type,
>> ts)
>> )
>> SELECT * FROM C1
>>
>> thank you.
>>
>
> Looks like Sequel doesn't support VALUES on MSSQL (I assume you are using
> MSSQL). This should work, though:
>
> DB[:c1].
> with(:c1, DB[:elig].
> cross_apply(DB['VALUES (1, START_DATE), (-1, DATE)'].as(:a, [:type,
> :ts])).
> select(:member_id, :parent_gid, :ts, :type,
> Sequel.case({1=>nil},
> Sequel.function(:row_number).over(:partition=>[:member_id, :parent_gid,
> :type], :order=>:end_date), :type).as(:e),
> Sequel.case({-1=>nil},
> Sequel.function(:row_number).over(:partition=>[:member_id, :parent_gid,
> :type], :order=>:start_date), :type).as(:s)))
>
> WITH [C1] AS (
> SELECT [MEMBER_ID], [PARENT_GID], [TS], [TYPE],
> (CASE [TYPE] WHEN 1 THEN NULL ELSE row_number() OVER (PARTITION BY
> [MEMBER_ID], [PARENT_GID], [TYPE] ORDER BY [END_DATE]) END) AS [E],
> (CASE [TYPE] WHEN -1 THEN NULL ELSE row_number() OVER (PARTITION BY
> [MEMBER_ID], [PARENT_GID], [TYPE] ORDER BY [START_DATE]) END) AS [S]
> FROM [ELIG]
> CROSS APPLY (VALUES (1, START_DATE), (-1, DATE)) AS [A]([TYPE], [TS])
> )
> SELECT * FROM [C1]
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/64a85490-0213-458e-b8ed-e8168126dee9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
