March 28, 2013 at 8:36 am
Hello ,
I have a scenario which is similar to the ones below :
There are two tables Transaction and Product
Transaction Table has columns
Id, Amount ProductId TransactionDate ,DepartmentId
1, 100 100 01/01/2013 1
2, 200 201 02/01/2013 2 and so on....
Product Table has columns
Id, Name, ProductType.
100, AB , 1
101 C , 2
I want to write a single stored procedure which outputs the following :
Month Year Department Count(Transactions) Count(Transactions of ProductType1)
Feb 2012 1 100 50
Mar 2012 1 100 50
Apr 2012 1 100 50
Feb 2012 2 100 50
I got till here :
select YEAR(T.TransactionDate) AS [YEAR],MONTH(T.TransactionDate) AS [MONTH], Count(T.Id)
from Transaction T INNER JOIN Product P ON P.Id = T.ProductId
group by T.DepartmentId, YEAR(T.TransactionDate), MONTH(T.TransactionDate);
It outputs the following :
Month Year Department Count(Transactions)
I want to know how I can also include :
Count(Transactions of ProductType1)
I do not want to write an seperate query ..but I want to know if there is an efficient way of getting
the sql statement to return the following in one single query ?
March 28, 2013 at 8:40 am
Like this?
sum(case when ProductType = 1 then 1 else 0 end)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 29, 2013 at 6:42 am
Absolutely !
Thats exactly what I was looking for !
Thank you Sean Lange !
March 29, 2013 at 7:42 am
Love & Peace (3/29/2013)
Absolutely !Thats exactly what I was looking for !
Thank you Sean Lange !
You're welcome. Glad that worked for you and thanks for letting me know. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply