December 4, 2007 at 2:14 pm
/*
we have customer complaints as below. Any staging tables can be created.
Task is how a model upgrade effects customer complaints. Expected results in the bottom.
@CustomerComplaints has millions of data. Datadates for any MachineID can be close to daily.
Any help in getting this query is greatly appreciated.
*/
DECLARE @CustomerComplaints TABLE
(
MachineID VARCHAR(32),
Model VARCHAR(16),
Complaints INT,
DataDate DATETIME
)
INSERT @CustomerComplaints
SELECT '34000904', 'Model 2', 15, '2007-04-23'UNION
SELECT '34000904', 'Model 2', 16, '2007-04-30'UNION
SELECT '34000904', 'Model 2', 18, '2007-05-05'UNION
SELECT '34000904', 'Model 2', 12, '2007-05-06'UNION
SELECT '34000904', 'Model 2', 08, '2007-05-07'UNION
SELECT '34000904', 'Model 4', 04, '2007-05-07'UNION
SELECT '34000904', 'Model 4', 13, '2007-05-13'UNION
SELECT '34000904', 'Model 4', 18, '2007-05-20'UNION
SELECT '34000904', 'Model 4', 15, '2007-05-27'UNION
SELECT '34000904', 'Model 4', 57, '2007-06-05'UNION
SELECT '34000904', 'Model 4', 12, '2007-06-12'UNION
SELECT '34000904', 'Model 4', 34, '2007-06-19'UNION
SELECT '68800314', 'Model 2', 15, '2007-04-27'UNION
SELECT '68800314', 'Model 2', 16, '2007-04-30'UNION
SELECT '68800314', 'Model 2', 18, '2007-05-08'UNION
SELECT '68800314', 'Model 2', 12, '2007-05-13'UNION
SELECT '68800314', 'Model 2', 13, '2007-05-15'UNION
SELECT '68800314', 'Model 2', 18, '2007-05-20'UNION
SELECT '68800314', 'Model 2', 15, '2007-05-27'UNION
SELECT '68800314', 'Model 2', 57, '2007-06-05'UNION
SELECT '68800314', 'Model 2', 12, '2007-06-12'UNION
SELECT '68800314', 'Model 2', 10, '2007-06-21'UNION
SELECT '68800314', 'Model 2', 14, '2007-06-24'UNION
SELECT '68800314', 'Model 2', 28, '2007-06-30'UNION
SELECT '68800314', 'Model 2', 36, '2007-07-06'UNION
SELECT '68800314', 'Model 2', 10, '2007-07-10'UNION
SELECT '68800314', 'Model 3', 26, '2007-07-10'UNION
SELECT '68800314', 'Model 3', 54, '2007-07-12'UNION
SELECT '68800314', 'Model 3', 37, '2007-07-19'UNION
SELECT '68800314', 'Model 3', 12, '2007-07-20'UNION
SELECT '68800314', 'Model 3', 23, '2007-07-26'UNION
SELECT '68800314', 'Model 3', 17, '2007-07-27'UNION
SELECT '68800314', 'Model 4', 06, '2007-07-27'UNION
SELECT '68800314', 'Model 4', 56, '2007-07-29'UNION
SELECT '68800314', 'Model 4', 78, '2007-08-05'UNION
SELECT '68800314', 'Model 4', 16, '2007-08-11'UNION
SELECT '68800314', 'Model 4', 45, '2007-08-17'
SELECT * FROM @CustomerComplaints ORDER BY DataDate DESC
/*
@pFromModel and @pToModel are the parameters
If @pFromModel is not passed any model from to @pToModel should be resulted
If @pToModel is not passed from @pFromModel to any upgrade should be resulted.
Upgrades can happen at any time. Result needed if from upgrade date Plus Minus 12 weeks.
At that time WeekBefore will be summ of the two machines' complaints but they will have totally different dates. All that matters is after week(s) from update and before week(s) from update.
Example if @pToModel = 'Model 3'
Upgrade to 'Model 3' (Machine 34000904 is not upgraded to 'Model 3' so it is ignored)
[font="Courier New"]
06/20 06/27- 07/04- 07/10- 07/17- 07/24
06/26 07/03 07/10 07/16 07/23 07/30
3WeeksBefore 2WeeksBefore WeekBefore WeekAfter 2WeeksAfter 3WeeksAfter
------------ ------------ ---------- --------- ----------- -----------
24 28 50 80 49 40[/font]
My format is gone.
*/
/*
I created a staging or temp table which just holds Model history.
This table may or may not be considered for computing.
*/
DECLARE @ModelHistory TABLE
(
MachineID VARCHAR(32),
ModelVARCHAR(16),
ModelDate DATETIME
)
INSERT @ModelHistory
SELECT '34000904', 'Model 2', '2007-05-07'UNION
SELECT '34000904', 'Model 4', '2007-06-04'UNION
SELECT '68800314', 'Model 2', '2007-06-22'UNION
SELECT '68800314', 'Model 3', '2007-07-10'UNION
SELECT '68800314', 'Model 4', '2007-07-27'
--SELECT * FROM @ModelHistory
Regards,
gova
December 7, 2007 at 9:51 am
Just to bring it to the front page.
Regards,
gova
December 7, 2007 at 10:10 am
You'd need self joins to join each table based on the date.
so
select a.model
, b.modeldate '3 weeks'
, c.modeldate ' 2 weeks'
...
from modelhistory a
inner join modelhistory b
on a.model = b.model
inner join modelhistory c
on a.model = b.model
where b.modeldate = datepadd( wk, -3, a.modeldate)
and b.modeldate = datepadd( wk, -2, a.modeldate)
....
Not super efficient.
December 10, 2007 at 10:32 am
Thanks for the reply.
I tried that it returns noresults. Can some one write a query from the sample tables I have in the original post.
Regards,
gova
December 10, 2007 at 1:37 pm
You lll need more clear specs
but this will return almost what you need(not finished since there is an error maybe in sample? ) :
DECLARE @pToModel varchar(16)
SET @pToModel='Model 3'
DECLARE @pToModelDate datetime
SELECT @pToModelDate=MIN(DataDate)
FROM @CustomerComplaints
WHERE Model=@pToModel
SELECT MachineID,
CONVERT(varchar,DATEADD(day,-3*7+1,@pToModelDate))+'-'+CONVERT(Varchar,DATEADD(day,-2*7+1,@pToModelDate)) as ThreeWeeksBefore_Period,
SUM(CASE WHEN DataDate BETWEEN DATEADD(day,-3*7+1,@pToModelDate) and DATEADD(day,-2*7+1,@pToModelDate) THEN Complaints ELSE 0 END) as ThreeWeeksBefore_NoOFComplaints,
CONVERT(varchar,DATEADD(day,-2*7+1,@pToModelDate))+'-'+CONVERT(Varchar,DATEADD(day,-1*7+1,@pToModelDate)) as TwoWeeksBefore_Period,
SUM(CASE WHEN DataDate BETWEEN DATEADD(day,-2*7+1,@pToModelDate) and DATEADD(day,-1*7+1,@pToModelDate) THEN Complaints ELSE 0 END) as TwoWeeksBefore_NoOFComplaints,
CONVERT(varchar,DATEADD(day,-1*7+1,@pToModelDate))+'-'+CONVERT(Varchar,DATEADD(day,-0*7+1,@pToModelDate)) as OneWeekBefore_Period,
SUM(CASE WHEN DataDate BETWEEN DATEADD(day,-1*7+1,@pToModelDate) and DATEADD(day,-0*7+1,@pToModelDate) THEN Complaints ELSE 0 END) as OneWeekBefore_NoOFComplaints
/* 07/04- 07/10 here are 6 days dunno what s the idea */
FROM @CustomerComplaints
WHERE DataDate BETWEEN DATEADD(day,-21,@pToModelDate) and DATEADD(day,21,@pToModelDate)
GROUP BY MachineID
Vasc
December 10, 2007 at 8:52 pm
Thanks Vasc. It looks like now I am going some where.
One point I did not explain well enough is MachineID is not needed in the result set as this is the analysis of effect of Model changes among all machines. Each machine is upgraded in a different date that is where I go clueless.
Upgrades are going on several machines from any model(@FromModel) to any model(@toModel). We need to get the increase or decrease of complaints after the upgrade.
Parameter can be fromModel or ToModel or both. We have to summarize the results for all the machines that went for the upgrade from or to or both models passed.
Upgrades may not happen at exact beginning or end of the day. That is why on the expected results we have a date repeated and looks like 6 days.
(i.e. I upgrade happened by '12/02/07' 12 PM then before that we could have received n complaints and after upgrade we may have received m complaints. This makes one date falling in both directions. I put the dates for information purpose only. In actual results it will be seen as Week -2, Week - 1 or Week +1, Week +2. This will go up to 24 weeks)
So 12/02/2007 is starting point and considered in both directions. Thats why it looks like 6 days in one direction. But all the dates are inclusive. so it is always 7 days. I would say if the upgrade happened on wednesday then it should be summarized for every wednesday both directions. But the exact upgrade date is for both Week -1 and Week +1. Hope I explained good enough.
Our dev database is in maintenace now. I will try with your query tomorrow. If you can give me a query without machineID and with weekdays(I would like weekday as I have to analyze the data every month too. I can just convert the query for day of the month) it would be great.
Any kind of staging tables or snapshot tables allowed to improve the performance.
Regards,
gova
December 11, 2007 at 7:20 am
--might wanna check the defined periods(dunno if they are right)
--Indexes! (add as needed)
SET NOCOUNT ON
DECLARE @pToModel varchar(16)
DECLARE @pFromModel varchar(16)
SET @pFromModel='Model 2'
SET @pToModel='Model 3'
DECLARE @t_from TABLE(MachineID VARCHAR(32),DataDate DATETIME)
DECLARE @t_to TABLE(MachineID VARCHAR(32),DataDate DATETIME)
INSERT INTO @t_from
SELECT MachineID,MAX(DataDate)
FROM @CustomerComplaints
WHERE Model=@pFromModel
GROUP BY MachineID
INSERT INTO @t_to
SELECT MachineID,MIN(DataDate)
FROM @CustomerComplaints
WHERE Model=@pToModel
GROUP BY MachineID
SELECT * FROM @t_from
SELECT
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,-3*7-1,B.DataDate) and DATEADD(day,-2*7-1,B.DataDate) THEN Complaints ELSE 0 END) as ThreeWeeksBefore_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,-2*7-1,B.DataDate) and DATEADD(day,-1*7-1,B.DataDate) THEN Complaints ELSE 0 END) as TwoWeeksBefore_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,-1*7-1,B.DataDate) and DATEADD(day,-0*7-1,B.DataDate) THEN Complaints ELSE 0 END) as OneWeekBefore_NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
(--@t_from
SELECT MachineID,MAX(DataDate) DataDate
FROM @CustomerComplaints
WHERE Model=@pFromModel
GROUP BY MachineID
) B
ON A.MachineID=B.MachineID and A.DataDate between DATEADD(day,-21,B.DataDate) and B.DataDate
WHERE A.Model=@pFromModel--needed only because in the last 3 weeks the machine might have changed the model so those records shouldn t be considered
SELECT * FROM @t_to
SELECT
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,0*7,B.DataDate) and DATEADD(day,1*7,B.DataDate) THEN Complaints ELSE 0 END) as OneWeekAfter_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,1*7,B.DataDate) and DATEADD(day,2*7,B.DataDate) THEN Complaints ELSE 0 END) as TwoWeeksAfter_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,2*7,B.DataDate) and DATEADD(day,3*7,B.DataDate) THEN Complaints ELSE 0 END) as ThreeWeeksAfter_NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
(--@t_to
SELECT MachineID,MIN(DataDate) DataDate
FROM @CustomerComplaints
WHERE Model=@pToModel
GROUP BY MachineID
) B
ON A.MachineID=B.MachineID and A.DataDate between B.DataDate and DATEADD(day,21,B.DataDate)
WHERE A.Model=@pToModel--needed only because in the next 3 weeks the machine might have changed the model so those records shouldn t be considered
Vasc
December 11, 2007 at 8:31 am
--added staff to handle variable no of weeks
DECLARE @NoOfWeeks INT
SET @NoOfWeeks=3
SELECT
CAST(DATEDIFF(day,B.DataDate-1,A.DataDate)/7 as INT)-1 as WeekNo,
SUM(Complaints) NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
(--@t_from
SELECT MachineID,MAX(DataDate) DataDate
FROM @CustomerComplaints
WHERE Model=@pFromModel
GROUP BY MachineID
) B
ON A.MachineID=B.MachineID and A.DataDate between DATEADD(day,-7*@NoOfWeeks-1,B.DataDate) and DATEADD(day,-1,B.DataDate)
WHERE A.Model=@pFromModel--needed only because in the last 3 weeks the machine might have changed the model so those records shouldn t be considered
GROUP BY CAST(DATEDIFF(day,B.DataDate-1,A.DataDate)/7 as INT)-1
UNION ALL
SELECT
DATEDIFF(week,B.DataDate,A.DataDate)+1 as WeekNo,
SUM(Complaints) NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
(--@t_to
SELECT MachineID,MIN(DataDate) DataDate
FROM @CustomerComplaints
WHERE Model=@pToModel
GROUP BY MachineID
) B
ON A.MachineID=B.MachineID and A.DataDate between B.DataDate and DATEADD(day,7*@NoOfWeeks,B.DataDate)
WHERE A.Model=@pToModel--needed only because in the next 3 weeks the machine might have changed the model so those records shouldn t be considered
GROUP BY DATEDIFF(week,B.DataDate,A.DataDate)+1
Vasc
December 11, 2007 at 8:44 am
Great. Thanks a million.
I just modified the query a bit as the user may want to see how an upgrade to a model from any model or to any model from a model. So there is a possibility that either @pFromModel or @pToModel can be null. I would like a CASE instead of IF ELSE. But I am lost with = once and in the other.
Can you please check this query whether it would solve this.
SET NOCOUNT ON
DECLARE @pToModel varchar(16)
DECLARE @pFromModel varchar(16)
--SET @pFromModel='Model 2'
SET @pToModel='Model 3'
DECLARE @t_from TABLE(MachineID VARCHAR(32),DataDate DATETIME)
DECLARE @t_to TABLE(MachineID VARCHAR(32),DataDate DATETIME)
IF LEN(@pFromModel) > 0
BEGIN
INSERT INTO @t_from
SELECT MachineID,MAX(DataDate)
FROM @CustomerComplaints
WHERE Model=@pFromModel
GROUP BY MachineID
SELECT
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,-3*7-1,B.DataDate) and DATEADD(day,-2*7-1,B.DataDate) THEN Complaints ELSE 0 END) as ThreeWeeksBefore_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,-2*7-1,B.DataDate) and DATEADD(day,-1*7-1,B.DataDate) THEN Complaints ELSE 0 END) as TwoWeeksBefore_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,-1*7-1,B.DataDate) and DATEADD(day,-0*7-1,B.DataDate) THEN Complaints ELSE 0 END) as OneWeekBefore_NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
@t_from B
ON A.MachineID=B.MachineID and A.DataDate between DATEADD(day,-21,B.DataDate) and B.DataDate
WHERE A.Model=@pFromModel--needed only because in the last 3 weeks the machine might have changed the model so those records shouldn t be considered
END
ELSE
BEGIN
INSERT INTO @t_from
SELECT MachineID,MAX(DataDate)
FROM @CustomerComplaints
WHERE Model<@pToModel
GROUP BY MachineID
SELECT
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,-3*7-1,B.DataDate) and DATEADD(day,-2*7-1,B.DataDate) THEN Complaints ELSE 0 END) as ThreeWeeksBefore_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,-2*7-1,B.DataDate) and DATEADD(day,-1*7-1,B.DataDate) THEN Complaints ELSE 0 END) as TwoWeeksBefore_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,-1*7-1,B.DataDate) and DATEADD(day,-0*7-1,B.DataDate) THEN Complaints ELSE 0 END) as OneWeekBefore_NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
@t_from B
ON A.MachineID=B.MachineID and A.DataDate between DATEADD(day,-21,B.DataDate) and B.DataDate
WHERE A.Model<@pToModel--needed only because in the last 3 weeks the machine might have changed the model so those records shouldn t be considered
END
IF LEN(@pToModel) > 0
BEGIN
INSERT INTO @t_to
SELECT MachineID,MIN(DataDate)
FROM @CustomerComplaints
WHERE Model=@pToModel
GROUP BY MachineID
SELECT
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,0*7,B.DataDate) and DATEADD(day,1*7,B.DataDate) THEN Complaints ELSE 0 END) as OneWeekAfter_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,1*7,B.DataDate) and DATEADD(day,2*7,B.DataDate) THEN Complaints ELSE 0 END) as TwoWeeksAfter_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,2*7,B.DataDate) and DATEADD(day,3*7,B.DataDate) THEN Complaints ELSE 0 END) as ThreeWeeksAfter_NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
@t_to B
ON A.MachineID=B.MachineID and A.DataDate between B.DataDate and DATEADD(day,21,B.DataDate)
WHERE A.Model=@pToModel--needed only because in the next 3 weeks the machine might have changed the model so those records shouldn t be considered
END
ELSE
BEGIN
INSERT INTO @t_to
SELECT MachineID,MIN(DataDate)
FROM @CustomerComplaints
WHERE Model>@pFromModel
GROUP BY MachineID
SELECT
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,0*7,B.DataDate) and DATEADD(day,1*7,B.DataDate) THEN Complaints ELSE 0 END) as OneWeekAfter_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,1*7,B.DataDate) and DATEADD(day,2*7,B.DataDate) THEN Complaints ELSE 0 END) as TwoWeeksAfter_NoOFComplaints,
SUM(CASE WHEN A.DataDate BETWEEN DATEADD(day,2*7,B.DataDate) and DATEADD(day,3*7,B.DataDate) THEN Complaints ELSE 0 END) as ThreeWeeksAfter_NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
@t_to B
ON A.MachineID=B.MachineID and A.DataDate between B.DataDate and DATEADD(day,21,B.DataDate)
WHERE A.Model>@pFromModel--needed only because in the next 3 weeks the machine might have changed the model so those records shouldn t be considered
END
Regards,
gova
December 11, 2007 at 8:55 am
Vasc I found another mismatch.
It is considering all the upgrades no matter whether we give @pFromModel or not.
I guess We should consider @t_from data when we get @t_to data like
INSERT INTO @t_to
SELECT A.MachineID,MIN(DataDate)
FROM @CustomerComplaints A
JOIN
@t_from B
ON
A.MachineID = B.MachineID
WHERE A.Model=@pToModel
GROUP BY A.MachineID
I am working on it. Let me know what do you think!
Regards,
gova
December 11, 2007 at 9:01 am
--@t_from, @t_to are NOT used in the actual query. Their purpose was just to display the intermediate results
--when @pToModel or @pFromModel is missing all complaints before/after are calculated regardless of new/old model
SET NOCOUNT ON
DECLARE @pToModel varchar(16)
DECLARE @pFromModel varchar(16)
SET @pFromModel='Model 2'
--SET @pToModel='Model 3'
DECLARE @NoOfWeeks INT
SET @NoOfWeeks=11
SELECT
CAST(DATEDIFF(day,B.DataDate-1,A.DataDate)/7 as INT)-1 as WeekNo,
SUM(Complaints) NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
(--@t_from
SELECT MachineID,CASE WHEN @pFromModel IS NULL THEN MIN(DataDate) ELSE MAX(DataDate) END DataDate
FROM @CustomerComplaints
WHERE Model=COALESCE(@pFromModel,@pToModel)
GROUP BY MachineID
) B
ON A.MachineID=B.MachineID and A.DataDate between DATEADD(day,-7*@NoOfWeeks-1,B.DataDate) and DATEADD(day,-1,B.DataDate)
WHERE A.Model=@pFromModel OR
@pFromModel IS NULL --don t know the previous model
GROUP BY CAST(DATEDIFF(day,B.DataDate-1,A.DataDate)/7 as INT)-1
UNION ALL
SELECT
DATEDIFF(week,B.DataDate,A.DataDate)+1 as WeekNo,
SUM(Complaints) NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
(--@t_to
SELECT MachineID,CASE WHEN @pToModel IS NULL THEN MAX(DataDate) ELSE MIN(DataDate) END DataDate
FROM @CustomerComplaints
WHERE Model=COALESCE(@pToModel,@pFromModel)
GROUP BY MachineID
) B
ON A.MachineID=B.MachineID and A.DataDate between B.DataDate and DATEADD(day,7*@NoOfWeeks,B.DataDate)
WHERE A.Model=@pToModel
OR @pToModel IS NULL--don t know next model
GROUP BY DATEDIFF(week,B.DataDate,A.DataDate)+1
Vasc
December 11, 2007 at 9:53 am
Cool. COALESCE did not struck my mind. I was thinking all routs other than simple ones. Thanks a lot.
Let me try this in actual data.
Regards,
gova
December 11, 2007 at 10:18 am
It works great with real data. Thank you again.
I got a question in the specs now. When the model is changed from 2 to 3 then 4. If the user wants to see the analysis for 2 to 4 do we have to consider this type of upgrade or not. Current spec is silent about it.
I will as this question to BA. He will ask Users and will take a while to get this answer.
Regards,
gova
December 11, 2007 at 10:22 am
Vasc
I have to do this as monthly analysis. I cannot use 30 days for monthly.
Can you please give me a query with weekday so that I can modify for day of month.
Or please let me know any other simple way to do the monthly analysis.
Regards,
gova
December 11, 2007 at 11:03 am
--I don t understand what you mean by monthly ... you ll HAVE to use fixed period times to be able to do valid comparisation among period values (feb is 28 day so when you compare with apr 31 days is allready a 10% diff in the period (3 days) which will affect the result-not good. You can use 30 days as a standard.)
--fixed an issue with periods (start/end)
SET NOCOUNT ON
DECLARE @pToModel varchar(16)
DECLARE @pFromModel varchar(16)
SET @pFromModel='Model 2'
SET @pToModel='Model 3'
DECLARE @NoOfWeeks INT
SET @NoOfWeeks=3
SELECT
CAST(DATEDIFF(day,B.DataDate,A.DataDate)/7 as INT)-1 as WeekNo,
SUM(Complaints) NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
(--@t_from
SELECT MachineID,CASE WHEN @pFromModel IS NULL THEN MIN(DataDate) ELSE MAX(DataDate) END DataDate
FROM @CustomerComplaints
WHERE Model=COALESCE(@pFromModel,@pToModel)
GROUP BY MachineID
) B
ON A.MachineID=B.MachineID AND A.DataDate BETWEEN DATEADD(day,-7*@NoOfWeeks+1,B.DataDate) AND B.DataDate
WHERE A.Model=@pFromModel OR
(@pFromModel IS NULL AND A.Model<>@pToModel)--don t know the previous model but CAN T be equal to @pToModel
GROUP BY CAST(DATEDIFF(day,B.DataDate,A.DataDate)/7 as INT)-1
UNION ALL
SELECT
CAST(DATEDIFF(day,B.DataDate,A.DataDate)/7 as INT)+1 as WeekNo,
SUM(Complaints) NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
(--@t_to
SELECT MachineID,CASE WHEN @pToModel IS NULL THEN MAX(DataDate) ELSE MIN(DataDate) END DataDate
FROM @CustomerComplaints
WHERE Model=COALESCE(@pToModel,@pFromModel)
GROUP BY MachineID
) B
ON A.MachineID=B.MachineID AND A.DataDate BETWEEN B.DataDate AND DATEADD(day,7*@NoOfWeeks-1,B.DataDate)
WHERE A.Model=@pToModel
OR (@pToModel IS NULL AND A.Model<>@pFromModel)--don t know next model but can t be equal to previous model
GROUP BY CAST(DATEDIFF(day,B.DataDate,A.DataDate)/7 as INT)+1
Vasc
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply