October 26, 2012 at 2:02 pm
I have a stored procedure that calculates a quarter number. It accepts an input date and returns a quarter number based upon the start and end dates in a period master file.
Can this procedure be called/executed within an SQL program that reads an inventory history table passing it the transaction date from the record read? I'm fairly new to SQL so bear with me.
Sample code:
declare @Quarter varchar(1)
use BPCSARC
select a.RLPROD,
a.LIC#,
b.LICNBR,
b.LICNME,
b.RYLPCT,
c.IPROD,
c.IDESC,
d.TTDTE,
d.TQTY,
d.TPRIC,
d.TVAL,
d.TWHS,
d.TLOCT
--exec getQuarter @DateIn=TTDTE, @QuarterNumber=@Quarter output
from (((LRIM a
left join LRLM b on
a.LIC# = b.LICNBR)
left join IIM c on
a.RLPROD = c.ILPROD)
left join ITH d on
c.IPROD = d.TPROD)
where LIC# = 62 or LIC# = 91 and
TTDTE >= 20090101 and
TTDTE <= 20120331
order by RLPROD
October 26, 2012 at 2:32 pm
Welcome to the forums. 🙂
First, no, you can't call a procedure in-line of another SQL statement to run per row. Those are functions in SQL Server, and they're usually performance killers.
Secondly, if you show us the code you are using in your proc, we should be able to show you how to make it optimal for large queries and get you into some good practices. If you take a look at the link in my first signature, you'll see what we'd really prefer to help you get that working well.
In general, however, when you're working with quarters, weekends, holidays, etc, what we usually work with is a calendar table. Setting it up originally can be a bit of a pain but from there you can automate it to be kept up to date, and it solves a host of run-time computational problems, like determining a date's quarter for reporting.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 29, 2012 at 7:32 am
Hers is my procedure:
USE BPCSARC
GO
--// Create Stored Procedure with OUTPUT parameter
CREATE PROCEDURE getQuarter
@DateIn INT,
@QuarterNumber VARCHAR(1) OUTPUT
AS
BEGIN
SELECT @QuarterNumber = (case when PERIOD >= 01 and PERIOD <= 03 then '1'
when PERIOD >= 04 and PERIOD <= 06 then '2'
when PERIOD >= 07 and PERIOD <= 09 then '3'
when PERIOD >= 10 and PERIOD <= 12 then '4'
end)
FROM GPM
WHERE PEND <= @DateIn
end
GO
Being an Iseries RPG programmer, I'm trying to relate coding similarities between RPGIV ILE to SQL.
All of our libraries and files have been exported to the SQL Server and eventually the Iseries is disappearing so I'm taking a crash course in SQL programming.
Any help will be greatly appreciated.
October 29, 2012 at 12:03 pm
It looks like you may already have a calendar table in GPM. Can you give us the DDL and a few sample rows from that?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 30, 2012 at 7:08 am
GPM "Period Master" PPMID "Record Id" varchar(2)
PCMPNY "Company No" Numeric(2,0)
PYEAR "Fiscal Year" numeric(4,0)
PERIOD "Fiscal Period" numeric(2,0)
PSTART "Period Start Date" numeric(8,0)
PEND "Period End Date" numeric(8,0)
PM 20 2012 1 20120102 20120129
PM 20 2012 2 20120130 20120226
PM 20 2012 3 20120227 20120401
PM 20 2012 4 20120402 20120429
PM 20 2012 5 20120430 20120527
PM 20 2012 6 20120528 20120701
PM 20 2012 7 20120702 20120729
PM 20 2012 8 20120730 20120826
PM 20 2012 9 20120827 20120930
PM 20 2012 10 20121001 20121028
PM 20 2012 11 20121029 20121125
PM 20 2012 12 20121126 20121230
The end result of this task is to track inventory movements by quarter. I realize my approach to this task is not very efficient but it's the only way I could think of at this time. Old habits sometimes die hard.
If you could point me in the right direction, that would be most helpful.
Thanks again.
October 30, 2012 at 4:13 pm
bkatoch (10/30/2012)
The end result of this task is to track inventory movements by quarter. I realize my approach to this task is not very efficient but it's the only way I could think of at this time. Old habits sometimes die hard.If you could point me in the right direction, that would be most helpful.
Thanks again.
So we're talking apples to apples, and since you seem like a nice enough guy to keep around, when we request DDL and data we're looking for directly runnable data, not just a formatted set, like so:
CREATE TABLE #GPM
(PPMID VARCHAR(2) NOT NULL,--Record ID
PCMPNY NUMERIC( 2, 0) NOT NULL,-- Company Number
PYear Numeric( 4, 0) NOT NULL,-- Fiscal Year
Period NUMERIC( 2,0) NOT NULL,-- Fiscal Period
PStart NUMERIC(8,0) NOT NULL,-- Period Start Date
PEnd NUMERIC(8,0) NOT NULL-- Period End Date
)
INSERT INTO #GPM
SELECT'PM', 20, 2012, 1, 20120102, 20120129 UNION ALL
SELECT'PM', 20, 2012, 2, 20120130, 20120326
Now, to move past that. SQL Server has the DATETIME (and DATE) datatypes, and you'll find they're usually more effective for this than Numerics are. However, that said, you don't need an extra function for your dates, all you need is another column in your GPM table indicating the quarter. It'll repeat for the necessary rows the same way Fiscal Year would.
From there, you're looking at joining the dates from the table into the GPM table. Now, usually a range isn't the best of choices for direct joins like this but I'm not sure how much data you have coming off the primary table, and this table seems small enough that doing a between join shouldn't hurt you much. I'd avoid the function entirely and adjust the ranged calendar table you have here. I'd personally also switch PStart and PEnd into DATETIMEs.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply