Solved

Select Previous Year

Posted on 2007-12-03
11
1,415 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now