Solved

SQL Server Subquery

Posted on 2013-06-12
3
258 Views
Last Modified: 2013-06-12
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
Comment
Question by:dk04
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39242648
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
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39242652
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
 

Author Closing Comment

by:dk04
ID: 39242662
Thank you. It worked great!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

821 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