Procedure to extract data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Procedure to extract data

Author

Topic

Sanchit297
Starting Member

16 Posts

Posted - 2009-11-16 : 12:45:15

I am new to T-SQL programming and would be great if someone could assist me in writing a SQL proc based on the requirements mentioned below.


Location  BranchID   Items   Longest_Elapsed_Time  Avg_Elapsed_Time
-------   --------- ------   --------------------  ----------------
Delhi      1          345    2009-10-09 00:05:00      00:45:80
           2          500    2009-10-18 00:02:10      00:76:70
           3          1345   2009-10-02 00:55:75      00:45:40
           4          200    2009-10-29 00:23:24      00:15:50
Delhi                 2390   2009-10-02 00:55:75      xyz

(Same as above for rest of Locations)

As it is clear from the above representation, that i require to build an SP to get this sort of data. All this data is available in a table present in database.

The Longest_Elapsed_Time can be calculated by subtracting "Database time" from "ScanDateTime". And similarly the Avg_Elapsed_Time by calculating the Average of the "Longest_Elapsed_Time" captured for every BranchID in every location.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-16 : 13:50:25

Well...you've posted what the expected results shold be...how about the table DDL and what the sample data in that table is to produce the result

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Sanchit297
Starting Member

16 Posts

Posted - 2009-11-17 : 01:19:36

Brett,

The table DDL is as mentioned below and is using a Clustered Index as well.


CREATE TABLE [ImportData] (
	[BranchID] [int] NULL ,
	[BranchName] [char] (13) COLLATE Latin1_General_CI_AS NULL ,
	[BranchType] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
	[BranchTypeId] [int] ,
	[BranchCode] [char] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Location] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
	[DatabaseDateTime] [datetime] NULL ,
	[ScanDateTime] [datetime] NULL
) ON [PRIMARY]

CREATE CLUSTERED INDEX CX_ImportData ON dbo.ImportData
	(
	BranchCode,
	ScanDateTime
	) ON [PRIMARY]

However, in the output i do not want all fields (only the one's mentioned in the Expected Output above).

I want this to be implemented for about 25000 BranchID and about 1900 Locations i.e. each location will be shown having their respective BranchID's (as shown in the expected output above).

Longest Elapsed Time = "DatabaseDateTime" - "ScanDateTime"
Average Elapsed Time = Avg(Longest Elapsed Time) for every location level.
Items = Total no of BranchCodes received by every BranchID

Sanchit.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources