January 21, 2016 at 1:58 am
Hi,
I'm trying to run a query which sums up all transactions to arrive at today's inventory holding.
I'm now trying to get the inventory holding for the end of each month.
I'm simplifying the problem here so I can't use grouping etc.
I have an SQL Statement like the below:
DECLARE @CutOffDate varchar (6);
SET @CutOffDate = '201501';
INSERT INTO testtable (Quantity, YearMonth)
SELECT SUM(QUANTITY) AS TotalQty, @CutOffDate
FROM ICIVAL
WHERE LEFT(TRANSDATE,6) = @CutOffDate
I have another table containing the last 12 months in this format: 201601, 201512, 201511 etc.
I now want to run the above statement 12 times for each of the last 12 months.
I think I need to use a stored procedure but don't know how to pass the parameter dynamically.
Would appreciate any help.
Thanks.
January 21, 2016 at 2:30 am
Hi,
You just call the stored procedure with parameter after the procedure name as in the following example:
CREATE PROCEDURE MyProc
@CutOffDate varchar(6)
AS
INSERT INTO testtable (Quantity, YearMonth)
SELECT SUM(QUANTITY) AS TotalQty, @CutOffDate
FROM ICIVAL
WHERE LEFT(TRANSDATE,6) = @CutOffDate
GO
DECLARE @CutOffDate varchar (6);
SET @CutOffDate = '201501';
EXEC MyProc @CutOffdate
January 21, 2016 at 3:54 am
Thank you for your prompt reply.
The issue I have is that instead of using a fixed value for @CutOffDate I need to run my query for all the 'YearMonth' from another table.
SET @CutOffDate = 'SELECT YearMonth FROM Past12Months'; does not work.
It should run the stored procedure for all 'YearMonth' in the table 'Past12Monts'.
CREATE PROCEDURE MyProc
@CutOffDate varchar(6)
AS
INSERT INTO testtable (Quantity, YearMonth)
SELECT SUM(QUANTITY) AS TotalQty, @CutOffDate
FROM ICIVAL
WHERE LEFT(TRANSDATE,6) = @CutOffDate
GO
DECLARE @CutOffDate varchar (6);
SET @CutOffDate = 'SELECT YearMonth FROM Past12Months';
EXEC MyProc @CutOffdate
January 21, 2016 at 4:08 am
Sorry, I misunderstood. Creating a function to do the work could be the answer, something like the following:
CREATE FUNCTION MyFunc (@CutOffDate varchar(6))
RETURNS INT
AS
BEGIN
DECLARE @Qty INT
SELECT @Qty = SUM(QUANTITY)
FROM ICIVAL
WHERE LEFT(TRANSDATE,6) = @CutOffDate
RETURN @Qty
END
GO
INSERT INTO testtable (Quantity, YearMonth)
SELECT MyFunc(YearMonth), YearMotnh FROM Past12Months
January 21, 2016 at 5:04 am
You may wish to opt for a iTVF or APPLY instead of scalar function for better performance.
January 21, 2016 at 5:05 am
Thanks. Got it to work now.
January 21, 2016 at 5:16 am
Sorry, too quick.
How would I return multiple columns from the fucntion, i.e. not only quantity, but value also?
Can I specify what columns the function should return?
January 21, 2016 at 6:41 am
boettger.andreas (1/21/2016)
Sorry, too quick.How would I return multiple columns from the fucntion, i.e. not only quantity, but value also?
Can I specify what columns the function should return?
Convert the function to an Inline Table Valued Function (iTVF) which will allow you to return any number of columns you require. The iTVF simply RETURNS TABLE so whatever columns you define in the SELECT it returns will come back from the function, ie no need to explicitly define the shape of the resultset ahead of time. iTVFs perform and scale better than Multi-statement Table Valued Functions (mTVF) and Scalar Valued Functions (SVF).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 21, 2016 at 7:33 am
1) NEVER EVER use a scalar UDF if you can avoid it. HORRIBLY BAD STUFF!!!!!!!!
2) NEVER wrap a function around a column in a WHERE clause if you can avoid it!!!!! In this case a LIKE should work, right?? That is SARGable, seekable (where appropriate), gets good estimates, etc.
3) NEVER store a date as a string if you can avoid it!! So much would be better if you did that here!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 21, 2016 at 7:34 am
Thanks. I've got it now with iTVF and CROSS APPLY
January 21, 2016 at 4:32 pm
boettger.andreas (1/21/2016)
Thanks. I've got it now with iTVF and CROSS APPLY
Great. Can we see your code, please? A whole lot of people end up writing mTVFs instead of iTVFs.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 21, 2016 at 5:50 pm
Sure.
ALTER FUNCTION [dbo].[ICAgeingFuncT]
(
@CutOffDate varchar(6)
)
RETURNS TABLE
AS
RETURN
(
SELECT SUM(TRANSCOST) AS ItemValue, SUM(Quantity) AS Qty
FROM ICIVAL
WHERE LEFT(TRANSDATE,6) <= @CutOffDate
);
SELECT a.ItemValue, a.Qty, p.YearMonth, p.Period FROM Past12Months as p
CROSS APPLY dbo.ICAgeingFuncT(p.YearMonth) As a
January 21, 2016 at 8:47 pm
boettger.andreas (1/21/2016)
Sure.
ALTER FUNCTION [dbo].[ICAgeingFuncT](
@CutOffDate varchar(6)
)
RETURNS TABLE
AS
RETURN
(
SELECT SUM(TRANSCOST) AS ItemValue, SUM(Quantity) AS Qty
FROM ICIVAL
WHERE LEFT(TRANSDATE,6) <= @CutOffDate
);
SELECT a.ItemValue, a.Qty, p.YearMonth, p.PeriodFROM Past12Months as p
CROSS APPLY dbo.ICAgeingFuncT(p.YearMonth) As a
There could be a way to design your iTVF that would allow you to do away with the LEFT-function that wraps TRANSDATE. Wrapping a column with a function can hurt performance pretty bad depending on the schema. Is it correct that @CutoffDate will be a date formatted as YYYYMM? Could you provide the DDL for the table ICIVAL and Past12Months including index and constraint definitions?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 21, 2016 at 9:12 pm
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
January 21, 2016 at 9:41 pm
Funky stuff. Thanks for posting it. I would not expect anything but a scan out of the iTVF query plan even if you could remove the function. If you are in need of improving performance you could try adding a computed column to ICIVAL as LEFT(TRANSDATE,6) and then index the computed column to maybe speed up the query in the iTVF. Without knowing more about the data I could not attempt to flip the WHERE-clause or iTVF API to get rid of the function around TRANSDATE.
What I was hoping to see was a CHAR(8) for TRANSDATE. I must say, I did not expect a DECIMAL(9,0). Any idea why it is 9 digits? If anything I would have thought 8 for YYYYMMDD or 12 for YYYYMMDDHHMM or even 14 for YYYYMMDDHHMMSS, but not 9. Is there a prescribed data-format in that column?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply