1. 

   KnuttyKitten, Member

   I need to build a sales tax report that needs three elements

   1. gross sales over the entire biz,
   2. gross sales in the specific state
   3. Taxed Sales in the specific state

   I have three different queries that generate these elements . How do I combine the different sums into one table?

   ------3. Taxed Sales in the specific state

   select 'dbr.crosstab', 'Order State'; select 'dbr.hdr', 'Year', 'Month'; select 'dbr.sum', 'Taxed Gross Sales (In State)','Sales Tax Collected'; select 'dbr.colstyle', 'Year', mydbr_style('NumAsStr');

   select year (a.date/time) as 'Year', MONTHNAME(STR_TO_DATE(Month (a.date/time),'%m') )as 'Month', a.type as 'Order Type', a.order state as 'Order State', sum(a.Product Sales) as 'Taxed Gross Sales (In State)', sum(a.sales tax collected) as 'Sales Tax Collected' from dsguru_eBayMadeEasy.AzTransactionRpts a inner join dsguru_eBayMadeEasy.users u on u.cid = a.cid where (a.type = 'Order' or a.type = 'Refund') and a.sales tax collected <> 0 and u.username = inLogin group by year,Month (a.date/time) , a.type, a.order state order by year desc,Month (a.date/time) desc , a.type;

   ------ 2. gross sales in the specific state select 'dbr.crosstab', 'Order State'; select 'dbr.hdr', 'Year'; select 'dbr.hdr', 'Month'; select 'dbr.sum', 'Gross Sales (in state)','Sales Tax Collected'; select 'dbr.colstyle', 'Year', mydbr_style('NumAsStr'); select year (a.date/time) as 'Year', MONTHNAME(STR_TO_DATE(Month (a.date/time),'%m') )as 'Month', a.type as 'Order Type', a.order state as 'Order State', sum(a.Product Sales) as 'Gross Sales (in state)', sum(a.sales tax collected) as 'Sales Tax Collected' from dsguru_eBayMadeEasy.AzTransactionRpts a inner join dsguru_eBayMadeEasy.users u on u.cid = a.cid where (a.type = 'Order' or a.type = 'Refund') and u.username = inLogin and (a.Order State = 'CA' or a.Order State = 'FL' or a.Order State = 'TX') group by year,Month (a.date/time) , a.type, a.order state order by year desc,Month (a.date/time) desc , a.type;

   ----- 1. gross sales over the entire biz, select 'dbr.hdr', 'Year'; select 'dbr.hdr', 'Month'; select 'dbr.sum', 'Gross Sales (all locations)','Sales Tax Collected'; select 'dbr.colstyle', 'Year', mydbr_style('NumAsStr');

   select year (a.date/time) as 'Year', MONTHNAME(STR_TO_DATE(Month (a.date/time),'%m') )as 'Month', a.type as 'Order Type', sum(a.Product Sales) as 'Gross Sales (all locations)', sum(a.sales tax collected) as 'Sales Tax Collected' from dsguru_eBayMadeEasy.AzTransactionRpts a inner join dsguru_eBayMadeEasy.users u on u.cid = a.cid where (a.type = 'Order' or a.type = 'Refund') and u.username = inLogin group by year,Month (a.date/time) , a.type order by year desc,Month (a.date/time) desc , a.type;

   Thanks In Advance
2. 

   myDBR Team, Key Master

   How would you like the combined result to look like?

   You have three different queries (views) to same data. You could combine the first two by specifying an IF statement on 'Gross Sales (in state)' and 'Sales Tax Collected', but the end result would then show both Taxed Sales and Gross Sales in same result which might be difficult to read.

   The last query is the same as the second one it just contains all states. How would you like to separate California, Florida and Texas from othger states in output if all are combined?

   --
   myDBR Team
3. 

   KnuttyKitten, Member

   Thank you for the response,

   Your if statement suggestion started me down the path of using case statements.

   Since I only really care about 3 states I ended up making three reports and using the following to sum up the data (changing the state to match the report.

   sum(a.Product Sales) as 'Gross Sales', sum(case when a.order state <> 'CA' or (a.order state = 'CA' and a.sales tax collected = 0) then a.Product Sales else 0 end) as 'Exempt Sales', sum(case when a.order state = 'CA' then a.Product Sales else 0 end) as 'Gross Sales (CA)', sum(case when a.order state = 'CA' and a.sales tax collected <> 0 then a.Product Sales else 0 end) as 'Taxed Sales (CA)', sum(case when a.order state = 'CA' then a.sales tax collected else 0 end) as 'CA Tax Collected'

   Thank You!

Reply

You must log in to post.