Help with this query please - 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

Transact-SQL (2000)

Help with this query please

Author

Topic

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2005-05-31 : 09:42:54

I've looked in the online books and cannot find the correct syntax for combining the results of two queries. Basically, what I want to do is to take the total from query no. 1 and use it in a calculated column in query no. 2 Can someone please tell me how to accomplish this?

Any help is appreciated.

[Code Start]
Select tbl_Marks.chrDBLHandle, count(chrappno) as DBLHandletotal

from tbl_Handle
inner join tbl_Marks
on pkMarks = fkMarks
where fkMarksNo = 1 and markscount = 0 and
fkActionType <> '6'

group by chrDBLHandle
Order by chrDBLHandle

--LOAN TYPE--
Select 1 as Linking,
'Loan Type' as Report_Type,
chrDBLHandle as DBLHandle,
ChrDBLHandleName as DBLHandle_Name,
'Loan Type' as Category,
tbl_Handle_Type.chrName as Item,
Count(tbl_Handle_Type.chrName) as Application_Count,
Case when fkactiontype = '1' then count(tbl_Handle_Type.chrName) end as
Application_Count_Originated,

-- Need to insert percentage of application count from query #1

Case when fkactiontype = '2' then count(tbl_Handle_Type.chrName) end as
Application_Count_ANAccepted,
Case when fkactiontype = '3' then count(tbl_Handle_Type.chrName) end as
Application_Count_Denied,
Case when fkactiontype = '4' then count(tbl_Handle_Type.chrName) end as
Application_Count_Withdrawn,
Case when fkactiontype = '5' then count(tbl_Handle_Type.chrName) end as
Application_Count_ClosedFI,
Case when fkactiontype = '7' then count(tbl_Handle_Type.chrName) end as
Application_Count_PDenied,
Case when fkactiontype = '8' then count(tbl_Handle_Type.chrName) end as
Application_Count_PA_Not_Accepted

From tbl_Handle inner join tbl_Marks
on pkMarks = fkMarks
Inner join tbl_DBLHandle
on Tbl_DBLHandle.ChrDBLHandleCode = tbl_Marks.chrDBLHandle and
TBL_DBLHandle.fkreportyear = tbl_Handle.fkreportyear
left join tbl_Handle_Type
on tbl_Handle_Type.chrLoanType = tbl_Handle.chrloantype

where fkMarksNo = 1 and markscount = 0
and fkactiontype <> '6'

Group by tbl_Marks.ChrDBLHandle, tbl_DBLHandle.ChrDBLHandleName,tbl_Handle_Type.chrName,
tbl_Handle.fkActionType

[Code End]

mr_mist
Grunnio

1870 Posts

Posted - 2005-05-31 : 09:47:56

In your second query, join to the first as a derived query.

-------
Moo. :)

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2005-05-31 : 10:40:04

Thank you for your response, but I am having trouble joining the two queries. I don't find anything in the online books about this. Can you please help?

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-05-31 : 10:40:26

Just use a batch of SQL queries .Declare a variable and set this variable to the value
calculated in query no. 1.Then use the value of this variable in the calculated column of the second query.

Thanks,
Vivek

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2005-05-31 : 11:37:08

Thanks for your sugggestion. I tried, but apparently I cannot assign two values to a variable. The message I recieve is:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I have been trying unsuccessfully to join the two queries. Any help is appreciated.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-31 : 13:14:23

[code]
SELECT 1 as Linking,
, 'Loan Type' AS Report_Type
, chrDBLHandle AS DBLHandle
, ChrDBLHandleName AS DBLHandle_Name
, 'Loan Type' AS Category
, tbl_Handle_Type.chrName AS Item
, COUNT(tbl_Handle_Type.chrName) AS Application_Count
, CASE WHEN fkactiontype = '1'
THEN COUNT(tbl_Handle_Type.chrName)
END AS Application_Count_Originated
, xxx.DBLHandletotal
, CASE WHEN fkactiontype = '2'
THEN COUNT(tbl_Handle_Type.chrName)
END AS Application_Count_ANAccepted
, CASE WHEN fkactiontype = '3'
THEN COUNT(tbl_Handle_Type.chrName)
END AS Application_Count_Denied
, CASE WHEN fkactiontype = '4'
THEN COUNT(tbl_Handle_Type.chrName)
END AS Application_Count_Withdrawn
, CASE WHEN fkactiontype = '5'
THEN COUNT(tbl_Handle_Type.chrName)
END AS Application_Count_ClosedFI
, CASE WHEN fkactiontype = '7'
THEN COUNT(tbl_Handle_Type.chrName)
END AS Application_Count_PDenied
, CASE WHEN fkactiontype = '8'
THEN COUNT(tbl_Handle_Type.chrName)
END AS Application_Count_PA_Not_Accepted
FROM tbl_Handle
INNER JOIN tbl_Marks
ON pkMarks = fkMarks
INNER JOIN tbl_DBLHandle
ON Tbl_DBLHandle.ChrDBLHandleCode = tbl_Marks.chrDBLHandle
AND TBL_DBLHandle.fkreportyear = tbl_Handle.fkreportyear
LEFT JOIN tbl_Handle_Type
ON tbl_Handle_Type.chrLoanType = tbl_Handle.chrloantype
INNER JOIN ( SELECT tbl_Marks.chrDBLHandle, COUNT(chrappno) AS DBLHandletotal
FROM tbl_Handle
INNER JOIN tbl_Marks
ON pkMarks = fkMarks
WHERE fkMarksNo = 1
AND markscount = 0
AND fkActionType <> '6'
GROUP BY chrDBLHandle
ORDER BY chrDBLHandle) AS xxx
ON -- you'll need a Join predicate here, and if it's not in the derived tables SELECT clause you'll need to add it. WHERE fkMarksNo = 1
AND markscount = 0
AND fkactiontype <> '6'
GROUP BY tbl_Marks.ChrDBLHandle
, tbl_DBLHandle.ChrDBLHandleName
, tbl_Handle_Type.chrName
, tbl_Handle.fkActionType
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2005-05-31 : 16:04:41

Brett:

Many thanks. I was able to get this to work using your syntax and adding a case statement to conditionally sum the totals.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources