May 27, 2022 at 6:25 pm
I have data in table like this.
I want to fill/insert the rows with missing quarters from 2019-Q1 to 2022-Q4 with Sales as 0 for missing quarters and rest of the dimensions as it is.
For example: West has only two rows of quarter 2019-Q1 AND 2020-Q3. I also need other 14 rows of remaining quarters between 2019-Q1 AND 2022-Q4.
Sample fiddle data set: https://www.db-fiddle.com/f/a819dNsKzdk8kXiYjkRNzz/2
Can some please help with sql code to achieve the same ?
May 27, 2022 at 7:23 pm
;WITH cte_distinct_customer_regions AS (
SELECT DISTINCT Customer_Key, Customer_Name, Component, Region
FROM Fill_Gaps
)
SELECT
cdc.Customer_Key, cdc.Customer_Name, cdc.Component, cdc.Region,
q.Quarter, ISNULL(fg.Sales, 0) AS Sales
FROM cte_distinct_customer_regions cdc
CROSS JOIN Quarters q
LEFT OUTER JOIN Fill_Gaps fg ON fg.Customer_Key = cdc.Customer_key AND fg.Region = cdc.Region AND fg.Quarter = q.Quarter
ORDER BY cdc.Customer_Name, q.Quarter
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 28, 2022 at 1:26 am
@ombir ,
I know that the Fill_Gaps table is just a test table here. What value is returned when you run the following code against the real table?
WITH cteUnique AS
(
SELECT DISTINCT Customer_Key,Customer_Name,Component,Region
FROM PutRealTableNameHre --<-----<<<<< LOOK!!! CHANGE THIS BEFORE YOU RUN IT!!!!
)
SELECT COUNT(*)
FROM cteUnique
;
--Jeff Moden
Change is inevitable... Change for the better is not.
May 28, 2022 at 2:50 am
Thanks Scott. I will check this out.
May 28, 2022 at 2:52 am
@Ombir ,
I know that the Fill_Gaps table is just a test table here. What value is returned when you run the following code against the real table?
WITH cteUnique AS
(
SELECT DISTINCT Customer_Key,Customer_Name,Component,Region
FROM PutRealTableNameHre --<-----<<<<< LOOK!!! CHANGE THIS BEFORE YOU RUN IT!!!!
)
SELECT COUNT(*)
FROM cteUnique
;
Thanks Jeff for having a look. The count is 8,518,436.
May 28, 2022 at 4:01 am
Let me give more context on this. I am trying to identify if multiple components have been purchased by customers (Sales>0) in Last 4 quarters and then on basis of components purchased club the customer into 3 groups.
Group 1: Purchased Component 1 and Component 2 (Rolling 4 quarters)
Group 2: Purchased Component 1 and Component 3 (Rolling 4 quarters)
Group 3: Purchased All three components (Rolling 4 quarters)
Basically a customer can fall into Group 1 ,say in 2020-Q3 and then into Group 3 in 2021-Q1 and then into Group 2 in 2022-Q3. The group is decided on rolling sales in last 4 quarters at any point of time. I have generated another data set https://www.db-fiddle.com/f/neBPKhKbTu7cYgUwLf2naa/1 which is sufficient to generate all 3 groups. In my data if there is no sales in a quarter for component then that row isn't available.
Thats why I am trying to fill the missing quarters. May be there is direct solution which don't need to fill the gaps but I am not sure how to do it.
May 30, 2022 at 3:18 am
Let me give more context on this. I am trying to identify if multiple components have been purchased by customers (Sales>0) in Last 4 quarters and then on basis of components purchased club the customer into 3 groups.
Group 1: Purchased Component 1 and Component 2 (Rolling 4 quarters) Group 2: Purchased Component 1 and Component 3 (Rolling 4 quarters) Group 3: Purchased All three components (Rolling 4 quarters)
Basically a customer can fall into Group 1 ,say in 2020-Q3 and then into Group 3 in 2021-Q1 and then into Group 2 in 2022-Q3. The group is decided on rolling sales in last 4 quarters at any point of time. I have generated another data set https://www.db-fiddle.com/f/neBPKhKbTu7cYgUwLf2naa/1 which is sufficient to generate all 3 groups. In my data if there is no sales in a quarter for component then that row isn't available. Thats why I am trying to fill the missing quarters. May be there is direct solution which don't need to fill the gaps but I am not sure how to do it.
"Rolling 4 quarters"... from what date?
For example, always the previous 4 quarters not including the current quarter?
... or ...
From a given completed quarter?
... or ???
And what should the output look like?
AND, are the 3 rules for components 1, 2, and 3 fixed or variable and are there more than 3 components?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2022 at 7:06 am
Ombir wrote:Let me give more context on this. I am trying to identify if multiple components have been purchased by customers (Sales>0) in Last 4 quarters and then on basis of components purchased club the customer into 3 groups.
Group 1: Purchased Component 1 and Component 2 (Rolling 4 quarters) Group 2: Purchased Component 1 and Component 3 (Rolling 4 quarters) Group 3: Purchased All three components (Rolling 4 quarters)
Basically a customer can fall into Group 1 ,say in 2020-Q3 and then into Group 3 in 2021-Q1 and then into Group 2 in 2022-Q3. The group is decided on rolling sales in last 4 quarters at any point of time. I have generated another data set https://www.db-fiddle.com/f/neBPKhKbTu7cYgUwLf2naa/1 which is sufficient to generate all 3 groups. In my data if there is no sales in a quarter for component then that row isn't available. Thats why I am trying to fill the missing quarters. May be there is direct solution which don't need to fill the gaps but I am not sure how to do it.
"Rolling 4 quarters"... from what date?
For example, always the previous 4 quarters not including the current quarter?
... or ...
From a given completed quarter?
... or ???
And what should the output look like?
AND, are the 3 rules for components 1, 2, and 3 fixed or variable and are there more than 3 components?
May 30, 2022 at 1:29 pm
So, just to be sure, "Component N" and "Region" can actually be anything, correct? And the goal is to assign the "N" of "Group N"? Do the requirements of "Group N" change because "Component "N" changes?
For example, for one company, Components 1, 2, and 3 might be Pistons, Sawdust Pumps, and Cow Catchers while Components 1, 2, and 3 might be Seat Covers, Bum Warmers, and Back Scratchers for a different company?
And can the definitions of "Group" change?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2022 at 1:36 pm
So, just to be sure, "Component N" and "Region" can actually be anything, correct? And the goal is to assign the "N" of "Group N"? Do the requirements of "Group N" change because "Component "N" changes?
For example, for one company, Components 1, 2, and 3 might be Pistons, Sawdust Pumps, and Cow Catchers while Components 1, 2, and 3 might be Seat Covers, Bum Warmers, and Back Scratchers for a different company?
And can the definitions of "Group" change?
No. Components will be same (Fixed 3) for every company and definition of group will be also same.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply