Get next 2 weeks

  • 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 ..

  • You have 2 options:

    - Normalize your table

    - Use dynamic SQL

    You decide.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

  • Yes , its for reporting

  • 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

  • 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.

  • @sharonsql2013,

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • What are you using to do your reporting? SSRS?

  • 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

  • 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

  • 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

  • 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