Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Group by date query Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 4:09 PM
Points: 2, Visits: 3
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
Post #1566468
Posted Wednesday, April 30, 2014 10:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1566481
Posted Wednesday, April 30, 2014 10:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 10,381, Visits: 13,436
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566504
Posted Wednesday, April 30, 2014 1:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 4:09 PM
Points: 2, Visits: 3
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
Post #1566587
Posted Wednesday, April 30, 2014 1:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 10,381, Visits: 13,436
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566595
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse