Alexander Suprun (9/8/2014)
navie22 (9/5/2014)
Get date in where condition will also change with each moth likeAND 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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question