July 23, 2014 at 2:25 pm
I am trying to get the numbers for the next two week from now . Need help with querying this ..
The table looks like this
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')
So if today is 23rd July , next two weeks sum will be 28th July and 4th Aug ie 0+5 = 5 . Need help with querying this ..
July 23, 2014 at 2:57 pm
You have 2 options:
- Normalize your table
- Use dynamic SQL
You decide.
July 23, 2014 at 3:10 pm
Luis Cazares (7/23/2014)
You have 2 options:- Normalize your table
- Use dynamic SQL
You decide.
I agree with Luis...normalise your data...
what are you expecting the results to be from your post?
what other tables do you have that store this data.....is this just for reporting ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 23, 2014 at 3:28 pm
Yes , its for reporting
July 23, 2014 at 3:35 pm
sharonsql2013 (7/23/2014)
Yes , its for reporting
what are you expecting the results to be from your post?
what other tables do you have that store this data.....?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 23, 2014 at 5:57 pm
I vote for normalizing too... then this query becomes trivial. Been down the unnormalized path once before and it was not a place I want to go again. The time you take to normalize with make the rest of this super easy and fast.
July 23, 2014 at 10:31 pm
Many folks have correctly identified that the table should be "normalized". While it may not be possible for you to change the table, do you know how you would normalize the table if you had to? I ask because THAT's the key to this problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2014 at 11:12 pm
What do (Name, Project, Date) represent? Some event? If you explain that part, we can help you sort out the design of your table. (As Jeff mentioned, are you allowed to restructure this table? I hope so, because this is going to be awful to query... and what happens when you add another date?)
Apologies for all the questions, but once you answer, I think figuring out a more flexible structure for your data should be pretty easy.
July 25, 2014 at 8:34 am
True, I cannot change the table .
Name is the name of the person , Project is the projectname and all the weeks represent the hours worked on the project.
July 25, 2014 at 11:01 am
sharonsql2013 (7/25/2014)
True, I cannot change the table .Name is the name of the person , Project is the projectname and all the weeks represent the hours worked on the project.
You haven't answered my previous question. I understand that you can't change the table. Do you understand the normalization process that you'd use if you could? THAT's the key to solving this problem even if it doesn't result in you changing the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2014 at 11:41 pm
What are you using to do your reporting? SSRS?
July 26, 2014 at 2:06 am
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
August 2, 2014 at 3:19 am
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
August 2, 2014 at 3:43 am
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
August 2, 2014 at 11:50 am
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply