Make last Week to zero
-
Thanks Phil, I believe this one is coming close, But what will happen if i have to two more columns DepartmentID and EmployeeID to my #table data.
FROM Create Table #Table (Data char(10), Week int, sales int)
TO Create Table #Table (EmployeeID int, DepartmentID int, Data char(10), Week int, sales int)
What changes need to be done here in the below code.
WITH BaseData
AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
,Week = rt.yearwk
,Sales = ISNULL(t.sales, 0)
,YearMnt = rt.yearmnt
,PrevYearMnt = LAG(rt.yearmnt, 1) OVER (ORDER BY rt.yearwk)
FROM #RefTable rt
LEFT JOIN #Table t
ON rt.yearwk = t.Week)
SELECT bd.Data
,bd.Week
,bd.Sales
FROM BaseData bd
WHERE bd.Data IS NOT NULL
AND
(
bd.YearMnt = bd.PrevYearMnt
OR bd.Sales <> 0
)
AND NOT EXISTS
(
SELECT 1
FROM #Table t
WHERE t.Data = bd.Data
AND t.Week > bd.Week
AND bd.Sales = 0
)
ORDER BY bd.Week
,bd.Data; -
That looks trivial and is something which I suggest you attempt yourself.
Just add the columns to the BaseData CTE and then to the SELECT ... FROM BaseData.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Here is what i see when i add the new columns to select statement which is not having value for 1234,A with Zero.
Drop table if exists #RefTable
Create Table #RefTable ( yearwk int,yearmnt int)
Insert into #RefTable
Values
(202201,202201),
(202202,202201),
(202203,202201),
(202204,202201),
(202205,202202),
(202206,202202),
(202207,202202),
(202208,202202),
(202209,202203),
(202210,202203),
(202211,202203),
(202212,202203),
(202213,202203),
(202214,202204),
(202215,202204),
(202216,202204),
(202217,202204),
(202218,202205),
(202219,202205)
--Select * from #RefTable
Drop Table if exists #Table
Create Table #Table (Empid int, Data char(10), Week int, sales int)
Insert into #Table
SELECT 1234,'A',202201,10
UNION SELECT 123, 'A',202202,20
UNION SELECT 123,'A',202203,30
UNION SELECT 123,'A',202204,40
UNION SELECT 1234,'B',202201,10
UNION SELECT 123, 'B',202202,20
UNION SELECT 123,'B',202203,30
UNION SELECT 123,'B',202204,40
UNION SELECT 124,'B',202205,20
UNION SELECT 124,'B',202206,40
UNION SELECT 1234,'B',202207,60
UNION SELECT 111,'C',202209,20
UNION SELECT 111,'C',202210,30
UNION SELECT 112,'C',202211,30
UNION SELECT 111,'C',202212,30
UNION SELECT 123,'C',202213,30
UNION SELECT 111,'C',202217,30
--Select Top 2 * from #RefTable
--Select Top 2 * from #Table
;WITH BaseData
AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
,Empid = ISNULL(t.Empid, LAG(t.Empid, 1, NULL) OVER (ORDER BY rt.yearwk))
,Week = rt.yearwk
,Sales = ISNULL(t.sales, 0)
,YearMnt = rt.yearmnt
,PrevYearMnt = LAG(rt.yearmnt, 1) OVER (ORDER BY rt.yearwk)
FROM #RefTable rt
LEFT JOIN #Table t
ON rt.yearwk = t.Week)
SELECT bd.Empid,
bd.Data
,bd.Week
,bd.Sales
FROM BaseData bd
WHERE bd.Data IS NOT NULL
AND
(
bd.YearMnt = bd.PrevYearMnt
OR bd.Sales <> 0
)
AND NOT EXISTS
(
SELECT 1
FROM #Table t
WHERE t.Data = bd.Data
and bd.Empid=t.Empid
AND t.Week > bd.Week
AND bd.Sales = 0
)
And bd.Empid=1234
ORDER BY bd.Week
,bd.Data; -
Are you saying that the line in blue is incorrect?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I am not getting the blue lines.. that is what i was expecting vs what has i received.
what i received is on left vs what it was suppose to be on right.
-
OK, I see the problem. The query logic is such that the generation of a notional Sales = 0 row depends on column 'Data', not on EmpId.
In the case where Data = 'A', there are entries for the entire YearMonth 202201, so no new row is generated (your row in blue).
If you want your query to generate new rows depending on EmpId rather than Data, the query must be adjusted.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I am stuck here with this now.. As i need to add few more columns like EmpID, DepID. And hence it is failing..
I tried different partition by in the order , but it is still failing..
;WITH BaseData
AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY t.Data,t.empid,rt.yearwk)) ,
Empid = ISNULL(t.Empid, LAG(t.Empid, 1, NULL) OVER (ORDER BY t.Data,t.empid,rt.yearwk))
,Week = rt.yearwk
,Sales = ISNULL(t.sales, 0)
,YearMnt = rt.yearmnt
,PrevYearMnt = LAG(rt.yearmnt, 1) OVER (ORDER BY t.Data,t.empid, rt.yearwk)
FROM #RefTable rt
LEFT JOIN #Table t
ON rt.yearwk = t.Week) -
Which column, or columns, of data should be considered when generating the Sales = 0 rows?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
As per the above example Combinations of Data, Empid ,sales by week.
-
That made it more complicated than I had expected and I had to adopt a different method, unfortunately containing cascading CTEs. Does this get you close?
WITH BaseData1
AS (SELECT c1.Empid
,c1.Data
,rt.yearwk
,rt.yearmnt
,Sales = SUM(IIF(rt.yearwk = c1.Week, c1.sales, 0))
FROM #RefTable rt
CROSS APPLY
(
SELECT t.Empid
,t.Data
,t.Week
,t.sales
,rt.yearwk
,rt.yearmnt
FROM #Table t
WHERE t.Week <= rt.yearwk
AND t.Week + 2 > rt.yearwk
) c1
GROUP BY c1.Empid
,c1.Data
,rt.yearwk
,rt.yearmnt)
,BaseData2
AS (SELECT bd1.Empid
,bd1.Data
,bd1.yearwk
,bd1.yearmnt
,PrevYearMth = LAG(bd1.yearmnt, 1, NULL) OVER (PARTITION BY bd1.Empid, bd1.Data ORDER BY bd1.yearwk)
,bd1.Sales
FROM BaseData1 bd1)
SELECT bd2.Empid
,bd2.Data
,Week = bd2.yearwk
,bd2.Sales
FROM BaseData2 bd2
WHERE bd2.Sales > 0
OR
(
bd2.PrevYearMth = bd2.yearmnt
AND NOT EXISTS
(
SELECT 1
FROM #Table t
JOIN #RefTable rt
ON rt.yearwk = t.Week
WHERE t.Empid = bd2.Empid
AND t.Data = bd2.Data
AND t.Week > bd2.yearwk
AND rt.yearmnt = bd2.yearmnt
)
)
ORDER BY bd2.Empid
,bd2.Data
,bd2.yearwk;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
It is working fine, but it is taking lot of time to do the process.
Data is around 20 Billion records...
-
That is a lot of data. Have you looked at the execution plan? Are the underlying tables appropriately keyed and indexed?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply