Need help with creating a query

Transact-SQL
1

I have a following table:
here are the columns:
|Job|Date|Prework|

|JOB01|24/09/2018|1|
|JOB01|25/09/2018|0|
|JOB02|22/08/1018|0|
|JOB03|15/07/2018|0|
|JOB04|12/06/2018|1|
|JOB04|13/06/2018|0|

I need to create a query that will only list the following:
|Job|Date|Prework|

|JOB01|24/09/2018|1|
|JOB02|22/08/1018|0|
|JOB03|15/07/2018|0|
|JOB04|12/06/2018|1|

Basicaly if Job has Prework 0 and Prework 1, only the one with Prework 0 should be listed.

Thanks You.

3 
;WITH cte AS
(
	SELECT 
		*,
		N = ROW_NUMBER() OVER (PARTITION BY Job ORDER BY PreWork)
	FROM
		YourTable
)
SELECT 
	Job,
	Date,
	PreWork
FROM
	cte
WHERE
	N=1;

If there can be multiple rows for the same job with Prework = 0, use DENSE_RANK instead of ROW_NUMBER

4

Min prework
Group by job date

Hope it helps

5

Your results show the exact opposite of that: if 0 and 1 exist, you're showing 1 in your results, not 0.

6

sorry i was not clear this is basically what i have:

image
image.png750×198 17.2 KB

I only need to list
|BBC00321|1|2019-05-30|3891|

|BBB00391|0|2019-05-31|3892|

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.

7

Then as James wrote, except sort in DESC order:
...
ROW_NUMBER() OVER (PARTITION BY Job ORDER BY PreWork DESC)
...

8

hi

i tried to do this ...

mine is a different way of doing this !!!
if it helps ... great ... :slight_smile: :slight_smile:

i love any feedback ...thanks

drop create data ... 
drop table #temp 
go 

create table #temp 
(
Job varchar(100),
Prework int,
jobDate date,
key1 int
)
go

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
go 

select * from #temp
go
SQL .. different ..way 
SELECT a.job, 
       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 * 
        FROM   #temp 
        WHERE  prework = 0) a 
       LEFT JOIN (SELECT * 
                  FROM   #temp 
                  WHERE  prework = 1) b 
              ON a.key1 = b.key1

image
image.png953×486 16.4 KB

9

hi

i looked at James K
SQL

seems it will not work for what PatyK wants