• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

SQL Server Subquery

I need some help restructuring this query so it does not bring in TotalSales that = 0. I end up with gaps in the data I would like to eliminate.



SELECT			pd.StoreID, st.ShortName, pd.Hour, ROUND(SUM(pd.TaxableSales) + SUM(pd.TaxExemptSales), - 2) AS TotalSales

FROM            POSDataDetailByHour AS pd 
				INNER JOIN FiscalDatesByDay AS fd ON fd.TransactionDate = pd.TransactionDate 
				INNER JOIN Stores AS st ON st.StoreID = pd.StoreID

WHERE			(pd.TransactionDate BETWEEN '6-11-13' AND '6-12-13')

GROUP BY		 pd.StoreID, st.ShortName, pd.Hour

Open in new window

0
dk04
Asked:
dk04
1 Solution
 
Patrick MatthewsCommented:
SELECT pd.StoreID, st.ShortName, pd.Hour, 
    ROUND(SUM(pd.TaxableSales) + SUM(pd.TaxExemptSales), - 2) AS TotalSales
FROM POSDataDetailByHour AS pd INNER JOIN 
    FiscalDatesByDay AS fd ON fd.TransactionDate = pd.TransactionDate INNER JOIN 
    Stores AS st ON st.StoreID = pd.StoreID
WHERE (pd.TransactionDate BETWEEN '6-11-13' AND '6-12-13')
GROUP BY pd.StoreID, st.ShortName, pd.Hour
HAVING ROUND(SUM(pd.TaxableSales) + SUM(pd.TaxExemptSales), - 2) <> 0

Open in new window

0
 
didnthaveanameCommented:
try:

SELECT			pd.StoreID, st.ShortName, pd.Hour, ROUND(SUM(pd.TaxableSales) + SUM(pd.TaxExemptSales), - 2) AS TotalSales

FROM            POSDataDetailByHour AS pd 
				INNER JOIN FiscalDatesByDay AS fd ON fd.TransactionDate = pd.TransactionDate 
				INNER JOIN Stores AS st ON st.StoreID = pd.StoreID

WHERE			(pd.TransactionDate BETWEEN '6-11-13' AND '6-12-13')

GROUP BY		 pd.StoreID, st.ShortName, pd.Hour
having
    ROUND(SUM(pd.TaxableSales) + SUM(pd.TaxExemptSales), - 2) > 0

Open in new window

0
 
dk04Author Commented:
Thank you. It worked great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now