June 13, 2015 at 1:47 pm
Hi I am trying to pull only open AP invoices, so when a invoice has been credited I don't want to pull that amount.
923037 03/27/15 05/11/15 111.63- 111.63
923037 03/27/15 05/11/15 111.63- 111.63-
Like this?
June 13, 2015 at 4:05 pm
cbrammer1219 (6/13/2015)
Hi I am trying to pull only open AP invoices, so when a invoice has been credited I don't want to pull that amount.923037 03/27/15 05/11/15 111.63- 111.63
923037 03/27/15 05/11/15 111.63- 111.63-
Like this?
Ok. Put yourself in our place and then go back and have a look at your own question. You haven't even included column names. Please see the following article before you post again.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2015 at 2:13 pm
Here is the table structure and attached is the data.
USE [dsg]
GO
/****** Object: Table [dbo].[DICE_APDH] Script Date: 6/14/2015 3:44:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[invoices](
[VEND_CODE] [varchar](10) NULL,
[INV_NUM] [varchar](15) NULL,
[INV_DATE] [datetime] NULL,
[INV_AMT] [decimal](13, 2) NULL,
[INV_DISC_BASED_ON] [decimal](13, 2) NULL,
[INV_FREIGHT_AMT] [decimal](13, 2) NULL,
[INV_SALES_TAX_AMT] [decimal](13, 2) NULL,
[INV_REF] [varchar](15) NULL,
[PURC_ORDR_NUM] [varchar](15) NULL,
[INV_RECUR_REF] [varchar](9) NULL,
[VEND_ALT_CODE] [varchar](6) NULL,
[INV_PAYMENT_CYCLE] [varchar](2) NULL,
[INV_STATUS] [varchar](1) NULL,
[TERM_CODE] [varchar](2) NULL,
[INV_DUE_DATE] [datetime] NULL,
[INV_DISC_DUE] [datetime] NULL,
[INV_DISC_AMT] [decimal](13, 2) NULL,
[INV_COMMENT] [varchar](35) NULL,
[INV_PAYMENTS] [decimal](13, 2) NULL,
[INV_DISC_TAKEN] [decimal](13, 2) NULL,
[INV_BALANCE] [decimal](13, 2) NULL,
[INV_LAST_PMT_DATE] [datetime] NULL,
[HIST_STAT_VEND_FLAG] [varchar](1) NULL,
[HIST_STAT_TERM_FLAG] [varchar](1) NULL,
[HIST_OPEN_FLAG] [varchar](1) NULL,
[HIST_1099_FLAG] [varchar](1) NULL,
[HIST_POSTING_DATE] [datetime] NULL,
[POST_CODE] [varchar](6) NULL,
[HIST_INIT_OPER] [varchar](3) NULL,
[HIST_LAST_OPER] [varchar](3) NULL,
[USE_TAX] [varchar](1) NULL,
[INV_LIAB_TAKEN] [decimal](13, 2) NULL,
[INV_WRKC_TAKEN] [decimal](13, 2) NULL,
[SQL_LAST_UPDATE] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
June 14, 2015 at 2:33 pm
ok...we have a table script.....then some data in excel.....what are your expected results from the data?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 14, 2015 at 2:52 pm
There's 499 I believe in the excel file, but this is including the credits. But I just want the open accounts payable, not the (-) ones. If those aren't brought back then my totals come back exact, I've tried to exclude the amts that are like '%-%' but there are values of the same amount with the same inv_number, so that is what is making it difficult to remove those invoices.
June 14, 2015 at 2:53 pm
There's 499 I believe in the excel file, but this is including the credits. But I just want the open accounts payable, not the (-) ones. If those aren't brought back then my totals come back exact, I've tried to exclude the amts that are like '%-%' but there are values of the same amount with the same inv_number, so that is what is making it difficult to remove those invoices.
June 14, 2015 at 2:57 pm
cbrammer1219 (6/14/2015)
Here is the table structure and attached is the data.
USE [dsg]
GO
/****** Object: Table [dbo].[DICE_APDH] Script Date: 6/14/2015 3:44:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[invoices](
[VEND_CODE] [varchar](10) NULL,
[INV_NUM] [varchar](15) NULL,
[INV_DATE] [datetime] NULL,
[INV_AMT] [decimal](13, 2) NULL,
[INV_DISC_BASED_ON] [decimal](13, 2) NULL,
[INV_FREIGHT_AMT] [decimal](13, 2) NULL,
[INV_SALES_TAX_AMT] [decimal](13, 2) NULL,
[INV_REF] [varchar](15) NULL,
[PURC_ORDR_NUM] [varchar](15) NULL,
[INV_RECUR_REF] [varchar](9) NULL,
[VEND_ALT_CODE] [varchar](6) NULL,
[INV_PAYMENT_CYCLE] [varchar](2) NULL,
[INV_STATUS] [varchar](1) NULL,
[TERM_CODE] [varchar](2) NULL,
[INV_DUE_DATE] [datetime] NULL,
[INV_DISC_DUE] [datetime] NULL,
[INV_DISC_AMT] [decimal](13, 2) NULL,
[INV_COMMENT] [varchar](35) NULL,
[INV_PAYMENTS] [decimal](13, 2) NULL,
[INV_DISC_TAKEN] [decimal](13, 2) NULL,
[INV_BALANCE] [decimal](13, 2) NULL,
[INV_LAST_PMT_DATE] [datetime] NULL,
[HIST_STAT_VEND_FLAG] [varchar](1) NULL,
[HIST_STAT_TERM_FLAG] [varchar](1) NULL,
[HIST_OPEN_FLAG] [varchar](1) NULL,
[HIST_1099_FLAG] [varchar](1) NULL,
[HIST_POSTING_DATE] [datetime] NULL,
[POST_CODE] [varchar](6) NULL,
[HIST_INIT_OPER] [varchar](3) NULL,
[HIST_LAST_OPER] [varchar](3) NULL,
[USE_TAX] [varchar](1) NULL,
[INV_LIAB_TAKEN] [decimal](13, 2) NULL,
[INV_WRKC_TAKEN] [decimal](13, 2) NULL,
[SQL_LAST_UPDATE] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
You're making it really tough for anyone to help you. An Excel spreadsheet isn't the "readily consumable data" cited in the article I asked you to read and you haven't told us what conditions exist if an invoice has been credited.
Sorry but I just can't figure out what you're trying to do with so little information.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2015 at 7:54 am
cbrammer1219 (6/14/2015)
Here is the table structure and attached is the data.
USE [dsg]
GO
/****** Object: Table [dbo].[DICE_APDH] Script Date: 6/14/2015 3:44:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[invoices](
[VEND_CODE] [varchar](10) NULL,
[INV_NUM] [varchar](15) NULL,
[INV_DATE] [datetime] NULL,
[INV_AMT] [decimal](13, 2) NULL,
[INV_DISC_BASED_ON] [decimal](13, 2) NULL,
[INV_FREIGHT_AMT] [decimal](13, 2) NULL,
[INV_SALES_TAX_AMT] [decimal](13, 2) NULL,
[INV_REF] [varchar](15) NULL,
[PURC_ORDR_NUM] [varchar](15) NULL,
[INV_RECUR_REF] [varchar](9) NULL,
[VEND_ALT_CODE] [varchar](6) NULL,
[INV_PAYMENT_CYCLE] [varchar](2) NULL,
[INV_STATUS] [varchar](1) NULL,
[TERM_CODE] [varchar](2) NULL,
[INV_DUE_DATE] [datetime] NULL,
[INV_DISC_DUE] [datetime] NULL,
[INV_DISC_AMT] [decimal](13, 2) NULL,
[INV_COMMENT] [varchar](35) NULL,
[INV_PAYMENTS] [decimal](13, 2) NULL,
[INV_DISC_TAKEN] [decimal](13, 2) NULL,
[INV_BALANCE] [decimal](13, 2) NULL,
[INV_LAST_PMT_DATE] [datetime] NULL,
[HIST_STAT_VEND_FLAG] [varchar](1) NULL,
[HIST_STAT_TERM_FLAG] [varchar](1) NULL,
[HIST_OPEN_FLAG] [varchar](1) NULL,
[HIST_1099_FLAG] [varchar](1) NULL,
[HIST_POSTING_DATE] [datetime] NULL,
[POST_CODE] [varchar](6) NULL,
[HIST_INIT_OPER] [varchar](3) NULL,
[HIST_LAST_OPER] [varchar](3) NULL,
[USE_TAX] [varchar](1) NULL,
[INV_LIAB_TAKEN] [decimal](13, 2) NULL,
[INV_WRKC_TAKEN] [decimal](13, 2) NULL,
[SQL_LAST_UPDATE] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
I have to echo Jeff Moden's words here. There's nowhere near enough information to go on, and that spreadsheet isn't consumable. Take a look at what I suspect is the data your looking to isolate, in the attached picture file (SPREADSHEET.JPG). There are all manner of problems here:
1.) Invoice number field can have a minus sign at the end. This is a data type issue that makes importing from the spreadsheet impractical.
2.) I looked at a couple of rows that were just near the numbers that I thought needed to be highlighted, just to be sure I could see what I might need to see, so that's why there are some rows in the picture that are NOT highlighted.
3.) There are invoices that have minus signs after the invoice number, and have minus signs after the invoice value, and some that don't. Which is which, and why?
4.) There are rows with the $ character in front of the invoice values and rows without. Why?
The spreadsheet looks like either a bad export or something someone manually massaged. I have trouble believing someone would actually store data this way. Let us know, cbrammer1219, what the scoop is...
EDIT: forgot attachment.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 15, 2015 at 8:04 am
This is exactly how the data is stored, that is why I am having so much trouble pulling the correct data. This data comes from a Unix system and is pushed to our SQL "Called our data warehouse" HA, that I have inherited. We are leaving the old system and this is why I am trying to pull this data to get it as clean as I can for our Pilot, and calling upon the experts here.
June 15, 2015 at 8:07 am
cbrammer1219 (6/15/2015)
This is exactly how the data is stored, that is why I am having so much trouble pulling the correct data. This data comes from a Unix system and is pushed to our SQL "Called our data warehouse" HA, that I have inherited. We are leaving the old system and this is why I am trying to pull this data to get it as clean as I can for our Pilot, and calling upon the experts here.
Okay, but we're going to be as much in the dark as you are unless you can establish with certainty that a particular value in a particular column has a specific meaning as to what it represents. Lacking that, we're all in the same sinking ship...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 16, 2015 at 7:28 am
cbrammer1219 (6/15/2015)
This is exactly how the data is stored, that is why I am having so much trouble pulling the correct data. This data comes from a Unix system and is pushed to our SQL "Called our data warehouse" HA, that I have inherited. We are leaving the old system and this is why I am trying to pull this data to get it as clean as I can for our Pilot, and calling upon the experts here.
I don't believe that the data is stored in an Excel file on your Unix system. So presumably what you actually mean is "this is the mess that our import process makes of the data when we push it into excel (via "our data warehouse"?) - and what you need to fix is that "push" process. Maybe if you tell us what the data format on the Unix system is, and post the Unix data, we can make sense of it.
Tom
July 12, 2015 at 2:45 pm
Every program has its own business rules which sits on the top of the database structure. Our have a list of transactions that you need to pair up effectively to work out if they have been paid or not. Only the program's author can help you with this. An example dynamics GP holds all invoices and payments together in one table but there are two things to help identify of an invoice has been paid. 1. The current amount column reduced to 0 after being paid. 2. There is a second table to that states which payments or credit notes link to which invoice. With this program there are two ways to obtain the record set that you need. You need to know how two documents are associated with each other first before then working out how to exclude them.
Hope this helps
Michael
Mickey4nay
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply