January 22, 2016 at 12:46 am
Not sure why dates are saved as decimals. Might be a legacy from the early days of the application still.
The table is part of SAGE 300 ERP (also known as Accpac).
January 22, 2016 at 3:22 am
If the date column is indexed, then you will probably get better performance if you select the month as "WHERE Date >= 20160100 AND Date < 20160201"
Also, instead of having a query that returns data for a single month and then executing that query twelve times and collecting the information, why not have a query that in one execution gives you the data for all twelve months?
January 22, 2016 at 4:11 am
Hi, I'm doing inventory snapshots for each of the last 12 months.
For this I need to sum up all transactions up to the month I'm looking at.
The function would help in just having one statement and run this 12 times with a different month.
The alternative I can think of is a UNION ALL.
I've already noticed that the function is super slow, maybe because of the CROSS APPLY.
The UNION ALL is way faster.
January 22, 2016 at 4:14 am
January 22, 2016 at 6:06 am
boettger.andreas (1/22/2016)
Hi, I'm doing inventory snapshots for each of the last 12 months.For this I need to sum up all transactions up to the month I'm looking at.
The function would help in just having one statement and run this 12 times with a different month.
The alternative I can think of is a UNION ALL.
No, the alternative would be a single query that roughly looks like this:
SELECT TransDate / 100, SUM(Quantity)
FROM IciVal
GROUP BY TransDate;
You may still need a WHERE clause to restrict it to the correct year.
January 22, 2016 at 6:07 am
MadAdmin (1/22/2016)
Just throwing in my 2 penceInstead of the non sargable
WHERE LEFT(TRANSDATE,6) = @CutOffDateuse the sargable
SELECT SUM(QUANTITY) AS TotalQty, @CutOffDateFROM ICIVAL
WHERE TRANSDATE like @CutOffDate+'%'
Since TransDate is defined as numeric, this is not sargable either. The implicit conversion to char that needs to be done in order to evaluate the LIKE has the same effect as wrapping the column in an explicit function.
January 22, 2016 at 6:17 am
Hugo Kornelis (1/22/2016)
MadAdmin (1/22/2016)
Just throwing in my 2 penceInstead of the non sargable
WHERE LEFT(TRANSDATE,6) = @CutOffDateuse the sargable
SELECT SUM(QUANTITY) AS TotalQty, @CutOffDateFROM ICIVAL
WHERE TRANSDATE like @CutOffDate+'%'
Since TransDate is defined as numeric, this is not sargable either. The implicit conversion to char that needs to be done in order to evaluate the LIKE has the same effect as wrapping the column in an explicit function.
...../ )
.....' /
---' (_____
......... ((__)
..... _ ((___)
....... -'((__)
--.___((_)
January 22, 2016 at 6:36 am
boettger.andreas (1/21/2016)
Yes, @CutOffDate is in the format 'YYYYMM'Past12Months is a view.
SELECTLEFT(CAST(CONVERT(varchar(8), DATEADD(MONTH, number, LEFT(CAST(CONVERT(varchar(8), DATEADD(MONTH, - 11, GETDATE()), 112) AS INT), 6) + '01'), 112) AS INT), 6) AS YearMonth,number + 1 AS Period
FROM master.dbo.spt_values AS x
WHERE (type = 'P') AND (number <= DATEDIFF(MONTH, LEFT(CAST(CONVERT(varchar(8), DATEADD(MONTH, - 11, GETDATE()), 112) AS INT), 6) + '01', LEFT(CAST(CONVERT(varchar(8), DATEADD(MONTH, - 0, GETDATE()), 112) AS INT), 6) + '01'))
The DDL for ICIVAL is below.
CREATE TABLE [dbo].[ICIVAL](
[ACCTSET] [char](6) NOT NULL,
[LOCATION] [char](6) NOT NULL,
[ITEMNO] [char](24) NOT NULL,
[FISCYEAR] [char](4) NOT NULL,
[FISCPERIOD] [smallint] NOT NULL,
[TRANSDATE] [decimal](9, 0) NOT NULL,
[DAYENDSEQ] [int] NOT NULL,
[ENTRYSEQ] [int] NOT NULL,
[LINENO] [smallint] NOT NULL,
[AUDTDATE] [decimal](9, 0) NOT NULL,
[AUDTTIME] [decimal](9, 0) NOT NULL,
[AUDTUSER] [char](8) NOT NULL,
[AUDTORG] [char](6) NOT NULL,
[CATEGORY] [char](6) NOT NULL,
[DOCNUM] [char](22) NOT NULL,
[TRANSTYPE] [smallint] NOT NULL,
[UNIT] [char](10) NOT NULL,
[QUANTITY] [decimal](19, 4) NOT NULL,
[CONVERSION] [decimal](19, 6) NOT NULL,
[TRANSCOST] [decimal](19, 3) NOT NULL,
[STKQTY] [decimal](19, 4) NOT NULL,
[OPTAMT] [decimal](19, 3) NOT NULL,
[APP] [char](2) NOT NULL,
[STOCKUNIT] [char](10) NOT NULL,
[DEFPRICLST] [char](6) NOT NULL,
[TOTALCOST] [decimal](19, 3) NOT NULL,
[RECENTCOST] [decimal](19, 6) NOT NULL,
[COST1] [decimal](19, 6) NOT NULL,
[COST2] [decimal](19, 6) NOT NULL,
[LASTCOST] [decimal](19, 6) NOT NULL,
[STDCOST] [decimal](19, 6) NOT NULL,
[COSTUNIT] [char](10) NOT NULL,
[COSTCONV] [decimal](19, 6) NOT NULL,
[TOTALQTY] [decimal](19, 4) NOT NULL,
[PRICELIST] [char](6) NOT NULL,
[PRICEDECS] [smallint] NOT NULL,
[BASEPRICE] [decimal](19, 6) NOT NULL,
[BASEUNIT] [char](10) NOT NULL,
[BASECONV] [decimal](19, 6) NOT NULL,
[DETAILNUM] [smallint] NOT NULL,
[COMPNUM] [int] NOT NULL,
[DATEBUS] [decimal](9, 0) NOT NULL,
CONSTRAINT [ICIVAL_KEY_0] PRIMARY KEY CLUSTERED
(
[ACCTSET] ASC,
[LOCATION] ASC,
[ITEMNO] ASC,
[FISCYEAR] ASC,
[FISCPERIOD] ASC,
[TRANSDATE] ASC,
[DAYENDSEQ] ASC,
[ENTRYSEQ] ASC,
[LINENO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [KEY_1] Script Date: 22/01/2016 12:09:07 PM ******/
CREATE NONCLUSTERED INDEX [KEY_1] ON [dbo].[ICIVAL]
(
[LOCATION] ASC,
[ITEMNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [KEY_2] Script Date: 22/01/2016 12:09:07 PM ******/
CREATE NONCLUSTERED INDEX [KEY_2] ON [dbo].[ICIVAL]
(
[DAYENDSEQ] ASC,
[ENTRYSEQ] ASC,
[LINENO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [KEY_3] Script Date: 22/01/2016 12:09:07 PM ******/
CREATE NONCLUSTERED INDEX [KEY_3] ON [dbo].[ICIVAL]
(
[ITEMNO] ASC,
[LOCATION] ASC,
[FISCYEAR] ASC,
[FISCPERIOD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [KEY_4] Script Date: 22/01/2016 12:09:07 PM ******/
CREATE NONCLUSTERED INDEX [KEY_4] ON [dbo].[ICIVAL]
(
[ACCTSET] ASC,
[LOCATION] ASC,
[ITEMNO] ASC,
[FISCYEAR] ASC,
[FISCPERIOD] ASC,
[TRANSTYPE] ASC,
[TRANSDATE] ASC,
[DOCNUM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Oh my... As some have alluded to (and I know you're probably not the one that designed the table), I'll intensely stress that the design of the date and time columns is a real performance killer and actually uses 1 byte more of storage space than even a DATETIME datatype. Heh... and with all the decimal dates, fiscal year is a CHAR(4). There's also going to be a price to pay for separating the date and time for the "Audt" (Seriously??? They abbreviated "Audit"????) that you may not have run into yet but will. Having two costs is also is both non-descript and is a form of denormalization that you'll learn to hate. And having no apparent standard for the number of decimal places for currency amounts will cause hidden problems during calculations, as well.
Of course, much of this table is pretty badly denormalized and, unless it's "just" a reporting table, that will also become a killer code-wise down the road. My recommendation is that they sit down and carefully consider fixing the table for all those things that I mentioned and maybe a couple of more. For example, what's the difference between "Recent" cost and "Last" cost and why is this information in this table at all?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2016 at 6:51 am
not sure what version of Sage 300 you are running
but this table def for ICIVAL shows transdate as date
http://sage300.guru/AOM55A/ICIVAL.HTM
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 22, 2016 at 7:22 am
Interesting. I use Sage 300 ERP 2014.
I guess what they mean is that only a date can be entered.
In the table it's as a decimal.
January 22, 2016 at 7:25 am
SELECT TransDate / 100, SUM(Quantity)FROM IciVal
GROUP BY TransDate;
You may still need a WHERE clause to restrict it to the correct year.
Does this devide TransDate by 100? Not sure why?
January 22, 2016 at 12:03 pm
boettger.andreas (1/22/2016)
SELECT TransDate / 100, SUM(Quantity)FROM IciVal
GROUP BY TransDate;
You may still need a WHERE clause to restrict it to the correct year.
Does this devide TransDate by 100? Not sure why?
From the context of the thread, I understood (perhaps incorrectly) that the date is stored in a numeric column, as a value. So today (Jan 22nd 2016 in my time zone) would be stored as the numeric value 20160122. Dividing that by 100 yields 201601.22, and with integer division the fraction gets discarded - so you are left with 201601, the yyyymm representation of the month that you use in the original question.
However, I trhink I made an error. I wrote the code above assuming an integer data type, but I now see that it's actually decimal(9,0) - which means that integer division will not be used. So please replace this with "CAST(TransDate AS int) / 100"
January 25, 2016 at 2:08 am
A possibility exists that ERP's decimal(9) date representation is for example, 20080304? for 03/04/2008. That is 10000*y+1000*m+10*d + ? where ? is day of week or something.
To ensure WHERE predicates are sargable i'd prefer to convert procedure argument to ERP representation rather then to convert ERP data to SQL standard types.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply