Solved

Select Previous Year

Posted on 2007-12-03
11
1,425 Views
Last Modified: 2010-05-18
Hi,
I have a SQL database that stores department sales for my items. I want a query that can show me the previous year sales for a department at the same time.

Here is what I have so far, can anyone please help me?
SELECT     Department.Name, SUM([Transaction].Total) AS [Total Department Sales]
FROM         [Transaction] INNER JOIN
                      TransactionEntry ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber INNER JOIN
                      Item ON TransactionEntry.ItemID = Item.ID INNER JOIN
                      Department ON Item.DepartmentID = Department.ID
WHERE     ([Transaction].Time BETWEEN '10/01/2007' AND '10/30/2007')
Group by Department.Name

Open in new window

0
Comment
Question by:ALawrence007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 14

Expert Comment

by:twoboats
ID: 20396049
WHERE     ([Transaction].Time BETWEEN '01/01/2006' AND '12/31/2007')
0
 
LVL 14

Expert Comment

by:twoboats
ID: 20396054
doh

WHERE     ([Transaction].Time BETWEEN '01/01/2006' AND '12/31/2006')
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20396057
Do you mean like this. Please explain your comment "I want a query that can show me the previous year sales for a department at the same time."

SELECT     Department.Name, SUM([Transaction].Total) AS [Total Department Sales]
FROM         [Transaction] INNER JOIN
                      TransactionEntry ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber INNER JOIN
                      Item ON TransactionEntry.ItemID = Item.ID INNER JOIN
                      Department ON Item.DepartmentID = Department.ID
WHERE     ([Transaction].Time BETWEEN '10/01/2006' AND '10/30/2006')
Group by Department.Name
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 14

Expert Comment

by:twoboats
ID: 20396092
If there's a time element in the datetime

WHERE     ([Transaction].Time BETWEEN '01/01/2006 00:00:00.000' AND '12/31/2006 23:59:59.999')

or use datepart (yyyy, [Transaction].Time) = 2006
0
 
LVL 5

Expert Comment

by:nicolasdiogo
ID: 20396108
you could change the following portion as:

WHERE     ([Transaction].Time BETWEEN dateAdd( year, -1, getdate() ) AND getdate() )


thus starts one year ago Until when you run the query
0
 

Author Comment

by:ALawrence007
ID: 20396109
I am sorry guys, I meant the following:
SELECT     Department.Name, SUM([Transaction].Total) AS [Total Department Sales]
FROM         [Transaction] INNER JOIN
                      TransactionEntry ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber INNER JOIN
                      Item ON TransactionEntry.ItemID = Item.ID INNER JOIN
                      Department ON Item.DepartmentID = Department.ID
WHERE     ([Transaction].Time BETWEEN '10/01/2007' AND '10/30/2007')
Group by Department.Name
The above gives me 2 column's. Department and then total sales for the selection. I want to ADD Another DATE column with last years sales for the same month LAST year. (English is not my first language)
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20396125
use this
SELECT     Department.Name, SUM([Transaction].Total) AS [Total Department Sales]
FROM         [Transaction] INNER JOIN
                      TransactionEntry ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber INNER JOIN
                      Item ON TransactionEntry.ItemID = Item.ID INNER JOIN
                      Department ON Item.DepartmentID = Department.ID
WHERE     ([Transaction].Time BETWEEN '10/01/2007' AND '10/30/2007') Or ([Transaction].Time BETWEEN '10/01/2006' AND '10/30/2006')

Group by Department.Name
0
 

Author Comment

by:ALawrence007
ID: 20396165
That just gives me 2 Columns. Department name and then Total sales for this year and last year combined.
I have to have 3 columns. Department name, This years sales, Last years sales.
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20396168
Okay i see, what you mean, please let me know if i am wrong, you want four columns, the 2 which are are having right now for this year and another 2 for last year....right?
0
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 500 total points
ID: 20396191
Okay i got you point, and here is one kind of query which can help you. Please change the column name as what suits you.


Select A.Name,  A.ThisYearTotal,  B.LastYearTotal From
(SELECT     Department.Name, SUM([Transaction].Total) AS ThisYearTotal
FROM         [Transaction] INNER JOIN
                      TransactionEntry ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber INNER JOIN
                      Item ON TransactionEntry.ItemID = Item.ID INNER JOIN
                      Department ON Item.DepartmentID = Department.ID
WHERE     ([Transaction].Time BETWEEN '10/01/2007' AND '10/30/2007') Group by Department.Name) A,
(SELECT     Department.Name, SUM([Transaction].Total) AS LastYearTotal
FROM         [Transaction] INNER JOIN
                      TransactionEntry ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber INNER JOIN
                      Item ON TransactionEntry.ItemID = Item.ID INNER JOIN
                      Department ON Item.DepartmentID = Department.ID
WHERE     ([Transaction].Time BETWEEN '10/01/2006' AND '10/30/2006') Group by Department.Name) B

0
 

Author Comment

by:ALawrence007
ID: 20396194
If I can get only 3 that would be wonderful.
Department Name, This year, Last Year.

I think you mean:
Department Name, This year, Department Name, Last Year

This would be fine as well if we can't do the 3 column one.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question