SQL Query for Sales Tax Report

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!
LVL 6
worthyking1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dany BalianCTOCommented:
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
worthyking1Author Commented:
@mrdany - SQL Execution Error: 'list' is not a recognized built-in function name
0
Dany BalianCTOCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dany BalianCTOCommented:
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
Brian CroweDatabase AdministratorCommented:
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
worthyking1Author Commented:
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
awking00Commented:
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
Dany BalianCTOCommented:
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
worthyking1Author Commented:
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
Dany BalianCTOCommented:
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
worthyking1Author Commented:
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
Brian CroweDatabase AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dany BalianCTOCommented:
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
worthyking1Author Commented:
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
worthyking1Author Commented:
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
Dany BalianCTOCommented:
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
worthyking1Author Commented:
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
Dany BalianCTOCommented:
what does the parse error say?
0
worthyking1Author Commented:
Error in WHERE clause near '('. Unable to parse query text.
0
Dany BalianCTOCommented:
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
Brian CroweDatabase AdministratorCommented:
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
worthyking1Author Commented:
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
Brian CroweDatabase AdministratorCommented:
I don't get that same warning when I run the code you posted so I'm not sure what it could be.
0
Dany BalianCTOCommented:
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
worthyking1Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.