[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Select Previous Year

Posted on 2007-12-03
11
Medium Priority
?
1,429 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

656 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