[RESOLVED] is there any query string that can do this?
This are the tables examples.
ServicesAndProductsTable
productID, productDescription, type, productCategory
1000, product1, product, skincare
1001, product2, service, facial
1002, product3, product, skincare
1003, product4, service, manicure
ReceiptSummaryTable
ReceiptID, clientID, totalAmt, employeeName
11000, 100, $10, James
11001, 110, $5, James
11002, 150, $15, Sandy
ReceiptRowsTable
ReceiptID, productID, type, amt
11000, 1000, product, $5
11000, 1003, service, $5
11001, 1002, product, $5
11002, 1003, service, $5
11002, 1001, service, $10
==============================================
Expected output:
employee ID, employee Name, productTotal, facialTotal, manicureTotal
1, James, $10, $0, $5
6, Sandy, $0, $10, $5
is there a query which can do this?
productTotal is calculated from the ReceiptSummaryTable where type = product
As for facialTotal and manicureTotal will be slightly complicated.
facialTotal is calculated from ReceiptSummaryTable where type = product AND category = facial in ServicesAndProductsTable.
likewise for manicureTotal.
anyway, i'm using sql server 2000.
Re: is there any query string that can do this?
Your "Expected output" looks like crosstab report. You may experiment with it using ms access database (you'll need to link or import some tables) or perhaps using crystal reports.
Links below may get you started:
http://www.crystalreportsbook.com/Cr....asp?Page=10_1
http://www.blueclaw-db.com/report_dy...stab_field.htm
http://aspalliance.com/1041_creating...rystal_reports
Re: is there any query string that can do this?
i'm working on a window application thus i need the result to be returned in such a way therefore i can pass it over to my window application to be shown on a datagridview.
1 Attachment(s)
Re: is there any query string that can do this?
Well, there is a way but I would first recommend dropping the summary table (you really don't need it) and adding Employees and Clients (if you don't already have those).
See attached image.
Re: is there any query string that can do this?
i had my employee and client table all along.
anyway, manage to get the solution to it from someone else. thanks alot for ur help too!! ^^
for the benefits of whoever that might come across with this problem, below is the solution
Quote:
Originally Posted by visakh16
SELECT RST.employeeName,
SUM(CASE WHEN SPT.type='product' THEN RRT.amt ELSE 0 END) AS productTotal,
SUM(CASE WHEN SPT.productCategory='facial' THEN RRT.amt ELSE 0 END) AS facialTotal,
SUM(CASE WHEN SPT.productCategory='manicure' THEN RRT.amt ELSE 0 END) AS manicureTotal
FROM ReceiptSummaryTable RST
INNER JOIN ReceiptRowsTable RRT
ON RRT.ReceiptID = RST.ReceiptID
INNER JOIN ServicesAndProductsTable SPT
ON SPT.productID = RRT.productID AND SPT.type = RRT.type
GROUP BY RST.employeeName