Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2005
    Posts
    5

    Thumbs up Unanswered: A Great Sybase Procedure using Dynamic Query

    Hi,

    Here is a great Dynamic query procedure which results summarised data in tabular form. You will love to see it. The procedure takes 6 parameters (table_name, column for left side date, column for date, numeric column for summary,value of date from, value of date to) respectively.

    Here is the procedure Result

    Item 2006/01 2006/02 2006/03 Total
    ------- ---------------- ---------------- ---------------- ----------------
    CHIP-D 17488952 28414788 23003495 68907235
    CHIP-S 0 2938335 12245869 15184204
    CHIP-T 650004 0 337699 987703
    CHIPTS 9771 0 632277 642048
    DO-35 585941 1831419 2018691 4436051
    DO-41 59108 96544 509136 664788
    TO-126 1011056 866169 3996900 5874125
    TO-18 18891 18921 197982 235794
    TO-220 81938 218279 546591 846808
    TO-237 4434 2877 0 7311
    TO-3 2325 9812 11032 23169
    TO-39 2048 5937 142443 150428
    TO-72 0 1936 602 2538
    TO-92 949213 2260458 8555957 11765628
    TO-92S 393961 1275163 400111 2069235
    TO126S 116247 48597 213702 378546
    TO220F 0 3505 11510 15015
    TO220S 5439 5055 9882 20376
    Total 21379328 37997795 52833879 112211002

    The Procedure for the above is

    drop proc MthQy
    go
    create proc MthQy (
    @table_name char(16)='stg', /* Name Of The Table */
    @col_left char(16)='pkgdesc', /* Column That Appears In left side of the Result Of Query */
    @col_date char(16)='dated', /* Column Name For Date On Which Months Derived */
    @col_data char(16)='qty', /* Numeric Column Whose Sum Has to be calculated */
    @mf char(11)='01 apr 2005', /* Value Of Date From */
    @mt char(11)='31 mar 2006' /* Value Of Date To */
    ) as

    declare @stmnt varchar(8000),@mmon char(7),@mcut int,@j smallint,@k smallint
    select @table_name=ltrim(rtrim(@table_name))
    select @col_left=ltrim(rtrim(@col_left))
    select @col_date=ltrim(rtrim(@col_date))
    select @col_data=ltrim(rtrim(@col_data))

    if (select id from sysobjects where name=@table_name and type='U') is null
    begin
    select "<<Table "+rtrim(@table_name)+" Does Not Exists in the Current Database>>"
    return
    end

    if (select colid from syscolumns where id=object_id(@table_name) and name=@col_left) is null
    begin
    select "<<No Such column '"+rtrim(@col_left)+"' In the table '"+rtrim(@table_name)+"'>>"
    return
    end

    if (select colid from syscolumns where id=object_id(@table_name) and name=@col_date) is null
    begin
    select "<<No Such column '"+rtrim(@col_date)+"' In the table '"+rtrim(@table_name)+"'>>"
    return
    end

    if (select colid from syscolumns where id=object_id(@table_name) and name=@col_data) is null
    begin
    select "<<No Such column '"+rtrim(@col_data)+"' In the table '"+rtrim(@table_name)+"'>>"
    return
    end

    create table #tmp (col_left char(16) null,mon char(7) null,qty numeric(14))
    select @stmnt="insert into #tmp select "+rtrim(@col_left)
    select @stmnt=@stmnt+",convert(char(7),"+rtrim(@col_date) +",111)"
    select @stmnt=@stmnt+",sum("+rtrim(@col_data)+") from "+rtrim(@table_name)
    select @stmnt=@stmnt+" where dated between '"+@mf+"' and '"+@mt+"'"
    select @stmnt=@stmnt+" group by "+rtrim(@col_left)+",convert(char(7),"+rtrim(@col_ date)+",111)"

    /* select @stmnt */

    exec (@stmnt)

    create table #tmp1(mmyy char(7),no numeric(3) identity)
    insert into #tmp1 select mon from #tmp group by mon
    select @j=0
    select @k=isnull(max(no),0) from #tmp1

    select @stmnt="select col_left "+rtrim(@col_left)

    while (@j<@k)
    begin
    select @j=@j+1
    select @mmon=mmyy from #tmp1 where no=@j
    select @stmnt=@stmnt+",convert(numeric(14),sum(qty*charin dex(mon,'"+@mmon+"'))) '"+@mmon+"'"
    end
    select @stmnt=@stmnt+",convert(numeric(14),sum(qty)) Total"
    select @stmnt=@stmnt+" from #tmp"
    select @mcut=charindex(',',@stmnt)
    select @stmnt=@stmnt+" group by col_left union all select 'Total',"+substring(@stmnt,@mcut+1,char_length(@st mnt)-@mcut)
    --select @stmnt
    exec (@stmnt)
    drop table #tmp
    drop table #tmp1
    return
    go

    MthQy @table_name='sold',
    @col_left='devic',
    @col_date='dated',
    @col_data='qty',
    @mf='01 oct 2005',
    @mt='31 may 2006'

  2. #2
    Join Date
    Jul 2005
    Posts
    5
    Yes, It is.

Posting Permissions

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