I have a following table:
here are the columns:
I need to create a query that will only list the following:
Basicaly if Job has Prework 0 and Prework 1, only the one with Prework 0 should be listed.
;WITH cte AS
N = ROW_NUMBER() OVER (PARTITION BY Job ORDER BY PreWork)
If there can be multiple rows for the same job with Prework = 0, use DENSE_RANK instead of ROW_NUMBER
Group by job date
Hope it helps
Your results show the exact opposite of that: if 0 and 1 exist, you're showing 1 in your results, not 0.
sorry i was not clear this is basically what i have:
I only need to list
Job number needs to be updated if prework 1 and prework 1 line displayed instead prework 0, if prework 1 exists. Most of the records have only prework 0.
Then as James wrote, except sort in DESC order:
ROW_NUMBER() OVER (PARTITION BY Job ORDER BY PreWork DESC)
i tried to do this ...
mine is a different way of doing this !!!
if it helps ... great ...
i love any feedback ...thanks
drop create data ...
drop table #temp
create table #temp
insert into #temp select '' ,1,'2019-05-30',3891
insert into #temp select 'BBC00321',0,'2019-05-31',3891
insert into #temp select '' ,1,'2019-09-11',3245
insert into #temp select 'BBC00309',0,'2019-10-31',3245
insert into #temp select 'BBC00391',0,'2019-05-31',3892
insert into #temp select 'BBC00396',0,'2019-06-09',3801
select * from #temp
SQL .. different ..way
Iif(b.prework = 1, 1, 0),
Iif(b.prework = 1, b.jobdate, a.jobdate),
Iif(b.key1 IS NULL, a.key1, a.key1)
FROM (SELECT *
WHERE prework = 0) a
LEFT JOIN (SELECT *
WHERE prework = 1) b
ON a.key1 = b.key1
i looked at James K
seems it will not work for what PatyK wants