On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
Course Of The Month
9 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL SUBQUERY SYNTAX ISSUE
Posted on 2012-12-23
First off happy holidays to you all and I hope if anyone else is working this weekend like me everything is going smoothly. so here is this situation. I have two separate queries I am trying to merge into a sub-query however I can’t seem to get it to work properly and I know it is a simple syntax issue that someone with more skills can identify.
Here are the 2 queries:
QUERY 1
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_PLACED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
Which returns:
YEAR MONTH TOTAL_PLACED
2012 8 3101542.03
2012 9 285786.43
2012 10 1224473.49
2012 11 327823.32
2012 12 315477.44
QUERY 2
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
WHICH RETURNS:
YEAR MONTH TOTAL_LOCATED
2012 8 2905070.72
2012 9 202220.81
2012 10 462872.94
2012 11 77451.27
2012 12 22331.84
Both return the values expected. The problem I have is when I try to nest (sub-query) the 2nd query into the first with this query:
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_PLACED,
(SELECT SUM(AFACINIAMT)
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR')AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
I get the following which is incorrect:
YEAR MONTH TOTAL_PLACED TOTAL_LOCATED
2012 8 3101542.03 3669947.58
2012 9 285786.43 3669947.58
2012 10 1224473.49 3669947.58
2012 11 327823.32 3669947.58
2012 12 315477.44 3669947.58
Please help if you can!. Thanks and Happy Holidays
Here are the 2 queries:
QUERY 1
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_PLACED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
Which returns:
YEAR MONTH TOTAL_PLACED
2012 8 3101542.03
2012 9 285786.43
2012 10 1224473.49
2012 11 327823.32
2012 12 315477.44
QUERY 2
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
WHICH RETURNS:
YEAR MONTH TOTAL_LOCATED
2012 8 2905070.72
2012 9 202220.81
2012 10 462872.94
2012 11 77451.27
2012 12 22331.84
Both return the values expected. The problem I have is when I try to nest (sub-query) the 2nd query into the first with this query:
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_PLACED,
(SELECT SUM(AFACINIAMT)
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR')AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
I get the following which is incorrect:
YEAR MONTH TOTAL_PLACED TOTAL_LOCATED
2012 8 3101542.03 3669947.58
2012 9 285786.43 3669947.58
2012 10 1224473.49 3669947.58
2012 11 327823.32 3669947.58
2012 12 315477.44 3669947.58
Please help if you can!. Thanks and Happy Holidays
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
9
3-
3
- +3
20 Comments
You should look at doing the UNION ALL command as it will allow the joining of two result sets.
http://www.w3schools.com/sql/sql_union.asp
http://www.w3schools.com/sql/sql_union.asp
I would rather do a JOIN of the 2 subquerys...
SELECT q1.YEAR, q1.MONTH, q1.TOTAL_PLACED, q2.TOTAL_LOCATED
FROM ( <yourquery1 ) q1
JOIN ( <yourquery2) q2
ON q1.YEAR = q2.YEAR
AND q1.MONTH = q2.MONTH
A join is dependant on relationship between the two tables, whereas a UNION ALL allows completely distinct queries to be independent of each other.
Just because I forgot to mention I am running these queries from AQT on a Cache database via ODBC. That said 'angelIII' would your solution still be suitable?
@jmdl1983 - I think you are definitley pointing me in the right direction. I tried union when I first started working on this and then had someone point me towards subqueries. I have just tried a few variations and remembered the individual queries with unions must contain the final count of columns so I tried this and no luck. I'm sure I'm close... Thoughts?
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_PLACED,'' as TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
UNION ALL
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,'' as TOTAL_PLACED,
SUM(AFACINIAMT) AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_PLACED,'' as TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
UNION ALL
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,'' as TOTAL_PLACED,
SUM(AFACINIAMT) AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
Error during Prepare
37000(25)[Cache ODBC][State : 37000][Native Code 25]
[C:\Program Files (x86)\Advanced Query Tool v9\aqtv9.exe]
[SQLCODE: <-25>:<Input encountered after end of query>]
[Cache Error: <<SYNTAX>errdone+2^%qaqqt>
[Details: <Prepare>]
[%msg: < SQL ERROR #25: Input (() encountered after end of query^SELECT YEAR ( araclstdte ) AS YEAR , MONTH ( araclstdte ) AS MONTH , SUM ( AFACINIAMT ) AS TOTAL_PLACED , :%qpar(1) as TOTAL_LOCATED FROM SQLUser . STFIELDAUD STF , SQLUser . ARRELATIONSHIP ARR , SQLUser . ARACCOUNT ARA
37000(25)[Cache ODBC][State : 37000][Native Code 25]
[C:\Program Files (x86)\Advanced Query Tool v9\aqtv9.exe]
[SQLCODE: <-25>:<Input encountered after end of query>]
[Cache Error: <<SYNTAX>errdone+2^%qaqqt>
[Details: <Prepare>]
[%msg: < SQL ERROR #25: Input (() encountered after end of query^SELECT YEAR ( araclstdte ) AS YEAR , MONTH ( araclstdte ) AS MONTH , SUM ( AFACINIAMT ) AS TOTAL_PLACED , :%qpar(1) as TOTAL_LOCATED FROM SQLUser . STFIELDAUD STF , SQLUser . ARRELATIONSHIP ARR , SQLUser . ARACCOUNT ARA
if I remove:
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
I of course get the details I need but not the summary. This is the bigger reason I pushed to subquerry so I could apply the grouping one time. If I try to apply in both "legs" (for lack of a better term) of the union query I get an error SQL ERROR #25: Input (UNION) encountered after end of query
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
I of course get the details I need but not the summary. This is the bigger reason I pushed to subquerry so I could apply the grouping one time. If I try to apply in both "legs" (for lack of a better term) of the union query I get an error SQL ERROR #25: Input (UNION) encountered after end of query
I tried just Union instead of Union all and removed the Group/Sort and now much closer than I was however the numbers are now wrong? See below:
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_PLACED,'' as TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
UNION
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,'' as TOTAL_PLACED,
SUM(AFACINIAMT) AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR'
RETURNS:
YEAR MONTH TOTAL_PLACED TOTAL_LOCATED
2012 8 5255102.71
2012 9 5255102.71
2012 10 5255102.71
2012 11 5255102.71
2012 12 5255102.71
2012 8 0 3669947.58
2012 9 0 3669947.58
2012 10 0 3669947.58
2012 11 0 3669947.58
2012 12 0 3669947.58
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_PLACED,'' as TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
UNION
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,'' as TOTAL_PLACED,
SUM(AFACINIAMT) AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR'
RETURNS:
YEAR MONTH TOTAL_PLACED TOTAL_LOCATED
2012 8 5255102.71
2012 9 5255102.71
2012 10 5255102.71
2012 11 5255102.71
2012 12 5255102.71
2012 8 0 3669947.58
2012 9 0 3669947.58
2012 10 0 3669947.58
2012 11 0 3669947.58
2012 12 0 3669947.58
I think SQL join should definatly work for you....
please refer below link might helps you....
http://www.sqlnewsgroups.net/sqlserver/t11142-urgent-slow-join-across-sql-linked-server-intersystems-cache.aspx
if you still wants to follow unian all option i think you should try pivot table option....
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
please refer below link might helps you....
http://www.sqlnewsgroups.net/sqlserver/t11142-urgent-slow-join-across-sql-linked-server-intersystems-cache.aspx
if you still wants to follow unian all option i think you should try pivot table option....
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
@: keyu
The only issue with join like I was saying above is this is not a SQL database, I am using ODBC to query an Intersystem's Cache DB. That said I can't join queries as tables like you can with SQL server or Microsoft Access. If I could use Access I would but one of the tables I am using in this query has 40 keys (That's the whole purpose of that table to be the center relationship for this DB) and that is beyond the maximum keys Acess allows per table.
The only issue with join like I was saying above is this is not a SQL database, I am using ODBC to query an Intersystem's Cache DB. That said I can't join queries as tables like you can with SQL server or Microsoft Access. If I could use Access I would but one of the tables I am using in this query has 40 keys (That's the whole purpose of that table to be the center relationship for this DB) and that is beyond the maximum keys Acess allows per table.
Active 1 day ago
I think a standard CASE statement can make this much easier (and possibly more efficient as well):
SELECT
YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(CASE WHEN STF.STAUDFLDEXTNEW like 'BAD%' THEN AFACINIAMT ELSE 0 END) AS TOTAL_PLACED,
SUM(CASE WHEN STF.STAUDFLDEXTNEW like 'BAD%' THEN 0 ELSE AFACINIAMT END) AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND ((
STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
)
OR
(
STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR'
))
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
SELECT
YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(CASE WHEN STF.STAUDFLDEXTNEW like 'BAD%' THEN AFACINIAMT ELSE 0 END) AS TOTAL_PLACED,
SUM(CASE WHEN STF.STAUDFLDEXTNEW like 'BAD%' THEN 0 ELSE AFACINIAMT END) AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND ((
STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
)
OR
(
STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR'
))
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
@ ScottPletcher:
Aren't you just the superstar! Great job! Thank you for the Xmas present and have a happy new year.
T
Aren't you just the superstar! Great job! Thank you for the Xmas present and have a happy new year.
T
Active 2 days ago
I think the case statement should work with a little tweaking -
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(CASE WHEN STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD = 'NEWACCT'
THEN AFACINIAMT ELSE 0
END) AS TOTAL_PLACED,
SUM(CASE WHEN STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR'
THEN AFACINIAMT ELSE 0
END) AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte);
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(CASE WHEN STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD = 'NEWACCT'
THEN AFACINIAMT ELSE 0
END) AS TOTAL_PLACED,
SUM(CASE WHEN STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR'
THEN AFACINIAMT ELSE 0
END) AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte);
Active 2 days ago
Took too long testing and typing :-(
Out of curiosity, do the two queries ScottPletcher's and mine return the same results?
Out of curiosity, do the two queries ScottPletcher's and mine return the same results?
Active 1 day ago
I can't test it either, since I don't have data, but I can tell you: yes, they do produce the same results, albeit that yours could execute more slowly, depending on table indexes and the sophistication of the SQL optimizer.
Look closely at the first condition in each set of tests: they are mutually exclusive:
STF.STAUDFLDEXTNEW like 'BAD%'
--vs.--
STF.STAUDFLDEXTNEW NOT like 'BAD%'
Thus, you don't need to do additional checks to distinguish between them when adding the amounts.
Look closely at the first condition in each set of tests: they are mutually exclusive:
STF.STAUDFLDEXTNEW like 'BAD%'
--vs.--
STF.STAUDFLDEXTNEW NOT like 'BAD%'
Thus, you don't need to do additional checks to distinguish between them when adding the amounts.
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud.
This article shows how to create one of these functions to write directly to Azure Table Storage.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
- Superb Internet
- IT Administration
- Ransomware
- Storage Software
- Databases
- Cloud Computing, Cloud Services, *Backups, *Online Backup Services, *Backup Software
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 5 hours left to enroll
615 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.