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

Other SQL Server Topics (2005)

Need Help

Author

Topic

derozza
Starting Member

13 Posts

Posted - 2010-10-20 : 22:11:30

Hello Master, I need help with my current sql query situation. What am doing is, i try to combining 2 query in the same table. How do i solve this problem. This is my two query statement.

1.

SELECT pos_cashreceiptdetail.LocationCode,pos_cashreceiptpayment.TerminalCode,pos_cashreceiptpayment.ReceiptDate,
pos_cashreceiptdetail.ShortDescription,pos_cashreceiptpayment.ReceiptNo,SUM(Totalprice)
FROM pos_cashreceiptdetail,pos_cashreceiptpayment
WHERE pos_cashreceiptdetail.ShortDescription LIKE 'Food Stall%'
AND pos_cashreceiptdetail.ReceiptDate = pos_cashreceiptpayment.ReceiptDate
AND pos_cashreceiptdetail.ReceiptNo = pos_cashreceiptpayment.ReceiptNo
AND pos_cashreceiptdetail.ReceiptDate BETWEEN '2010-10-06' AND '2010-10-20'
GROUP BY pos_cashreceiptpayment.ReceiptDate

SELECT pos_cashreceiptdetail.LocationCode,pos_cashreceiptpayment.TerminalCode,pos_cashreceiptpayment.ReceiptDate,
pos_cashreceiptdetail.Description,pos_cashreceiptpayment.ReceiptNo,SUM(TotalCost)
FROM pos_cashreceiptdetail,pos_cashreceiptpayment
WHERE pos_cashreceiptdetail.Description LIKE 'Raw%'
AND pos_cashreceiptdetail.ReceiptNo = pos_cashreceiptpayment.ReceiptNo
AND pos_cashreceiptdetail.ReceiptDate = pos_cashreceiptpayment.ReceiptDate
AND pos_cashreceiptdetail.ReceiptDate BETWEEN '2010-10-06' AND '2010-10-20'
GROUP BY pos_cashreceiptpayment.ReceiptDate

What my problem is;
1. The "total cost" query by using Description in the A Table.
2. The "Total Price" query by using ShortDescription in A Table.
3. I need to show the result like this;-
|LocationCode|TerminalCode|ReceiptDate|TotalCost(Query from Description)|TotalPrice(Query from ShortDescription)| both this query in same table.

Please help me.This output i need to put in VB6 system. I need it urgent. Thank u in advance..

Cheers

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-21 : 03:04:16

Not sure I totally understand but seems like you want this (or something very close to it):


SELECT	a.LocationCode, a.TerminalCode, a.ReceiptDate,
	b.TotalCost, a.TotalPrice
FROM	(
	SELECT	d.LocationCode, p.TerminalCode, p.ReceiptDate,
		d.ShortDescription, p.ReceiptNo, SUM(Totalprice) TotalPrice
	FROM	pos_cashreceiptdetail d
	JOIN	pos_cashreceiptpayment p
	On	d.ReceiptDate = p.ReceiptDate
	AND	d.ReceiptNo = p.ReceiptNo
	WHERE	d.ShortDescription LIKE 'Food Stall%'
	AND	d.ReceiptDate BETWEEN '2010-10-06' AND '2010-10-20'
	GROUP BY
		p.ReceiptDate
) a
JOIN	(	
	SELECT	d.LocationCode, p.TerminalCode, p.ReceiptDate,
		d.Description, p.ReceiptNo, SUM(TotalCost) TotalCost
	FROM	pos_cashreceiptdetail p
	JOIN	pos_cashreceiptpayment p
	On	d.ReceiptNo = p.ReceiptNo
	AND	d.ReceiptDate = p.ReceiptDate
	WHERE	d.Description LIKE 'Raw%'
	AND	d.ReceiptDate BETWEEN '2010-10-06' AND '2010-10-20'
	GROUP BY
		p.ReceiptDate
) b
On	a.LocationCode = b.LocationCode
And	a.TerminalCode = b.TerminalCode
And	a.ReceiptDate = b.ReceiptDate
And	a.ReceiptNo = b.ReceiptNo

derozza
Starting Member

13 Posts

Posted - 2010-10-21 : 03:47:15

Thank your for the reply Master. I already follow the query

Like this:-

SELECT	a.LocationCode, a.TerminalCode, a.ReceiptDate,
	b.TotalCost, a.TotalPrice
FROM	(
	SELECT	pos_cashreceiptdetail.LocationCode, pos_cashreceiptpayment.TerminalCode,pos_cashreceiptpayment.ReceiptDate,
		pos_cashreceiptdetail.ShortDescription, pos_cashreceiptpayment.ReceiptNo, SUM(Totalprice) AS TotalPrice
	FROM	pos_cashreceiptdetail 
	JOIN	pos_cashreceiptpayment
	ON	pos_cashreceiptdetail.ReceiptDate = pos_cashreceiptpayment.ReceiptDate
	AND	pos_cashreceiptdetail.ReceiptNo = pos_cashreceiptpayment.ReceiptNo
	WHERE	pos_cashreceiptdetail.ShortDescription LIKE 'Food Stall%'
	AND	pos_cashreceiptdetail.ReceiptDate BETWEEN '2010-10-06' AND '2010-10-20'
	GROUP BY
		pos_cashreceiptpayment.ReceiptDate
) AS a
JOIN	(	
	SELECT	pos_cashreceiptdetail.LocationCode, pos_cashreceiptpayment.TerminalCode, pos_cashreceiptpayment.ReceiptDate,
		pos_cashreceiptdetail.Description, pos_cashreceiptpayment.ReceiptNo, SUM(TotalCost)AS TotalCost
	FROM	pos_cashreceiptdetail 
	JOIN	pos_cashreceiptpayment
	ON	pos_cashreceiptdetail.ReceiptNo = pos_cashreceiptpayment.ReceiptNo
	AND	pos_cashreceiptdetail.ReceiptDate = pos_cashreceiptpayment.ReceiptDate
	WHERE	pos_cashreceiptdetail.Description LIKE 'Raw%'
	AND	pos_cashreceiptdetail.ReceiptDate BETWEEN '2010-10-06' AND '2010-10-20'
	GROUP BY
		pos_cashreceiptdetail.ReceiptDate
)AS b
ON	a.LocationCode = b.LocationCode
AND	a.TerminalCode = b.TerminalCode
AND	a.ReceiptDate = b.ReceiptDate
AND	a.ReceiptNo = b.ReceiptNo

Once i execute i didn't get any data. I just wondering coz once am doing single query i get my data. Do u have any solution for this. The query above is exactly on my query in sql.

Please advise me.. thank u

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-21 : 05:19:01

How can the query even run!?
You are only grouping by Receipt date but uses several other nonaggregated columns in your select.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-21 : 05:22:18

[code]SELECT crp.ReceiptDate,
crp.ReceiptNo,
SUM(CASE WHEN crd.ShortDescription LIKE 'Food Stall%' THEN crd.TotalPrice ELSE 0 END),
SUM(CASE WHEN crd.[Description] LIKE 'Raw%' THEN crd.TotalCost ELSE 0 END)
FROM dbo.Pos_CashReceiptDetail AS crd
INNER JOIN dbo.Pos_CashReceiptPayment AS crp ON crp.ReceiptDate = crd.ReceiptDate
AND crp.ReceiptNo = crd.ReceiptNo
WHERE crd.ReceiptDate BETWEEN '20101006' AND '20101020'
AND (
crd.ShortDescription LIKE 'Food Stall%'
OR crd.[Description] LIKE 'Raw%'
)
GROUP BY crp.ReceiptDate,
crp.ReceiptNo[/code]

N 56°04'39.26"
E 12°55'05.63"

derozza
Starting Member

13 Posts

Posted - 2010-10-21 : 20:54:01

Thank your for ur help Master. Your query reach my need.. Yes.. i really appreciate. I struggle this query almost 4day. to settle it up and at last.. this forum helping me to settle my problem.

Thank you again.. SQLTEAM.COM is the best...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources