Combine 2 Queries - 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)

Combine 2 Queries

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-13 : 17:41:31

Is it possible to combine these 2 queries into one? Our accounting software inserts the sales reps and their sales for the day. Each day it appends the new rows to the database. I would like to comine these into 1 query, so I can display the previous day's sales for each rep.

SELECT A.slspsn_no, A.Slspsn_name, A.sls_ptd, A.sls_ytd, A.cost_ptd, A.cost_ytd, B.costptd, B.costytd, B.slsptd, B.slsytd
FROM ARSLMFIL2_SQL A, (
SELECT SUM(cost_ptd) AS costptd, SUM(cost_ytd) AS costytd, SUM(sls_ptd) AS slsptd, SUM(sls_ytd) AS slsytd
FROM ARSLMFIL2_SQL
) AS B
WHERE A.slspsn_no NOT IN ('114', '115', 'WEB')
AND idate IN (SELECT TOP 1 idate FROM ARSLMFIL2_SQL ORDER BY idate DESC)
ORDER BY User_Def_Fld_1 ASC

This next query will display the prior day's sales


SELECT A.slspsn_no, A.Slspsn_name, A.sls_ptd, A.sls_ytd, A.cost_ptd, A.cost_ytd, B.costptd, B.costytd, B.slsptd, B.slsytd
FROM ARSLMFIL2_SQL A, (
SELECT SUM(cost_ptd) AS costptd, SUM(cost_ytd) AS costytd, SUM(sls_ptd) AS slsptd, SUM(sls_ytd) AS slsytd
FROM ARSLMFIL2_SQL
) AS B
WHERE A.slspsn_no NOT IN ('114', '115', 'WEB')
AND idate IN (SELECT TOP 1 idate
FROM ARSLMFIL2_SQL
WHERE idate IN (SELECT DISTINCT Top 2 idate FROM ARSLMFIL2_SQL ORDER BY idate DESC)
ORDER BY idate ASC)
ORDER BY User_Def_Fld_1 ASC

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2004-07-13 : 17:50:49

I'm not sure what you are asking here. If you are looking to get the today's and the previous days sales in one quarter this may work for you.

SELECT A.slspsn_no, A.Slspsn_name, A.sls_ptd, A.sls_ytd, A.cost_ptd, A.cost_ytd, B.costptd, B.costytd, B.slsptd, B.slsytd
FROM ARSLMFIL2_SQL A, (
SELECT SUM(cost_ptd) AS costptd, SUM(cost_ytd) AS costytd, SUM(sls_ptd) AS slsptd, SUM(sls_ytd) AS slsytd
FROM ARSLMFIL2_SQL
) AS B
WHERE A.slspsn_no NOT IN ('114', '115', 'WEB')
AND idate IN (SELECT DISTINCT Top 2 idate FROM ARSLMFIL2_SQL ORDER BY idate DESC)
ORDER BY idate ASC)
ORDER BY User_Def_Fld_1 ASC

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-14 : 12:16:48

Yep! That worked. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources