April 16, 2018 at 10:01 am
CREATE TABLE #MyTemp
(
YearCol INT ,
MonthCol INT ,
RegionName VARCHAR(200) ,
JobSector VARCHAR(20) ,
ReviewsConduct INT ,
ReviewsPass INT ,
ReviewsFail INT
);
INSERT INTO #MyTemp
VALUES
(2018, 04, 'NewYork', 'Sales', 20, 15, 5);
INSERT INTO #MyTemp
VALUES
(2018, 03, 'NewYork', 'Sales', 165, 100, 65);
INSERT INTO #MyTemp
VALUES
(2018, 02, 'NewYork', 'Sales', 48, 20, 28);
INSERT INTO #MyTemp
VALUES
(2018, 01, 'NewYork', 'Sales', 50, 30, 20);
INSERT INTO #MyTemp
VALUES
(2017, 12, 'NewYork', 'Sales', 200, 125, 75);
INSERT INTO #MyTemp
VALUES
(2017, 11, 'NewYork', 'Sales', 300, 220, 80);
INSERT INTO #MyTemp
VALUES
(2017, 10, 'NewYork', 'Sales', 600, 200, 400);
INSERT INTO #MyTemp
VALUES
(2018, 04, 'Jersey', 'Sales', 200, 180, 20);
INSERT INTO #MyTemp
VALUES
(2018, 03, 'Jersey', 'Sales', 150, 125, 25);
INSERT INTO #MyTemp
VALUES
(2018, 02, 'Jersey', 'Sales', 300, 225, 75);
INSERT INTO #MyTemp
VALUES
(2018, 01, 'Jersey', 'Sales', 100, 100, 0);
INSERT INTO #MyTemp
VALUES
(2017, 12, 'Jersey', 'Sales', 130, 100, 30);
INSERT INTO #MyTemp
VALUES
(2018, 04, 'DC', 'Sales', 210, 180, 30);
INSERT INTO #MyTemp
VALUES
(2018, 03, 'DC', 'Sales', 220, 150, 70);
INSERT INTO #MyTemp
VALUES
(2018, 02, 'DC', 'Sales', 90, 50, 40);
INSERT INTO #MyTemp
VALUES
(2018, 01, 'DC', 'Sales', 200, 120, 80);
INSERT INTO #MyTemp
VALUES
(2017, 12, 'DC', 'Sales', 300, 150, 150);
SELECT YearCol ,
MonthCol ,
RegionName ,
JobSector ,
ReviewsConduct ,
ReviewsPass ,
ReviewsFail
FROM #MyTemp;
to get the Sum of the 3 months ReviewsConduct, ReviewsPass fields based on user selected monthcol
Here I am trying Here I am trying
Trying to solve the business rules
if values passed in month is 4 and year is 2018 then
it look back(3 months) Apr 2018, mar 2018, feb 2018 if any of these months ReviewsConduct is less than 100 then we need to consider 2017 dec, 2017 nov till we see 3 months data more than ReviewsConduct 100
then sumup from the selected month (april in this case) till we look back...
expecting answer for below criteria is
DECLARE @yearValue int
DECLARE @monthValue int
SET @yearValue = 2018
SET @monthValue = 4
Create Table #MyOutput (RegionName Varchar(200), JobSector Varchar(20), ReviewsConductTotal int, ReviewsPassTotal int)
Insert Into #MyOutput Values('NewYork','Sales',783, 510 )
--- The reason 783 is first it looked first three rows (March, Feb , Jan here ReviewsConduct is less than 100 for april (20) , feb 48 so we need two more rows more than 100
Insert Into #MyOutput Values('Jersey','Sales',650, 530 )
--- consider only three rows (apr, mar, feb) as the each reviewsConduct is > 100
Insert Into #MyOutput Values('DC','Sales',720, 500 )
--- consider only three rows (apr, mar, feb) as the each reviewsConduct is > 100
Please help me
Thank you in advance
Monaika
April 16, 2018 at 2:07 pm
I'm not completely sure if I understand. Do you mean that given a date, you want to sum all the reviews conducted and the reviews passed. However you need to use enough months so that you have 3 months with more than 100 reviews conducted?
April 16, 2018 at 4:08 pm
Correct Steve.
Or let say my table #MyTemp has two additional columns 1 is ConductedTotal, PassedTotal these two columns need to populate using the above rules ..
can you please help me
Thank you in advance
April 18, 2018 at 10:54 am
Try this on for size:CREATE TABLE #MyTemp (
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
YearCol INT,
MonthCol INT,
RegionName VARCHAR(200),
JobSector VARCHAR(20),
ReviewsConduct INT,
ReviewsPass INT,
ReviewsFail INT
);
INSERT INTO #MyTemp (YearCol, MonthCol, RegionName, JobSector, ReviewsConduct, ReviewsPass, ReviewsFail)
VALUES (2018, 04, 'NewYork', 'Sales', 20, 15, 5),
(2018, 03, 'NewYork', 'Sales', 165, 100, 65),
(2018, 02, 'NewYork', 'Sales', 48, 20, 28),
(2018, 01, 'NewYork', 'Sales', 50, 30, 20),
(2017, 12, 'NewYork', 'Sales', 200, 125, 75),
(2017, 11, 'NewYork', 'Sales', 300, 220, 80),
(2017, 10, 'NewYork', 'Sales', 600, 200, 400),
(2018, 04, 'Jersey', 'Sales', 200, 180, 20),
(2018, 03, 'Jersey', 'Sales', 150, 125, 25),
(2018, 02, 'Jersey', 'Sales', 300, 225, 75),
(2018, 01, 'Jersey', 'Sales', 100, 100, 0),
(2017, 12, 'Jersey', 'Sales', 130, 100, 30),
(2018, 04, 'DC', 'Sales', 210, 180, 30),
(2018, 03, 'DC', 'Sales', 220, 150, 70),
(2018, 02, 'DC', 'Sales', 90, 50, 40),
(2018, 01, 'DC', 'Sales', 200, 120, 80),
(2017, 12, 'DC', 'Sales', 300, 150, 150);
WITH Aggregates AS (
SELECT
MT.YearCol,
MT.MonthCol,
MT.RegionName,
MT.JobSector,
MT.ReviewsConduct,
MT.ReviewsPass,
MT.ReviewsFail,
SUM(CASE WHEN MT.ReviewsConduct >= 100 THEN 1 ELSE 0 END)
OVER(PARTITION BY MT.RegionName, MT.JobSector ORDER BY MT.YearCol DESC, MT.MonthCol DESC) AS ReviewsConductCount,
SUM(CASE WHEN MT.ReviewsPass >= 100 THEN 1 ELSE 0 END)
OVER(PARTITION BY MT.RegionName, MT.JobSector ORDER BY MT.YearCol DESC, MT.MonthCol DESC) AS ReviewsPassCount
FROM #MyTemp AS MT
)
SELECT
A.RegionName,
A.JobSector,
SUM(CASE WHEN A.ReviewsConductCount <= 3 THEN A.ReviewsConduct ELSE 0 END) AS TotalReviewsConduct,
SUM(CASE WHEN A.ReviewsPassCount <= 3 THEN A.ReviewsPass ELSE 0 END) AS TotalReviewsPass
FROM Aggregates AS A
WHERE A.ReviewsConductCount <= 3
OR A.ReviewsPassCount <= 3
GROUP BY
A.RegionName,
A.JobSector;
-- This SELECT is here only to demonstrate what the Aggregates CTE is doing... You don't need this as part of your solution
SELECT
MT.YearCol,
MT.MonthCol,
MT.RegionName,
MT.JobSector,
MT.ReviewsConduct,
MT.ReviewsPass,
MT.ReviewsFail,
SUM(CASE WHEN MT.ReviewsConduct >= 100 THEN 1 ELSE 0 END)
OVER(PARTITION BY MT.RegionName, MT.JobSector ORDER BY MT.YearCol DESC, MT.MonthCol DESC) AS ReviewsConductCount,
SUM(CASE WHEN MT.ReviewsPass >= 100 THEN 1 ELSE 0 END)
OVER(PARTITION BY MT.RegionName, MT.JobSector ORDER BY MT.YearCol DESC, MT.MonthCol DESC) AS ReviewsPassCount
FROM #MyTemp AS MT
DROP TABLE #MyTemp;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply