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.
| Author |
Topic |
|
Pete_N
Posting Yak Master
181 Posts |
 Posted - 2013-09-09 : 16:24:17
|
| 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 #ProcessingListSELECT [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/balieWould be includedLicence Description Val999999 OPEN/BAL 10.00999999 CREDIT 9.00999999 DEBIT -1.00 Would NOT be includedLicence Description Val999991 OPEN/BAL 10.00999991 CREDIT 9.00999991 DEBIT -9.00 Would NOT be includedLicence Description Val999991 OPEN/BAL 10.00999991 DEBIT -9.00 |
|
|
|
|
|
|
|
Help Needed on query