query 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

Transact-SQL (2005)

query help

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2008-07-05 : 21:08:50

Hi everyone, please help me with below query. I was not sure what I did wrong..but I got syntax error.

SELECT A.ID_CASE, A.ID_ORG AS AGENCY,
C.NM_ORG AS AGENCYNAME,
A.MIN(DT_ASSGNMENT) AS FIRST_ASSIGNMENT,
SUM(B.AM_TOT_COLL_EFFECT) AS PAYMENT,
B.ID_FIN_DETAIL AS PAYMENTDATE,
SUM(A.AM_CASE_TOTAL) AS NETBALANCE,

FROM DBA.TF1CASDEH A

LEFT JOIN DBA.TF6CPAYME B ON B.ID_CASE=A.ID_CASE
LEFT JOIN DBA.TF1ORG C ON C.ID_ORG=A.ID_ORG
WHERE A.ID_ORG='1393'
AND A.ID_CASE IN (

SELECT ID_CASE,
MAX(DT_ASSIGNMENT) AS CLOSED_ASSIGNMENT,
ID_ORG
FROM DBA.TF1CASDET
WHERE ID_ORG <> '1393'
AND DT_ASSIGNMENT > '2008-06-30'
GROUP BY ID_CASE)

B.DT_PROCESS>A.DT_ASSIGNMENT

group by A.ID_CASE, AGENCY, AGENCYNAME, ASSIGNMENTDATE, PAYMENTDATE

order by 2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-06 : 04:00:45

It's your IN clause. An IN list of values should be treated as a resultset of one column.
You have two columns.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-06 : 04:03:46

[code]SELECT A.ID_CASE,
A.ID_ORG AS AGENCY,
C.NM_ORG AS AGENCYNAME,
MIN(A.DT_ASSGNMENT) AS FIRST_ASSIGNMENT,
SUM(B.AM_TOT_COLL_EFFECT) AS PAYMENT,
B.ID_FIN_DETAIL AS PAYMENTDATE,
SUM(A.AM_CASE_TOTAL) AS NETBALANCE,
FROM DBA.TF1CASDEH AS A
LEFT JOIN DBA.TF6CPAYME AS B ON B.ID_CASE = A.ID_CASE
LEFT JOIN DBA.TF1ORG AS C ON C.ID_ORG = A.ID_ORG
WHERE A.ID_ORG = '1393'
AND A.ID_CASE IN (
SELECT ID_CASE
FROM DBA.TF1CASDET
WHERE ID_ORG <> '1393'
AND DT_ASSIGNMENT > '2008-06-30'
)

WHERE B.DT_PROCESS > A.DT_ASSIGNMENT
group by A.ID_CASE,
A.ID_ORG,
C.NM_ORG,
ASSIGNMENTDATE,
B.ID_FIN_DETAIL
order by 2[/code]

E 12°55'05.25"
N 56°04'39.16"

ntn104
Posting Yak Master

175 Posts

Posted - 2008-07-06 : 16:29:25

quote:

Originally posted by Peso

SELECT		A.ID_CASE,
		A.ID_ORG AS AGENCY,
		C.NM_ORG AS AGENCYNAME,
		MIN(A.DT_ASSGNMENT) AS FIRST_ASSIGNMENT,
		SUM(B.AM_TOT_COLL_EFFECT) AS PAYMENT,
		B.ID_FIN_DETAIL AS PAYMENTDATE,
		SUM(A.AM_CASE_TOTAL) AS NETBALANCE,
FROM		DBA.TF1CASDEH AS A
LEFT JOIN	DBA.TF6CPAYME AS B ON B.ID_CASE = A.ID_CASE
LEFT JOIN	DBA.TF1ORG AS C ON C.ID_ORG = A.ID_ORG
WHERE		A.ID_ORG = '1393'
		AND A.ID_CASE IN (
					SELECT		ID_CASE
					FROM		DBA.TF1CASDET
					WHERE		ID_ORG <> '1393'
							AND DT_ASSIGNMENT > '2008-06-30'
				) 

WHERE		B.DT_PROCESS > A.DT_ASSIGNMENT
group by	A.ID_CASE,
		A.ID_ORG,
		C.NM_ORG,
		ASSIGNMENTDATE,
		B.ID_FIN_DETAIL
order by	2

E 12°55'05.25"
N 56°04'39.16"

Thank you Peso. I will try this and inform you the result....

ntn104
Posting Yak Master

175 Posts

Posted - 2008-07-07 : 06:57:51

My query still does not work right. I declared wrong table for the netbalance column, so I made a correction for that (see red highlight. Then I have to declare the netbalance column in the sub-query, but I got errror message saying that sub-query allowed only one select item. Please help...thanks,

My requirement is to get all cases that have been closed with ID_ORG=1393 as of 6/30/08.

SELECT
A.ID_CASE,
A.ID_ORG AS AGENCY,
C.NM_ORG AS AGENCYNAME,
MIN(A.DT_ASSIGNMENT) AS FIRST_ASSIGNMENT,
SUM(B.AM_TOT_COLL_EFFECT) AS PAYMENT,
B.ID_FIN_DETAIL AS PAYMENTDATE,
SUM(D.AM_CASE_TOTAL) AS NETBALANCE

FROM DBA.TF1CASDEH AS A

LEFT JOIN DBA.TF6CPAYME AS B ON B.ID_CASE=A.ID_CASE
LEFT JOIN DBA.TF1ORG AS C ON C.ID_ORG=A.ID_ORG
WHERE A.ID_ORG= '1393'
AND A.ID_CASE IN (
SELECT ID_CASE, SUM(AM_CASE_TOTAL) AS NETBALANCE
FROM DBA.TF1CASDET
WHERE ID_ORG <>'1393'
AND DT_ASSIGNMENT > '2008-06-30'
GROUP BY ID_CASE
) D ON D.ID_CASE=A.ID_CASE

AND B.DT_PROCESS > A.DT_ASSIGNMENT
GROUP BY A.ID_CASE,
A.ID_ORG,
C.NM_ORG,
FIRST_ASSIGNMENT,
PAYMENTDATE
ORDER BY 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 13:26:55

[code]SELECT
A.ID_CASE,
A.ID_ORG AS AGENCY,
C.NM_ORG AS AGENCYNAME,
MIN(A.DT_ASSIGNMENT) AS FIRST_ASSIGNMENT,
SUM(B.AM_TOT_COLL_EFFECT) AS PAYMENT,
B.ID_FIN_DETAIL AS PAYMENTDATE,
SUM(D.AM_CASE_TOTAL) AS NETBALANCE

FROM DBA.TF1CASDEH AS A
INNER JOIN (
SELECT ID_CASE, SUM(AM_CASE_TOTAL) AS NETBALANCE
FROM DBA.TF1CASDET
WHERE ID_ORG <>'1393'
AND DT_ASSIGNMENT > '2008-06-30'
GROUP BY ID_CASE
) D
ON D.ID_CASE=A.ID_CASE
LEFT JOIN DBA.TF6CPAYME AS B ON B.ID_CASE=A.ID_CASE
LEFT JOIN DBA.TF1ORG AS C ON C.ID_ORG=A.ID_ORG
WHERE A.ID_ORG= '1393'
AND B.DT_PROCESS > A.DT_ASSIGNMENT
GROUP BY A.ID_CASE,
A.ID_ORG,
C.NM_ORG,
FIRST_ASSIGNMENT,
PAYMENTDATE
ORDER BY 2[/code]

ntn104
Posting Yak Master

175 Posts

Posted - 2008-07-07 : 14:06:45

quote:

Originally posted by visakh16

SELECT
A.ID_CASE,
A.ID_ORG AS AGENCY,
C.NM_ORG AS AGENCYNAME,
MIN(A.DT_ASSIGNMENT) AS FIRST_ASSIGNMENT,
SUM(B.AM_TOT_COLL_EFFECT) AS PAYMENT,
B.ID_FIN_DETAIL AS PAYMENTDATE,
SUM(D.AM_CASE_TOTAL) AS NETBALANCE
D.NETBALANCE

FROM DBA.TF1CASDEH AS A
INNER JOIN (
SELECT ID_CASE, SUM(AM_CASE_TOTAL) AS NETBALANCE
FROM DBA.TF1CASDET
WHERE ID_ORG <>'1393'
AND DT_ASSIGNMENT > '2008-06-30'
GROUP BY ID_CASE
) D
ON D.ID_CASE=A.ID_CASE
LEFT JOIN DBA.TF6CPAYME AS B ON B.ID_CASE=A.ID_CASE
LEFT JOIN DBA.TF1ORG AS C ON C.ID_ORG=A.ID_ORG
WHERE A.ID_ORG= '1393'
AND B.DT_PROCESS > A.DT_ASSIGNMENT
GROUP BY A.ID_CASE,
A.ID_ORG,
C.NM_ORG,
FIRST_ASSIGNMENT,
PAYMENTDATE
ORDER BY 2

I ran this query, then it said that the column "AM_CASE_TOTAL" not found since I declared in sub-query. Then I replace as "D.NETBALANCE" as I called in subquery. However, the query does not work. I still got error message saying that: invalid use of aggregate function.. Please show me why that happened.

ntn104
Posting Yak Master

175 Posts

Posted - 2008-07-08 : 09:33:54

If I organize the query like below it work...butnot sure I am right with the requirement. Please check and give me your input..thanks,

My requirement is to get all cases that have been closed with ID_ORG=1393 as of 6/30/08

SELECT DISTINCT A.ID_CASE, MIN(A.DT_ASSIGNMENT) AS FIRST_ASSIGNMENT,
D.DT_ASSIGNMENT AS CLOSEDDATE,
A.ID_ORG AS AGENCY, B.NM_ORG AS AGENCYNAME,
SUM(C.AM_TOT_COLL_EFFECT) AS PAYMENT,
C.ID_FIN_DETAIL AS PAYMENTDATE,
SUM(D.AM_CASE_TOTAL ) AS NETBALANCE

FROM DBA.TF1CASDEH A
LEFT JOIN DBA.TF1ORG AS B ON B.ID_ORG = A.ID_ORG
LEFT JOIN DBA.TF6CPAYME AS C ON C.ID_CASE = A.ID_CASE
LEFT JOIN DBA.TF1CASDET AS D ON D.ID_CASE = A.ID_CASE

WHERE A.ID_ORG = '1393'
AND A.ID_CASE IN(SELECT ID_CASE
FROM DBA.TF1CASDET
WHERE DT_ASSIGNMENT > '2008-06-30' AND
ID_ORG <> '1393')

GROUP BY A.ID_CASE, AGENCY, AGENCYNAME, PAYMENTDATE, CLOSEDDATE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 13:15:03

quote:
Originally posted by ntn104

If I organize the query like below it work...butnot sure I am right with the requirement. Please check and give me your input..thanks,

My requirement is to get all cases that have been closed with ID_ORG=1393 as of 6/30/08

SELECT DISTINCT A.ID_CASE, MIN(A.DT_ASSIGNMENT) AS FIRST_ASSIGNMENT,
D.DT_ASSIGNMENT AS CLOSEDDATE,
A.ID_ORG AS AGENCY, B.NM_ORG AS AGENCYNAME,
SUM(C.AM_TOT_COLL_EFFECT) AS PAYMENT,
C.ID_FIN_DETAIL AS PAYMENTDATE,
SUM(D.AM_CASE_TOTAL ) AS NETBALANCE

FROM DBA.TF1CASDEH A
LEFT JOIN DBA.TF1ORG AS B ON B.ID_ORG = A.ID_ORG
LEFT JOIN DBA.TF6CPAYME AS C ON C.ID_CASE = A.ID_CASE
LEFT JOIN DBA.TF1CASDET AS D ON D.ID_CASE = A.ID_CASE

WHERE A.ID_ORG = '1393'
AND A.ID_CASE IN(SELECT ID_CASE
FROM DBA.TF1CASDET
WHERE DT_ASSIGNMENT > '2008-06-30' AND
ID_ORG <> '1393')

GROUP BY A.ID_CASE, AGENCY, AGENCYNAME, PAYMENTDATE, CLOSEDDATE

if you want all cases details,you dont need group by. can you elaborate on what you're looking at with some sample data?

ntn104
Posting Yak Master

175 Posts

Posted - 2008-07-08 : 20:34:50

quote:
Originally posted by visakh16

quote:
Originally posted by ntn104

If I organize the query like below it work...butnot sure I am right with the requirement. Please check and give me your input..thanks,

My requirement is to get all cases that have been closed with ID_ORG=1393 as of 6/30/08

SELECT DISTINCT A.ID_CASE, MIN(A.DT_ASSIGNMENT) AS FIRST_ASSIGNMENT,
D.DT_ASSIGNMENT AS CLOSEDDATE,
A.ID_ORG AS AGENCY, B.NM_ORG AS AGENCYNAME,
SUM(C.AM_TOT_COLL_EFFECT) AS PAYMENT,
C.ID_FIN_DETAIL AS PAYMENTDATE,
SUM(D.AM_CASE_TOTAL ) AS NETBALANCE

FROM DBA.TF1CASDEH A
LEFT JOIN DBA.TF1ORG AS B ON B.ID_ORG = A.ID_ORG
LEFT JOIN DBA.TF6CPAYME AS C ON C.ID_CASE = A.ID_CASE
LEFT JOIN DBA.TF1CASDET AS D ON D.ID_CASE = A.ID_CASE

WHERE A.ID_ORG = '1393'
AND A.ID_CASE IN(SELECT ID_CASE
FROM DBA.TF1CASDET
WHERE DT_ASSIGNMENT > '2008-06-30' AND
ID_ORG <> '1393')

GROUP BY A.ID_CASE, AGENCY, AGENCYNAME, PAYMENTDATE, CLOSEDDATE

if you want all cases details,you dont need group by. can you elaborate on what you're looking at with some sample data?

I want to display all closed case from an agency at the end of the month. For example, Agency1 has 2 closed cases as of July 2, see sample one as the result:

IDCase=1234
First_Assignment=6/12/07
ClosedDate= 7/2/08
AgencyName=FirstCredit
OriginalBalance=$10000
Payment=$4000
Netbalance=$6000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 23:58:32

quote:
Originally posted by ntn104

quote:
Originally posted by visakh16

quote:
Originally posted by ntn104

If I organize the query like below it work...butnot sure I am right with the requirement. Please check and give me your input..thanks,

My requirement is to get all cases that have been closed with ID_ORG=1393 as of 6/30/08

SELECT DISTINCT A.ID_CASE, MIN(A.DT_ASSIGNMENT) AS FIRST_ASSIGNMENT,
D.DT_ASSIGNMENT AS CLOSEDDATE,
A.ID_ORG AS AGENCY, B.NM_ORG AS AGENCYNAME,
SUM(C.AM_TOT_COLL_EFFECT) AS PAYMENT,
C.ID_FIN_DETAIL AS PAYMENTDATE,
SUM(D.AM_CASE_TOTAL ) AS NETBALANCE

FROM DBA.TF1CASDEH A
LEFT JOIN DBA.TF1ORG AS B ON B.ID_ORG = A.ID_ORG
LEFT JOIN DBA.TF6CPAYME AS C ON C.ID_CASE = A.ID_CASE
LEFT JOIN DBA.TF1CASDET AS D ON D.ID_CASE = A.ID_CASE

WHERE A.ID_ORG = '1393'
AND A.ID_CASE IN(SELECT ID_CASE
FROM DBA.TF1CASDET
WHERE DT_ASSIGNMENT > '2008-06-30' AND
ID_ORG <> '1393')

GROUP BY A.ID_CASE, AGENCY, AGENCYNAME, PAYMENTDATE, CLOSEDDATE

if you want all cases details,you dont need group by. can you elaborate on what you're looking at with some sample data?

I want to display all closed case from an agency at the end of the month. For example, Agency1 has 2 closed cases as of July 2, see sample one as the result:

IDCase=1234
First_Assignment=6/12/07
ClosedDate= 7/2/08
AgencyName=FirstCredit
OriginalBalance=$10000
Payment=$4000
Netbalance=$6000

couple of questions:-

1. what is the last subquery for?
2. You've used aliases directly in GROUP BY. cant understand how query is working?You cant use aliases directly in GROUP BY

ntn104
Posting Yak Master

175 Posts

Posted - 2008-07-09 : 07:28:05

quote:
Originally posted by visakh16

quote:
Originally posted by ntn104

quote:
Originally posted by visakh16

quote:
Originally posted by ntn104

If I organize the query like below it work...butnot sure I am right with the requirement. Please check and give me your input..thanks,

My requirement is to get all cases that have been closed with ID_ORG=1393 as of 6/30/08

SELECT DISTINCT A.ID_CASE, MIN(A.DT_ASSIGNMENT) AS FIRST_ASSIGNMENT,
D.DT_ASSIGNMENT AS CLOSEDDATE,
A.ID_ORG AS AGENCY, B.NM_ORG AS AGENCYNAME,
SUM(C.AM_TOT_COLL_EFFECT) AS PAYMENT,
C.ID_FIN_DETAIL AS PAYMENTDATE,
SUM(D.AM_CASE_TOTAL ) AS NETBALANCE

FROM DBA.TF1CASDEH A
LEFT JOIN DBA.TF1ORG AS B ON B.ID_ORG = A.ID_ORG
LEFT JOIN DBA.TF6CPAYME AS C ON C.ID_CASE = A.ID_CASE
LEFT JOIN DBA.TF1CASDET AS D ON D.ID_CASE = A.ID_CASE

WHERE A.ID_ORG = '1393'
AND A.ID_CASE IN(SELECT ID_CASE
FROM DBA.TF1CASDET
WHERE DT_ASSIGNMENT > '2008-06-30' AND
ID_ORG <> '1393')

GROUP BY A.ID_CASE, AGENCY, AGENCYNAME, PAYMENTDATE, CLOSEDDATE

if you want all cases details,you dont need group by. can you elaborate on what you're looking at with some sample data?

I want to display all closed case from an agency at the end of the month. For example, Agency1 has 2 closed cases as of July 2, see sample one as the result:

IDCase=1234
First_Assignment=6/12/07
ClosedDate= 7/2/08
AgencyName=FirstCredit
OriginalBalance=$10000
Payment=$4000
Netbalance=$6000

couple of questions:-

1. what is the last subquery for?
2. You've used aliases directly in GROUP BY. cant understand how query is working?You cant use aliases directly in GROUP BY

The last sub-query will get prove that those case was assigned to agency:1393 previous, has stop with this agency after 6/30/08. As above example: ID_Case:1234 assigned to Agency:FirstCredit on 6/12/07 and as of 07/02/08 this case was no longer at this agency: FirstCredit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources