February 7, 2012 at 10:55 am
Hello,
I am having difficulty, as newby of course to create two columns from one columns on the query results
Final results I want is
Employee TotalHoursWorked TotalInternalHours
All this data is on JobTable that I can separate InternalHours by the JobNo or JobType (because all internal hours are logged to one job number). I have been trying to use WITH to create first TotalHoursWorked and then join again JobTable to get only Intrnal hours but not working.
I appreciate some hlep on this query
Lonex
February 7, 2012 at 11:29 am
If you could post the table structure and some sample data that would help.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 7, 2012 at 2:40 pm
With query below i get
EMPLID--- HoursLogged----HoursInternal
1001------100-------------30
1002------150-------------40
1003 (Employee 1003 that has for example 200 total HoursLogged but no hours posted for the internal project 2001 so it wont show on the query results).
I have also with Left Join but same result.
WITH
TempHoursLogged AS
(
SELECT SUM(QTY) AS HoursLogged
,PROJEMPLTRANS.EMPLID
FROM [PROJEMPLTRANS]
GROUP BY PROJEMPLTRANS.EMPLID
)
SELECT TempHoursLogged.EMPLID, TempHoursLogged.HoursLogged, SUM(QTY) AS HoursInternal
FROM TempHoursLogged
FULL JOIN PROJEMPLTRANS
ON TempHoursLogged.EMPLID = PROJEMPLTRANS.EMPLID
WHERE PROJEMPLTRANS.PROJID = '2001'
GROUP BY TempHoursLogged.EMPLID, TempHoursLogged.HoursLogged
February 7, 2012 at 2:54 pm
Hi and welcome to SSC. There seems to be something missing here. First Chrissy asked you to post ddl (create table statements) and sample data (insert statements) along with desired output based on your sample data.
Then in your explanation you said:
With query below i get
EMPLID--- HoursLogged----HoursInternal
1001------100-------------30
1002------150-------------40
1003 (Employee 1003 that has for example 200 total HoursLogged but no hours posted for the internal project 2001 so it wont show on the query results).
I have also with Left Join but same result.
However your query says:
WHERE PROJEMPLTRANS.PROJID = '2001'
Of course it won't find any hours for this person because they don't have any hours for that project.
Take a look at the link Chrissy posted, or the first link in my signature for best practices on posting questions with enough supporting documentation and clarification of the issue to provide you the best answers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2012 at 3:11 pm
There are some real pros here who will help if you provide create table statements and insert statements.
Without that I'll provide untested air code, that is quite possibly syntactically incorrect, unlikely
to follow best practices, does not consider any perfomance considerations and worst of all is created by an 'advanced beginner'.:-P
I think you can use a subquery and the COALESCE statement.
SELECT
SUM(QTY) AS HoursLogged,
PROJEMPLTRANS.EMPLID,
COALESCE(HoursInternal,0)
FROM [PROJEMPLTRANS] PET
INNER JOIN
(
SELECT
SUM(QTY) AS HoursInternal,
PROJEMPLTRANS.EMPLID
FROM [PROJEMPLTRANS]
WHERE PROJEMPLTRANS.PROJID = '2001'
GROUP BY
PROJEMPLTRANS.EMPLID
)
dataset
ON PET.EMPLID = dataset.EMPLID
GROUP BY
PROJEMPLTRANS.EMPLID
February 8, 2012 at 9:46 am
Thanks SSC Veteran!
You were right that without the data it is hard to validate code. I will be trying to create sample data and code as per best practice and post it here. In meantime I did test this code and I got two times error line
'The multi-part identifier "PROJEMPLTRANS.EMPLID" could not be bound.'
Lonex
February 8, 2012 at 10:19 am
lonex (2/8/2012)
Thanks SSC Veteran!You were right that without the data it is hard to validate code. I will be trying to create sample data and code as per best practice and post it here. In meantime I did test this code and I got two times error line
'The multi-part identifier "PROJEMPLTRANS.EMPLID" could not be bound.'
Lonex
That is because the table is aliased and then referenced by the original name. This should do it.
SELECT
SUM(QTY) AS HoursLogged,
PET.EMPLID,
COALESCE(HoursInternal,0)
FROM [PROJEMPLTRANS] PET
INNER JOIN
(
SELECT
SUM(QTY) AS HoursInternal,
PROJEMPLTRANS.EMPLID
FROM [PROJEMPLTRANS]
WHERE PROJEMPLTRANS.PROJID = '2001'
GROUP BY
PROJEMPLTRANS.EMPLID
)
dataset
ON PET.EMPLID = dataset.EMPLID
GROUP BY
PET.EMPLID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 1:30 pm
Thanks for revision, now I see how important is to get that sample data and have tried but not getting any luck on that as that is another issue on its own....
Anyway I have run this last revision and get the following, not sure if you want to suggest another attempt or will see when I get that table creation process figured out. Either way thanks for your input
An error occurred while executing the query.
Column 'dataset.HoursInternal' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'dataset.HoursInternal' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (Microsoft SQL Server Report Builder)
February 8, 2012 at 1:42 pm
lonex (2/8/2012)
Thanks for revision, now I see how important is to get that sample data and have tried but not getting any luck on that as that is another issue on its own....
Hi...please confirm what version of SQL you are running
.....there are easy ways to script out tables (and data)...if you rightclick on a table (withinn SSMS) you will see options "Script table as".....this will give you a start.
dependent upon which SQL version you have....I know that SQL2008R2 can also script data insert as well....though you have to right click on the database .generate scripts....can help you if required.
alternatively Google SSMS tools....excellent (free if you wish) addin that will also help you.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply