November 28, 2016 at 10:02 pm
Hi Gurus,
I have list of quarters in my db table as below:
PERIOD_QTR
2014 QUARTER 1
2014 QUARTER 2
2014 QUARTER 3
2014 QUARTER 4
2015 QUARTER 1
2015 QUARTER 2
2015 QUARTER 3
2015 QUARTER 4
2016 QUARTER 1
2016 QUARTER 2
I need to populate Running MAT based on above quarters. The expected records are as below:
PERIOD_MAT | PERIOD_QTR
2014 MAT Q4| 2014 QUARTER 1
2014 MAT Q4| 2014 QUARTER 2
2014 MAT Q4| 2014 QUARTER 3
2014 MAT Q4| 2014 QUARTER 4
2015 MAT Q1| 2014 QUARTER 2
2015 MAT Q1| 2014 QUARTER 3
2015 MAT Q1| 2014 QUARTER 4
2015 MAT Q1| 2015 QUARTER 1
2015 MAT Q2| 2014 QUARTER 3
2015 MAT Q2| 2014 QUARTER 4
2015 MAT Q2| 2015 QUARTER 1
2015 MAT Q2| 2015 QUARTER 2
2015 MAT Q3| 2014 QUARTER 4
2015 MAT Q3| 2015 QUARTER 1
2015 MAT Q3| 2015 QUARTER 2
2015 MAT Q3| 2015 QUARTER 3
2015 MAT Q4| 2015 QUARTER 1
2015 MAT Q4| 2015 QUARTER 2
2015 MAT Q4| 2015 QUARTER 3
2015 MAT Q4| 2015 QUARTER 4
2016 MAT Q1| 2015 QUARTER 2
2016 MAT Q1| 2015 QUARTER 3
2016 MAT Q1| 2015 QUARTER 4
2016 MAT Q1| 2016 QUARTER 1
2016 MAT Q2| 2015 QUARTER 3
2016 MAT Q2| 2015 QUARTER 4
2016 MAT Q2| 2016 QUARTER 1
2016 MAT Q2| 2016 QUARTER 2
Can you please help me to achieve this in SQL server?
Thanks in advance.
November 29, 2016 at 3:23 am
What is MAT, and where is it coming from? We don't have any informaiton on that. I've made a simple guess that you could do this with an CROSS APPLY, but we probably need more informaiton. Take a look at the link in my signature on how you should be posting questions such as this. Effectively this is pure guess work without true DDL.
USE DevTestDB;
GO
IF EXISTS(SELECT name from sys.tables where name = 'Quarters') BEGIN
DROP TABLE Quarters;
END
CREATE TABLE Quarters (PERIOD_QTR VARCHAR (20));
IF EXISTS(SELECT name from sys.tables where name = 'MatQ') BEGIN
DROP TABLE MatQ;
END
CREATE TABLE MatQ (PERIOD_MAT VARCHAR(20));
INSERT INTO Quarters
VALUES ('2014 QUARTER 1'),
('2014 QUARTER 2'),
('2014 QUARTER 3'),
('2014 QUARTER 4'),
('2015 QUARTER 1'),
('2015 QUARTER 2'),
('2015 QUARTER 3'),
('2015 QUARTER 4'),
('2016 QUARTER 1'),
('2016 QUARTER 2');
INSERT INTO MatQ
VALUES ('MAT Q1'),
('MAT Q2'),
('MAT Q3'),
('MAT Q4');
SELECT LEFT(Q.PERIOD_QTR,4) + ' ' + MQ.PERIOD_MAT AS PERIOD_MAT,
Q.PERIOD_QTR
FROM MatQ MQ
CROSS APPLY Quarters Q
ORDER BY LEFT(Q.PERIOD_QTR,4),
Q.PERIOD_QTR;
DROP TABLE Quarters;
DROP TABLE MatQ;
P.s. You probably really want a Date table.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 29, 2016 at 3:36 am
Thanks for your effort. but it is not giving the expected result.
For quarter data '2014 quarter 1' , '2014 quarter 2', '2014 quarter 3', '2014 quarter 4'
the period_mat column value will be '2014 mat q4'.
For quarter data '2014 quarter 2', '2014 quarter 3', '2014 quarter 4', '2015 quarter 1'
the period_mat column value will be '2015 mat q1'.
For quarter data '2014 quarter 3', '2014 quarter 4', '2015 quarter 1' , '2015 quarter 2'
the period_mat column value will be '2015 mat q2'
For quarter data '2014 quarter 4', '2015 quarter 1', '2015 quarter 2' , '2015 quarter 3'
the period_mat column value will be '2015 mat q3'
....
....
so on
This is how I need to generate the Running MAT (Moving Annual Total) period
November 29, 2016 at 3:38 am
mokarem (11/29/2016)
Thanks for your effort. but it is not giving the expected result.For quarter data '2014 quarter 1' , '2014 quarter 2', '2014 quarter 3', '2014 quarter 4'
the period_mat column value will be '2014 mat q4'.
For quarter data '2014 quarter 2', '2014 quarter 3', '2014 quarter 4', '2015 quarter 1'
the period_mat column value will be '2015 mat q1'.
For quarter data '2014 quarter 3', '2014 quarter 4', '2015 quarter 1' , '2015 quarter 2'
the period_mat column value will be '2015 mat q2'
For quarter data '2014 quarter 4', '2015 quarter 1', '2015 quarter 2' , '2015 quarter 3'
the period_mat column value will be '2015 mat q3'
....
....
so on
This is how I need to generate the Running MAT (Moving Annual Total) period
As I said, what I've done is total guess work, I don't have a DDL or sample data to work with. Take a look at the link and post what it advises and then you'll have a much better success rate for other being able to help you.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 29, 2016 at 6:14 am
Since you didn't explain much about the results that you need, I also guessed what you want to get. If my guess was not good (and there is a very good chance that it isn't a good guess), please specify and explain what you need. It will only improve you chance of getting help.
declare @Quaters table (Y int, Q int)
insert into @Quaters (Y, Q) VALUES ('2014', 1), ('2014', 2),('2014', 3),('2014', 4),
('2015', 1), ('2015', 2),('2015', 3),('2015', 4),
('2016', 1), ('2016', 2);
WITH MyCTE AS (
SELECT Y, Q, ROW_NUMBER() OVER (ORDER BY Y,Q) as RowNum
FROM @Quaters)
SELECT CONCAT(MAT.Y, ' MAT Q', MAT.Q) as Period_MAT, CONCAT(M2.Y, 'QUARTER ', M2.Q), MAT.RowNum AS MatRowNum, M2.RowNum
FROM MyCTE MAT INNER JOIN MyCTE M2 on MAT.RowNum <= M2.RowNum + 3 AND MAT.RowNum >= M2.RowNum
WHERE MAT.RowNum >=4
ORDER BY MAT.RowNum, M2.RowNum
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 29, 2016 at 11:06 am
You say you want running totals, but your expected results look like they are the first step in getting your expected running totals. If that is the case, there is a better way.
SELECT *, SUM(amount)OVER(ORDER BY period_qtr ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS period_MAT
FROM your_table
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 29, 2016 at 9:35 pm
Many thanks for this brilliant help.
I am getting my expected out put from this code.
November 29, 2016 at 9:37 pm
Hi allen
You right. Sorry for your inconvenience. Thanks for your effort.
November 30, 2016 at 12:50 am
Hi Adi Cohn
Many thanks. Your solution is giving my desired out put precisely. I created a function using your code. That also worked very fine.
Then I altered the function to provide the table name and column name dynamically. But this is not working. The dynamic SQL showing below error
' is not a valid identifier '
My SQL Server Database version is : 'Microsoft SQL Server 2005 - 9.00.5057.00'
Below is the full function code:
ALTER FUNCTION [ONC].[FncGenRuningMATfrmQTR_1]
(
@SchemaName VARCHAR(100),
@table_name varchar(500),
@Period_column_name varchar(500)
)
RETURNS @Output TABLE (
Period_MAT VARCHAR(20),
Period_QTR VARCHAR(20)
)
AS
--**************************************************************
--Created by Mokarem on 30-Nov-2016
--**************************************************************
BEGIN
DECLARE @l_sql NVARCHAR(max)
SET @l_sql =
'WITH MyCTE AS
(
SELECT Y, Q, ROW_NUMBER() OVER (ORDER BY Y,Q) as RowNum
FROM (SELECT left(period_qtr,4) as Y, right(period_qtr,1) as Q
FROM
( SELECT DISTINCT '+@Period_column_name+'
FROM '+@SchemaName+'.'+@table_name+'
) a
) b
)
INSERT INTO @Output (Period_MAT,Period_QTR)
SELECT convert(varchar,MAT.Y)+'' MAT Q''+convert(varchar,MAT.Q) as Period_MAT,
convert(varchar,M2.Y)+'' QUARTER ''+convert(varchar,M2.Q) as Period_QTR, MAT.RowNum AS MatRowNum, M2.RowNum
FROM MyCTE MAT
INNER JOIN MyCTE M2 on (MAT.RowNum <= M2.RowNum + 3 AND MAT.RowNum >= M2.RowNum )
WHERE MAT.RowNum >=4
ORDER BY MAT.RowNum, M2.RowNum';
--PRINT (@l_sql)
EXEC @l_sql
RETURN
END;
November 30, 2016 at 1:42 am
User defined functions have some limitations. One of the limitations is that you can't use dynamic SQL in a user defined function, so unfortunately you won't be able to do it this way.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2016 at 1:44 am
User-defined functions cannot make use of dynamic SQL or temp table
Source: https://msdn.microsoft.com/en-us/library/ms191320.aspx
If you need to use Dynamic SQL, then change your function to a Stored Procedure.
November 30, 2016 at 1:58 am
Like I asked before, can we please get some DDL and sample data? This would really make things a lot easier. Could you perhaps give a more detailed explanation of your goals as well? We now know you want to do this inside an udf, which is fine (although in 2005 you only have access to scalar functions), but it gives us a bit more to work with
P.s. You should of really posted this in the 2005 forum then, if you're running 2005. There's a lot of differences between 2012 and 2005.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply