December 11, 2007 at 12:41 pm
Thanks Vasc.
It makes a lot of sense to have fixed period of time for analysis and charts. The way our all monthly reports run is for the month. I brought this up once and if there is change it need to be changed at several places. So it was decided to keep per month analysis.
To correct this a bit there is another result set I will be making that would be complaints per machine. Still a month with more days will always be having a higher values. Every one knows it and it okay for now.
Regards,
gova
December 11, 2007 at 12:51 pm
gova (12/11/2007)
Thanks Vasc.It makes a lot of sense to have fixed period of time for analysis and charts. The way our all monthly reports run is for the month. I brought this up once and if there is change it need to be changed at several places. So it was decided to keep per month analysis.
To correct this a bit there is another result set I will be making that would be complaints per machine. Still a month with more days will always be having a higher values. Every one knows it and it okay for now.
Those monthly reports probably makes sense for "monthly periods". Not this case because a model is not changed at the begining of a month so how I m supposed to decide the "month period".
Look in the last post I fixed the "period error" because before I used datediff(WEEK to get the week for second result set wich is wrong (I was getting week 4 till I noticed composed of prolly 2-3 days) The same will be with monthly periods...
I d simply try tp explain to the client the difference between normal reports that make sense to be used in "monthly periods" context and this one(you can prepare a sample by changing the grouping formula to be based on month/week and see the difference)
Vasc
December 12, 2007 at 6:59 am
Vasc (12/11/2007)
Those monthly reports probably makes sense for "monthly periods". Not this case because a model is not changed at the begining of a month so how I m supposed to decide the "month period".Look in the last post I fixed the "period error" because before I used datediff(WEEK to get the week for second result set wich is wrong (I was getting week 4 till I noticed composed of prolly 2-3 days) The same will be with monthly periods...
I d simply try tp explain to the client the difference between normal reports that make sense to be used in "monthly periods" context and this one(you can prepare a sample by changing the grouping formula to be based on month/week and see the difference)
Very good point. I did not think in that way. I explained it to my Manager and it is accepted. Thanks for the valid input.
Regards,
gova
December 12, 2007 at 7:07 am
Vasc
I see a small glitch in the query. Please see the following query. I added a two new machines 'Not Upgraded' and a 'New Machine' which should not get involved in the anlalysis. They are in the results making the result set not a reliable analysis.
I am able to do it in a long way by creating 4 temp tables.
t_preFrom, t_preTo has only data for the parameters passed.
t_From, t_To has data joining with t_preTo and t_preFrom.
Then use t_From, t_To in your query.
Is there any other easy way to do that?
DECLARE @CustomerComplaints TABLE
(
MachineID VARCHAR(32),
Model VARCHAR(16),
Complaints INT,
DataDate DATETIME
)
INSERT @CustomerComplaints
SELECT 'NotUpgraded', 'Model 2', 15, '2007-04-23' UNION
SELECT 'NotUpgraded', 'Model 2', 16, '2007-04-30' UNION
SELECT 'NotUpgraded', 'Model 2', 18, '2007-05-05' UNION
SELECT 'NotUpgraded', 'Model 2', 12, '2007-05-06' UNION
SELECT 'NotUpgraded', 'Model 2', 08, '2007-05-07' UNION
SELECT 'New Machine', 'Model 4', 04, '2007-05-07' UNION
SELECT 'New Machine', 'Model 4', 13, '2007-05-13' UNION
SELECT 'New Machine', 'Model 4', 18, '2007-05-20' UNION
SELECT 'New Machine', 'Model 4', 15, '2007-05-27' UNION
SELECT 'New Machine', 'Model 4', 57, '2007-06-05' UNION
SELECT 'New Machine', 'Model 4', 12, '2007-06-12' UNION
SELECT 'New Machine', 'Model 4', 34, '2007-06-19' UNION
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'
SET NOCOUNT ON
DECLARE @pToModel varchar(16)
DECLARE @pFromModel varchar(16)
SET @pFromModel='Model 2'
SET @pToModel='Model 4'
DECLARE @NoOfWeeks INT
SET @NoOfWeeks=11
SELECT A.MachineID,
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 A.MachineID, CAST(DATEDIFF(day,B.DataDate-1,A.DataDate)/7 as INT)-1
UNION ALL
SELECT A.MachineID,
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 A.MachineID, DATEDIFF(week,B.DataDate,A.DataDate)+1
Regards,
gova
December 12, 2007 at 8:06 am
--@tmp might be a killer without the right index
--dunno if in your design you have another table that keeps "status data" about machines like :
-- InstallationData
-- StartingModelNo
-- CurrentModelNo
-- ...
--You could build @Tmp from that table and just compare that @pFrom<>CurrentModelNo and @pTo<>StartingModelNo
DECLARE @Tmp TABLE(
MachineID VARCHAR(32),
minModel VARCHAR(16),
maxModel VARCHAR(16)
)
INSERT INTO @Tmp
SELECT MachineID, MIN(Model) minModel, MAX(Model) maxModel
FROM @CustomerComplaints
GROUP BY MachineID
HAVING MIN(Model)<>MAX(Model)
IF (@pFromModel IS NOT NULL)
DELETE FROM @Tmp WHERE maxModel=@pFromModel
IF (@pToModel IS NOT NULL)
DELETE FROM @Tmp WHERE minModel=@pToModel
SELECT * FROM @Tmp
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) AND MachineID IN (SELECT MachineID FROM @Tmp)
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) AND MachineID IN (SELECT MachineID FROM @Tmp)
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
December 12, 2007 at 9:17 am
It doesn't work well when we use a Model in between.
SET @pFromModel='Model 2'
SET @pToModel='Model 3'
Should not list machine '34000904'. It does.
Vasc (12/12/2007)
--@tmp might be a killer without the right index--dunno if in your design you have another table that keeps "status data" about machines like :
-- InstallationData
-- StartingModelNo
-- CurrentModelNo
-- ...
I am free to create any table to make the results faster.
My first / original table has the update history. If necessary it can be used.
Or if you have any suggestions to create a new table to have the update history that would be fine too.
Regards,
gova
December 12, 2007 at 9:51 am
--I think the whole design is flawed...
--nothing tells me when the machine is working K with no complains. In your sample data WHAT will tell me that there were no complains BUT the machine actually had "model 3"? Easy to avoid by adding a record with 0 complaints.
--nothing tells me if the machine is "in service". Beeing out of service and no complains... doesn t mean that the model is behaving K ....
--should use something like an AVG/TimePeriodInService ...
--the table that I mentioned before it s very usefull for reporting machines status and not calculate based on a table with millions of rows.
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
DECLARE @Tmp TABLE(
MachineID VARCHAR(32),
minModel VARCHAR(16),
maxModel VARCHAR(16)
)
INSERT INTO @Tmp
SELECT MachineID, MIN(Model) minModel, MAX(Model) maxModel
FROM @CustomerComplaints
GROUP BY MachineID
HAVING MIN(Model)<>MAX(Model)
IF (@pFromModel IS NOT NULL)
DELETE FROM @Tmp WHERE maxModel=@pFromModel
IF (@pToModel IS NOT NULL)
DELETE FROM @Tmp WHERE minModel=@pToModel
IF ((@pFromModel IS NOT NULL) AND (@pToModel IS NOT NULL))
BEGIN
DELETE A FROM @Tmp A WHERE NOT EXISTS(SELECT MachineID,Model FROM @CustomerComplaints WHERE MachineID=A.MachineID and Model=@pfromModel)
DELETE A FROM @Tmp A WHERE NOT EXISTS(SELECT MachineID,Model FROM @CustomerComplaints WHERE MachineID=A.MachineID and Model=@ptoModel)
END
SELECT * FROM @Tmp
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) AND MachineID IN (SELECT MachineID FROM @Tmp)
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) AND MachineID IN (SELECT MachineID FROM @Tmp)
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
December 12, 2007 at 10:17 am
Vasc (12/12/2007)
--I think the whole design is flawed...--nothing tells me when the machine is working K with no complains. In your sample data WHAT will tell me that there were no complains BUT the machine actually had "model 3"? Easy to avoid by adding a record with 0 complaints.
--nothing tells me if the machine is "in service". Beeing out of service and no complains... doesn t mean that the model is behaving K ....
Thank you Vasc.
It is not the design that is flawed.
It is my lack of foresight in giving the sample data.
Machine with 0 complaints will be having a complaint column with 0. This is derived summary table from dailyReports table. In the daily reports table we have a row for each complaint. There is a weekly configuration report(row which is not a compalint) to show the machne is alive. Also when the machine is installed or configuration changed there will be a configuration report.
The table has several other columns and the specifications. This report is vast like report by complaint type, Model Type etc. etc. etc. I did not want to give all that to avoid complexity in the post.
Rest of the specs I would be able to give a solution though not as elegant as your queries.
I will add some of the 0 complaint machines in sample data and check the query and come back.
Regards,
gova
December 12, 2007 at 10:44 am
/*
This is the new sample data with 0 complaints and with your new query.
This works great for combinations of from to I can think of.
I will play with real data. If I find any thing beyond my abilities I will be back.
Thanks a lot.
One question some machines that need not be considered are in the tmp table. They are not in the final results. So the end results are fine. Will it improve the performance if we eliminate them in tmp.
*/
DECLARE @CustomerComplaints TABLE
(
MachineID VARCHAR(32),
Model VARCHAR(16),
Complaints INT,
DataDate DATETIME
)
INSERT @CustomerComplaints
SELECT 'NotUpgraded', 'Model 2', 15, '2007-04-23' UNION
SELECT 'NotUpgraded', 'Model 2', 16, '2007-04-30' UNION
SELECT 'NotUpgraded', 'Model 2', 18, '2007-05-05' UNION
SELECT 'NotUpgraded', 'Model 2', 12, '2007-05-06' UNION
SELECT 'NotUpgraded', 'Model 2', 08, '2007-05-07' UNION
SELECT 'New Machine', 'Model 4', 04, '2007-05-07' UNION
SELECT 'New Machine', 'Model 4', 13, '2007-05-13' UNION
SELECT 'New Machine', 'Model 4', 18, '2007-05-20' UNION
SELECT 'New Machine', 'Model 4', 15, '2007-05-27' UNION
SELECT 'New Machine', 'Model 4', 57, '2007-06-05' UNION
SELECT 'New Machine', 'Model 4', 12, '2007-06-12' UNION
SELECT 'New Machine', 'Model 4', 34, '2007-06-19' UNION
SELECT 'No Comp Before Upgrade', 'Model 2', 0, '2007-04-23' UNION
SELECT 'No Comp Before Upgrade', 'Model 2', 0, '2007-04-30' UNION
SELECT 'No Comp Before Upgrade', 'Model 2', 0, '2007-05-05' UNION
SELECT 'No Comp Before Upgrade', 'Model 2', 0, '2007-05-06' UNION
SELECT 'No Comp Before Upgrade', 'Model 2', 0, '2007-05-07' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 26, '2007-07-10' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 54, '2007-07-12' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 37, '2007-07-19' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 12, '2007-07-20' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 23, '2007-07-26' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 17, '2007-07-27' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 26, '2007-07-10' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 54, '2007-07-12' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 37, '2007-07-19' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 12, '2007-07-20' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 23, '2007-07-26' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 17, '2007-07-27' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-05-07' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-05-13' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-05-20' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-05-27' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-06-05' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-06-12' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-06-19' UNION
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'
SET NOCOUNT ON
DECLARE @pToModel varchar(16)
DECLARE @pFromModel varchar(16)
SET @pFromModel='Model 3'
SET @pToModel='Model 4'
DECLARE @NoOfWeeks INT
SET @NoOfWeeks=11
DECLARE @Tmp TABLE(
MachineID VARCHAR(32),
minModel VARCHAR(16),
maxModel VARCHAR(16)
)
INSERT INTO @Tmp
SELECT MachineID, MIN(Model) minModel, MAX(Model) maxModel
FROM @CustomerComplaints
GROUP BY MachineID
HAVING MIN(Model)<>MAX(Model)
IF (@pFromModel IS NOT NULL)
DELETE FROM @Tmp WHERE maxModel=@pFromModel
IF (@pToModel IS NOT NULL)
DELETE FROM @Tmp WHERE minModel=@pToModel
IF ((@pFromModel IS NOT NULL) AND (@pToModel IS NOT NULL))
BEGIN
DELETE A FROM @Tmp A WHERE NOT EXISTS(SELECT MachineID,Model FROM @CustomerComplaints WHERE MachineID=A.MachineID and Model=@pfromModel)
DELETE A FROM @Tmp A WHERE NOT EXISTS(SELECT MachineID,Model FROM @CustomerComplaints WHERE MachineID=A.MachineID and Model=@ptoModel)
END
SELECT * FROM @Tmp
SELECT A.MachineID,
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) AND MachineID IN (SELECT MachineID FROM @Tmp)
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 A.MachineID, CAST(DATEDIFF(day,B.DataDate,A.DataDate)/7 as INT)-1
UNION ALL
SELECT A.MachineID,
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) AND MachineID IN (SELECT MachineID FROM @Tmp)
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 A.MachineID, CAST(DATEDIFF(day,B.DataDate,A.DataDate)/7 as INT)+1
Regards,
gova
December 12, 2007 at 11:12 am
One question some machines that need not be considered are in the tmp table. They are not in the final results. So the end results are fine. Will it improve the performance if we eliminate them in tmp
if you have a valid criteria to eliminate them yes. Since to get the result you ll have to join the big table with the small one @Tmp.
Vasc
January 28, 2008 at 1:18 pm
There is a new requirement.
We need a report for multiple from AV.
@pFromModel or @pToModel can be more than one model. (i.e Upgrades of multiple models to a model or upgrades of a Model to multiple models)
SET @pFromModel = 'Model 2', 'Model 3'
SET @pToModel='Model 4'
I tried with my SQL skills. Could not do it. Can you please help me to add this feature in my Stored procedure?
Regards,
gova
January 30, 2008 at 1:22 pm
No answers from any one. I did some how managed it. Please find faults or errors in this.
DECLARE @CustomerComplaints TABLE
(
MachineID VARCHAR(32),
Model VARCHAR(64),
Complaints INT,
DataDate DATETIME
)
INSERT @CustomerComplaints
SELECT 'NotUpgraded', 'Model 2', 15, '2007-04-23' UNION
SELECT 'NotUpgraded', 'Model 2', 16, '2007-04-30' UNION
SELECT 'NotUpgraded', 'Model 2', 18, '2007-05-05' UNION
SELECT 'NotUpgraded', 'Model 2', 12, '2007-05-06' UNION
SELECT 'NotUpgraded', 'Model 2', 08, '2007-05-07' UNION
SELECT 'New Machine', 'Model 4', 04, '2007-05-07' UNION
SELECT 'New Machine', 'Model 4', 13, '2007-05-13' UNION
SELECT 'New Machine', 'Model 4', 18, '2007-05-20' UNION
SELECT 'New Machine', 'Model 4', 15, '2007-05-27' UNION
SELECT 'New Machine', 'Model 4', 57, '2007-06-05' UNION
SELECT 'New Machine', 'Model 4', 12, '2007-06-12' UNION
SELECT 'New Machine', 'Model 4', 34, '2007-06-19' UNION
SELECT 'No Comp Before Upgrade', 'Model 2', 0, '2007-04-23' UNION
SELECT 'No Comp Before Upgrade', 'Model 2', 0, '2007-04-30' UNION
SELECT 'No Comp Before Upgrade', 'Model 2', 0, '2007-05-05' UNION
SELECT 'No Comp Before Upgrade', 'Model 2', 0, '2007-05-06' UNION
SELECT 'No Comp Before Upgrade', 'Model 2', 0, '2007-05-07' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 26, '2007-07-10' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 54, '2007-07-12' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 37, '2007-07-19' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 12, '2007-07-20' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 23, '2007-07-26' UNION
SELECT 'No Comp Before Upgrade', 'Model 3', 17, '2007-07-27' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 26, '2007-07-10' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 54, '2007-07-12' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 37, '2007-07-19' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 12, '2007-07-20' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 23, '2007-07-26' UNION
SELECT 'No Comp After Upgrade', 'Model 3', 17, '2007-07-27' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-05-07' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-05-13' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-05-20' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-05-27' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-06-05' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-06-12' UNION
SELECT 'No Comp After Upgrade', 'Model 4', 0, '2007-06-19' UNION
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'
SET NOCOUNT ON
DECLARE @pToModel varchar(16)
DECLARE @pFromModel varchar(16)
SET @pFromModel='Model 2'
SET @pToModel='Model 4, Model 3'
DECLARE @NoOfWeeks INT
SET @NoOfWeeks=11
DECLARE @FromAV TABLE
(
Model VARCHAR(64)
)
DECLARE @ToAV TABLE
(
Model VARCHAR(64)
)
/* I have a udf to the split and get this tables populated */
/* Here it is done manually for readability */
INSERT @FromAV
SELECT 'Model 2'
INSERT @ToAV
SELECT 'Model 4' UNION
SELECT 'Model 3'
SELECT A.MachineID,
CAST(DATEDIFF(day,B.DataDate,A.DataDate)/7 as INT)-1 as WeekNo,
SUM(Complaints) NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
(--@t_from
SELECT C.MachineID, C.Model, MAX(C.DataDate) DataDate
FROM
@CustomerComplaints C
JOIN
@FromAV F
ON
C.Model = F.Model
JOIN
(
SELECT C.*
FROM
@CustomerComplaints C
JOIN
@ToAV T
ON
C.Model = T.Model
) T
ON
C.MachineID = T.MachineID
GROUP BY
C.MachineID, C.Model
) 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 A.MachineID, CAST(DATEDIFF(day,B.DataDate,A.DataDate)/7 as INT)-1
UNION
SELECT A.MachineID,
CAST(DATEDIFF(day,B.DataDate,A.DataDate)/7 as INT)+1 as WeekNo,
SUM(Complaints) NoOFComplaints
FROM @CustomerComplaints A INNER JOIN
(--@t_to
SELECT C.MachineID, C.Model, MIN(C.DataDate) DataDate
FROM
@CustomerComplaints C
JOIN
@ToAV T
ON
C.Model = T.Model
JOIN
(
SELECT C.*
FROM
@CustomerComplaints C
JOIN
@FromAV F
ON
C.Model = F.Model
) F
ON
C.MachineID = F.MachineID
GROUP BY
C.MachineID, C.Model
) 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 A.MachineID, CAST(DATEDIFF(day,B.DataDate,A.DataDate)/7 as INT)+1
ORDER BY 2
Regards,
gova
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply