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/8e541a8d-3661-4af9-9bc5-a32c9a13198b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
