[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query for Sales Tax Report

Posted on 2014-08-15
26
Medium Priority
?
199 Views
Last Modified: 2014-08-17
I need help with designing a SQL 2008 query to pull the data needed for a quarterly sales tax report.  The data is contained in the orders table and each order includes the taxing state, county & city, along with the tax percentages for each, and the total tax percentage. Most counties will have the same total tax BUT one or two cities will have their own additional local tax which needs to NOT be included in the total for the overall county.

So, here's what my table looks like with some test data in it:

 
ID, OrderDT, Tax_State, Tax_County, Tax_City, Tax_Percent_City, Tax_Percent_County, Tax_Percent_State, Tax_Percent_Total, Tax_Shipping, Order_Tax, Order_Shipping, Order_SubTotal, Order_Total

8	2014-08-08 11:42:40.000	CA	LOS ANGELES	GLENDALE	0.000000	0.015000	0.075000	0.090000	True	12.9500	13.3400	130.5900	156.8800
9	2014-08-08 11:41:03.000	CA	LOS ANGELES	GLENDALE	0.000000	0.015000	0.075000	0.090000	True	12.9500	13.3400	130.5900	156.8800
10	2014-08-08 11:40:11.000	CA	LOS ANGELES	GLENDALE	0.000000	0.015000	0.075000	0.090000	True	12.9500	13.3400	130.5900	156.8800
11	2014-08-08 11:31:17.000	CA	LOS ANGELES	GLENDALE	0.000000	0.015000	0.075000	0.090000	True	12.9500	13.3400	130.5900	156.8800
12	2014-08-08 11:14:14.000	CA	LOS ANGELES	BURBANK	0.000000	0.015000	0.075000	0.090000	True	12.9500	13.3400	130.5900	156.8800
14	2014-08-08 10:16:35.000	CA	LOS ANGELES	INGLEWOOD	0.000000	0.015000	0.075000	0.090000	True	4.4100	8.8000	40.2300	53.4400
15	2014-08-08 10:14:13.000	CA	LOS ANGELES	GLENDALE	0.000000	0.015000	0.075000	0.090000	True	4.4100	8.8000	40.2300	53.4400
19	2014-08-08 11:31:17.000	CA	LOS ANGELES	SUNLAND	0.000000	0.015000	0.075000	0.090000	True	12.9500	13.3400	130.5900	156.8800
20	2014-08-08 11:14:14.000	CA	LOS ANGELES	GLENDALE	0.000000	0.015000	0.075000	0.090000	True	12.9500	13.3400	130.5900	156.8800
21	2014-08-08 10:16:35.000	CA	LOS ANGELES	GLENDALE	0.000000	0.015000	0.075000	0.090000	True	4.4100	8.8000	40.2300	53.4400
22	2014-08-08 10:14:13.000	CA	ONYX	KERN	0.000000	0.000000	0.075000	0.075000	True	4.4100	8.8000	40.2300	53.4400
42	2014-08-08 10:14:13.000	CA	ONYX	KERN	0.000000	0.000000	0.075000	0.075000	True	4.4100	8.8000	40.2300	53.4400
28	2014-08-08 10:14:13.000	CA	ONYX	LOST HILLS	0.010000	0.000000	0.075000	0.085000	True	4.4100	8.8000	40.2300	53.4400
25	2014-08-08 10:09:54.000	CA	ORANGE	SAN JUAN CAPISTRANO	0.000000	0.005000	0.075000	0.080000	True	4.4100	8.8000	40.2300	53.4400
26	2014-08-08 10:09:54.000	CA	ORANGE	SAN JUAN CAPISTRANO	0.000000	0.005000	0.075000	0.080000	True	4.4100	8.8000	40.2300	53.4400
27	2014-08-08 10:09:54.000	CA	ORANGE	SANTA ANA	0.000000	0.005000	0.075000	0.080000	True	4.4100	8.8000	40.2300	53.4400
16	2014-08-08 10:09:54.000	CA	ORANGE	SANTA ANA	0.000000	0.005000	0.075000	0.080000	True	4.4100	8.8000	40.2300	53.4400
18	2014-08-08 10:09:54.000	CA	SANTA CLARA	SAN JOSE	0.000000	0.012500	0.075000	0.087500	True	4.4100	8.8000	40.2300	53.4400
23	2014-08-08 10:09:54.000	CA	SANTA CLARA	SAN JOSE	0.000000	0.012500	0.075000	0.087500	True	4.4100	8.8000	40.2300	53.4400
24	2014-08-08 10:09:54.000	CA	SANTA CLARA	SAN JOSE	0.000000	0.012500	0.075000	0.087500	True	4.4100	8.8000	40.2300	53.4400
7	2014-08-08 11:47:54.000	MO	JACKSON	BLUE SPRINGS	0.025000	0.012500	0.042250	0.079750	True	11.4800	13.3400	130.5900	155.4100
17	2014-08-08 10:07:16.000	MO	JACKSON	BLUE SPRINGS	0.025000	0.012500	0.042250	0.079750	True	4.2300	7.8000	45.2000	57.2300
13	2014-08-08 10:56:56.000	MO	JACKSON	BLUE SPRINGS	0.025000	0.012500	0.042250	0.079750	True	4.2300	7.8000	45.2000	57.2300 

Open in new window


I need a query that returns rows containing the following columns:

1. SUM of the Order_Total for each county that has the same Tax_Percent_Total, for example: one row for LOS ANGELES county with the sum of all orders, but two rows for ONYX county because one city therein (LOST HILLS) has a higher tax total (due to local city tax) than the rest.

2. Tax_State

3. Tax_County

4. Tax_City

5. Tax_Percent_Total


I would like my output to look something like this:

Tax_State	Tax_County	Tax_City	Tax_Percent_Total	Sales_Total									
MO	JACKSON		BLUE SPRINGS		0.07975			269.87
CA	LOS ANGELES	[MULTIPLE] 		0.090000		1268.88			
CA	ONYX		[MULTIPLE]		0.075			53.44
CA	ONYX		LOST HILLS		0.085			53.44
CA	ORANGE		[MULTIPLE]		0.08			213.66

Open in new window

Thanks for your help!
0
Comment
Question by:worthyking1
  • 10
  • 10
  • 4
  • +1
25 Comments
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40263752
select Tax_State, tax_county, list(Tax_City), Tax_Percent_Total, sum(Order_Total)
from yourtablename
group by tax_state, tax_county, tax_percent_total

Open in new window

0
 
LVL 6

Author Comment

by:worthyking1
ID: 40263763
@mrdany - SQL Execution Error: 'list' is not a recognized built-in function name
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40263780
select Tax_State, tax_county, Tax_Percent_Total, sum(Order_Total)
from yourtablename
group by tax_state, tax_county, tax_percent_total

Open in new window


try this.. if this is what you want, we can get the multiple city names in another sql
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 11

Expert Comment

by:Dany Balian
ID: 40263791
select t1.Tax_State, t1.tax_county, t1.Tax_Percent_Total, sum(t1.Order_Total), stuff((select ', ' + t2.tax_city 
           from YourTablename t2 where t1.ID = t2.ID 
           for xml path('')),
          1,2,'') [Values]
from yourtablename t1
group by t1.tax_state, t1.tax_county, t1.tax_percent_total

Open in new window

maybe something like this.. sorry i dont' have sql server.. and list is a function that i use a lot in sybase
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40263808
SELECT Tax_State, Tax_County, Tax_Percent_Total,
      CASE WHEN COUNT(DISTINCT Tax_City) > 1 THEN 'MULTIPLE' ELSE MIN(Tax_City) END AS Tax_City,
      SUM(Order_Total)
FROM #Order
GROUP BY Tax_State, Tax_County, Tax_Percent_Total
0
 
LVL 6

Author Comment

by:worthyking1
ID: 40263820
There must be a syntax error in the STUFF portion of the above query because it spits out numerous errors. I have never used STUFF before so I am at a loss as to how to debug it.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40263821
A couple of questions -
Shouldn't the sales_total for CA LOS ANGELES be 1258.48 (7 * 156.88 + 3 * 53.44)?
Shouldn't the sales_total for CA ORANGE be 213.76 (4 * 53.44)?
Shouldn't the sales_total for CA ONYX [MULTIPLE] be 106.88 (2 * 53.44)?
Why isn't the tax_city for MO JACKSON [MULTIPLE] instead of BLUE SPRINGS? Is it because only one county is shown for that state?
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40263829
brian's sql should do the trick..
i thought about using the case when count()>1 then 'multiple' but i had a problem in the other case.. but min(tax_city) or max(tax_city) is what i missed.. good one brian
0
 
LVL 6

Author Comment

by:worthyking1
ID: 40263830
Brian: yes, that's close to what I'm after. The trouble I'm having is getting a list of the multiple tax_city values into that output column. Is that possible?
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40263840
well, if you need the list of the counties then, in that case we need to work on my syntax.. the stuff is only there to remove the first ,

can you try just this to see if it works?
select t1.Tax_State, t1.tax_county, t1.Tax_Percent_Total, sum(t1.Order_Total), (select ', ' + t2.tax_city 
           from YourTablename t2 where t1.ID = t2.ID)
from yourtablename t1
group by t1.tax_state, t1.tax_county, t1.tax_percent_total

Open in new window

0
 
LVL 6

Author Comment

by:worthyking1
ID: 40263843
awking00: yes, I manually totaled those for the example and didn't have time to use a calculator :)  So, for the purposes of this illustration I'm not too concerned about the amounts.

And, yes, MO is our warehouse ship-from zone, so all MO orders will always be taxed at the BLUE SPRINGS rate ie. the orders table will always only have that one zone for all MO orders.
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 1600 total points
ID: 40263844
Replace #Order in both cases with the name of your table...

SELECT Tax_State, Tax_County, Tax_Percent_Total,
	CASE WHEN COUNT(DISTINCT Tax_City) > 1 THEN 'MULTIPLE' ELSE MIN(Tax_City) END AS Tax_City,
	STUFF((
		SELECT DISTINCT ', ' + O2.Tax_City
		FROM #Order AS O2
		WHERE O2.Tax_State = O.Tax_State
			AND O2.Tax_County = O.Tax_County
			AND O2.Tax_Percent_Total = O.Tax_Percent_Total
		FOR XML PATH('')
	), 1, 2, '') AS Tax_City2,
	SUM(Order_Total)
FROM #Order AS O
GROUP BY Tax_State, Tax_County, Tax_Percent_Total

Open in new window

0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40263849
select t1.Tax_State, t1.tax_county, t1.Tax_Percent_Total, sum(t1.Order_Total), (select ', ' + t2.tax_city 
           from YourTablename t2 where t1.ID = t2.ID for xml path(''))
from yourtablename t1
group by t1.tax_state, t1.tax_county, t1.tax_percent_total

Open in new window

0
 
LVL 6

Author Comment

by:worthyking1
ID: 40263868
mrdany: with your query I get the error "TableName.ID is invalid in the select list because it is not included in an aggregate function..."
0
 
LVL 6

Author Comment

by:worthyking1
ID: 40263871
Brian: that query is awesome, except that is throws a "Error in WHERE clause near '('. Unable to parse query text." error.

After I clear the error it does however produce the results I need.
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40263873
ok then link table1 and table2 using the group by fields

select t1.Tax_State, t1.tax_county, t1.Tax_Percent_Total, sum(t1.Order_Total), stuff((select ', ' + t2.tax_city 
           from YourTablename t2 where t1.tax_state=t2.tax_state and t1.tax_count=t2.tax_county and t1.tax_percent_total=t2.tax_percent_total 
           for xml path('')),
          1,2,'') [Values]
from yourtablename t1
group by t1.tax_state, t1.tax_county, t1.tax_percent_total

Open in new window

0
 
LVL 6

Author Comment

by:worthyking1
ID: 40263905
mrdany: yes, that does work, however not quite as elegant a solution as Brian's as it doesn't output the flag column ('MULTIPLE') and doesn't use DISTINCT values, but I can adjust that as needed.

The last issue I have is that both queries (yours and Brian's) produce a parse error as i mentioned above when running the query through Mgt Studio. I have to clear the warning before it produces the output. This will still, of course, produce a recordset via a web query, but I'd rather not fill up my logs with needless warnings.
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40263917
what does the parse error say?
0
 
LVL 6

Author Comment

by:worthyking1
ID: 40263986
Error in WHERE clause near '('. Unable to parse query text.
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40264004
are you using the exact sql, or modifying it.. can you paste the exact sql you're using?
try creating a view..

create view dbo.yourviewname
as
select t1.Tax_State, t1.tax_county, t1.Tax_Percent_Total, sum(t1.Order_Total), stuff((select ', ' + t2.tax_city 
           from YourTablename t2 where t1.tax_state=t2.tax_state and t1.tax_count=t2.tax_county and t1.tax_percent_total=t2.tax_percent_total 
           for xml path('')),
          1,2,'') [Values]
from yourtablename t1
group by t1.tax_state, t1.tax_county, t1.tax_percent_total
GO

Open in new window


then use this view in your queries

select * from yourviewname

Open in new window

0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40264067
There shouldn't be any syntax errors in what I posted as I am running the exact code on my own machine without issue.  Is it possible you lost something in the copy/paste/edit process?  Do you have both opening parentheses after STUFF?  One for the STUFF function and the second for the subquery?
0
 
LVL 6

Author Comment

by:worthyking1
ID: 40264099
Strange. The query does run, but I still get the warning first "Error in list of function arguments: '(' not recognized."

Here's my exact query, as modified:

SELECT YEAR(OrderDT) as Year, DATEPART(QUARTER, OrderDT) AS Quarter,Tax_State, Tax_County, Tax_Percent_Total,
      CASE WHEN COUNT(DISTINCT Tax_City) > 1 THEN 'MULTIPLE' ELSE MIN(Tax_City) END AS Tax_City,
      STUFF((
            SELECT DISTINCT ', ' + O2.Tax_City
            FROM REKO_Orders_TaxDev AS O2
            WHERE O2.Tax_State = O.Tax_State
                  AND O2.Tax_County = O.Tax_County
                  AND O2.Tax_Percent_Total = O.Tax_Percent_Total
                  AND YEAR(O2.OrderDT) = YEAR(O.OrderDT)
                  AND DATEPART(QUARTER, O2.OrderDT) = DATEPART(QUARTER, O.OrderDT)
            FOR XML PATH('')
      ), 1, 2, '') AS Tax_City2,      
      SUM(Order_SubTotal+Order_Shipping) as SalesTotal, SUM(Order_Tax) AS TaxCollected
FROM REKO_Orders_TaxDev AS O
GROUP BY YEAR(OrderDT), DATEPART(QUARTER, OrderDT),Tax_State, Tax_County, Tax_Percent_Total
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40264131
I don't get that same warning when I run the code you posted so I'm not sure what it could be.
0
 
LVL 11

Assisted Solution

by:Dany Balian
Dany Balian earned 400 total points
ID: 40264138
Try This:
i removed the distinct in the subquery, and used group by instead
SELECT YEAR(OrderDT) as Year, DATEPART(QUARTER, OrderDT) AS Quarter,Tax_State, Tax_County, Tax_Percent_Total,
      CASE WHEN COUNT(DISTINCT Tax_City) > 1 THEN 'MULTIPLE' ELSE MIN(Tax_City) END AS Tax_City,
      STUFF((
            SELECT ', ' + O2.Tax_City
            FROM REKO_Orders_TaxDev AS O2
            WHERE O2.Tax_State = O.Tax_State
                  AND O2.Tax_County = O.Tax_County
                  AND O2.Tax_Percent_Total = O.Tax_Percent_Total
                  AND YEAR(O2.OrderDT) = YEAR(O.OrderDT)
                  AND DATEPART(QUARTER, O2.OrderDT) = DATEPART(QUARTER, O.OrderDT)
            Group by O2.Tax_City
            FOR XML PATH('')
      ), 1, 2, '') AS Tax_City2,      
      SUM(Order_SubTotal+Order_Shipping) as SalesTotal, SUM(Order_Tax) AS TaxCollected
FROM REKO_Orders_TaxDev AS O
GROUP BY YEAR(OrderDT), DATEPART(QUARTER, OrderDT),Tax_State, Tax_County, Tax_Percent_Total 

Open in new window

0
 
LVL 6

Author Comment

by:worthyking1
ID: 40264239
No, still parser error, but it doesn't affect my web queries, so I'm not going to worry about that little detail for the moment. It's probably our SQL config maybe being more literal than others.

Anyway, thanks for you help guys!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

829 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