SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get next 2 weeks


Get next 2 weeks

Author
Message
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4831 Visits: 13157
What are you using to do your reporting? SSRS?
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 18591
Here is a quick demonstration of a dynamic SQL solution, in case there is no option of changing/adding any tables in the database.
Cool

USE tempdb;
GO
/* TEST DATA */
CREATE TABLE dbo.Planner(
[Name] [nvarchar](255) NULL
,[ProjectName] [nvarchar](255) NULL
,[7-Jul-14][nvarchar](255) NULL
,[14-Jul-14][nvarchar](255) NULL
,[21-Jul-14][nvarchar](255) NULL
,[28-Jul-14][nvarchar](255) NULL
,[4-Aug-14][nvarchar](255) NULL
,[11-Aug-14][nvarchar](255) NULL
,[18-Aug-14][nvarchar](255) NULL
,[25-Aug-14][nvarchar](255) NULL
,[1-Sep-14][nvarchar](255) NULL
,[8-Sep-14][nvarchar](255) NULL
,[15-Sep-14][nvarchar](255) NULL)

insert into Planner (Name,ProjectName,[7-Jul-14]
,[14-Jul-14]
,[21-Jul-14]
,[28-Jul-14]
,[4-Aug-14]
,[11-Aug-14]
,[18-Aug-14]
,[25-Aug-14]
,[1-Sep-14]
,[8-Sep-14]
,[15-Sep-14]) Values ('Mike','Audit','10','10','10','0','5','5','5','4','15','14','10')


DECLARE @TABLE_NAME NVARCHAR(128) = N'Planner'
DECLARE @TABLE_SCHEMA NVARCHAR(128) = N'dbo'
DECLARE @PARAM NVARCHAR(128) = N'@TABLE_NAME NVARCHAR(128),@TABLE_SCHEMA NVARCHAR(128)';
DECLARE @NORMALIZED_DATA TABLE
(
ND_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,P_NAME NVARCHAR(50) NOT NULL
,P_PROJECT_NAME NVARCHAR(100) NOT NULL
,DATE_VAL DATE NOT NULL
,P_VALUE INT NOT NULL
);

DECLARE @SQL_STR NVARCHAR(MAX) = N'';

SELECT @SQL_STR = N'SELECT
P.Name
,P.ProjectName
,CONVERT(DATE,C.COLUMN_NAME,105) AS DATE_VAL
,CASE
'+ (
SELECT
N'WHEN C.COLUMN_NAME = N' + NCHAR(39) + C.COLUMN_NAME + NCHAR(39) + N' THEN P.[' + C.COLUMN_NAME + N']
'
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = N'Planner'
AND C.TABLE_SCHEMA = N'dbo'
AND C.ORDINAL_POSITION > 2
FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)') + N' END AS COL_VAL
FROM INFORMATION_SCHEMA.COLUMNS C
OUTER APPLY dbo.Planner P
WHERE C.TABLE_NAME = @TABLE_NAME
AND C.TABLE_SCHEMA = @TABLE_SCHEMA
AND C.ORDINAL_POSITION > 2';

/*
PRINT @SQL_STR
*/
INSERT INTO @NORMALIZED_DATA (P_NAME,P_PROJECT_NAME,DATE_VAL,P_VALUE)
EXEC SP_EXECUTESQL @SQL_STR, @PARAM, @TABLE_NAME, @TABLE_SCHEMA;

DECLARE @TODAY DATE = '2014-07-23';
DECLARE @PER_LEN INT = 14;
DECLARE @FORTNIGHT DATE = DATEADD(DAY,14,@TODAY);

SELECT
ND.P_NAME
,ND.P_PROJECT_NAME
,@TODAY AS TODAY
,SUM(ND.P_VALUE) AS SUM_PERIOD
FROM @NORMALIZED_DATA ND
WHERE ND.DATE_VAL BETWEEN @TODAY AND @FORTNIGHT
GROUP BY ND.P_NAME,ND.P_PROJECT_NAME

/* CLEAN UP */
DROP TABLE dbo.Planner;



Results
P_NAME  P_PROJECT_NAME  TODAY      SUM_PERIOD
------- --------------- ---------- -----------
Mike Audit 2014-07-23 5

Daniel Matthee
Daniel Matthee
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 212
Hi

Yes normalize should work but if you can get your dates to be more like and integer value like 20140707 = 7-jul-14

then the following idea might work better for you.

CREATE TABLE #Planner(
[Name] [nvarchar](255) NULL,
[ProjectName] [nvarchar](255) NULL
,[20140707][nvarchar](255) NULL
,[20140714][nvarchar](255) NULL
,[20140721][nvarchar](255) NULL
,[20140728][nvarchar](255) NULL
,[20140804][nvarchar](255) NULL
,[20140811][nvarchar](255) NULL
,[20140818][nvarchar](255) NULL
,[20140825][nvarchar](255) NULL
,[20140901][nvarchar](255) NULL
,[20140908][nvarchar](255) NULL
,[20140915][nvarchar](255) NULL)

insert into #Planner (Name,ProjectName,[20140707]
,[20140714]
,[20140721]
,[20140728]
,[20140804]
,[20140811]
,[20140818]
,[20140825]
,[20140901]
,[20140908]
,[20140915]) Values ('Mike','Audit','10','10','10','0','5','5','5','4','15','14','10')

SELECT * from #Planner

SELECT Name,ProjectName,cast(Value as int) Value,Date
Into #table
FROM
(SELECT Name,ProjectName,[20140707],[20140714],[20140721],[20140728],[20140804]
,[20140811],[20140818],[20140825],[20140901],[20140908],[20140915]
FROM #Planner ) p
UNPIVOT
(Value FOR Date IN
( [20140707],[20140714],[20140721],[20140728],[20140804],[20140811],[20140818],[20140825],[20140901],[20140908],[20140915])
)AS unpvt
GO

select Name,ProjectName, Sum(value) As Value
from #table
where date between 20140728 and 20140804
group by Name,ProjectName

Please give your feeling about this method

--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 18591
Daniel Matthee (8/2/2014)

Please give your feeling about this method


Although your code works fine, it wouldn't be my first choice as if the OP could change the schema, normalizing the data would be a better option. Also your code has about the same flexibility as the sample below.
Cool

SELECT
P.Name
,P.ProjectName
,CONVERT(INT,P.[20140728]) + CONVERT(INT,P.[20140804])
FROM #Planner P


Daniel Matthee
Daniel Matthee
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 212
Eirikur Eiriksson (8/2/2014)

CONVERT(INT,P.[20140728]) + CONVERT(INT,P.[20140804])



Hi Eirikur

I dont fully agree that it has the same flexibility.
For example in my suggestion i can create any date range as a integer presentation and it will automatically add up all values that are valid.
Where in your example this is a manual process.

Dont get me wrong yours will still work. But i find my solution more dynamic and flexible.
I however do agree that any changes to the table structure will increase the complexity of the Pivot where your solution only will need more converts
:-)

Hope this makes sense?

--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 18591
Daniel Matthee (8/2/2014)
Eirikur Eiriksson (8/2/2014)

CONVERT(INT,P.[20140728]) + CONVERT(INT,P.[20140804])



Hi Eirikur

I dont fully agree that it has the same flexibility.
For example in my suggestion i can create any date range as a integer presentation and it will automatically add up all values that are valid.
Where in your example this is a manual process.

Dont get me wrong yours will still work. But i find my solution more dynamic and flexible.
I however do agree that any changes to the table structure will increase the complexity of the Pivot where your solution only will need more converts
:-)

Hope this makes sense?


Of course it makes lot more sense than the given table structure. What I meant was that if the structure changes, the query needs to change. Applications utilizing such a structure often append or inject new columns and for your query and my later sample, game overBlush

Both our queries are converting sysname to datetime, no difference there though.
Cool
Daniel Matthee
Daniel Matthee
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 212
Eirikur Eiriksson (8/2/2014)
Daniel Matthee (8/2/2014)
Eirikur Eiriksson (8/2/2014)

CONVERT(INT,P.[20140728]) + CONVERT(INT,P.[20140804])



Hi Eirikur

I dont fully agree that it has the same flexibility.
For example in my suggestion i can create any date range as a integer presentation and it will automatically add up all values that are valid.
Where in your example this is a manual process.

Dont get me wrong yours will still work. But i find my solution more dynamic and flexible.
I however do agree that any changes to the table structure will increase the complexity of the Pivot where your solution only will need more converts
:-)

Hope this makes sense?


Of course it makes lot more sense than the given table structure. What I meant was that if the structure changes, the query needs to change. Applications utilizing such a structure often append or inject new columns and for your query and my later sample, game overBlush

Both our queries are converting sysname to datetime, no difference there though.
Cool


Fully agreed
:-D

--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search