Solved

Select Previous Year

Posted on 2007-12-03
11
1,426 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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