Rollup and Subtotal - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Rollup and Subtotal

Author

Topic

das
Starting Member

8 Posts

Posted - 2002-11-19 : 07:27:13

I working on an accounting program for a manufacturing company and need to generate a report. Basically our company will give their customers product if our plant is phyiscally closer to the customer. Ex If they have customer on East coast and we have a plant in New York will ship the product on their behave to the customer. They in turn do the same for us. No money is exchange but a running total of who owes what is keep.

To simplify the query I have a table below that has company , name, us/them column, std cost and qty. This is a running total so the standard cost can vary. My report output should be group by company and product and should the difference between use and them.

The To Date dollars is the standard cost * the quantity.

Here's the ddl.

create table ##tblResults

( strAccountNo varchar(20),
strProductName varchar(40),
blnUsThem bit,
strProduct varchar(20)
numStdCost float,
intQty int,
)

insert ##tblResults values('1234','MyCompany', 1,'Cuastic Soda', .100, 100)
insert ##tblResults values('1234','TheirCompany', 0,'Cuastic Soda', .100, 60)
insert ##tblResults values('1234','TheirCompany', 0, 'Cuastic Soda',.005, 20)

The results would be

Account# Company StdCost Qty ToDate
1234 MyCompany .100 100 10
1234 TheirCompany .1 60 6
1234 TheirCompany .05 20 1
3

The value of 3 is the difference between us and them. Can someone help me with this query. The results will be dumped to Excel.

Thanks in Advance
Derek

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-19 : 12:57:43

Bit confused... the difference between us and them is what? I don't quite see how you get the value of '3' from the example provided. What columns are manipulated to get the 'difference between us and them'

-----------------------
SQL isn't just a hobby, It's an addiction

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-19 : 13:27:14

I THINK you want to do something like this:


SELECT 
    SUM((CASE WHEN Company = "MyCompany" THEN 1 ELSE -1 END) *    
    numStdCost * intQty) AS Result
FROM
    ##tblResults

das
Starting Member

8 Posts

Posted - 2002-11-19 : 19:35:31

Sorry for confussion, it was a typo. The value is 3.9 instead of 3. I meant to type .05 instead of .005. The 'difference was the (.005 * 20) + (.1 * 60) = 6.1 for them compared to the (.1 * 100) 10 for us.
10-6.1 = 3.9.

jsmith8858 you query did return what I was looking for but I wanted details followed but the result/subtotal returned in one result set so I could dump to Excel with the copy recordset method. Also there are many different account #'s in the final table so it needs grouped by account #.

Appreciate all the help.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-20 : 08:05:37

I didn't see your blnUs field, that should've been used instead of company name to decide whether to multiply by 1 or -1.

to get detail before the total, do a UNION of the detail records with the total records. Then you have to do some tricks to get it to sort correctly. By the way, if you are doing this in Excel anyway, I would just use subtotals in Excel from the detail, it's much easier. Just do the multiply by 1 or -1 thing I have done for the amount and your totals should just add right up for each acct.

But to get the results in 1 recordset, you could try something like:



SELECT * FROM 
(
SELECT strAccountNo,
  strProductName,
  sProduct,
  numStdCost,
  intQty,
  null as TOTAL
FROM
 ##tblResults

UNION ALL

SELECT 
  strAccountNo,
  'TOTAL' AS strProductName,
  Null AS sProduct,
  Null AS numStdCost,
  Null AS intQty,
  SUM((CASE WHEN blnUs = 1 THEN 1 ELSE -1 END) * numStdCost *
       intQty) AS TOTAL
FROM
    ##tblResults
GROUP BY strAccountNo
) A

ORDER BY
   strAccountNo,
   TOTAL DESC,
   strProductName

The above may need some tweaking (I am working w/o SQL or a query
analyzer on hand right now), but a UNION is one way to get detail
and also totals or subtotals in 1 table.

- Jeff

Edited by - jsmith8858 on 11/20/2002 08:08:20

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources