September 5, 2014 at 11:05 am
I have 13 columns based on each month like 01MONTHAGO,02MONTHAGO,03MONTHAGO,...13MONTHAGO. I need to update them by each month. my sp for one month works fine. how I can make it to run same to update each month.
USE [TEST]
GO
/****** Object: StoredProcedure [dbo].[spITEM_UPDATE_01MONTHAGO] Script Date: 9/5/2014 9:50:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spITEM_UPDATE_01MONTHAGO]
as
UPDATE TEST.[dbo].[ITEM]
SET TEST.[dbo].[ITEM].01MONTHAGO = sub.HITS
FROM (
SELECT 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)
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'
)
AND MT.TRANSACTIONDATE BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) --First day 1 months ago
AND DATEADD(MILLISECOND, - 3, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)+1) --Last day 1 months ago
GROUP BY MTT.DFN003
) sub
WHERE TEST.[dbo].[ITEM].RDITEMNUMID = sub.DFN003
thanks,
September 5, 2014 at 12:20 pm
Is there a reason you need your data denormalized like this? Would seem easier if you just calculate xMonths ago as needed.
You could just add some additional columns to your update statement. Make sure this is the same statement or it will no do what you want.
, 02MONTHAGO = 01MONTHAGO
, 03MONTHAGO = 02MONTHAGO
, 04MONTHAGO = 03MONTHAGO
, 05MONTHAGO = 04MONTHAGO
...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 5, 2014 at 1:03 pm
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,
September 5, 2014 at 1:07 pm
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,
You have me at an extreme disadvantage here. I can't see you screen, I have no idea what your tables are like and I have no idea what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 5, 2014 at 1:24 pm
here is table structure:
dbo.test.item([RDITEMNUMID] [decimal](28, 0) NOT NULL,
[RDITEMCHARID] [varchar](50) NOT NULL,
[RDSOURCENUMID] [int] NOT NULL,
[RDSOURCETBLID] [int] NOT NULL,
[ITEMNUMBER] [varchar](50) NULL,
[ITEMNAME] [varchar](50) NULL,
[ITEMCOMMODITYCODE] [varchar](50) NULL,
[ITEMCOMMODITYNAME] [varchar](50) NULL,
[ITEMCOMPANY] [varchar](50) NULL,
[HITS_YEAR3] [decimal](28, 0) NULL,
[HITS_YEAR2] [decimal](28, 0) NULL,
[HITS_YEAR1] [decimal](28, 0) NULL,
[HITS_YTD] [decimal](28, 0) NULL,
[HITS_13MONTHAGO] [decimal](28, 0) NULL,
[HITS_12MONTHAGO] [decimal](28, 0) NULL,
[HITS_11MONTHAGO] [decimal](28, 0) NULL,
[HITS_10MONTHAGO] [decimal](28, 0) NULL,
[HITS_09MONTHAGO] [decimal](28, 0) NULL,
[HITS_08MONTHAGO] [decimal](28, 0) NULL,
[HITS_07MONTHAGO] [decimal](28, 0) NULL,
[HITS_06MONTHAGO] [decimal](28, 0) NULL,
[HITS_05MONTHAGO] [decimal](28, 0) NULL,
[HITS_04MONTHAGO] [decimal](28, 0) NULL,
[HITS_03MONTHAGO] [decimal](28, 0) NULL,
[HITS_02MONTHAGO] [decimal](28, 0) NULL,
[HITS_01MONTHAGO] [decimal](28, 0) NULL,
[HITS_CURMONTH] [decimal](28, 0) NULL)
update statement for 01month:
UPDATE TEST.[dbo].[ITEM]
SET TEST.[dbo].[ITEM].01MONTHAGO = sub.HITS
FROM (
SELECT 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)
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'
)
AND MT.TRANSACTIONDATE BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) --First day 1 months ago
AND DATEADD(MILLISECOND, - 3, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)+1) --Last day 1 months ago
GROUP BY MTT.DFN003
) sub
WHERE TEST.[dbo].[ITEM].RDITEMNUMID = sub.DFN003
update for 08MONTH:
UPDATE TEST.[dbo].[ITEM]
SET TEST.[dbo].[ITEM].01MONTHAGO = sub.HITS
FROM (
SELECT 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)
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'
)
AND MT.TRANSACTIONDATE BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-8, 0) --First day 8 months ago
AND DATEADD(MILLISECOND, - 3, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-8, -1)+1) --Last day 8 months ago
GROUP BY MTT.DFN003
) sub
WHERE TEST.[dbo].[ITEM].RDITEMNUMID = sub.DFN003.
in this update statements, only column name change and in where condition month NUM change.
thanks,
September 8, 2014 at 10:31 am
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.
September 8, 2014 at 11:36 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply