Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Rewrite this SQL SP to return all records

Posted on 2009-12-18
4
Medium Priority
?
311 Views
Last Modified: 2012-05-08
I need to rewrite this SP to return the data for every GPRSDeviceHardwareID


IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[GetPercentageOfMaintenanceCycleUsedByGPRSDeviceHardwareID]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GetPercentageOfMaintenanceCycleUsedByGPRSDeviceHardwareID];
GO
CREATE PROCEDURE [dbo].[GetPercentageOfMaintenanceCycleUsedByGPRSDeviceHardwareID]
(
     @GPRSDeviceHardwareID As varchar(50)
)
AS

DECLARE @X As Int
DECLARE @Y As Int
DECLARE @A As Int
DECLARE @B As Int
DECLARE @HL As Int
DECLARE @Z As Int
DECLARE @ZZ As Double Precision
DECLARE @ZZZ As Double Precision
DECLARE @PercentageUsed As Int


BEGIN

            SET @X = (SELECT [PMHourMeter] FROM [PMSchedule] WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
            
            SET @A = (SELECT MAX(RunningTotal) FROM HourMeter WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID AND MaintenanceBit = 0)

            SET @B =(SELECT TotalHourMeterDifference FROM HourMeterDifference WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID AND MaintenanceBit = 0)

            Set @Y = (@A + @B) / 60
            
            Set @HL = (@X - @Y)
            
            SET @Z = (SELECT [Hours] FROM [GPRSDevice] WHERE [GPRSDeviceHardwareID] = @GPRSDeviceHardwareID)

            Set @ZZ = (@Z - @HL)

            Set @ZZZ = ((@ZZ / @Z) * 100)

            SET @PercentageUsed = @ZZZ

RETURN @PercentageUsed

END
GO
0
Comment
Question by:MBoy
  • 3
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26083868
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[GetPercentageOfMaintenanceCycleUsedByGPRSDeviceHardwareID]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GetPercentageOfMaintenanceCycleUsedByGPRSDeviceHardwareID];
GO
CREATE PROCEDURE [dbo].[GetPercentageOfMaintenanceCycleUsedByGPRSDeviceHardwareID]
(
     @GPRSDeviceHardwareID As varchar(50)
)
AS

BEGIN

 
  SELECT p.GPRSDeviceHardwareID,  ( g.Hours - ( p.[PMHourMeter] - ( (RunningTotal+h.TotalHourMeterDifference) /60 ) ) ) / g.Hours *100
  FROM [PMSchedule] p
  LEFT JOIN  
  (SELECT GPRSDeviceHardwareID, MAX(RunningTotal)RunningTotal FROM HourMeter WHERE MaintenanceBit = 0 GROUP BY GPRSDeviceHardwareID )a  on a.GPRSDeviceHardwareID = p.GPRSDeviceHardwareID
  LEFT JOIN HourMeterDifference h on h.GPRSDeviceHardwareID = p.GPRSDeviceHardwareID  and h.MaintenanceBit = 0
  INNER JOIN [GPRSDevice] g on g.GPRSDeviceHardwareID = p.GPRSDeviceHardwareID
   
END
GO
0
 
LVL 3

Author Comment

by:MBoy
ID: 26091529
I need to rewrite this SP to return the data for every GPRSDeviceHardwareID

"No @GPRSDeviceHardwareID As varchar(50) "

Return GPRSDeviceHardwareID, PercentageOfMaintenanceCycleUsed for each device
0
 
LVL 3

Author Comment

by:MBoy
ID: 26108501
Any ideas?
0
 
LVL 3

Author Comment

by:MBoy
ID: 26113219
Help me - I've fallen and I can't get up.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question