If the logic in the SQL statement

Question

What I'm trying to do should be very simple, but for some reason I cannot find the right answer to my problem. I asked something like this in the past, but the answer I was given earlier no longer meets the requirements. So, here's what happens - I need to conditionally select values ​​from a table in my database, unlike the usual way, for example:

Table:

• Id int (not null)
• ParentId int (not null)
• EventOn DateTime (not null)
• User int (null)

Next choice:

SELECT RST.* FROM RangeSheetTime RST
WHERE RST.[User] is not null

(in the case above, I take all the lines where the user is not zero)

Select RST.* FROM RangeSheetTime RST
WHERE RST.[User] is null

(in the above case, I take all the rows where the user is null)

, ? select, , EventOn < GETDATE() , USER . - , USER , , NULL, .

?

. , , .

EDIT:

. , 3 ParentId, 31. 2 StartOrEnd, 1. , 1- USER ; , USER 90. StartOrEnd, 0. startorend. . startorend, , USER, null, null, startorend. , , . , .

Answer 1

, , , . , , , , , .

, :

select 
            P.ParentId RangeSheet
            ,coalesce(max(P.[End]), max(P.Start)) EventOn
            ,case when isnull(max(P.[End]), 0) = 0 then 1 else 0 end StartOrEnd
        from 
        (
            select 
                RST.ParentId
                ,case RST.StartOrEnd when 1 then RST.EventOn else null end Start
                ,case RST.StartOrEnd when 0 then RST.EventOn else null end [End]
            from 
            (
                select 
                    coalesce(MAN.ParentId, AUT.ParentId) ParentId
                    ,coalesce(MAN.StartOrEnd, AUT.StartOrEnd) StartOrEnd
                    ,coalesce(max(MAN.CreatedOn), max(AUT.CreatedOn)) CreatedOn
                from
                (
                    -- Obter os manuais
                    select 
                        RST.ParentId
                        ,RST.StartOrEnd
                        ,MAX(RST.CreatedOn) CreatedOn
                    from RangeSheetTime RST
                    where RST.[User] is not null
                    group by RST.ParentId, RST.StartOrEnd
                ) MAN
                full outer join
                (
                    -- Obter os automáticos
                    select 
                        RST.ParentId
                        ,RST.StartOrEnd
                        ,MAX(RST.CreatedOn) CreatedOn
                    from RangeSheetTime RST
                    where RST.[User] is null
                    group by RST.ParentId, RST.StartOrEnd
                ) AUT on MAN.ParentId=AUT.ParentId and MAN.StartOrEnd=AUT.StartOrEnd
                group by coalesce(MAN.ParentId, AUT.ParentId), coalesce(MAN.StartOrEnd, AUT.StartOrEnd)
            ) FOJ
            inner join RangeSheetTime RST on FOJ.ParentId=RST.ParentId and FOJ.StartOrEnd=RST.StartOrEnd and FOJ.CreatedOn=RST.CreatedOn
        ) P
        group by P.ParentId

Answer 2

CASE...WHEN, IF...THEN SQL:

https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-5078041.html

Answer 3

:

SELECT RST.* FROM RangeSheetTime RST
WHERE RST.[User] IS NOT NULL

UNION ALL

SELECT RST.* FROM RangeSheetTime RST
WHERE RST.[User] IS NULL
AND NOT EXISTS (
    SELECT Id FROM RangeSheetTime
    WHERE [User] IS NOT NULL
)

, .

Answer 4

sqlserver, case, , . a, b,      , c , " "           else '      end [C]   T  

oracle, , .

.

Answer 5

Hallaghan, / SQL, , , .

Answer 6

id, parentid User, StartOrEnd

1, 31, null, 1

2, 31, 90, 1

3, 31, null, 0

and you want to say now, if startorEnd has several values ​​for one parent identifier, you want to see only a row where the user is not zero, otherwise a row with a zero user will appear.

if so, Jeff's answer should work