Help with SQL

  • I need to display 1 or 0 for the first occurrence of a certain field value in my sql. I thought of using partition, but cannot use where clause in there.

    I am trying to simulate the issue with the following sample data

    With this data set I want to display a column called FirstOccurance with 1 for each VID when Prg1 column = to P1 for the first occurrence

    In the following data for VID

    1111 the first record has P1 in Prg1 and the column for the first record should say 1

    2222 there is no P1 in Prg1, column should be 0

    3333 has 2 records with P1, column should say 1 only for the first time it finds P1. For the second it should say 0

    Any help is greatly appreciated.

    CREATE TABLE #t1([VID] [varchar](100) NULL, [OrdDate] [datetime] NOT NULL,
    [Prg1] [varchar](10) NULL
    )
    ON [PRIMARY]

    insert into #t1 values('1111','2022-01-10 13:37:06.000','P1')
    insert into #t1 values('1111','2022-01-10 13:37:06.000','P2')
    insert into #t1 values('1111','2022-01-10 13:37:06.000','P3')
    insert into #t1 values('2222','2022-01-10 13:37:06.000','P3')
    insert into #t1 values('2222','2022-01-10 13:37:06.000','P4')
    insert into #t1 values('2222','2022-01-10 13:37:06.000','P5')
    insert into #t1 values('3333','2022-01-10 13:37:06.000','P2')
    insert into #t1 values('3333','2022-01-10 13:37:06.000','P3')
    insert into #t1 values('3333','2022-01-10 13:37:06.000','P3')
    insert into #t1 values('3333','2022-01-10 13:37:06.000','P1')
    insert into #t1 values('3333','2022-01-10 13:37:06.000','P3')
    insert into #t1 values('3333','2022-01-13 13:37:06.000','P1')
    insert into #t1 values('3333','2022-01-10 13:37:06.000','')

    select * from #t1
    drop table #t1
  • The data contains a duplicate row.  How to handle duplicates?  Also, VID is nullable so are NULLs to be ignored or treated collectively as a VID?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Like this?

    SELECT t.VID
    ,t.OrdDate
    ,t.Prg1
    ,NewCol = (CASE
    WHEN t.Prg1 = 'P1'
    AND chk.EarlierP1Exists IS NULL THEN
    1
    ELSE
    0
    END
    )
    FROM #t1 t
    OUTER APPLY
    (
    SELECT EarlierP1Exists = 1
    FROM #t1 t1
    WHERE t1.Prg1 = 'P1'
    AND t.Prg1 = 'P1'
    AND t1.VID = t.VID
    AND t1.OrdDate < t.OrdDate
    ) chk
    ORDER BY t.OrdDate;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • All your date time values are the same, except for the second instance of p1 for 3333. How do you define which is the "first" row for a vid? Do you consider all of the rows that share the earliest time to be the first row(s) and want to flag P1 if it is in that cluster?

    select a.VID, a.OrdDate, a.Prg1,
    CASE WHEN a.OrdDate = b.mindate and a.Prg1 = 'P1'
    THEN 1
    ELSE 0
    END as FirstOccurance
    from #t1 AS A
    cross apply (select min(OrdDate) as mindate from #t1 where vid = a.vid) as b
  • Thanks for all the replies. Much appreciated. Yes the date value could be the same for many records. Unfortunately, I don't have any other data to uniquely identify each row. This is a 3rd party database and I don't have access to the tables. They are giving us access through a view.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply