August 9, 2010 at 8:28 am
Hi Everybody.
I writing applications in C# with SQL server 2005 database storing the data for my project. I have started writing a suite of applications for a Photocopier Company and trying to work out a monthly volume for a device given dated meter counts. Not sure whether this should be done in C# or the SQL but I am trying SQL first as I think this would be the better solution for processing speed, I am aiming to produce a stored procedure (function) that will just return the required values.
I found some code on a website that gave me the foundation for my work but had problems trying to reference the final stages.
SELECT Power.MeterNo, Power.PDate, Power.PMeterReading,
(SELECT Prev.PMeterReading FROM Power AS Prev
WHERE Prev.PDate = (SELECT MAX(Prev2.PDate) FROM Power AS Prev2
WHERE Prev2.PDate < Power.PDate
AND Prev2.MeterNo = Power.MeterNo)
AND Prev.MeterNo = Power.MeterNo) AS Prev,
[PmeterReading]-[Prev] AS Diff
FROM Power
ORDER BY Power.MeterNo, Power.PDate DESC
Below is my table and some sample data.
CREATE TABLE #meterReading(
[mReadingCallReference] [int] NULL,
[mReadingDeviceReference] [int] NULL,
[mReadingReadingType] [varchar](4) NULL,
[mReadingMeterReadingDate] [datetime] NULL,
[mReadingMeterType] [tinyint] NULL,
[mReadingMeterReading] [int] NULL,
[mReadingStatus] [tinyint] NULL
) ON [PRIMARY]
insert into #meterReading
([mReadingCallReference],[mReadingDeviceReference],[mReadingReadingType],
[mReadingMeterReadingDate],[mReadingMeterType],[mReadingMeterReading],
[mReadingStatus])
Select '1877369','110036','R ','May 27 2010 10:55AM','1','30000','5' union all
Select '1876573','113474','I ','May 27 2010 12:21PM','1','340001','5' union all
Select '1876573','113474','I ','May 27 2010 12:21PM','2','103000','5' union all
Select '1877365','111910','RO ','May 27 2010 2:41PM','1','190000','5' union all
Select '1877362','110130','R ','May 27 2010 3:22PM','1','200000','5' union all
Select '1877367','110342','K ','May 27 2010 4:09PM','1','423000','5' union all
Select '1877366','110154','RO ','May 27 2010 4:06PM','1','80999','5' union all
Select '1877249','102210','CO ','May 27 2010 4:58PM','1','123444','5' union all
Select '1877055','105246','PI ','May 27 2010 5:16PM','1','50000','5' union all
Select '1877055','105246','PI ','May 27 2010 5:16PM','2','190000','5' union all
Select '1876974','109506','CB ','May 28 2010 10:56AM','1','123232','5' union all
Select '1876975','109506','CB ','May 28 2010 9:03AM','1','100012','5' union all
Select '0','112525','RTEL','May 27 2010 12:00AM','1','170001','5' union all
Select '0','112525','RTEL','May 27 2010 12:00AM','2','37777','5' union all
Select '1877223','114500','I ','Jul 26 2010 11:33AM','1','2300','5' union all
Select '1884891','113474','I ','Aug 6 2010 10:45AM','1','350000','5' union all
Select '1884891','113474','I ','Aug 6 2010 10:45AM','2','120000','5' union all
Select '0','112525','CARD','Aug 6 2010 12:00AM','1','200000','5' union all
Select '0','112525','CARD','Aug 6 2010 12:00AM','2','50000','5' union all
Select '1877247','101064','CO ','May 27 2010 4:59PM','1','834322','5' union all
Select '1877034','103232','PI ','May 28 2010 9:41AM','1','3100000','5' union all
Select '1877033','103232','PI ','May 28 2010 1:28PM','1','3100200','5'
I have a query that works for this information see below
SELECT [mReadingCallReference]
,[mReadingDeviceReference]
,[mReadingReadingType]
,[mReadingMeterReadingDate]
,[mReadingMeterType]
,[mReadingMeterReading]
-- ,[mReadingStatus]
,
-- First extra field - should be previous meter reading
-- find previous meter reading value for this machine that is more than 1 month prior to this
(select prv.[mReadingMeterReading] from #meterReading as prv
where prv.[mReadingMeterReadingDate] =
(select max(prv2.[mReadingMeterReadingDate]) from #meterReading as prv2
where prv2.mReadingMeterReadingDate < dateadd(Month, -1, meterReading.mReadingMeterReadingDate)
and prv2.mReadingDeviceReference = meterReading.mReadingDeviceReference
and prv2.mReadingMeterType = meterReading.mReadingMeterType
and prv2.mReadingReadingType not in ('PO','DN','RICO','TF'))
and prv.mReadingDeviceReference = meterReading.mReadingDeviceReference
and prv.mReadingMeterType = meterReading.mReadingMeterType
and prv.mReadingReadingType not in ('PO','DN','RICO','TF')) as prv,
-- Second extra field - monthly copies as number of copies made in an average month (ie 30.42 days)
-- calculation 30.42*(read_now - prev_read)/(date_now - prev_date)
30.42*(mReadingMeterReading - (select prv.[mReadingMeterReading] from #meterReading as prv
where prv.[mReadingMeterReadingDate] =
(select max(prv2.[mReadingMeterReadingDate]) from #meterReading as prv2
where prv2.mReadingMeterReadingDate < dateadd(Month, -1, meterReading.mReadingMeterReadingDate)
and prv2.mReadingDeviceReference = meterReading.mReadingDeviceReference
and prv2.mReadingMeterType = meterReading.mReadingMeterType
and prv2.mReadingReadingType not in ('PO','DN','RICO','TF'))
and prv.mReadingDeviceReference = meterReading.mReadingDeviceReference
and prv.mReadingMeterType = meterReading.mReadingMeterType
and prv.mReadingReadingType not in ('PO','DN','RICO','TF')) )
/ -- divide by time between the two readings
Convert(float, ([mReadingMeterReadingDate] -
(select prv.[mReadingMeterReadingDate] from #meterReading as prv
where prv.[mReadingMeterReadingDate] =
(select max(prv2.[mReadingMeterReadingDate]) from #meterReading as prv2
where prv2.mReadingMeterReadingDate < dateadd(Month, -1, meterReading.mReadingMeterReadingDate)
and prv2.mReadingDeviceReference = meterReading.mReadingDeviceReference
and prv2.mReadingMeterType = meterReading.mReadingMeterType
and prv2.mReadingReadingType not in ('PO','DN','RICO','TF'))
and prv.mReadingDeviceReference = meterReading.mReadingDeviceReference
and prv.mReadingMeterType = meterReading.mReadingMeterType
and prv.mReadingReadingType not in ('PO','DN','RICO','TF'))) ) as vol
-- end of second extra field
-- first try that did not work as columns were unable to be referenced.
--,30.42*([mReadingMeterReading]-[Prev])/([mReadingMeterReadingDate]-[pdate]) as vol
FROM #meterReading
where mReadingDeviceReference in (113474, 112525)
and mReadingReadingType not in ('PO','DN','RICO','TF')
order by 2,4,5
Having tried to produce a formula using the line
"30.42*([mReadingMeterReading]-[prv])/([mReadingMeterReadingDate]-[pdate]) as vol"
but got errors referencing the selected fields. To avoid this problem I had to select both the previous meter reading and its date by doing two different select statements, this seems to be quite a long winded method and possibly take extra time to complete.
The plans for future work on this are
1) Having a parameter for the date criteria so that we can use 3 months or a year.
2) Having a parameter for the mReadingDeviceReference so we can select a particular device.
3) Having a table for Types to ignore. (mReadingReadingType not in ('PO','DN','RICO','TF'))
Hope the above is clear, if you have any questions I will try to explain.
Am I starting in the right place or should I be looking at using temporary tables and going down that route. The meterReading file will turn out to be one of our largest files, getting on for 40000 devices with up to 400 readings for each device, so I need to get it right.
Thank you in advance
Jason
August 9, 2010 at 11:39 am
I applaud your efforts as a C# developer to do this in T-SQL, which is the right way to go. There are some issues with your basic approach and hopefully you will have a greater sense of direction once we work those out.
The basic problem is that in your query you are joining data over and over again. The result is that the code is very inefficient, difficult to read and prone to errors. What we want to do is one join (if possible) and then all the calculations based off that one join. I'm not 100% sure I understand exactly what you want to do, but I think this will be a start.
;WITH cteTemp AS
(SELECT RN = ROW_NUMBER() OVER(PARTITION BY mReadingDeviceReference, mReadingMeterType ORDER BY mReadingMeterReadingDate),
*
FROM #meterReading
WHERE mReadingReadingType NOT IN ('PO','DN','RICO','TF'))
SELECT ctc.mReadingDeviceReference,
ctc.mReadingMeterType,
ctc.mReadingMeterReadingDate AS currDate,
ctp.mReadingMeterReadingDate AS prevDate,
ctc.mReadingMeterReading - ctp.mReadingMeterReading AS copiesMade,
DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate) AS elapsedDays,
CAST(cast(ctc.mReadingMeterReading - ctp.mReadingMeterReading AS FLOAT)/DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)*30.42 AS INT) AS Vol
FROM cteTemp ctc --cteTemp current data
JOIN cteTemp ctp --cteTemp previous data
ON ctp.mReadingDeviceReference = ctc.mReadingDeviceReference
AND ctp.mReadingMeterType = ctc.mReadingMeterType
AND ctp.RN = ctc.RN - 1
where ctc.mReadingDeviceReference in (113474, 112525)
ORDER BY ctc.mReadingDeviceReference, ctc.mReadingMeterType, ctc.RN
One thing I am not currently doing is checking to make sure that the previous entry is more than 1 month prior. If it's possible that there would be entries that would essentially be skipped (since they're not far enough apart) I would need additional information to understand the logic in determining which ones would be skipped.
I am not anticipating this is a complete solution, but I am hoping that it gets the conversation more on track without a labyrinth of joins that need to be deciphered.
August 10, 2010 at 4:12 am
Thanks bt for that.
I will try and explain a few things and then ask some questions about your code so that I can work on it.
Both C# and SQL are new to me, (I have been using Informix 4GL & SQL for over 20 years) but this makes sense to be a SQL piece of code, rather than sending lots of data to C# and doing the processing there.
I found the first piece of code on how to find a previous reading on a website but found that I could not do the calculations with the fields as I required, I was getting an error so found out the only way to get what I wanted was to actually do the same select twice which did not seem to be an efficient method.
I hopefully explain where I am trying to get to with this query. In our current system we currently calculate the several different monthly copy speeds for a particular machine. Our first one needs readings to be 1 month apart, this is to avoid problems when two readings close together an could give either extremely low or high values because of a large number of copies been done in 1 day or the machine not used over a weekend. We also calculate with readings at least 3 months apart, 1 year apart and since installation of the copier. We can often find that some of these calculations will use the same readings for the 1 month & 3 month calculations. This information allows people within the company to look at trends in usage.
Looking at your solution, I would guess that you are creating a temporary table cteTemp and selecting all readings from the table for all machines, creating a reference number to order by.
This is a new concept from previous experience as Informix was all about defining cursor, but reading articles here on SQL Server Central cursors are frowned upon possibly due to speed issues.
Using this temporary table to reference itself to find the latest reading and selecting the previous one gives us the values we need to produce the calculations.
I have questions about what I am going to do today.
I envisage creating a stored function/procedure that C# will call to calculate for a particular devicereference so should I limit the creation of the CTE to include just the one reference cutting down the size of the CTE table.
Thank you for your reply.
Jason
August 10, 2010 at 9:29 am
Hi Everyone
Had a productive day with help from bt.
New code below
;WITH cteTemp AS
(SELECT RN = ROW_NUMBER() OVER(PARTITION BY mReadingDeviceReference, mReadingMeterType ORDER BY mReadingMeterReadingDate),
*
FROM dbo.meterReading
WHERE mReadingDeviceReference in (112525) -- (question 1)
and mReadingReadingType NOT IN ('PO','DN','RICO','TF','PREP','REFB'))
--select * from cteTemp (question 2)
SELECT ctc.mReadingDeviceReference,
ctc.mReadingMeterType,
ctc.mReadingMeterReadingDate AS currDate,
ctp.mReadingMeterReadingDate AS prevDate,
ctc.mReadingMeterReading - ctp.mReadingMeterReading AS copiesMade,
DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate) AS elapsedDays,
CAST(cast(ctc.mReadingMeterReading - ctp.mReadingMeterReading AS FLOAT)/(
case DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
when 0 then 1
else DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
end
)*30.42 AS INT) AS Vol
FROM cteTemp ctc --cteTemp current data
JOIN cteTemp ctp --cteTemp previous data
ON ctp.mReadingDeviceReference = ctc.mReadingDeviceReference
AND ctp.mReadingMeterType = ctc.mReadingMeterType
AND ctp.RN in (select max(RN) from cteTemp where mReadingMeterReadingDate < dateadd(Month, -1, ctc.mReadingMeterReadingDate))
--AND ctp.RN = 1 (question 3)
where ctc.RN in (select max(RN) from cteTemp)
ORDER BY ctc.mReadingDeviceReference, ctc.mReadingMeterType, ctc.RN
This seems to work as I would expect.
question 1 - Is this good practice to just get the data I require if i make a stored procedure for each machine?
question 2 - Is it possible to run more than 1 select on a CTE table? As when I create a stored procedure I would have to create the CTE 4 or 5 times to return each of the different calculations.
question 3 - I can pass a parameter to change the number of months that we skip before using a reading. However one calculation needs to use the first reading on the machine. Does this need a different stored procedure or is it possible to use a case in a select statement ?
Jason
August 10, 2010 at 12:00 pm
jasonshaw (8/10/2010)
question 1 - Is this good practice to just get the data I require if i make a stored procedure for each machine?
I would create one stored procedure with a parameter so you can pass in the machine you want data for. It's really easy if you're going to query one at a time, but a little more complex if you want to be able to pass in multiple values.
jasonshaw (8/10/2010)
question 2 - Is it possible to run more than 1 select on a CTE table? As when I create a stored procedure I would have to create the CTE 4 or 5 times to return each of the different calculations.
You should be able to do all the calculations off the one CTE. In the example I gave, you would have a return of a few different calculations all off of the same join based on the same CTE.
jasonshaw (8/10/2010)
question 3 - I can pass a parameter to change the number of months that we skip before using a reading. However one calculation needs to use the first reading on the machine. Does this need a different stored procedure or is it possible to use a case in a select statement ?
Not sure if I fully follow what you're asking but I'll try to answer. Yes you can use a parameter if months to skip is a variable number. Also, in the example I gave, we're only taking data from 2 months. We could do an additional join if we wanted data included from another month.
One thing that may help at this point would be if you put together a more extensive and realistic sample dataset to work with. Then, based on that sample, what do you want to see and why? The more detailed it is and the better you explain the business logic, the more likely it is that you'll get good suggestions.
August 20, 2010 at 4:56 am
Hi Everybody
Continued on to produce the following stored procedure which does what I want.
create proc [dbo].[usp_calc_volumes]
@unq_ref int=null
as
begin
set nocount on
;WITH cteTemp AS
(SELECT RN = ROW_NUMBER() OVER(PARTITION BY mReadingDeviceReference, mReadingMeterType ORDER BY mReadingMeterReadingDate),
*
FROM dbo.meterReading
WHERE mReadingDeviceReference in (@unq_ref) -- (question 1)
and mReadingReadingType NOT IN ('PO','DN','RICO','TF','PREP','REFB'))
SELECT 1 as ty,
ctc.mReadingDeviceReference,
ctc.mReadingMeterType,
MT.mTypeDescription,
ctc.mReadingMeterReadingDate AS currDate,
ctp.mReadingMeterReadingDate AS prevDate,
ctc.mReadingMeterReading - ctp.mReadingMeterReading AS copiesMade,
DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate) AS elapsedDays,
CAST(cast(ctc.mReadingMeterReading - ctp.mReadingMeterReading AS FLOAT)/(
case DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
when 0 then 1
else DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
end
)*365/12.0 AS int) AS Vol
FROM cteTemp ctc --cteTemp current data
JOIN cteTemp ctp --cteTemp previous data
ON ctp.mReadingDeviceReference = ctc.mReadingDeviceReference
AND ctp.mReadingMeterType = ctc.mReadingMeterType
AND ctp.RN in (select max(RN) from cteTemp where mReadingMeterReadingDate < dateadd(Month, -1, ctc.mReadingMeterReadingDate))
JOIN meterTypes MT
on ctc.mReadingMeterType = mTypeReference
where ctc.RN in (select max(RN) from cteTemp)
union
SELECT 3 as ty,
ctc.mReadingDeviceReference,
ctc.mReadingMeterType,
MT.mTypeDescription,
ctc.mReadingMeterReadingDate AS currDate,
ctp.mReadingMeterReadingDate AS prevDate,
ctc.mReadingMeterReading - ctp.mReadingMeterReading AS copiesMade,
DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate) AS elapsedDays,
CAST(cast(ctc.mReadingMeterReading - ctp.mReadingMeterReading AS FLOAT)/(
case DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
when 0 then 1
else DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
end
)*365/12.0 AS int) AS Vol
FROM cteTemp ctc --cteTemp current data
JOIN cteTemp ctp --cteTemp previous data
ON ctp.mReadingDeviceReference = ctc.mReadingDeviceReference
AND ctp.mReadingMeterType = ctc.mReadingMeterType
AND ctp.RN in (select max(RN) from cteTemp where mReadingMeterReadingDate < dateadd(Month, -3, ctc.mReadingMeterReadingDate))
JOIN meterTypes MT
on ctc.mReadingMeterType = mTypeReference
where ctc.RN in (select max(RN) from cteTemp)
union
SELECT 6 as ty,
ctc.mReadingDeviceReference,
ctc.mReadingMeterType,
MT.mTypeDescription,
ctc.mReadingMeterReadingDate AS currDate,
ctp.mReadingMeterReadingDate AS prevDate,
ctc.mReadingMeterReading - ctp.mReadingMeterReading AS copiesMade,
DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate) AS elapsedDays,
CAST(cast(ctc.mReadingMeterReading - ctp.mReadingMeterReading AS FLOAT)/(
case DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
when 0 then 1
else DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
end
)*365/12.0 AS int) AS Vol
FROM cteTemp ctc --cteTemp current data
JOIN cteTemp ctp --cteTemp previous data
ON ctp.mReadingDeviceReference = ctc.mReadingDeviceReference
AND ctp.mReadingMeterType = ctc.mReadingMeterType
AND ctp.RN in (select max(RN) from cteTemp where mReadingMeterReadingDate < dateadd(day, -183, ctc.mReadingMeterReadingDate))
JOIN meterTypes MT
on ctc.mReadingMeterType = mTypeReference
where ctc.RN in (select max(RN) from cteTemp)
union
SELECT 12 as ty,
ctc.mReadingDeviceReference,
ctc.mReadingMeterType,
MT.mTypeDescription,
ctc.mReadingMeterReadingDate AS currDate,
ctp.mReadingMeterReadingDate AS prevDate,
ctc.mReadingMeterReading - ctp.mReadingMeterReading AS copiesMade,
DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate) AS elapsedDays,
CAST(cast(ctc.mReadingMeterReading - ctp.mReadingMeterReading AS FLOAT)/(
case DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
when 0 then 1
else DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
end
)*365/12.0 AS int) AS Vol
FROM cteTemp ctc --cteTemp current data
JOIN cteTemp ctp --cteTemp previous data
ON ctp.mReadingDeviceReference = ctc.mReadingDeviceReference
AND ctp.mReadingMeterType = ctc.mReadingMeterType
AND ctp.RN in (select max(RN) from cteTemp where mReadingMeterReadingDate < dateadd(Month, -12, ctc.mReadingMeterReadingDate))
JOIN meterTypes MT
on ctc.mReadingMeterType = mTypeReference
where ctc.RN in (select max(RN) from cteTemp)
union
SELECT 99 as ty,
ctc.mReadingDeviceReference,
ctc.mReadingMeterType,
MT.mTypeDescription,
ctc.mReadingMeterReadingDate AS currDate,
ctp.mReadingMeterReadingDate AS prevDate,
ctc.mReadingMeterReading - ctp.mReadingMeterReading AS copiesMade,
DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate) AS elapsedDays,
CAST(cast(ctc.mReadingMeterReading - ctp.mReadingMeterReading AS FLOAT)/(
case DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
when 0 then 1
else DATEDIFF(dd,ctp.mReadingMeterReadingDate,ctc.mReadingMeterReadingDate)
end
)*365/12.0 AS int) AS Vol
FROM cteTemp ctc --cteTemp current data
JOIN cteTemp ctp --cteTemp previous data
ON ctp.mReadingDeviceReference = ctc.mReadingDeviceReference
AND ctp.mReadingMeterType = ctc.mReadingMeterType
AND ctp.RN = 1 -- Use first reading found to get a so far value...
JOIN meterTypes MT
on ctc.mReadingMeterType = mTypeReference
where ctc.RN in (select max(RN) from cteTemp)
ORDER BY ty, ctc.mReadingMeterType
end
Please find below more suitable data as requested.
CREATE TABLE #meterReading(
[mReadingCallReference] [int] NULL,
[mReadingDeviceReference] [int] NULL,
[mReadingReadingType] [varchar](4) NULL,
[mReadingMeterReadingDate] [datetime] NULL,
[mReadingMeterType] [tinyint] NULL,
[mReadingMeterReading] [int] NULL,
[mReadingStatus] [tinyint] NULL
) ON [PRIMARY]
insert into #meterReading
([mReadingCallReference],[mReadingDeviceReference],[mReadingReadingType],
[mReadingMeterReadingDate],[mReadingMeterType],[mReadingMeterReading],
[mReadingStatus])
Select '916282','96789','INIT','Dec 1 2003 12:00AM','1','0','6', UNION ALL
Select '916193','96789','R ','Dec 8 2003 2:40PM','1','579','6', UNION ALL
Select '923743','96789','CARD','Jan 31 2004 12:00AM','1','1000','6', UNION ALL
Select '1512116','96789','CARD','Apr 30 2004 12:00AM','1','2000','6', UNION ALL
Select '1523431','96789','R ','Aug 6 2004 10:50AM','1','179228','6', UNION ALL
Select '1529779','96789','CARD','Jul 31 2004 12:00AM','1','6000','6', UNION ALL
Select '1537213','96789','R ','Oct 7 2004 3:05PM','1','216907','6', UNION ALL
Select '1537500','96789','DN ','Oct 14 2004 12:00AM','1','216907','6', UNION ALL
Select '1537500','96789','DN ','Oct 14 2004 12:00AM','2','0','5', UNION ALL
Select '1533153','96789','R ','Sep 22 2004 11:00AM','1','208903','6', UNION ALL
Select '1531987','96789','R ','Sep 16 2004 9:45AM','1','206507','6', UNION ALL
Select '1551109','96789','RICO','Oct 31 2004 12:00AM','1','231755','6', UNION ALL
Select '1561148','96789','R ','Jan 27 2005 10:20AM','1','278813','6', UNION ALL
Select '1570597','96789','CARD','Jan 31 2005 12:00AM','1','278838','6', UNION ALL
Select '1573099','96789','DN ','Mar 11 2005 12:00AM','1','278838','6', UNION ALL
Select '1573099','96789','DN ','Mar 11 2005 12:00AM','2','0','5', UNION ALL
Select '1586842','96789','R ','May 13 2005 4:45PM','1','350378','6', UNION ALL
Select '1586170','96789','DN ','May 12 2005 12:00AM','1','278838','6', UNION ALL
Select '1586170','96789','DN ','May 12 2005 12:00AM','2','0','5', UNION ALL
Select '1588762','96789','DN ','May 23 2005 12:00AM','1','350378','6', UNION ALL
Select '1588762','96789','DN ','May 23 2005 12:00AM','2','0','5', UNION ALL
Select '1592521','96789','CARD','Apr 30 2005 12:00AM','1','338523','6', UNION ALL
Select '1612444','96789','TF ','Sep 13 2005 3:10PM','1','422588','6', UNION ALL
Select '1612444','96789','TF ','Sep 13 2005 3:10PM','2','0','5', UNION ALL
Select '1610254','96789','CARD','Jul 31 2005 12:00AM','1','403067','6', UNION ALL
Select '1611741','96789','R ','Sep 9 2005 11:20AM','1','422588','6', UNION ALL
Select '1620713','96789','R ','Oct 20 2005 3:45PM','1','447970','6', UNION ALL
Select '1627175','96789','R ','Nov 17 2005 2:55PM','1','464599','6', UNION ALL
Select '1630749','96789','CARD','Oct 31 2005 12:00AM','1','449396','6', UNION ALL
Select '1641634','96789','R ','Feb 1 2006 11:45AM','1','485445','6', UNION ALL
Select '1648820','96789','CARD','Jan 31 2006 12:00AM','1','484079','6', UNION ALL
Select '1658664','96789','DN ','Apr 20 2006 12:00AM','1','510740','6', UNION ALL
Select '1658664','96789','DN ','Apr 20 2006 12:00AM','2','0','5', UNION ALL
Select '1657948','96789','R ','Apr 12 2006 3:45PM','1','510740','6', UNION ALL
Select '1667746','96789','CARD','Apr 30 2006 12:00AM','1','513092','6', UNION ALL
Select '1669577','96789','R ','Jun 9 2006 12:25PM','1','538455','6', UNION ALL
Select '1684443','96789','CARD','Jul 31 2006 12:00AM','1','552852','6', UNION ALL
Select '1693145','96789','RO ','Oct 12 2006 11:20AM','1','576047','6', UNION ALL
Select '1690170','96789','DN ','Oct 3 2006 12:00AM','1','552852','6', UNION ALL
Select '1690170','96789','DN ','Oct 3 2006 12:00AM','2','0','5', UNION ALL
Select '1703823','96789','CARD','Oct 31 2006 12:00AM','1','580778','6', UNION ALL
Select '1704397','96789','RO ','Dec 4 2006 3:45PM','1','591894','6', UNION ALL
Select '1714173','96789','RO ','Feb 1 2007 11:50AM','1','605310','6', UNION ALL
Select '1716287','96789','CA ','Feb 9 2007 11:40AM','1','607718','6', UNION ALL
Select '1716075','96789','R ','Feb 7 2007 11:45AM','1','607641','6', UNION ALL
Select '1721041','96789','CARD','Jan 31 2007 12:00AM','1','595000','6', UNION ALL
Select '1726282','96789','RO ','Mar 29 2007 10:05AM','1','621880','6', UNION ALL
Select '1740577','96789','RO ','Jun 7 2007 3:45PM','1','643174','6', UNION ALL
Select '1738987','96789','CARD','Apr 30 2007 12:00AM','1','627868','6', UNION ALL
Select '1737814','96789','RO ','May 29 2007 4:00PM','1','637761','6', UNION ALL
Select '1745021','96789','RO ','Jul 3 2007 2:45PM','1','651652','6', UNION ALL
Select '1744589','96789','RO ','Jun 29 2007 12:00PM','1','644120','6', UNION ALL
Select '1756798','96789','CARD','Jul 31 2007 12:00AM','1','656000','6', UNION ALL
Select '1753523','96789','R ','Aug 16 2007 10:10AM','1','665685','6', UNION ALL
Select '1774992','96789','CARD','Oct 31 2007 12:00AM','1','687995','6', UNION ALL
Select '1779889','96789','RO ','Jan 4 2008 9:50AM','1','706696','6', UNION ALL
Select '1792947','96789','RICO','Jan 31 2008 12:00AM','1','700000','6', UNION ALL
Select '1809730','96789','CARD','Apr 30 2008 12:00AM','1','740137','6', UNION ALL
Select '1829270','96789','CARD','Jul 31 2008 12:00AM','1','768387','6', UNION ALL
Select '1918796','96789','FINA','Jan 22 2010 12:00AM','1','768387','5', UNION ALL
Select '1925115','96789','FINA','Jan 23 2010 12:00AM','1','768387','5', UNION ALL
Select '1694696','112525','WF ','Jan 22 2009 1:40PM','1','404','6', UNION ALL
Select '1694696','112525','WF ','Jan 22 2009 1:40PM','2','1482','5', UNION ALL
Select '1694524','112525','DN ','Dec 19 2008 12:00AM','1','0','6', UNION ALL
Select '1694524','112525','DN ','Dec 19 2008 12:00AM','2','0','5', UNION ALL
Select '1694526','112525','LI ','Dec 19 2008 12:30PM','1','19','6', UNION ALL
Select '1694526','112525','LI ','Dec 19 2008 12:30PM','2','52','5', UNION ALL
Select '1694525','112525','I ','Dec 19 2008 10:40AM','1','19','6', UNION ALL
Select '1694525','112525','I ','Dec 19 2008 10:40AM','2','52','5', UNION ALL
Select '1693301','112525','PREP','Dec 18 2008 3:30PM','1','11','6', UNION ALL
Select '1693301','112525','PREP','Dec 18 2008 3:30PM','2','20','5', UNION ALL
Select '1703711','112525','DN ','Feb 16 2009 12:00AM','1','2827','6', UNION ALL
Select '1703711','112525','DN ','Feb 16 2009 12:00AM','2','10045','5', UNION ALL
Select '1702679','112525','R ','Feb 5 2009 12:40PM','1','2827','6', UNION ALL
Select '1702679','112525','R ','Feb 5 2009 12:40PM','2','10045','5', UNION ALL
Select '1696630','112525','DN ','Jan 12 2009 12:00AM','1','404','6', UNION ALL
Select '1696630','112525','DN ','Jan 12 2009 12:00AM','2','1482','5', UNION ALL
Select '1696628','112525','R ','Jan 8 2009 3:00PM','1','404','6', UNION ALL
Select '1696628','112525','R ','Jan 8 2009 3:00PM','2','1482','5', UNION ALL
Select '1698407','112525','INIT','Jan 7 2009 12:00AM','1','19','6', UNION ALL
Select '1698407','112525','INIT','Jan 7 2009 12:00AM','2','52','6', UNION ALL
Select '1715541','112525','EMAI','Mar 25 2009 12:00AM','1','7630','6', UNION ALL
Select '1715541','112525','EMAI','Mar 25 2009 12:00AM','2','31307','5', UNION ALL
Select '1714564','112525','X ','Mar 24 2009 5:10PM','1','7630','6', UNION ALL
Select '1714564','112525','X ','Mar 24 2009 5:10PM','2','31307','5', UNION ALL
Select '1713209','112525','DN ','Mar 19 2009 12:00AM','1','2827','6', UNION ALL
Select '1713209','112525','DN ','Mar 19 2009 12:00AM','2','10045','5', UNION ALL
Select '1732644','112525','DN ','Jun 24 2009 12:00AM','1','13297','6', UNION ALL
Select '1732644','112525','DN ','Jun 24 2009 12:00AM','2','57459','5', UNION ALL
Select '1732643','112525','PO ','Jun 24 2009 12:00AM','1','13297','6', UNION ALL
Select '1732643','112525','PO ','Jun 24 2009 12:00AM','2','57459','5', UNION ALL
Select '1731627','112525','EMAI','Jun 10 2009 12:00AM','1','13297','6', UNION ALL
Select '1731627','112525','EMAI','Jun 10 2009 12:00AM','2','57459','5', UNION ALL
Select '1724950','112525','DN ','May 21 2009 12:00AM','1','7630','6', UNION ALL
Select '1724950','112525','DN ','May 21 2009 12:00AM','2','31307','5', UNION ALL
Select '1752269','112525','DN ','Sep 21 2009 12:00AM','1','18548','6', UNION ALL
Select '1752269','112525','DN ','Sep 21 2009 12:00AM','2','83223','5', UNION ALL
Select '1747953','112525','FAX ','Aug 31 2009 12:00AM','1','18548','6', UNION ALL
Select '1747953','112525','FAX ','Aug 31 2009 12:00AM','2','83223','5', UNION ALL
Select '1747276','112525','DN ','Sep 7 2009 12:00AM','1','18548','6', UNION ALL
Select '1747276','112525','DN ','Sep 7 2009 12:00AM','2','83223','5', UNION ALL
Select '1756671','112525','R ','Oct 2 2009 3:55PM','1','21838','6', UNION ALL
Select '1756671','112525','R ','Oct 2 2009 3:55PM','2','98998','5', UNION ALL
Select '1766130','112525','PO ','Nov 16 2009 12:00AM','1','24406','6', UNION ALL
Select '1766130','112525','PO ','Nov 16 2009 12:00AM','2','111810','5', UNION ALL
Select '1766732','112525','FAX ','Oct 31 2009 12:00AM','1','24406','6', UNION ALL
Select '1766732','112525','FAX ','Oct 31 2009 12:00AM','2','111810','5', UNION ALL
Select '1776770','112525','R ','Dec 23 2009 12:15PM','1','27990','6', UNION ALL
Select '1776770','112525','R ','Dec 23 2009 12:15PM','2','126702','5', UNION ALL
Select '1776897','112525','DN ','Jan 4 2010 12:00AM','1','27990','6', UNION ALL
Select '1776897','112525','DN ','Jan 4 2010 12:00AM','2','126702','5', UNION ALL
Select '1789930','112525','FAX ','Feb 28 2010 12:00AM','1','32119','6', UNION ALL
Select '1789930','112525','FAX ','Feb 28 2010 12:00AM','2','146233','5', UNION ALL
Select '1802943','112525','R ','Apr 12 2010 8:25AM','1','35735','6', UNION ALL
Select '1802943','112525','R ','Apr 12 2010 8:25AM','2','164646','5', UNION ALL
Select '1805663','112525','R ','Apr 23 2010 10:45AM','1','36851','6', UNION ALL
Select '1805663','112525','R ','Apr 23 2010 10:45AM','2','169792','5', UNION ALL
Select '1809103','112525','DN ','May 10 2010 12:00AM','1','36851','5', UNION ALL
Select '1809103','112525','DN ','May 10 2010 12:00AM','2','169792','5', UNION ALL
Select '1811255','112525','EMAI','Apr 30 2010 12:00AM','1','38211','6', UNION ALL
Select '1811255','112525','EMAI','Apr 30 2010 12:00AM','2','175642','5', UNION ALL
Select '1822121','112525','DN ','Jul 2 2010 12:00AM','1','41829','5', UNION ALL
Select '1822121','112525','DN ','Jul 2 2010 12:00AM','2','195308','5', UNION ALL
Select '1819005','112525','DN ','Jun 24 2010 12:00AM','1','36851','5', UNION ALL
Select '1819005','112525','DN ','Jun 24 2010 12:00AM','2','169792','5', UNION ALL
Select '1816004','112525','PO ','Jun 14 2010 12:00AM','1','36851','5', UNION ALL
Select '1816004','112525','PO ','Jun 14 2010 12:00AM','2','169792','5', UNION ALL
Select '1832946','112525','PO ','Aug 16 2010 12:00AM','1','41829','5', UNION ALL
Select '1832946','112525','PO ','Aug 16 2010 12:00AM','2','195308','5', UNION ALL
Select '1731183','113474','REFB','Jun 23 2009 12:21PM','1','84381','6', UNION ALL
Select '1731183','113474','REFB','Jun 23 2009 12:21PM','2','280170','5', UNION ALL
Select '1733954','113474','REFB','Jun 24 2009 5:28PM','1','84452','6', UNION ALL
Select '1733954','113474','REFB','Jun 24 2009 5:28PM','2','280189','5', UNION ALL
Select '1734406','113474','DN ','Jun 26 2009 12:00AM','1','84453','6', UNION ALL
Select '1734406','113474','DN ','Jun 26 2009 12:00AM','2','280191','5', UNION ALL
Select '1734407','113474','LI ','Jun 26 2009 3:20PM','1','84453','6', UNION ALL
Select '1734407','113474','LI ','Jun 26 2009 3:20PM','2','280191','5', UNION ALL
Select '1734569','113474','PI ','Jun 26 2009 11:38AM','1','84452','6', UNION ALL
Select '1734569','113474','PI ','Jun 26 2009 11:38AM','2','280189','5', UNION ALL
Select '1734536','113474','WF ','Jul 20 2009 9:38AM','1','84546','6', UNION ALL
Select '1734536','113474','WF ','Jul 20 2009 9:38AM','2','282115','5', UNION ALL
Select '1736847','113474','R ','Jul 7 2009 4:05PM','1','84546','6', UNION ALL
Select '1736847','113474','R ','Jul 7 2009 4:05PM','2','282115','5', UNION ALL
Select '1735259','113474','INIT','Jun 3 2009 12:00AM','1','84453','6', UNION ALL
Select '1735259','113474','INIT','Jun 3 2009 12:00AM','2','280191','6', UNION ALL
Select '1735676','113474','PAGE','Jun 26 2009 12:00AM','1','84453','6', UNION ALL
Select '1735676','113474','PAGE','Jun 26 2009 12:00AM','2','280191','5', UNION ALL
Select '1739351','113474','PAGE','Jul 7 2009 12:00AM','1','84546','6', UNION ALL
Select '1739351','113474','PAGE','Jul 7 2009 12:00AM','2','282115','5', UNION ALL
Select '1752904','113474','R ','Sep 17 2009 10:40AM','1','87500','6', UNION ALL
Select '1752904','113474','R ','Sep 17 2009 10:40AM','2','293614','5', UNION ALL
Select '1766985','113474','CB ','Nov 17 2009 11:45AM','1','90624','6', UNION ALL
Select '1766985','113474','CB ','Nov 17 2009 11:45AM','2','303444','5', UNION ALL
Select '1766987','113474','DN ','Nov 11 2009 12:00AM','1','90452','6', UNION ALL
Select '1766987','113474','DN ','Nov 11 2009 12:00AM','2','302651','5', UNION ALL
Select '1767082','113474','TELE','Nov 11 2009 12:00AM','1','90369','6', UNION ALL
Select '1767082','113474','TELE','Nov 11 2009 12:00AM','2','302584','5', UNION ALL
Select '1766885','113474','R ','Nov 11 2009 1:20PM','1','90452','6', UNION ALL
Select '1766885','113474','R ','Nov 11 2009 1:20PM','2','302651','5', UNION ALL
Select '1769751','113474','DN ','Nov 30 2009 12:00AM','1','90624','6', UNION ALL
Select '1769751','113474','DN ','Nov 30 2009 12:00AM','2','303444','5', UNION ALL
Select '1779350','113474','PAGE','Nov 12 2009 12:00AM','1','90452','6', UNION ALL
Select '1779350','113474','PAGE','Nov 12 2009 12:00AM','2','302651','5', UNION ALL
Select '1779064','113474','PAGE','Nov 17 2009 12:00AM','1','90624','6', UNION ALL
Select '1779064','113474','PAGE','Nov 17 2009 12:00AM','2','303444','5', UNION ALL
Select '1796739','113474','DN ','Mar 15 2010 12:00AM','1','90624','6', UNION ALL
Select '1796739','113474','DN ','Mar 15 2010 12:00AM','2','303444','5', UNION ALL
Select '1797367','113474','DN ','Mar 18 2010 12:00AM','1','90624','6', UNION ALL
Select '1797367','113474','DN ','Mar 18 2010 12:00AM','2','303444','5', UNION ALL
Select '1797583','113474','DN ','Mar 18 2010 12:00AM','1','90624','6', UNION ALL
Select '1797583','113474','DN ','Mar 18 2010 12:00AM','2','303444','5', UNION ALL
Select '1809810','113474','PAGE','Apr 29 2010 12:00AM','1','101132','6', UNION ALL
Select '1809810','113474','PAGE','Apr 29 2010 12:00AM','2','331358','5', UNION ALL
Select '1806851','113474','R ','Apr 29 2010 1:04PM','1','101132','6', UNION ALL
Select '1806851','113474','R ','Apr 29 2010 1:04PM','2','331358','5', UNION ALL
Select '1828007','113474','FINA','Jun 25 2010 12:00AM','1','101132','5', UNION ALL
Select '1828007','113474','FINA','Jun 25 2010 12:00AM','2','331358','5'
The requirement is to find the latest reading on a machine, and find the average number of copies that have been taken between two readings that were at least a set period apart. These are at least 1 whole month, 3 whole months, six months and a year, then the last calculation is the whole life of the machine by going back to the earliest reading.
Thank you
Jason Shaw
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply