stupid question and easy answer im sure

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

SQL Server 2012 Forums

Transact-SQL (2012)

stupid question and easy answer im sure

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-01-10 : 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

226 Posts

Posted - 2013-01-10 : 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

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-01-10 : 11:01:57

PERFECT!

theboyholty
Posting Yak Master

226 Posts

Posted - 2013-01-10 : 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

652 Posts

Posted - 2013-01-10 : 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."

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-10 : 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

652 Posts

Posted - 2013-01-11 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources