[Resolved] is there any query tat can do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

[Resolved] is there any query tat can do this?

Author

Topic

sphericalx
Starting Member

10 Posts

Posted - 2008-12-11 : 03:06:56

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.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-11 : 04:39:33

try this

SET NOCOUNT ON

DECLARE		@ServicesAndProductsTable TABLE (productID INT, productDescription VARCHAR(1000), 
					type VARCHAR(1000), productCategory VARCHAR(1000))
		
INSERT INTO @ServicesAndProductsTable 
			SELECT 1000, 'product1', 'product', 'skincare'
UNION ALL	SELECT 1001, 'product2', 'service', 'facial'
UNION ALL	SELECT 1002, 'product3', 'product', 'skincare'
UNION ALL	SELECT 1003, 'product4', 'service', 'manicure'

DECLARE		@ReceiptSummaryTable TABLE (ReceiptID INT, clientID INT, totalAmt INT, employeeName VARCHAR(1000))


INSERT INTO	@ReceiptSummaryTable
			SELECT 11000, 100, 10, 'James'
UNION ALL	SELECT 11001, 110, 5, 'James'
UNION ALL	SELECT 11002, 150, 15, 'Sandy'

DECLARE		@ReceiptRowsTable TABLE (ReceiptID INT, productID INT, type VARCHAR(1000), amt INT)

INSERT INTO	@ReceiptRowsTable 
			SELECT 11000, 1000, 'product', $5
UNION ALL	SELECT 11000, 1003, 'service', $5
UNION ALL	SELECT 11001, 1002, 'product', $5
UNION ALL	SELECT 11002, 1003, 'service', $5
UNION ALL	SELECT 11002, 1001, 'service', $10


SELECT		employeeName
			, ISNULL([skincare], 0) AS 'productTotal'
			, ISNULL([facial], 0) AS 'facialTotal'
			, ISNULL([manicure], 0) AS 'manicureTotal'
FROM
			(
					SELECT		RST.employeeName
								, RRT.amt
								, SPT.productCategory
					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
			) P
PIVOT		(
					SUM(amt)
					FOR productCategory IN ([manicure], [skincare], [facial])
			) AS Pvt
ORDER BY	employeeName

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 10:27:30

also


SELECT		RST.employeeName,
SUM(CASE WHEN SPT.productCategory='skincare' 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

sphericalx
Starting Member

10 Posts

Posted - 2008-12-11 : 23:45:49

thnx.. its working perfectly great..

thnx guys for all ur help.. ^^

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources