April 30, 2014 at 9:41 am
I need to write a query which involves 3 tables. Each table has a date field. I want to run a query to group data by month eg
Month Costtotal Repairtotal Servicetotal
April 1243 2344 123123
May 3123 213123 21312
Each column comes from a diferent table that has its own date
April 30, 2014 at 10:03 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
April 30, 2014 at 10:50 am
As Sean has said it would be much easier to provide a workable answer with additional information.
Here's an idea on how to do it (I have no way of knowing how to join the tables):
SELECT
DATENAME(MONTH, T.theDate) AS theMonth,
SUM(T.value) AS value1,
SUM(T1.value) AS value2,
SUM(T2.value) AS value3
FROM
dbo.test AS T
JOIN dbo.test1 AS T1
ON T.primarykey = T1.foreignkeytoT
JOIN dbo.test2
ON T.primarykey = T2.foreignkeytoT
GROUP BY
DATENAME(MONTH, T.theDate)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 30, 2014 at 1:39 pm
serviceinfo{vid, servicecost, servicedate}
repairinfo{vid, repaircost, repairdate}
fuelinfo{vid, costtotal, costdate}
Those are the tables. I need a sum of all figures grouped by month
April 30, 2014 at 1:46 pm
I'm pretty sure the pattern I provided in my first answer will do what you need. Just replace the made up object names with the names of your tables and columns.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply