var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Grouping of Fields
I have a 3rd Party Table with the following fields
What I require is to group the Codes and sum the Times for each code based on the text value of the code.
date Code1 Time1 Code2 Time2 Code3 Time3 Code4 Time4 Code5 Time5.
01.01.2015 AAA 0:15 CCC 1:00 AAA 0:10 BBB 0:15
01.01.2015 BBB 0:10 AAA 1:00 EEE 0:10 BBB 0:30 CCC 0:15
02.01.2015 CCC 0:15 CCC 1:00 BBB 0:10 DDD 1:00 AAA 0:15
The query will alway be run for a week so DATE1-DATE7 and HOURS1-HOURS7 required.
CODE DATE1 HOURS2 DATE2 HOURS2
AAA 01.01.2015 1:25 02.01.2015 0:15
BBB 01.01.2015 0:55 02.01.2015 0:10
CCC 01.01.2015 1:15 02.01.2015 1:15
DDD 01.01.2015 0:00 02.01.2015 1:00
AAA BBB CCC etc can be of multiple options so these can't be hard coded. eg there could be a ZZZ or ZAC etc.
How would I go about this?
Thanks in advance,
select code, sum(hours2), date2, sum(hours2)
group by code, date2
This is a problem you should tackle with pivotal tables and is best done on the client.
If you need to do it with SQL, you may find a solution by Googling for "crosstab SQL". This is what I came up with.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
Thanks, I managed to get this with a number of sql queries for each row but your suggestion looks helpful should I require again