TSQL Help
-
Hello,
I was tasked with deploying a solution to monitor "Daily Activity Frequency" in Sales Person, Region and Team level. I came up with the following structure.
What I need to do is to consolidate the data to display the Daily Activity Frequencies of Sales Persons, Districts and Teams in Monthly, Quarterly, YTD and MAT level. I'm not sure if the following TimeDimension works well to pull off this task.
The output that I'm trying to yield is as follows; // TimeMetricID refers Month, Quarter, YTD and MAT.
Actually, I was wondering if I could come up with a solution that I can eliminate User ID, District ID and Team ID columns, so that I can store all the information in one column with unique Item ID's, but honestly I couldn't make it work. Below output works well as I can calculate District and Team results from there, but it is worth to keep it compact.
Please find the sample data below;
It would be deeply appreciated if you can help me out with a coded answer.
CREATE TABLE [dbo].[TimeDim](
[TimeDimensionID] [int] NOT NULL,
[Year] [int] NOT NULL,
[Month] [int] NOT NULL,
[Quarter] [int] NOT NULL,
[Half] [int] NOT NULL,
[YTD] [int] NOT NULL,
[MAT] [int] NULL,
[MonthEN] [nvarchar](20) NOT NULL,
[YearMonth] [int] NULL,
[MonthENShort] [varchar](10) NULL,
[QuarterShort] [varchar](2) NULL,
[HalfShort] [varchar](2) NULL,
CONSTRAINT [PK_IMSTimeDimension] PRIMARY KEY CLUSTERED
(
[TimeDimensionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GOINSERT INTO [TimeDim] (TimeDimensionID, Year, Month, Quarter, Half, YTD, MAT, MonthEN, YearMonth, MonthENShort, QuarterShort, HalfShort)
VALUES (1, 2016, 1, 1, 1, 1, NULL, 'January', 201601, 'Jan', 'Q1', 'H1'),
(2, 2016, 2, 1, 1, 1, NULL, 'February', 201602, 'Feb', 'Q1', 'H1'),
(3, 2016, 3, 1, 1, 1, NULL, 'March', 201603, 'Mar', 'Q1', 'H1'),
(4, 2016, 4, 2, 1, 1, NULL, 'April', 201604, 'Apr', 'Q2', 'H1'),
(5, 2016, 5, 2, 1, 1, NULL, 'May', 201605, 'May', 'Q2', 'H1'),
(6, 2016, 6, 2, 1, 1, NULL, 'June', 201606, 'Jun', 'Q2', 'H1'),
(7, 2016, 7, 3, 2, 1, NULL, 'July', 201607, 'Jul', 'Q3', 'H2'),
(8, 2016, 8, 3, 2, 1, NULL, 'August', 201608, 'Aug', 'Q3', 'H2'),
(9, 2016, 9, 3, 2, 1, NULL, 'September', 201609, 'Sep', 'Q3', 'H2'),
(10, 2016, 10, 4, 2, 1, NULL, 'October', 201610, 'Oct', 'Q4', 'H2'),
(11, 2016, 11, 4, 2, 1, NULL, 'November', 201611, 'Nov', 'Q4', 'H2'),
(12, 2016, 12, 4, 2, 1, 1, 'December', 201612, 'Dec', 'Q4', 'H2'),
(13, 2017, 1, 1, 1, 13, 2, 'January', 201701, 'Jan', 'Q1', 'H1'),
(14, 2017, 2, 1, 1, 13, 3, 'February', 201702, 'Feb', 'Q1', 'H1'),
(15, 2017, 3, 1, 1, 13, 4, 'March', 201703, 'Mar', 'Q1', 'H1'),
(16, 2017, 4, 2, 1, 13, 5, 'April', 201704, 'Apr', 'Q2', 'H1'),
(17, 2017, 5, 2, 1, 13, 6, 'May', 201705, 'May', 'Q2', 'H1'),
(18, 2017, 6, 2, 1, 13, 7, 'June', 201706, 'Jun', 'Q2', 'H1'),
(19, 2017, 7, 3, 2, 13, 8, 'July', 201707, 'Jul', 'Q3', 'H2'),
(20, 2017, 8, 3, 2, 13, 9, 'August', 201708, 'Aug', 'Q3', 'H2'),
(21, 2017, 9, 3, 2, 13, 10, 'September', 201709, 'Sep', 'Q3', 'H2'),
(22, 2017, 10, 4, 2, 13, 11, 'October', 201710, 'Oct', 'Q4', 'H2'),
(23, 2017, 11, 4, 2, 13, 12, 'November', 201711, 'Nov', 'Q4', 'H2'),
(24, 2017, 12, 4, 2, 13, 13, 'December', 201712, 'Dec', 'Q4', 'H2')CREATE TABLE [dbo].[Workdays](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [nvarchar](12) NOT NULL,
[TimeID] [int] NOT NULL,
[WorkDays] [float] NOT NULL,
[ActivityTypeID] [int] NOT NULL
) ON [PRIMARY]
GOINSERT INTO [Workdays] (UserID, TimeID, WorkDays, ActivityTypeID)
VALUES
('USER-UT12', 19, 12, 1),
('USER-UT12', 19, 12, 2),
('USER-BG14', 20, 17, 1),
('USER-BG14', 20, 17, 2),
('USER-NG16', 21, 19, 1),
('USER-NG16', 21, 19, 2),
('USER-3NJL', 22, 18, 1),
('USER-3NJL', 22, 18, 2),
('USER-11B6', 23, 17, 1),
('USER-11B6', 23, 17, 2),
('USER-C00C', 23, 18, 1),
('USER-C00C', 23, 18, 2),
('USER-11B4', 24, 19.5, 1),
('USER-11B4', 24, 19.5, 2),
('USER-11F7', 24, 20, 1),
('USER-11F7', 24, 20, 2)CREATE TABLE [dbo].[Activities](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [nvarchar](12) NOT NULL,
[TimeID] [int] NOT NULL,
[CallCount] [int] NOT NULL,
[ActivityTypeID] [int] NOT NULL,
[ProductID] [int] NULL
) ON [PRIMARY]
GOINSERT INTO [Activities] (UserID, TimeID, CallCount, ActivityTypeID, ProductID)
VALUES
('USER-UT12', 19, 50, 1, 1),
('USER-UT12', 19, 10, 2, 1),
('USER-BG14', 20, 175, 1, 1),
('USER-BG14', 20, 25, 2, 1),
('USER-NG16', 21, 125, 1, 1),
('USER-NG16', 21, 15, 2, 1),
('USER-3NJL', 22, 105, 1, 1),
('USER-3NJL', 22, 35, 2, 1),
('USER-11B6', 23, 120, 1, 1),
('USER-11B6', 23, 50, 2, 1),
('USER-C00C', 23, 105, 1, 1),
('USER-C00C', 23, 25, 2, 1),
('USER-11B4', 24, 175, 1, 1),
('USER-11B4', 24, 55, 2, 1),
('USER-11F7', 24, 135, 1, 1),
('USER-11F7', 24, 15, 2, 1);CREATE TABLE [dbo].[MonthlyProducts](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](50) NOT NULL,
[IsDeleted] [bit] NOT NULL,
CONSTRAINT [PK_MonthlyProducts] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GOINSERT INTO [MonthlyProducts] (ProductName, IsDeleted)
VALUES ('All', 0),
('Product_1', 0),
('Product_2', 0),
('Product_3', 0),
('Product_4', 0),
('Product_5', 0)CREATE TABLE [dbo].[ActivityTypes](
[ActivityTypeID] [int] NOT NULL,
[ActivityTypes] [varchar](50) NULL,
CONSTRAINT [PK_ActivityTypes] PRIMARY KEY CLUSTERED
(
[ActivityTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GOINSERT INTO [ActivityTypes] (ActivityTypeID, ActivityTypes)
VALUES (1, 'APType1'),
(2, 'APType2')CREATE TABLE [dbo].[User](
[ID] [nvarchar](255) NULL,
[UserName] [nvarchar](255) NULL,
[TeamID] [int] NULL,
[District] [nvarchar](255) NULL
) ON [PRIMARY]
GOINSERT INTO [User] (ID, UserName, TeamID, District)
VALUES ('USER-UT12', 'UserUT12', 1, 'District1'),
('USER-BG14', 'UserBG14', 1, 'District2'),
('USER-NG16', 'UserNG16', 2, 'District1'),
('USER-3NJL', 'User3NJL', 2, 'District2'),
('USER-11B6', 'User11B6', 3, 'District1'),
('USER-C00C', 'UserC00C', 3, 'District2'),
('USER-11B4', 'User11B4', 4, 'District3'),
('USER-11F7', 'User11F7', 5, 'District2')CREATE TABLE [dbo].[Team](
[TeamID] [int] NOT NULL,
[TeamName] [nvarchar](50) NOT NULL
CONSTRAINT [PK_Teams] PRIMARY KEY CLUSTERED
(
[TeamID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GOINSERT INTO [Team] (TeamID, TeamName)
VALUES (1, 'Team1'),
(2, 'Team2'),
(3, 'Team3'),
(4, 'Team4'),
(5, 'Team5')CREATE TABLE [dbo].[District](
[DistrictID] [int] NOT NULL,
[DistrictName] [varchar](20) NOT NULL,
[TeamID] [int] NOT NULL,
CONSTRAINT [PK_Districts] PRIMARY KEY CLUSTERED
(
[DistrictID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GOINSERT INTO [District] (DistrictID, DistrictName, TeamID)
VALUES (1, 'District1', 1),
(2, 'District2', 1),
(3, 'District1', 2),
(4, 'District2', 2),
(5, 'District1', 3),
(6, 'District2', 3),
(7, 'District3', 4),
(8, 'District2', 5)CREATE TABLE [dbo].[TimeMetricTypes] (
[TimeMetricID] [int] NOT NULL,
[TimeMetricName] [varchar](20) NOT NULL,
);INSERT INTO [TimeMetricTypes] (TimeMetricID, TimeMetricName)
VALUES (1, 'Month'),
(2, 'YTD'),
(3, 'Quarter'),
(4, 'MAT')Thanks
-
It would be good to see what you've tried here, and also, what are you calling frequency here? What is the math you're using.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply