Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    8

    Unanswered: Help for Extract data Procedure...

    I am new to T-SQL programming and would be great if someone could provide a short code sample to write a SP based on the requirements mentioned below.

    Code:
    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 table DDL is as mentioned below and is using a Clustered Index as well.

    Code:
    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

    I have attached an excel sheet inside the Word doc below, it will be clear from that as to what i require.

    Sankur.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This should be a very simple SELECT statement that can be represented as a view rather than a stored procedure, UNLESS you want to actually output all the data rows with the total as an additional row at the bottom. If this is the case, then your whole approach is wrong.
    Please clarify the output you are seeking.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2009
    Posts
    8
    Blindman,

    Yes - i do require the output as shown in the excel sheet(attached) as this is the actual requirement.

    This a a part of a bigger task to Export all the data rows to Excel.

    It would be great if you could assist via short code sample of how to go about the task.

    Sankur.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This type of logic does not belong in a database.
    You are trying to format output, and that is the job of the presentation layer. You could cobble together some sql code to do this (ok, you couldn't, but I could), but it would and EXTREMELY bad design.

    The proper way to do this would be to output the data as a simple recordset, link to it through Excel, and then use spreadsheet Pivot tables to slice/dice/summarize the data how ever the user desires. That is what Pivot tables and spreadsheets are for.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2009
    Posts
    8
    Blindman,

    You are right - I forgot to attach another sheet in the Excel which I had attached in my post.

    Basically I want to get data from SQL table (dbo.ImportData) to a "RawData" sheet in an Excel and would then try to use that RawData sheet to provide Summary for each location at BranchID level in another excel sheet.

    So consider that I want to Extract data from SQL table to a Sheet in Excel. The RawData sheet columns would be:

    Code:
    Location
    BranchID
    BranchCode
    Longest Elapsed Time
    Average Elapsed Time
    I would first want to get started with the SQL part and would then proceed to "Spreadsheet Pivot tables ".

    Hope it helps.

    Sankur.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't understand why you think you need a stored procedure, or even a view or a select statement.
    Just link Excel to the table directly, unless you need to filter the result set.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2009
    Posts
    8
    Blindman,

    I have an Execute task in a DTS package which creates the Table - ImportData to fetch data from a source to the table.

    For making this report (format which ive shown in my attachment), i require to calculate the Longest Elapsed time for every BranchID (per location) and Average Elapsed Time for every BranchID (per location). And then the summary row for each Location showing Total BranchCode and Longest/Average Elapsed Time(from values calculated above).

    Currently from the table, i do not get the Longest Elapsed time and Average Elapsed Time. I need to calculatye it from the 2 columns which are already being fetched - ScanDatetime and DatabaseDatetime.

    What could be done in this case ?

    Sankur.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    My last word on this subject: do it in your Excel pivot table. That is what Excel excels at. Max, Min, Average, and other summary data calculations.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •