• Alexander Suprun (9/8/2014)


    navie22 (9/5/2014)


    Get date in where condition will also change with each moth like

    AND MT.TRANSACTIONDATE BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-2, 0) --First day 2 months ago

    AND DATEADD(MILLISECOND, - 3, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-2, -1)+1) --Last day 2 months ago

    thanks,

    If you really want to recalculate values for 13 months every time and you are not concerned about performance at all then create a 13 records table with numbers 1,2,3...13 and CROSS JOIN it with your query.

    I would suggest something similar with either a numbers/tally table or a calendar table, if you are stuck with this design. Something like this (which won't work, but should get you in the right direction I think):

    /* Create a virtual numbers table based on Itzik Ben Gan's SQL Server Pro article

    http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers */

    WITH L0

    AS (

    SELECT

    1 AS c

    UNION ALL

    SELECT

    1

    ),

    L1

    AS (

    SELECT

    1 AS c

    FROM

    L0 AS A

    CROSS JOIN L0 AS B

    ),

    L2

    AS (

    SELECT

    1 AS c

    FROM

    L1 AS A

    CROSS JOIN L1 AS B

    ),

    L3

    AS (

    SELECT

    1 AS c

    FROM

    L2 AS A

    CROSS JOIN L2 AS B

    ),

    L4

    AS (

    SELECT

    1 AS c

    FROM

    L3 AS A

    CROSS JOIN L3 AS B

    ),

    L5

    AS (

    SELECT

    1 AS c

    FROM

    L4 AS A

    CROSS JOIN L4 AS B

    ),

    Nums

    AS (

    SELECT

    ROW_NUMBER() OVER (ORDER BY (

    SELECT

    NULL

    )) AS n

    FROM

    L5

    ),

    /* this is a virtual calendar table note the top 13 just to get the last 12 months, not including this month right now,

    could be modified to include the current month */

    calendar

    AS (

    SELECT TOP 13

    n,

    DATEADD(MONTH,

    DATEDIFF(MONTH, 0, DATEADD(MONTH, -n, GETDATE())), 0) AS startDate,

    DATEADD(MONTH,

    DATEDIFF(MONTH, 0, DATEADD(MONTH, -(n - 1), GETDATE())),

    0) AS endDate

    FROM

    Nums

    ),

    hitsbyMonth

    AS (

    SELECT

    C.n AS monthsAgo,

    MTT.DFN003,

    COUNT(ISNULL(CASE WHEN TT.DESC001 = 'Cycle Count Adjust'

    THEN CASE WHEN SIGN(MTT.QTYTRANSACTION) = -1

    THEN MT.TRANSACTIONID

    ELSE 0

    END

    WHEN TT.DESC001 = 'Physical Inv Adjust'

    THEN CASE WHEN SIGN(MTT.QTYTRANSACTION) = -1

    THEN MT.TRANSACTIONID

    ELSE 0

    END

    ELSE MT.TRANSACTIONID

    END, 0)) HITS

    FROM

    [TEST1].[dbo].[IN_STAR_FACTSDETAILS] MTT

    INNER JOIN [TEST1].[dbo].[IN_STAR_FACTSDETEXT_EBS] MT

    ON (MT.RDFACTSEXTNUMID = MTT.RDFACTSEXTNUMID)

    INNER JOIN TEST1.[dbo].[IN_STAR_INVENTORYTRANSTYPE] TT

    ON (TT.RDINVTRANSACTIONTYPENUMID = MTT.DFN005)

    INNER JOIN calendar AS C

    ON MT.TransactionDate >= C.startDate AND

    MT.TransactionDate < C.endDate

    WHERE

    MT.rdsourcenumid = 40002 AND

    TT.DESC001 IN ('Account issue', 'Sales order issue',

    'Internal order issue', 'WIP component issue',

    'Direct Org Transfer', 'Int Order Direct Ship',

    'Intransit Shipment', 'Int Order Intr Ship',

    'Move Order Issue', 'Logical Sales Order Issue',

    'Cycle Count Adjust', 'Physical Inv Adjust')

    GROUP BY

    MTT.DFN003

    )

    UPDATE

    TEST.[dbo].[ITEM]

    SET

    [dbo].[ITEM].HITS01MONTHAGO = CASE WHEN hitsbyMonth.monthsAgo = 1 THEN hitsByMonth.Hits ELSE [dbo].[ITEM].HITS01MONTHAGO END

    ...

    [dbo].[ITEM].HITS13MONTHAGO = CASE WHEN hitsbyMonth.monthsAgo = 13 THEN hitsByMonth.Hits ELSE [dbo].[ITEM].HITS01MONTHAGO END

    FROM

    hitsByMonth

    WHERE

    TEST.[dbo].[ITEM].RDITEMNUMID = hitsbyMonth.DFN003;

    My preference would be to normalize the data and store the hits in a separate table fact type table that links back to the item table. Something like:

    CREATE TABLE historical_hits

    (

    RDITEMNUMID DECIMAL(28,0) CONSTRAINT FK_historical_hits_item FOREIGN KEY REFERENCES item(RDITEMNUMID),

    theYear SMALLINT,

    theMonth TINYINT,

    startDate DATE,

    endDate DATE,

    hits DECIMAL(28,0)

    )

    Then to get the previous 13 months you could do something like this:

    SELECT

    *

    FROM

    item AS I

    JOIN dbo.historical_hits AS HH

    ON I.RDITEMNUMID = HH.RDITEMNUMID AND

    DATEDIFF(MONTH, HH.startDate, GETDATE()) <= 13;

    You could use PIVOT to get the data as columns in one row instead of 13 rows.