Calculated field (I think) in SQL Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculated field (I think) in SQL Query

Author

Topic

Cerbera
Starting Member

3 Posts

Posted - 2012-01-22 : 17:41:05

Hi all,

I'm new to the site, so a big hello to everyone!

I'm also pretty new to SQL (dabbled for about 1 year) and I'm having difficulty with a query - i haven't written one for about 6 months!

Basically I'm linking two Excel tables in the query:

SELECT MasterData.Date, Downtime.Month, Downtime.Weekending, Downtime.Date, Downtime.Machine, Downtime.PO, Downtime.Shift, Downtime.`Reason Code`, Downtime.`Downtime Minutes`, Downtime.Reason, MasterData.`Time spent on PO`
FROM Downtime Downtime, MasterData MasterData
WHERE Downtime.PO = MasterData.PO AND Downtime.Shift = MasterData.Shift AND Downtime.Date = MasterData.Date

What I'm looking to do is add another column/field which is a selection of Downtime.`Downtime Minutes` where the reason code (Downtime.`Reason Code') is equal to the value 21. I want to name this field Setup Time.

I'm not sure how to code the field - I've done something similar before, but it's been a while and right now i need more coffee before my brain starts working!!!

Thanks for your help!

biswajitdas
Starting Member

44 Posts

Posted - 2012-01-22 : 18:37:23

SELECT MasterData.Date, Downtime.Month, Downtime.Weekending, Downtime.Date, Downtime.Machine, Downtime.PO, Downtime.Shift, Downtime.`Reason Code`, Downtime.`Downtime Minutes`, Downtime.Reason, MasterData.`Time spent on PO`
FROM Downtime Downtime, MasterData MasterData
WHERE Downtime.PO = MasterData.PO and Downtime.Reason Code=21

Sr Sql server DBA/Artitech

Cerbera
Starting Member

3 Posts

Posted - 2012-01-22 : 18:51:25

Hi biswajitdas,

thanks for the help, however it's not quite what i'm looking to achieve.

I'm wanting to retain what is in my original query but add another field called Setup time, which is based on a filter of Downtime Minutes where the reason code = 21.

I though of something along the lines of the below (though this obviously won't work, as my coding knowledge is terrible!):

SELECT MasterData.Date, Downtime.Month, Downtime.Weekending, Downtime.Date, Downtime.Machine, Downtime.PO, Downtime.Shift, Downtime.`Reason Code`, Downtime.`Downtime Minutes`, Downtime.Reason, MasterData.`Time spent on PO`, (
Downtime.`Downtime Minutes` WHERE Downtime.`Reason Code` = 21) AS "Setup Time"

FROM Downtime Downtime, MasterData MasterData

WHERE Downtime.PO = MasterData.PO AND Downtime.Shift = MasterData.Shift AND Downtime.Date = MasterData.Date

Any help is greatly appreciated as you can tell that i need it and my boss is back tomorrow and will expect this to be done :(

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-22 : 19:24:15

May be this?


...
Downtime.`Reason Code`, Downtime.`Downtime Minutes`, Downtime.Reason, MasterData.`Time spent on PO`, (
CASE WHEN Downtime.`Reason Code` = 21 THEN Downtime.`Downtime Minutes` END) AS "Setup Time"
...

Cerbera
Starting Member

3 Posts

Posted - 2012-01-22 : 20:42:02

Thanks sunitabeck,

I've given it a shot, but it didn't seem to like it, however my brain suddenly kicked in and came up with the below which seemed to do the job spot on.

SELECT MasterData.Date, Downtime.Month, Downtime.Weekending, Downtime.Date, Downtime.Machine, Downtime.PO, Downtime.Shift, Downtime.`Reason Code`, Downtime.`Downtime Minutes`, Downtime.Reason, MasterData.`Time spent on PO`, iif( Downtime.`Reason Code`=21,Downtime.`Downtime Minutes`) AS "Setup Time"
FROM Downtime Downtime, MasterData MasterData
WHERE Downtime.PO = MasterData.PO AND Downtime.Shift = MasterData.Shift AND Downtime.Date = MasterData.Date

Don't even know why i put an iif in rather than an if, i'm not sure what the difference is, but it seems ok - i must have tried an if in SQL before and found it prefered iif.

Thanks for your help though guys it really is appreciated - it's good that people like you are prepared to help the lost like me :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources