Help with SQL

Question

Help with SQL

SSC-Addicted

Points: 425

September 24, 2022 at 6:19 pm

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

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

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

Steve Collins SSCrazy Points: 2174 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246988 More actions · Answer 2

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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ed B Hall of Fame Points: 3669 More actions · Answer 3

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

don075 SSC-Addicted Points: 425 More actions · Answer 4

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.