| Author |
 Topic  |
|
|
hbadministrator
Yak Posting Veteran
63 Posts |
 Posted - 01/10/2013 : 10:05:01
|
I am trying to get an extra column made that either displays Labor or Material. I am guessing can say if SUM comes from joblabordetail then Labor and if Sum comes from [job-costs] then Material. Here is my code.
SELECT [job-no], Yr, Prd, SUM(TotalEarnings + TotalFringes + TotalPayroll) AS Expr1
FROM joblabordetail AS l
GROUP BY [job-no], Yr, Prd
UNION
SELECT [Job-no], Yr, Prd, SUM([Trans-amt]) AS Expr1
FROM [job-costs] AS j
GROUP BY [Job-no], Yr, Prd
Example data.
600123 2013 2 46.06 Labor
461183AC 2013 2 19484.48 Material
462929AC 2013 2 52283.76 Material
600229 2013 4 -31.90 Labor
434550 2012 11 1150.10
600076 2013 4 1162.25
600133 2013 1 2115.14
443567 2012 11 194.89
21 2013 3 2310.54
600057 2013 4 108.50
460563 2012 11 530.37
461059 2012 11 173.45
461469 2013 1 181.40
462263 2012 11 1413.87
455210ac 2012 11 25176.38
30 2013 3 10.00
438657kc 2012 11 14082.37
441533KC 2013 2 53.00
600069 2012 12 4102.64
446819EC 2013 1 3287.40
600113 2013 1 22.35
461470 2012 12 3589.11
600093 2013 1 3405.03
462204 2012 11 4675.44
462516AC 2013 2 44.69 |
|
|
theboyholty
Posting Yak Master
United Kingdom
220 Posts |
Posted - 01/10/2013 : 10:50:06
|
without re-creating your tables, I'm thinking you could just hard code the values onto each query either side of the UNION.
Something like:
SELECT [job-no], Yr, Prd, SUM(TotalEarnings + TotalFringes + TotalPayroll) AS Expr1,'Labor' as NewColumn
FROM joblabordetail AS l
GROUP BY [job-no], Yr, Prd
UNION
SELECT [Job-no], Yr, Prd, SUM([Trans-amt]) AS Expr1,'Material'
FROM [job-costs] AS j
GROUP BY [Job-no], Yr, Prd
Give it a go, see what happens.
---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum |
Edited by - theboyholty on 01/10/2013 10:50:43 |
 |
|
|
hbadministrator
Yak Posting Veteran
63 Posts |
Posted - 01/10/2013 : 11:01:57
|
| PERFECT! |
|
|
|
theboyholty
Posting Yak Master
United Kingdom
220 Posts |
Posted - 01/10/2013 : 11:17:49
|
Happy to help. And there's no such thing as a stupid question. They're all perfectly valid.
---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/10/2013 : 19:32:16
|
Actually, I've found many stupid questions. They're the one's that have been answered but the person asking was too busy talking to listen to the answer and they ask again and again because of the same reason. 
(And, no... this one didn't fit that category).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it." |
Edited by - Jeff Moden on 01/10/2013 19:33:20 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 01/10/2013 : 20:43:00
|
Hey Jeff,
We have a forum for just those people. It's called the "twit list". It's not for people who ask "stupid questions", it's for people who ask questions but refuse to accept the answers. It's very entertaining.
Jim
P.S. I'm glad to see you posting here more often. I'm a big fan of yours and SQL Team will benefit greatly from your continued input.
Everyday I learn something that somebody else already knew |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/11/2013 : 19:00:25
|
quote:
Originally posted by jimf
Hey Jeff,
We have a forum for just those people. It's called the "twit list". It's not for people who ask "stupid questions", it's for people who ask questions but refuse to accept the answers. It's very entertaining.
Jim
P.S. I'm glad to see you posting here more often. I'm a big fan of yours and SQL Team will benefit greatly from your continued input.
Everyday I learn something that somebody else already knew
Thank you for the warm welcome, Jim. There are some pretty good people on this forum (like yourself, Sunita, Visakh, MVJ, Peso, etc, for example). I'll have to really be on the ball to keep up. SQL Team has been in pretty good hands all along.
Heh... "Twit List", huh? Wonder if my name is on it. 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it." |
Edited by - Jeff Moden on 01/12/2013 12:54:01 |
|
|
| |
Topic |
|
|
|
stupid question and easy answer im sure
