July 16, 2014 at 8:56 am
Hello,
I have a requirement to join actual sales data to projected sales data based on periods, quarters and years. Each year has 12 periods (but they do not resemble calendar months)and 4 quarters. I am trying to figure out if the solution might be doing a coalesce or cross tab.
Here is my query so far:
DECLARE @FileId int
SET @FileId=345
SELECT f.Brand, COUNT(recID), p.Period, p.Quarter, p.Year
from forecast_data f LEFT OUTER JOIN Periods p on
(CASE
WHEN ISDATE(f.Store_Opens_Actual) = 1 THEN f.Store_Opens_Actual
WHEN ISDATE(f.Store_Opens_Forecast) = 1 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Forecast
WHEN ISDATE(f.Store_Opens_Baseline) = 1 AND ISDATE(f.Store_Opens_Forecast) = 0 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Baseline
END)>=p.PeriodStarts and
(CASE
WHEN ISDATE(f.Store_Opens_Actual) = 1 THEN f.Store_Opens_Actual
WHEN ISDATE(f.Store_Opens_Forecast) = 1 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Forecast
WHEN ISDATE(f.Store_Opens_Baseline) = 1 AND ISDATE(f.Store_Opens_Forecast) = 0 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Baseline
END)<=p.PeriodEnds
where FileID=@FileID and
f.[Status] <> '12 - Dead Project' AND
f.Relocation <> 'Yes' AND
f.Stoplight_Status <> 'Red' and
p.year='2014'
group by p.Period, p.Quarter, p.Year, f.Brand
order by f.Brand
This is the result that I get (I have simplified this by including only one brand):
BrandCount PeriodQuarterYear
Carvel1212014
Carvel1422014
Carvel1622014
Carvel1732014
Carvel1832014
Carvel2932014
What I need is to have period 1 appear with Count of 0, period 3 to appear with Count of 0 etc. So basically any period that does have any sales still needs to appear, but with zero.
I know this will be probably fairly simple but I have not been able to figure it out.
thanks for the help or advice
July 16, 2014 at 9:09 am
Please post the ddl for the tables 'actual sales data', 'projected sales data' and 'periods', along with INSERT scripts to populate them with data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 16, 2014 at 9:15 am
DDL for forecast date (sales data)
USE [International_Forecast]
GO
/****** Object: Table [dbo].[forecast_data] Script Date: 7/16/2014 11:12:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[forecast_data](
[recID] [bigint] IDENTITY(1,1) NOT NULL,
[FileID] [int] NULL,
[Project_Name] [nvarchar](255) NULL,
[Stoplight_Status] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[Region] [nvarchar](255) NULL,
[Brand] [nvarchar](255) NULL,
[Franchise_Fee] [float] NULL,
[Store_Opens_Baseline] [nvarchar](255) NULL,
[Store_Opens_Forecast] [datetime] NULL,
[Store_Opens_Actual] [datetime] NULL,
[SAR_Accepted] [datetime] NULL,
[Construction_Started] [datetime] NULL,
[Lease_Signed] [datetime] NULL,
[Closed_Date_Baseline] [datetime] NULL,
[Closed_Date_Forecast] [datetime] NULL,
[Closed_Date_Actual] [datetime] NULL,
[Status] [nvarchar](50) NULL,
[Relocation] [nvarchar](50) NULL,
CONSTRAINT [PK_forecast_data] PRIMARY KEY CLUSTERED
(
[recID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
DDL for Periods:
USE [International_Forecast]
GO
/****** Object: Table [dbo].[Periods] Script Date: 7/16/2014 11:13:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Periods](
[ID] [int] NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL
) ON [PRIMARY]
GO
and here is the projections table:
USE [International_Forecast]
GO
/****** Object: Table [dbo].[Projections] Script Date: 7/16/2014 11:14:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Projections](
[Brand] [nvarchar](50) NULL,
[Period] [int] NULL,
[Year] [int] NULL,
[Projection] [int] NULL,
[Type] [nvarchar](50) NULL,
[BrandForecast] [nvarchar](50) NULL
) ON [PRIMARY]
GO
I will get you the insert scripts as soon as I can. thanks for all of your help and time.
Petr
July 16, 2014 at 9:25 am
This is an idea on what you could do. You might want to create a computed column to remove the case from the join clause.
DECLARE @FileId int
SET @FileId=345;
WITH Brands AS(
SELECT DISTINCT Brand --Get all the brands
FROM forecast_data
),
BrandPeriods AS( --Create a cartesian product between brands and periods
SELECT b.Brand,
p.Period,
p.Quarter,
p.Year,
p.PeriodStarts,
p.PeriodEnds
FROM Brands b
CROSS JOIN Periods p
WHERE p.year='2014'
)
SELECT p.Brand,
COUNT(b.recID),
p.Period,
p.Quarter,
p.Year
FROM BrandPeriods p
LEFT OUTER
JOIN forecast_data f ON
(CASE
WHEN ISDATE(f.Store_Opens_Actual) = 1 THEN f.Store_Opens_Actual
WHEN ISDATE(f.Store_Opens_Forecast) = 1 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Forecast
WHEN ISDATE(f.Store_Opens_Baseline) = 1 AND ISDATE(f.Store_Opens_Forecast) = 0 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Baseline
END)>=p.PeriodStarts
AND
(CASE
WHEN ISDATE(f.Store_Opens_Actual) = 1 THEN f.Store_Opens_Actual
WHEN ISDATE(f.Store_Opens_Forecast) = 1 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Forecast
WHEN ISDATE(f.Store_Opens_Baseline) = 1 AND ISDATE(f.Store_Opens_Forecast) = 0 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Baseline
END)<=p.PeriodEnds
AND f.Brand = p.Brand -- use the row with the brand and period
AND FileID = @FileID -- include conditions here to preserve the outer join
AND f.[Status] <> '12 - Dead Project'
AND f.Relocation <> 'Yes'
AND f.Stoplight_Status <> 'Red'
GROUP BY p.Period, p.Quarter, p.Year, p.Brand
ORDER BY p.Brand
July 16, 2014 at 9:45 am
The ISDATE is for NULL checking...
SELECT
f.Brand,
COUNT(recID),
p.Period,
p.Quarter,
p.Year
FROM forecast_data f
LEFT OUTER JOIN Periods p
ON COALESCE(f.Store_Opens_Actual, f.Store_Opens_Forecast, f.Store_Opens_Baseline) BETWEEN p.PeriodStarts AND p.PeriodEnds
--(CASE
--WHEN ISDATE(f.Store_Opens_Actual) = 1 THEN f.Store_Opens_Actual
--WHEN ISDATE(f.Store_Opens_Forecast) = 1 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Forecast
--WHEN ISDATE(f.Store_Opens_Baseline) = 1 AND ISDATE(f.Store_Opens_Forecast) = 0 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Baseline
--END)>=p.PeriodStarts and
--(CASE
--WHEN ISDATE(f.Store_Opens_Actual) = 1 THEN f.Store_Opens_Actual
--WHEN ISDATE(f.Store_Opens_Forecast) = 1 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Forecast
--WHEN ISDATE(f.Store_Opens_Baseline) = 1 AND ISDATE(f.Store_Opens_Forecast) = 0 AND ISDATE(f.Store_Opens_Actual) = 0 THEN f.Store_Opens_Baseline
--END)<=p.PeriodEnds
WHERE FileID = @FileID
AND f.[Status] <> '12 - Dead Project'
AND f.Relocation <> 'Yes'
AND f.Stoplight_Status <> 'Red'
and p.[year] = '2014'
GROUP BY p.Period, p.[Quarter], p.[Year], f.Brand
ORDER BY f.Brand
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 16, 2014 at 9:49 am
ChrisM@Work (7/16/2014)
The ISDATE is for NULL checking...
You're right, I didn't see the DDL before I posted.
However, this puzzles me:
[Store_Opens_Baseline] [nvarchar](255) NULL,
July 16, 2014 at 9:51 am
Luis Cazares (7/16/2014)
ChrisM@Work (7/16/2014)
The ISDATE is for NULL checking...You're right, I didn't see the DDL before I posted.
However, this puzzles me:
[Store_Opens_Baseline] [nvarchar](255) NULL,
Me too, and it will probably necessitate additional processing. We'll know when the data comes in.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 16, 2014 at 10:48 am
Hi there,
appreciate the input so far. The reason why that particular column can be null is because the date might not be available at the time. The open date is determined through a series of case statements. so the logic is basically like this:
1. When the store is open - the open date is the date.
2. when the store is to be opened and it is guaranteed (but still in future) - it is the store_opens_forecast
3. when the store is in planning stages, the open date is the store_open_baseline which is typically 180 days from signed contract.
I know that the logic is not the easiest to follow, I had trouble figuring out how to code that too.
I will get you the data shortly,
thanks,
Petr
July 16, 2014 at 11:19 am
here is the test data for projections:
USE [International_Forecast]
GO
INSERT INTO [dbo].[Projections]
([Brand]
,[Period]
,[Year]
,[Projection]
,[Type]
,[BrandForecast]) VALUES
('Carvel','1','2014','0','Openings','Carvel'),
('Carvel','2','2014','1','Openings','Carvel'),
('Carvel','3','2014','0','Openings','Carvel'),
('Carvel','4','2014','1','Openings','Carvel'),
('Carvel','5','2014','2','Openings','Carvel'),
('Carvel','6','2014','2','Openings','Carvel'),
('Carvel','7','2014','2','Openings','Carvel'),
('Carvel','8','2014','2','Openings','Carvel'),
('Carvel','9','2014','4','Openings','Carvel'),
('Carvel','10','2014','2','Openings','Carvel'),
('Carvel','11','2014','1','Openings','Carvel'),
('Carvel','12','2014','3','Openings','Carvel')
and here is the data for period:
USE [International_Forecast]
GO
INSERT INTO [dbo].[Periods]
([ID]
,[Period]
,[Quarter]
,[Year]
,[PeriodStarts]
,[PeriodEnds])
VALUES
(1,1,1,2014,'2013-12-30','2014-01-26'),
(2,2,1,2014,'2014-01-27','2014-02-23'),
(3,3,1,2014,'2014-02-24','2014-03-30'),
(4,4,2,2014,'2014-03-31','2014-04-27'),
(5,5,2,2014,'2014-04-28','2014-05-25'),
(6,6,2,2014,'2014-05-26','2014-06-29'),
(7,7,3,2014,'2014-06-30','2014-07-27'),
(8,8,3,2014,'2014-07-28','2014-08-24'),
(9,9,3,2014,'2014-08-25','2014-09-28'),
(10,10,4,2014,'2014-09-29','2014-10-26'),
(11,11,4,2014,'2014-10-27','2014-11-23'),
(12,12,4,2014,'2014-11-24','2014-12-28')
July 16, 2014 at 11:27 am
vecerda (7/16/2014)
I get this error:Msg 4104, Level 16, State 1, Line 20
The multi-part identifier "b.recID" could not be bound.
not sure what I am doing wrong as the alias is clearly there???
Please, don't send PMs. It will only slow down the responses. 😉
The problem is that b isn't used as an alias. You need to change it to f or change the alias for forecast_data.
The dates logic isn't the problem. The definition of the date as nvarchar(255) is what seems incorrect.
July 16, 2014 at 11:33 am
Got it and I am sorry. I did not realize that.
The data comes over from a flat file import so I can convert the nvarchar to datetime. That does not really create a problem though (I know it is bad design) - it was done way before I came here.
thanks,
Petr
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply