Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Get next 2 weeks Expand / Collapse
Author
Message
Posted Friday, July 25, 2014 11:41 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:16 PM
Points: 839, Visits: 5,422
What are you using to do your reporting? SSRS?
Post #1596454
Posted Saturday, July 26, 2014 2:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 2,214, Visits: 5,982
Here is a quick demonstration of a dynamic SQL solution, in case there is no option of changing/adding any tables in the database.


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
Post #1596462
Posted Saturday, August 2, 2014 3:19 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:04 AM
Points: 33, Visits: 58
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.
Post #1598871
Posted Saturday, August 2, 2014 3:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 2,214, Visits: 5,982
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.


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

Post #1598884
Posted Saturday, August 2, 2014 11:50 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:04 AM
Points: 33, Visits: 58
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.
Post #1598958
Posted Saturday, August 2, 2014 11:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 2,214, Visits: 5,982
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 over

Both our queries are converting sysname to datetime, no difference there though.
Post #1598960
Posted Saturday, August 2, 2014 12:01 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:04 AM
Points: 33, Visits: 58
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 over

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


Fully agreed


--------------------------------------------------------------------------------------------------------------------------------------------------------
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.
Post #1598962
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse