January 7, 2014 at 9:45 am
I have another issue (I feel like I am taking this forum over) and need help.
I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).
The structure of the periods table is as follows:
[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL
The stored procedure is currently:
USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Petr Caslavka>
-- Create date: <01/07/2014>
-- Description:<SP to pull data for international openings report>
-- =============================================
ALTER PROCEDURE [dbo].[GetOpenResult]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PeriodStart DATETIME
DECLARE @PeriodEnd DATETIME
DECLARE @YTDCount INT
SET @PeriodStart = (SELECT PeriodStarts FROM Periods WHERE Period = 1 AND [Year] = 2014)
SET @PeriodEnd = (SELECT PeriodEnds FROM Periods WHERE Period = 12 AND [Year] = 2014)
SET @YTDCount = (SELECT Count(Store_Opens_Actual) FROM forecast_data WHERE [Status] <> '12 - Dead Project' AND Relocation <> 'Yes' AND Stoplight_Status <> 'Red' AND Store_Opens_Actual BETWEEN @PeriodStart AND GETDATE())
SELECT
d.Project_Name,
d.[Status],
d.[Relocation],
d.Stoplight_Status,
d.Country,
d.Region,
d.Brand,
d.Franchise_Fee,
CONVERT(VARCHAR(10),
CASE
WHEN ISDATE(d.Store_Opens_Actual) = 1 THEN d.Store_Opens_Actual
WHEN ISDATE(d.Store_Opens_Forecast) = 1 AND ISDATE(d.Store_Opens_Actual) = 0 THEN d.Store_Opens_Forecast
WHEN ISDATE(d.Store_Opens_Baseline) = 1 AND ISDATE(d.Store_Opens_Forecast) = 0 AND ISDATE(d.Store_Opens_Actual) = 0 THEN d.Store_Opens_Baseline
END
,101) AS "Store_Open",
CASE WHEN ISDATE(d.SAR_Accepted) = 1 THEN 'YES' ELSE 'NO' END AS "SAR_Accepted",
CASE WHEN ISDATE(d.Construction_Started) = 1 THEN 'YES' ELSE 'NO' END AS "Const_Started",
CASE WHEN ISDATE(d.Lease_Signed) = 1 THEN 'YES' ELSE 'NO' END AS "Lease_Signed",
CASE WHEN ISDATE(d.Closed_Date_Actual) = 1 THEN 'YES' ELSE 'NO' END AS "Closed",
CONVERT(VARCHAR(10),
CASE
WHEN ISDATE(d.Closed_Date_Actual) = 1 THEN d.Closed_Date_Actual
WHEN ISDATE(d.Closed_Date_Forecast) = 1 AND ISDATE(d.Closed_Date_Actual) = 0 THEN d.Closed_Date_Forecast
--WHEN ISDATE(d.Closed_Date_Baseline) = 1 AND ISDATE(d.Closed_Date_Forecast) = 0 AND ISDATE(d.Closed_Date_Actual) = 0 THEN d.Closed_Date_Baseline
END
,101) AS "Store_Closed",
CASE WHEN ISDATE(d.Store_Opens_Actual) = 1 THEN 'Y' ELSE 'N' END AS "Open_Flag"
FROM
forecast_data d
WHERE
d.[Status] <> '12 - Dead Project' AND
d.Relocation <> 'Yes' AND
d.Stoplight_Status <> 'Red' AND
(
SELECT
CASE
WHEN ISDATE(fd.Store_Opens_Actual) = 1 THEN fd.Store_Opens_Actual
WHEN ISDATE(fd.Store_Opens_Forecast) = 1 AND ISDATE(fd.Store_Opens_Actual) = 0 THEN fd.Store_Opens_Forecast
WHEN ISDATE(fd.Store_Opens_Baseline) = 1 AND ISDATE(fd.Store_Opens_Forecast) = 0 AND ISDATE(fd.Store_Opens_Actual) = 0 THEN fd.Store_Opens_Baseline
END
FROM
forecast_data fd
WHERE
fd.recID = d.recID) BETWEEN @PeriodStart AND @PeriodEnd
ORDER BY
"Store_Open"
-------------------------------------------
SELECT @YTDCount AS "YTDCount"
-------------------------------------------
SELECT
brand
,count(*) AS "Count"
FROM
forecast_data d
WHERE
Project_Name like '%000%'
AND Project_Name NOT LIKE '%TBD%'
AND LEFT([Status],2) <> '12'
AND SAR_Accepted IS NULL
AND Relocation <> 'Yes'
AND Stoplight_Status <> 'Red'
AND
(
SELECT
CASE
WHEN ISDATE(fd.Store_Opens_Actual) = 1 THEN fd.Store_Opens_Actual
WHEN ISDATE(fd.Store_Opens_Forecast) = 1 AND ISDATE(fd.Store_Opens_Actual) = 0 THEN fd.Store_Opens_Forecast
WHEN ISDATE(fd.Store_Opens_Baseline) = 1 AND ISDATE(fd.Store_Opens_Forecast) = 0 AND ISDATE(fd.Store_Opens_Actual) = 0 THEN fd.Store_Opens_Baseline
END
FROM
forecast_data fd
WHERE
fd.recID = d.recID) BETWEEN @PeriodStart AND @PeriodEnd
GROUP BY
Brand
END
What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.
For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23
So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.
I hope I am explaining this in a easy to follow manner.
thanks for help,
Petr
January 13, 2014 at 10:42 am
Hi,
Hopefully the below solves your problem. Without any dummy data this took me about half an hour to put together. To make things easier in future can you put together some dummy data so we can help as quick as possible? Check the link in my signature for a quick guide on how to get the best help.
You could do what you need quite simply in Reporting services by just adding the fields together in a calculated field in your dataset as well.
--Declare table variables
DECLARE @forecast TABLE
(
StoreId INT,
Store_Open DATETIME
)
DECLARE @periods TABLE
(
ID INT,
Period INT,
[Quarter] INT,
[Year] INT,
PeriodStarts DATETIME,
PeriodEnds DATETIME
)
--Insert some dummy data
INSERT @forecast
SELECT 3, DATEADD(DAY,-10,GETDATE())
UNION ALL
SELECT 2, DATEADD(DAY,-1,GETDATE())
UNION ALL
SELECT 1, DATEADD(DAY,+5,GETDATE())
INSERT @periods
SELECT 1,1,1,2013,'20140101','20140107'
UNION ALL
SELECT 2,2,1,2013,'20140108','20140114'
UNION ALL
SELECT 3,3,1,2013,'20140115','20140121'
--Get the results of the query.
--You need to cast the integers to varchars to get the results you require.
SELECT f.StoreId, f.Store_Open, 'Period ' + CAST(p.Period AS VARCHAR(3)) + ', Quarter' + CAST(p.[Quarter] AS VARCHAR(3)) + ', Year ' + CAST(p.[Year] AS VARCHAR(4)) [Period]
FROM @forecast f
INNER JOIN @periods p ON f.Store_Open >= p.PeriodStarts
AND f.Store_Open < p.PeriodEnds
Cheers,
Jim.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply