Query for Sum of item by month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query for Sum of item by month

Author

Topic

DennisDG00
Starting Member

4 Posts

Posted - 2010-10-19 : 22:54:22

Hello,

I am hoping this is in the right place forgive if it isn't.
I am trying to get a query to return data in the following format.

empname sumitemsept sumitemoct sumitemnov

I have a basic query that returns it by month but if there are null items it skips. I than have to paste into Excel and insert/delete. There has to be a better way. Tried figuring this out myself but I am out of time.

here is the basic query I am using.

SELECT Emp_General.FirstName, Emp_General.LastName,
SUM(Reg_Transactions.quantity)AS 'Eft Sold'
FROM Reg_Transactions INNER JOIN
Emp_General ON Reg_Transactions.EmployeeUID = Emp_General.EmployeeUID
WHERE (Reg_Transactions.DateofSale >= '2010-08-01 00:00:00') AND
(Reg_Transactions.DateofSale < '2010-09-01 00:00:00') AND
(Reg_Transactions.subitem LIKE '%EFT%')
GROUP BY Emp_General.FirstName, Emp_General.LastName, Emp_General.EmployeeUID
ORDER BY Emp_General.LastName, Emp_General.FirstName

SQL 2005 and again if in the wrong place sorry.
DG

Sachin.Nand

2937 Posts

Posted - 2010-10-20 : 02:37:12

Can you please post sample data & the output you are expecting?

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 12:53:29

null items in which fields? can you explain with an example?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DennisDG00
Starting Member

4 Posts

Posted - 2010-10-21 : 15:28:16

Here is the SQL statement I am using.

SELECT Emp_General.FirstName, Emp_General.LastName,
SUM(Reg_Transactions.quantity)AS Sept_EFT
FROM Reg_Transactions INNER JOIN
Emp_General ON Reg_Transactions.EmployeeUID = Emp_General.EmployeeUID
WHERE (Reg_Transactions.DateofSale >= '2010-09-01 00:00:00') AND
(Reg_Transactions.DateofSale < '2010-10-26 00:00:00') AND
(Reg_Transactions.subitem LIKE '%EFT%')
GROUP BY Emp_General.FirstName, Emp_General.LastName, Emp_General.EmployeeUID
ORDER BY Emp_General.LastName, Emp_General.FirstName

The Result looks as follows.

Firstname Lastname Sept_EFT
MALINE Smith 4
MICHELLE Jones 5
TIFFANY Edwards 6

I want the query to produce the same results but add additional months and if the
name has no data than return null or zero. The query now if it doesn't find any in
does not include it in the results set.

Firstname Lastname Sept_EFT Oct_EFT Nov_EFT
MALINE Smith 4 5 0
MICHELLE Jones 5 0 2
TIFFANY Edwards 6 1 4

I hope this clears it up.

thanks again for all the help.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-10-21 : 15:40:34

Stop changing what you're asking for. Your second query has columns not even mentioned in the first query, different filters, etc. Please provide sample data that will produce the results you want, with some explanation as to what you want. For example, Oct_Eft and Nov_Eft are new columns, what data do you want to be summed in them?

Here's a guess, though.
SELECT Emp_General.FirstName, Emp_General.LastName, Emp_General.EmployeeUID
SUM(CASE WHEN Reg_Transactions.DateofSale >= '2010-09-01 00:00:00'
and Reg_Transactions.DateofSale < '2010-10-01 00:00:00'
THEN Reg_Transactions.quantity
END
)AS Sept_EFT
,
SUM(CASE WHEN Reg_Transactions.DateofSale >= '2010-10-01 00:00:00'
and Reg_Transactions.DateofSale < '2010-11-01 00:00:00'
THEN Reg_Transactions.quantity
END
)AS OCT_EFT
FROM Reg_Transactions INNER JOIN
Emp_General ON Reg_Transactions.EmployeeUID = Emp_General.EmployeeUID
WHERE
Reg_Transactions.subitem LIKE '%EFT%')
GROUP BY Emp_General.FirstName, Emp_General.LastName, Emp_General.EmployeeUID
ORDER BY Emp_General.LastName, Emp_General.FirstName

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-23 : 02:26:10

quote:
Originally posted by DennisDG00

Here is the SQL statement I am using.

SELECT Emp_General.FirstName, Emp_General.LastName,
SUM(Reg_Transactions.quantity)AS Sept_EFT
FROM Reg_Transactions INNER JOIN
Emp_General ON Reg_Transactions.EmployeeUID = Emp_General.EmployeeUID
WHERE (Reg_Transactions.DateofSale >= '2010-09-01 00:00:00') AND
(Reg_Transactions.DateofSale < '2010-10-26 00:00:00') AND
(Reg_Transactions.subitem LIKE '%EFT%')
GROUP BY Emp_General.FirstName, Emp_General.LastName, Emp_General.EmployeeUID
ORDER BY Emp_General.LastName, Emp_General.FirstName

The Result looks as follows.

Firstname Lastname Sept_EFT
MALINE Smith 4
MICHELLE Jones 5
TIFFANY Edwards 6

I want the query to produce the same results but add additional months and if the
name has no data than return null or zero. The query now if it doesn't find any in
does not include it in the results set.

Firstname Lastname Sept_EFT Oct_EFT Nov_EFT
MALINE Smith 4 5 0
MICHELLE Jones 5 0 2
TIFFANY Edwards 6 1 4

I hope this clears it up.

thanks again for all the help.

ok for that you need to build a calendar table like

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

between your dateranges
and take cross join of this with distinct list of your names and then use it as master table.
then left join your current query to that and use ISNULL(values,0) to convert nulls to 0s for non sale months

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources