Need query help asap. Please Help.

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)

Need query help asap. Please Help.

Author

Topic

MGrassman
Starting Member

4 Posts

Posted - 2006-07-05 : 17:00:12

I have the following tables and need help creating a query. Please let me know if you need any other information.

Modules
M_RecID int
M_Name varchar
M_Desc varchar
M_Monthly decimal (this is what I need summed)
M_Annually decimal (this is what I need summed)
M_Deleted date/time (needs to be null)
M_Updated date/time
M_OrderBy int

Org_Modules
OM_M_RecID int (refers to the M_RecID)
OM_O_RecID int (refers to the OrgID)
OM_M_Annually 1 or 0 (if 1 add to the sum of the M_Annually field)
OM_M_Monthly 1 or 0 (if 1 add to the sum of the M_Monthly field)

Organizations
OrgID int (This is what I group by)
deleted date/time (needs to be null)

The ending recordset I need is as follows:
OrgID, Monthly_Total, Annual_Total
1 113.95 245.00
2 0.00 500.00

MGrassman
Starting Member

4 Posts

Posted - 2006-07-05 : 17:09:01

Please Disregard I have figured it out. If any one knows of a better way I would love to hear about it.

Thanks,

Select
OM_C_RecID,
isNull((Select sum(M_Monthly) from Modules where M_RecID in (Select OM_M_RecID from charity_modules where OM_M_Monthly = 1 and OM_C_RecID = OM.CM_C_REcID)), 0) as Monthly_Fee,
isNull((Select sum(M_Annually) from Modules where M_RecID in (Select OM_M_RecID from charity_modules where OM_M_Annually = 1 and OM_C_RecID = OM.CM_C_RecID)), 0) as Annual_Fee
from Org_Modules OM
where OM_C_RecID in (Select OrgID from Organizations where deleted is null)
Group By OM_C_RecID
order by OM_C_RecID

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-06 : 02:27:17

[code]SELECT O.OrgID
,SUM(CASE OM.OM_M_Monthly WHEN 1 THEN M.M_Monthly ELSE 0 END)
,SUM(CASE OM.OM_M_Annually WHEN 1 THEN M_Annually ELSE 0 END)
FROM Organizations O
LEFT OUTER JOIN Org_Modules OM
ON OM.OM_O_RecID = O.OrgID
LEFT OUTER JOIN Modules M
ON M.M_RecID = OM_M_RecID AND M.M_Deleted IS NULL
WHERE O.Deleted IS NULL
GROUP BY O.OrgID[/code]

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources