Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Complex Query Expand / Collapse
Author
Message
Posted Friday, June 14, 2013 7:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 139, Visits: 369
I have a table with millions of records and I want to extract the data into 2 different tables.

Criteria1:
When Sum of InvAmt by VndNbr + InvNbr Is Greater or Less than Zero. There will be 1 record that will equal the sum of InvAmt by VndNbr + Invnbr. That record will go into Table1(Sum). The rest of the records will go to Table2(Sum_Removed).
e.i.
VndNbr InvNbr InvAmt BatNbr
3746 1-127 -5034.96 9855950
3746 1-127 -5034.96 8957210
3746 1-127 -5034.96 9258910
3746 1-127 5034.96 9259236
3746 1-127 5034.96 9259236

Table1 :
3746 1-127 -5034.96 9855950

Table2:
3746 1-127 -5034.96 8957210
3746 1-127 -5034.96 9258910
3746 1-127 5034.96 9259236
3746 1-127 5034.96 9259236

Criteria2:
When Sum of InvAmt by VndNbr + InvNbr Does NOT Equal Zero and The sum of InvAmt does not equal 1 of those recrods. All those records will go into Table1 (Sum)
i.e.

VndNbr InvNbr InvAmt BatNbr
4255 1524 0.77 4595988
4255 1524 59.05 4595988
4255 1524 715.45 4595988
4255 1524 15034.84 4595988

Table1:
4255 1524 0.77 4595988
4255 1524 59.05 4595988
4255 1524 715.45 4595988
4255 1524 15034.84 4595988

Criteria3:
When Sum of InvAmt by VndNbr + InvNbr Equal Zero, then all the records will go into Table2

In the end all the records in Table2 will have the sum of Invamt by vndnbr + invnbr equal to zero.
----------------------------------------------------------------------
SAMPLE Data:
VndNbr InvNbr InvAmt BatNbr
3746 1-127 -5034.96 9855950
3746 1-127 -5034.96 8957210
3746 1-127 -5034.96 9258910
3746 1-127 5034.96 9259236
3746 1-127 5034.96 9259236

4255 1524 0.77 4595988
4255 1524 59.05 4595988
4255 1524 715.45 4595988
4255 1524 15034.84 4595988

603031 106541 -781.41 6879249
603031 106541 -781.41 6879249
603031 106541 781.41 6873385

603031 108887 -421.92 8236774
603031 108887 421.92 8236737

603031 BS 041207 1000000 3731291
603031 BS 041207 9000000 3724588

685998 47730 -45022.05 8466639
685998 47730 492.06 7741083
685998 47730 4686.54 7741083
685998 47730 39843.45 7741083
685998 47730 39843.63 8466640

687840 423099 -23 10116179
687840 423099 -23 10200163
687840 423099 23 10200177
------------------------------------------------------------------------------------
Total Desired Results:
Table1 Name: StdPmt_InvAmtSum
Records:
3746 1-127 -5034.96 9855950
4255 1524 0.77 4595988
4255 1524 59.05 4595988
4255 1524 715.45 4595988
4255 1524 15034.84 4595988
603031 106541 -781.41 6879249
603031 BS 041207 1000000 3731291
603031 BS 041207 9000000 3724588
685998 47730 39843.63 8466640
687840 423099 -23 10116179


Table2 Name: StdPmt_InvAmtSum_Removed
3746 1-127 -5034.96 8957210
3746 1-127 -5034.96 9258910
3746 1-127 5034.96 9259236
3746 1-127 5034.96 9259236
603031 106541 -781.41 6879249
603031 106541 781.41 6873385
603031 108887 -421.92 8236774
603031 108887 421.92 8236737
685998 47730 -45022.05 8466639
685998 47730 492.06 7741083
685998 47730 4686.54 7741083
685998 47730 39843.45 7741083
687840 423099 -23 10200163
687840 423099 23 10200177


This is the code I have been using. I am getting most of my desired results. There is 1 example which is not working for me

666555 107775 -300000 7996100
666555 107775 300000 7996100
666555 107775 300000 7904930

The above is not going into the right tables.

This is the result I am getting:
Table1:
666555 107775 300000 7996100
666555 107775 300000 7904930


Table2:
666555 107775 -300000 7996100

Should be:
Table1:
666555 107775 300000 7996100

Table2:
666555 107775 -300000 7996100
666555 107775 300000 7904930


Code:

-- Get records by Invoices with only 2 records that don't equal 0.
Select S1.*, S2.Cnt, Cast(Null as Integer) as rn
Into AS_Workspace.dbo.StdPmt_InvAmt_SUM
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt <> S2.InvAmt_Sum
AND S2.InvAmt_Sum > 0

Where S2.Cnt = 2

UNION

Select S1.*, S2.Cnt, Cast(Null as Integer) as rn
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt <> S2.InvAmt_Sum

WHERE S2.InvAmt_Sum > 0
AND S2.Cnt > 2
--AND S2.VndNbr = '666555' AND S2.InvNbr = '107775'

AND NOT EXISTS(Select InvAmt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S3
Where S2.InvNbr = S3.InvNbr
AND S2.VndNbr = S3.VndNbr
AND S2.InvAmt_Sum = S3.InvAmt )


UNION

Select S1.*, S2.Cnt, Cast(Null as Integer) as rn
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
AND S2.InvAmt_Sum > 0
Where S2.Cnt > 0
AND S2.VndNbr = '666555' AND S2.InvNbr = '107775'

UNION

-- Insert records to get Invoices with more than 2 records that don't equal 0 and have a negative amount of the sum of invamt.
--Pull in 1 of the 2 Negative records.
--Insert Into AS_Workspace.dbo.StdPmt_InvAmt_SUM2(VndNbr, VndName, InvNbr, InvDt, InvAmt, NetAmt, FInvAmt, DiscAmt, FDiscAmt, TaxBaseAmt, GSTAmt, FGstAmt, InvCurCd, InvStat, PmtTrms, BatNbr, PONbr, CstCntr, Cmpy, VchNbr, TrTyp, Descr, TaxCd, AdjDoc, Void, chknbr, ChkAmt, ChkCurr, ChkDt, ChkClearDt, ChkTyp, VndBnkAcct, AmtPaid, Chk_payId, ChkBatNbr, void_gl_date, VatPct, DiscPct, Cnt, rn)

Select *
FROM (
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY S2.VndNbr, S2.InvNbr ORDER BY BatNbr DESC)
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
Where S2.Cnt > 2
AND S2.InvAmt_Sum < 0

) NegQry1
Where rn = 1

/*################# REMOVED TABLE #######################*/
-- Insert records to get Invoices with 2 or more records that don't equal 0 and have a negative amount of the sum of invamt.
--Pull in the Positive record.
--Insert Into AS_Workspace.dbo.StdPmt_InvAmt_SUM2_Removed
Select S1.*, S2.Cnt, Cast(Null as Integer) as rn
Into AS_Workspace.dbo.StdPmt_InvAmt_SUM_Removed
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt <> S2.InvAmt_Sum
AND S2.InvAmt_Sum < 0
Where S2.Cnt > 2
AND EXISTS(Select InvAmt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S3
Where S2.InvNbr = S3.InvNbr
AND S2.VndNbr = S3.VndNbr
AND S2.InvAmt_Sum = S3.InvAmt )

UNION

-- Get the records that don't equal 0 and Sum of records is > 0.
-- Put these into the SUM2_Removed Table
Select S1.*, S2.Cnt , Cast(Null as Integer) as rn
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt <> S2.InvAmt_Sum
AND S2.InvAmt_Sum > 0
Where EXISTS(Select InvAmt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S3
Where S2.InvNbr = S3.InvNbr
AND S2.VndNbr = S3.VndNbr
AND S2.InvAmt_Sum = S3.InvAmt )

UNION

-- Insert records to get Invoices with more than 2 records that don't equal 0 and have a negative amount of the sum of invamt.
--Pull in 1 of the 2 Negative records into the Removed table.
--Insert Into AS_Workspace.dbo.StdPmt_InvAmt_SUM2_Removed(VndNbr, VndName, InvNbr, InvDt, InvAmt, NetAmt, FInvAmt, DiscAmt, FDiscAmt, TaxBaseAmt, GSTAmt, FGstAmt, InvCurCd, InvStat, PmtTrms, BatNbr, PONbr, CstCntr, Cmpy, VchNbr, TrTyp, Descr, TaxCd, AdjDoc, Void, chknbr, ChkAmt, ChkCurr, ChkDt, ChkClearDt, ChkTyp, VndBnkAcct, AmtPaid, Chk_payId, ChkBatNbr, void_gl_date, VatPct, DiscPct, Cnt, rn)
Select *
FROM (
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY S2.VndNbr, S2.InvNbr ORDER BY BatNbr DESC)
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
AND S2.InvAmt_Sum < 0
Where S2.Cnt > 2
AND EXISTS(Select InvAmt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S3
Where S2.InvNbr = S3.InvNbr
AND S2.VndNbr = S3.VndNbr
AND S2.InvAmt_Sum = S3.InvAmt )

) NegQry2
WHERE rn >= 2

UNION

/*################## NETZero ######################*/
-- Get records by Invoices that don't equal 0.
Select S1.*, S2.Cnt, Cast(Null as Integer) as rn
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
Where S2.InvAmt_Sum = 0




Post #1463551
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse