SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help Needed on query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

176 Posts

Posted - 09/09/2013 :  16:24:17  Show Profile  Reply with Quote
CREATE TABLE #ProcessingList
(
Licence varchar(6),
[Description] varchar(50),
new_facilitiesmanagement_processingId varchar(50),
StatusCode INT,
ClearedFunds smalldatetime,
ItemValue Decimal(11,2),
ProcessedDate smalldatetime,
new_StatementID varchar(50)
)

INSERT INTO #ProcessingList
SELECT
[new_LicenceName]
,(SELECT Label FROM #DISP WHERE #DISP.Id = [new_description]) AS description
,[new_facilitiesmanagement_processingId]
,[statecode]
,[new_ClearedFunds]
,[new_ItemValue]
,[new_ProcessedDate]
,[new_StatementID]
FROM [MSCRM].[dbo].[new_facilitiesmanagement_processing]
WHERE new_ClearedFunds <= Convert(datetime, @ProcessDate, 103 )

This fills the # table with all records. Each record has a new_licenceName number which is unique to each customer. The new_ItemValue is a mixture of credit and debits. each customer will have an entry with a description of 'OPEN/BAL' What I want to do is add a where clause so that the # table is only populated with records for each customer where the sum of debits and credits are greater then the OPEN/BAL and each licence must have a credit other than open/bal

ie

Would be included
Licence Description Val
999999 OPEN/BAL 10.00
999999 CREDIT 9.00
999999 DEBIT -1.00

Would NOT be included
Licence Description Val
999991 OPEN/BAL 10.00
999991 CREDIT 9.00
999991 DEBIT -9.00

Would NOT be included
Licence Description Val
999991 OPEN/BAL 10.00
999991 DEBIT -9.00
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000