February 16, 2014 at 6:22 pm
HI,
I have these SEELECT statments. I do have the data in the table CheckNumbers_DM_DeleteAfter_03132014 and the BankAccountTrans tables and have 512 matching rows. But for some reason the below sql doesn't return any data.
SELECT * FROM CheckNumbers_DM_DeleteAfter_03132014
SELECT *
FROM BankAccountTrans b
INNER JOIN CheckNumbers_DM_DeleteAfter_03132014 c
ON b.ChequeNum = c.CheckNumber
Note : There is no direct relation between these two tables but the b.ChequeNum and c.CheckNUmber have matching values. If there is defined relation then I can't join the tables?
Thanks,
Dev
February 16, 2014 at 6:55 pm
What is the table definition for each of these tables?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 16, 2014 at 7:26 pm
movvap (2/16/2014)
Note : There is no direct relation between these two tables but the b.ChequeNum and c.CheckNUmber have matching values. If there is defined relation then I can't join the tables?
If you and I have checks with the same check number (not at all uncommon) and my Check# 1435 is for $1000 and your Check# 1435 is for $10, which would you rather have deducted from your account?
You can't match check numbers to get an account. Unless the CheckNumbers_DM_DeleteAfter_03132014 table has a Bank Account # in it, then you must not join these tables because there's nothing positive to join on.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2014 at 7:57 pm
Hi SSCoach,
Thanks for the quick reply. Please see below.
For BankAccountTrans
/****** Object: Table [dbo].[BANKACCOUNTTRANS] Script Date: 2/16/2014 9:47:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BANKACCOUNTTRANS](
[TRANSDATE] [datetime] NOT NULL,
[VOUCHER] [nvarchar](20) NOT NULL,
[LEDGERTRANSTYPE] [int] NOT NULL,
[ACCOUNTID] [nvarchar](10) NOT NULL,
[AMOUNTCUR] [numeric](32, 16) NOT NULL,
[AMOUNTMST] [numeric](32, 16) NOT NULL,
[CURRENCYCODE] [nvarchar](3) NOT NULL,
[ACCOUNTSTATEMENT] [nvarchar](20) NOT NULL,
[ACCOUNTSTATEMENTDATE] [datetime] NOT NULL,
[BANKTRANSTYPE] [nvarchar](15) NOT NULL,
[PAYMREFERENCE] [nvarchar](20) NOT NULL,
[PAYMENTMODE] [nvarchar](15) NOT NULL,
[DEPOSITNUM] [nvarchar](20) NOT NULL,
[RECONCILED] [int] NOT NULL,
[INCLUDED] [int] NOT NULL,
[AMOUNTCORRECT] [numeric](32, 16) NOT NULL,
[MANUAL] [int] NOT NULL,
[CHEQUENUM] [nvarchar](20) NOT NULL,
[TXT] [nvarchar](255) NOT NULL,
[CANCEL] [int] NOT NULL,
[BANKTRANSCURRENCYCODE] [nvarchar](3) NOT NULL,
[BANKTRANSAMOUNTCUR] [numeric](32, 16) NOT NULL,
[LEDGERDIMENSION] [bigint] NOT NULL,
[TAXGROUP] [nvarchar](15) NOT NULL,
[TAXITEMGROUP] [nvarchar](15) NOT NULL,
[ACKNOWLEDGEMENTDATE] [datetime] NOT NULL,
[CANCELPENDING] [int] NOT NULL,
[DEFAULTDIMENSION] [bigint] NOT NULL,
[REASONREFRECID] [bigint] NOT NULL,
[CREATEDDATETIME] [datetime] NOT NULL,
[CREATEDBY] [nvarchar](8) NOT NULL,
[DATAAREAID] [nvarchar](4) NOT NULL,
[RECVERSION] [int] NOT NULL,
[RECID] [bigint] NOT NULL,
[DEL_LEDGERACCOUNTNUM] [nvarchar](20) NOT NULL,
[DEL_DIMENSION] [nvarchar](15) NOT NULL,
[DEL_DIMENSION2_] [nvarchar](15) NOT NULL,
[DEL_DIMENSION3_] [nvarchar](15) NOT NULL,
[DEL_DIMENSION4_] [nvarchar](15) NOT NULL,
[DEL_DIMENSION5_] [nvarchar](15) NOT NULL,
[DEL_CREATEDTIME] [int] NOT NULL,
[SKS_BR_BANKTRANRECID] [bigint] NOT NULL,
[SKS_BR_MANUALRECONCILIATIONID] [uniqueidentifier] NOT NULL,
CONSTRAINT [I_008ACCTTRANSDATE] PRIMARY KEY CLUSTERED
(
[DATAAREAID] ASC,
[ACCOUNTID] ASC,
[TRANSDATE] ASC,
[RECID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The CheckNumbers_DM_DeleteAfter_03132014 table was created by importing an excel spread sheet data. When I did a create table script this is what I got.
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CheckNumbers_DM_DeleteAfter_03132014](
[Date] [datetime] NULL,
[VoucherNumber] [nvarchar](255) NULL,
[CheckNumber] [nvarchar](255) NULL,
[Currency] [nvarchar](255) NULL,
[AmountInTransactionCurrency] [float] NULL,
[F6] [nvarchar](255) NULL
) ON [PRIMARY]
GO
I also tried doing ltrim and rtrim functions to trim the chequenum but no luck.
February 16, 2014 at 8:11 pm
Looks like you have no valid means to join the tables (see Jeff's comment).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 16, 2014 at 8:52 pm
Hi Jeff,
Your point is correct. But I have a different scenario, our accounting team having some issues - checks were not properly reconcilied with some earlier date and they wanted those checks to be unreconcilid. All they provided me was the Check numbers that need to be fixed and the Voucher numbers in an Excel. I see multiple checks were cut for a single vendor so I can't match with the Voucher number.
So, I imported the Excel data to our db and trying to update the AccountStatementDate column in the BankAccountTrans table for the given checks.
As a work around I just created a temp table and only copied the Check numbers to the #Checks from the CheckNumbers_DM_DeleteAfter_03132014 table as below
CREATE TABLE #Checks
(
ChequeNum Int
)
INSER INTO #Checks (chequenum)
SELECT DISTINCT(CheckNumber) FROM CheckNumbers_DM_DeleteAfter_03132014
and once I have the checkque numbers filled into $Checks to compare against then I can write an update statement for the BankAccountTrans like belowbased on certain conditions as below
UPDATE b
SET AccountStatementDate = '1900-01-01 00:00:00.000'
FROM BankAccountTrans b INNER JOIN #CHECKS c on b.ChequeNum = c.ChequeNum
WHERE b.sks_br_BankTranRecID = 0
AND b.SKS_BR_MANUALRECONCILIATIONID = '{00000000-0000-0000-0000-000000000000}'
AND b.Included=0
AND b.Reconciled=0
AND b.SKS_Br_BankTranRecID=0
AND b.AccountStatementDate <> '1900-01-01 00:00:00.000'
This updated the records that need to be updated in our TEST server. Have I done it right or any better ways to do it?
Thanks!
M
February 17, 2014 at 12:09 am
As I said previously, there's going to have to be something other than just a check number. Even if check number is unique in both tables, there's no guarantee that it's correct because check numbers are no unique in and of themselves.
For example, does the Vouncher Number and the Check Number or the Check Number, Date, and Amount form a unique key? If not, there's no way to do this without an account number in the CheckNumbers_DM_DeleteAfter_03132014 table.
And how do they "know" that the "right" rows were updated? There's no way to tell. Just because you get check number matches, that doesn't mean a thing because check numbers are not unique across accounts. The fact that you had to do a DISTINCT is proof enough of that.
As a bit of a sidebar, please tell me which bank this is for so I can make sure I don't have any involvement with them. This is spooky stuff they're having you do. Real Spooky.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2014 at 12:37 am
you can check few things before joining these two tables
let say u have check number in table 1 that i-e "12012410"
and
you have check number in table 2 that i-e " 12012410" like this then this will not match.
there might be few special characters in the table 2 or table 1. so i would recommend that you first remove these special characters and then match then, from this way u will be able to get few data out of it.
as the look of it you may have got that data from a excel file or a flat file. (just my assumption).
hope it helps
February 17, 2014 at 5:03 am
twin.devil (2/17/2014)
you can check few things before joining these two tableslet say u have check number in table 1 that i-e "12012410"
and
you have check number in table 2 that i-e " 12012410" like this then this will not match.
there might be few special characters in the table 2 or table 1. so i would recommend that you first remove these special characters and then match then, from this way u will be able to get few data out of it.
as the look of it you may have got that data from a excel file or a flat file. (just my assumption).
hope it helps
That all might be true but matching on Check Numbers across multiple accounts is a disaster waiting to happen because check numbers across account are NOT unique!
Do you suppose more than one person at the bank had check #300 (for example)???
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2014 at 5:17 am
Jeff Moden (2/17/2014)
twin.devil (2/17/2014)
you can check few things before joining these two tableslet say u have check number in table 1 that i-e "12012410"
and
you have check number in table 2 that i-e " 12012410" like this then this will not match.
there might be few special characters in the table 2 or table 1. so i would recommend that you first remove these special characters and then match then, from this way u will be able to get few data out of it.
as the look of it you may have got that data from a excel file or a flat file. (just my assumption).
hope it helps
That all might be true but matching on Check Numbers across multiple accounts is a disaster waiting to happen because check numbers across account are NOT unique!
Do you suppose more than one person at the bank had check #300 (for example)???
you understanding is absolutely correct Jeff, the only issue i referred OP for this working.
Following is the scope mentioned in the earlier comments
Your point is correct. But I have a different scenario, our accounting team having some issues - checks were not properly reconcilied with some earlier date and they wanted those checks to be unreconcilid. All they provided me was the Check numbers that need to be fixed and the Voucher numbers in an Excel. I see multiple checks were cut for a single vendor so I can't match with the Voucher number
beside that i am at your side that account # can be repeated for multiple business partners.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply