April 15, 2008 at 11:42 pm
I have the following data in a Table. Note that this table holds Sales Qty till Today and also Forecast Qty for the next n Months (where n could be between 1 - 15 months)
YYYYMM Stock_Code Whse_code Sales_Qty
----------- ---------------- --------- ------------
200710 009040 B 505
200711 009040 B 370
200712 009040 B 385
200801 009040 B 335
200802 009040 B 305
200803 009040 B 250
200804 009040 B 250
200805 009040 B 250
200806 009040 B 250
200807 009040 B 250
200808 009040 B 250
200809 009040 B 250
200810 009040 B 250
200710 009040 M 549
200711 009040 M 565
200712 009040 M 535
200801 009040 M 685
200802 009040 M 305
200803 009040 M 240
200804 009040 M 240
200805 009040 M 240
200806 009040 M 240
200807 009040 M 240
200808 009040 M 240
200809 009040 M 240
200810 009040 M 240
200811 009040 M 240
200812 009040 M 240
200901 009040 M 240
200902 009040 M 240
200903 009040 M 240
200904 009040 M 240
200905 009040 M 240
200906 009040 M 240
How can I create a Column (6MthQtyToDate) that is the Sum of the Qty of the Last6Mths. I need this Column for each Row of data.
April 16, 2008 at 3:42 pm
What's the difference between the warehouse codes B and M?
April 16, 2008 at 4:26 pm
Those are Warehouse Codes; So I need the 6MthQy Column by Whse_Code.
April 17, 2008 at 2:01 am
Hi, this is my solution, performance problem can occurs but...
create function uf_Period_Previous:
CREATE FUNCTION [dbo].[uf_Period_Previous](
@Period INT)
RETURNS INT
AS
BEGIN
DECLARE @Year INT
DEClARE @Month TINYINT
DEClARE @PrevPeriod INT
SET @Year = @Period / 100
SET @Month = @Period % 100
-- Next Period
IF @Month = 1
BEGIN
SET @Year = @Year - 1
SET @Month = 12
END
ELSE
BEGIN
SET @Month = @Month - 1
END
SET @PrevPeriod = @Year * 100 + @Month
RETURN (@PrevPeriod)
END
---------------------------------------------------------------------------
Create function Get_Last6Months: could be better by using a while loop at the beginning
CREATE FUNCTION [dbo].[Get_Last6Months](
@Period int)
RETURNS int
AS
BEGIN
DECLARE @Res int
declare @PeriodSearch int
set @PeriodSearch = dbo.uf_Period_Previous(@Period)
set @PeriodSearch = dbo.uf_Period_Previous(@PeriodSearch)
set @PeriodSearch = dbo.uf_Period_Previous(@PeriodSearch)
set @PeriodSearch = dbo.uf_Period_Previous(@PeriodSearch)
set @PeriodSearch = dbo.uf_Period_Previous(@PeriodSearch)
set @PeriodSearch = dbo.uf_Period_Previous(@PeriodSearch)
SELECT
@Res = ISNULL(SUM([Sales_Qty]), 0)
FROM
[dbo].[Sales]
WHERE
[YYYYMM] between @PeriodSearch AND @Period
RETURN @Res
END
---------------------------------------------------------------------------
Create computed column 6MthQtyToDate with formula ([dbo].[Get_Last6Months]([YYYYMM]))
April 17, 2008 at 6:58 am
You provided some sample data, how about showing us what you would expect the output of the query based on that data? It would help us in developing possible solutions.
😎
April 17, 2008 at 7:11 am
Try this:
DECLARE @vcStart varchar(6),
@vcEnd varchar(6)
SET @vcStart = CONVERT(char(4), YEAR(DATEADD(month, -6, GETDATE()))) + RIGHT('00' + CONVERT(varchar(2), MONTH(DATEADD(month, -6, GETDATE()))), 2)
SET @vcEnd = CONVERT(char(4), YEAR(GETDATE())) + RIGHT('00' + CONVERT(varchar(2), MONTH(GETDATE())), 2)
SELECT Whse_Code, SUM(Sales_Qty) AS [6MthQtyToDate]
FROM Table
WHERE YYYYMM BETWEEN @vcStart AND @vcEnd
GROUP BY Whse_Code
Dave Novak
April 17, 2008 at 7:49 am
Here's a solution with table and UDF DDL and example. Then you can use the function as a computed column if you wish, or just create a view and use the function there:
---------------------------------------------
CREATE TABLE [dbo].[Sales](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Period] [varchar](6) NOT NULL,
[Item] [varchar](6) NOT NULL,
[WarehouseCode] [char](1) NULL,
[Sales] [money] NOT NULL,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
----------------------------------------------
create function Past6MonthSales
(
@CurrentPeriod varchar(6)
, @Item varchar(6)
, @WarehouseCode char(1)
)
returns money
as
begin
declare @Result money
select @Result = sum(sales)
from Sales
where cast(period + '01' as smalldatetime)
between dateadd(month,-7,cast(@CurrentPeriod + '01' as smalldatetime))
and dateadd(month,-1,cast(@CurrentPeriod + '01' as smalldatetime))
and item = @Item
and warehouseCode = @WarehouseCode
group by item, warehouseCode
return isnull(@Result,0)
end
----------------------------------------------
select period, item, warehouseCode, sales, dbo.Past6MonthSales(period, item, warehouseCode) as past6Months
from Sales
April 17, 2008 at 6:20 pm
Thanks to everyone who put in their methods.
This is what I came up with:
[font="Courier New"]CREATE FUNCTION [dbo].[ufn_GetYYYYMMSixMonthsAgo_YYYYMM]
( @pYYYYMM int )
RETURNS int
AS
BEGIN
Declare @Mth char(2)
Declare @Yr char(4)
Declare @NewYYYYMM char (6)
Declare @New2YYYYMM int
set @NewYYYYMM = Cast(@pYYYYMM as char(6))
set @Mth = substring(@NewYYYYMM, 5, 2)
set @Yr = substring(@NewYYYYMM, 1, 4)
If @Mth = '01'
Set @NewYYYYMM = ((@Yr - 1)* 100) + 7
else if @Mth = '02'
Set @NewYYYYMM = ((@Yr - 1)* 100) + 8
else if @Mth = '03'
Set @NewYYYYMM = ((@Yr - 1)* 100) + 9
else if @Mth = '04'
Set @NewYYYYMM = ((@Yr - 1)* 100) + 10
else if @Mth = '05'
Set @NewYYYYMM = ((@Yr - 1)* 100) + 11
else if @Mth = '06'
Set @NewYYYYMM = ((@Yr - 1)* 100) + 12
Else
Set @NewYYYYMM = (@Yr * 100) + (@Mth - 6)
Return Cast(@NewYYYYMM as int)
END
SELECT
a.YYYYMM, a.Stock_Code, a.Whse_code, a.TotalForecast_Qty as Qty, SUM(b.TotalForecast_Qty) AS Total6MthQty
FROM
dbo.vw_L6MthSQ_CMMaxFCSQ_RestFC AS a INNER JOIN
dbo.vw_L6MthSQ_CMMaxFCSQ_RestFC AS b ON b.Stock_Code = a.Stock_Code
AND b.Whse_code = a.Whse_code
AND b.YYYYMM > dbo.ufn_GetYYYYMMSixMonthsAgo_YYYYMM(a.YYYYMM)
AND b.YYYYMM <= a.YYYYMM
GROUP BY a.YYYYMM, a.Stock_Code, a.Whse_code, a.TotalForecast_Qty
ORDER BY a.YYYYMM, a.Stock_Code[/font]
April 18, 2008 at 1:39 am
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply