?
Solved

Select Previous Year

Posted on 2007-12-03
11
Medium Priority
?
1,433 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
  • 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
Independent Software Vendors: 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!

 
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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

809 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