[quoted text, click to view]

Thanks for DDL:

select CentreA, CentreB, QS, QSP, Op, JobNo, case when CentreB = 'MTN'
then QS - QSP else NULL end from table1

You can add a where clause with your needs.

--
HTH,

I want to be able to select all the records from a table (query) and add a
column when a certain condition exists

eg
Table Contents
CentreA CentreB QS QSP Op JobNo
WS MTN 100 10 1 G123
RT RAT 100 0 1 G456
RT RAT 0 0 2 G123

Desired Result Set (all records where CentreB = 'RAT')
CentreA CentreB QS QSP Op JobNo AdditionalCol (QS-QSP
where CentreB = MTN)
RT RAT 100 0 1 G456 0
RT RAT 0 0 2 G123 90

Whatever I try always filters out the first record as the job doesn't have a
Op with MTN - what am I doing wrong

Here is the DDL


CREATE TABLE [dbo].[Table1] (
[CentreA] [char] (10) COLLATE Latin1_General_BIN NULL ,
[CentreB] [char] (10) COLLATE Latin1_General_BIN NULL ,
[QS] [numeric](18, 0) NULL ,
[QSP] [numeric](18, 0) NULL ,
[Op] [numeric](18, 0) NOT NULL ,
[JobNo] [char] (10) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Op],
[JobNo]
) ON [PRIMARY]
GO


INSERT Table1(CentreA,CentreB,QS,QSP,Op,JobNo)
VALUES('WS','MTN',100,10,1,'G123')
INSERT Table1(CentreA,CentreB,QS,QSP,Op,JobNo)
VALUES('RT','RAT',100,10,1,'G456')
INSERT Table1(CentreA,CentreB,QS,QSP,Op,JobNo)
VALUES('RT','RAT',0,0,2,'G123')

[quoted text, click to view]

This statement isn't correct.

--
HTH,

[quoted text, click to view]

This will do it I think:

SELECT *, (select qs - qsp from table1 TA1 inner join Centres C1 on
C1.CentreA = TA1.CentreA and TA1.JobNo = T1.JobNo and C1.CentreB =
'MTN') as QSMinusQSP
FROM Table1 t1 INNER JOIN Centres c ON t1.CentreA = c.CentreA
WHERE c.CentreB = 'RAT'

--
HTH,

[quoted text, click to view]

Huh? It doesn't give QS-QSP for both jobs as 90! Check the results I
get:

RT 100 10 1 G456 RT RAT NULL
RT 0 0 2 G123 RT RAT 90

Last column is QSMinusQSP

--
HTH,

Thanks but this doesn't return the desired result. I have also realised
that I have simplified it too much

Table1 is the result of a query

Centres Table and Table1

CREATE TABLE [dbo].[Centres] (
[CentreA] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
[CentreB] [char] (10) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table1] (
[CentreA] [char] (10) COLLATE Latin1_General_BIN NULL ,
[QS] [numeric](18, 0) NULL ,
[QSP] [numeric](18, 0) NULL ,
[Op] [numeric](18, 0) NOT NULL ,
[JobNo] [char] (10) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Op],
[JobNo]
) ON [PRIMARY]
GO

INSERT Table1(CentreA,QS,QSP,Op,JobNo) VALUES('WS','MTN',100,10,1,'G123')
INSERT Table1(CentreA,QS,QSP,Op,JobNo) VALUES('RT','RAT',100,10,1,'G456')
INSERT Table1(CentreA,QS,QSP,Op,JobNo) VALUES('RT','RAT',0,0,2,'G123')

INSERT Centres(CentreA,CentreB) VALUES ('WS','MTN')
INSERT Centres(CentreA,CentreB) VALUES ('RT','RAT')
INSERT Centres(CentreA,CentreB) VALUES ('FS','BUT')


SELECT * FROM Table1 t1 INNER JOIN Centres c ON t1.CentreA = c.CentreA
WHERE c.CentreB = 'RAT'

The key thing here is job no. I am trying to return all records where
CentreB = 'RAT' and if there is a record for the same job where CentreB =
'MTN' I want the QS-QSP to be added to the result.

Therefore in the following example
Job G123 Op 2 will be returned as Centre 'RT' has a CentreB = 'RAT' but Job
G123 also has a 'MTN' record therefore I want the result of QS-QSP for the
'MTN' record to be added as another column to the result set i.e.
representing a qty from the previous op

At the moment I create the table(query resultset) and then run another query
to update the Additional Col. I was hoping I could do all this in one
stored procedure.

Query Result
CentreA CentreB QS QSP Op JobNo
RT RAT 100 0 1 G456
RT RAT 0 0 2 G123

Desired Result Set (all records where CentreB = 'RAT')
CentreA CentreB QS QSP Op JobNo AdditionalCol (QS-QSP
where CentreB = MTN)
RT RAT 100 0 1 G456 Null
RT RAT 0 0 2 G123 90

What I have tried is
Select tmp.CentreA, tmp.CentreB, tmp.QS, tmp.QSP, tmp.Op, tmp.JobNo, QS-QSP
AS PrevOp
FROM
(
SELECT * FROM Table1 t1 INNER JOIN Centres c ON t1.CentreA = c.CentreA
WHERE c.CentreB = 'RAT') tmp
LEFT JOIN Table1 ON tmp.JobNo = JobNo AND CentreB = 'MTN'

but this doesn't work

Any ideas

Thanks

[quoted text, click to view]

sorry should read

INSERT Table1(CentreA,QS,QSP,Op,JobNo) VALUES('WS',100,10,1,'G123')
INSERT Table1(CentreA,QS,QSP,Op,JobNo) VALUES('RT',100,10,1,'G456')
INSERT Table1(CentreA,QS,QSP,Op,JobNo) VALUES('RT',0,0,2,'G123')


[quoted text, click to view]

Thanks but this doesn't work - it gives me the QS-QSP for both jobs as 90
when only job G123 should have a value. Somehow a join on Job No needs to
be incorporated but this is where I get stuck

[quoted text, click to view]

sorry - you are right - I have case sensitive database so I had to change
some of the aliases and in doing so I got my TA1 and t1 mixed up

Thanks for all your help
[quoted text, click to view]