SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 stupid question and easy answer im sure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

118 Posts

Posted - 01/10/2013 :  10:05:01  Show Profile  Reply with Quote
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
221 Posts

Posted - 01/10/2013 :  10:50:06  Show Profile  Visit theboyholty's Homepage  Reply with Quote
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
Go to Top of Page

hbadministrator
Posting Yak Master

118 Posts

Posted - 01/10/2013 :  11:01:57  Show Profile  Reply with Quote
PERFECT!
Go to Top of Page

theboyholty
Posting Yak Master

United Kingdom
221 Posts

Posted - 01/10/2013 :  11:17:49  Show Profile  Visit theboyholty's Homepage  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/10/2013 :  19:32:16  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/10/2013 :  20:43:00  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/11/2013 :  19:00:25  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000