Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    4

    Unanswered: Help me understand this T-SQL Query

    I need help understanding this query below, somebody wrote it and is not with the company anymore. I know what this query is doing, but need to understand how it works. This query is iterative and runs through call record databases from 10-28-2009 to 11-27-2009. Each date has its own separate call record database, the query is written for one database and made to run through all the 30 databases lying in that date range. I need help understand how to run the same query run for 30 databases iteratively.

    Thank you all for your help.


    -- Declare the starting and the ending date for which the query will run

    DECLARE @StartDate varchar(10), @EndDate varchar(10)
    SELECT @StartDate='2009-10-28'
    SELECT @EndDate='2009-11-27'


    --
    DECLARE @EndDt datetime, @StartDt datetime, @date datetime, @sql varchar(max)
    SELECT @EndDt = CONVERT(datetime, @EndDate)
    SELECT @StartDt = CONVERT(datetime, @StartDate)

    SELECT @date = @StartDt


    -- PLEASE EXPLAIN ME THIS PART OF LOOPING,

    SELECT @sql = ''

    WHILE (@date <= @EndDate)
    BEGIN

    IF(@date = @StartDt)
    BEGIN
    SELECT @sql = @sql + '
    SELECT ''' + CONVERT(nvarchar(10), @date, 120) + ''' As CDRDate
    ,SUM(c.[Duration]) as [Duration]
    ,CAST(CAST(SUM(c.[Duration]) As float)/60 as decimal(18,2)) As [Minutes]
    FROM [CDR_Billing_Telica_' + CONVERT(varchar(10), @date, 120) + '].[dbo].[cdr_main] as c
    WHERE c.[ocn]=''7229'' AND LATA=''730'' AND c.OutTrunkGroupName IN (''GW150722'',
    ''GW150723'',
    ''GW150729'',
    ''GW150730'',
    ''GW150731'',
    ''GW150741'')
    '
    END
    ELSE
    BEGIN
    SELECT @sql = @sql + '
    UNION ALL
    SELECT ''' + CONVERT(nvarchar(10), @date, 120) + ''' As CDRDate
    ,SUM(c.[Duration]) as [Duration]
    ,CAST(CAST(SUM(c.[Duration]) As float)/60 as decimal(18,2)) As [Minutes]
    FROM [CDR_Billing_Telica_' + CONVERT(varchar(10), @date, 120) + '].[dbo].[cdr_main] as c
    WHERE c.[ocn]=''7229'' AND LATA=''730''AND c.OutTrunkGroupName IN (''GW150722'',
    ''GW150723'',
    ''GW150729'',
    ''GW150730'',
    ''GW150731'',
    ''GW150741'')
    '
    END

    SELECT @date = DATEADD(dd, 1, @date) -- basically increment by 1 day
    END

    EXEC(@sql)

  2. #2
    Join Date
    Oct 2009
    Posts
    27
    Please use the commented procedure. So that you can understood the meaning.

    -- Declare the starting and the ending date for which the query will run
    DECLARE @StartDate varchar(10), @EndDate varchar(10)
    SELECT @StartDate='2009-10-28'
    SELECT @EndDate='2009-11-27'


    DECLARE @EndDt datetime, @StartDt datetime, @date datetime, @sql varchar(max)
    SELECT @EndDt = CONVERT(datetime, @EndDate)
    SELECT @StartDt = CONVERT(datetime, @StartDate)

    --Here the Startdate ie 2009-10-28 assigned to @date
    SELECT @date = @StartDt

    SELECT @sql = ''

    --Here the while loop starts with the date 2009-10-28 and ends with the date 2009-11-27
    --Each time incremented by one day
    WHILE (@date <= @EndDate)
    BEGIN
    --For the first time @date = @StartDt this condition satisfies
    --but the end of this loop @dateis added by one day @date = DATEADD(dd, 1, @date)
    --So the nect time the IF condition fails and go for the ELSE part
    IF(@date = @StartDt)
    BEGIN
    --In this case there must be a database named "CDR_Billing_Telica_2009-10-28" in your server
    --with the table [dbo].[cdr_main] and there is a column named "2009-10-28" , that column is selected by the name CDRDate
    --So the column name and a part of database name is dynamically generated by the variable @date
    --And the selected statement is not executed it is assigned to a variable @sql(It will be excuted after the loop completes)
    SELECT @sql = @sql + '
    SELECT ''' + CONVERT(nvarchar(10), @date, 120) + ''' As CDRDate
    ,SUM(c.[Duration]) as [Duration]
    ,CAST(CAST(SUM(c.[Duration]) As float)/60 as decimal(18,2)) As [Minutes]
    FROM [CDR_Billing_Telica_' + CONVERT(varchar(10), @date, 120) + '].[dbo].[cdr_main] as c
    WHERE c.[ocn]=''7229'' AND LATA=''730'' AND c.OutTrunkGroupName IN (''GW150722'',
    ''GW150723'',
    ''GW150729'',
    ''GW150730'',
    ''GW150731'',
    ''GW150741'')
    '
    END
    ELSE
    BEGIN
    --Else part comes after the first date up to the end of the date
    --Each time the date is incremented by one day and the query is builded same like the IF part
    --But the query starts with UNION ALL word. So for each date select statement is added to the variable @sql
    SELECT @sql = @sql + '
    UNION ALL
    SELECT ''' + CONVERT(nvarchar(10), @date, 120) + ''' As CDRDate
    ,SUM(c.[Duration]) as [Duration]
    ,CAST(CAST(SUM(c.[Duration]) As float)/60 as decimal(18,2)) As [Minutes]
    FROM [CDR_Billing_Telica_' + CONVERT(varchar(10), @date, 120) + '].[dbo].[cdr_main] as c
    WHERE c.[ocn]=''7229'' AND LATA=''730''AND c.OutTrunkGroupName IN (''GW150722'',
    ''GW150723'',
    ''GW150729'',
    ''GW150730'',
    ''GW150731'',
    ''GW150741'')
    '
    END

    SELECT @date = DATEADD(dd, 1, @date) -- basically increment by 1 day
    END --end for while loop

    --Finally @sql is dynamically executed. It gets the datas from the 30 databases
    EXEC(@sql)


    __________________
    SQL Server Programmers and Consultants
    Microsoft SQL Server Consultants and Developers - SQL Programmers Chicago

  3. #3
    Join Date
    Dec 2009
    Posts
    4
    Thank you for replying back, your comments are really helpful and I seem to have understood most part of this query. The only point which is still unclear to me is: Why do we use

    SELECT @sql = ''

    -- and then use

    SELECT @sql = @sql + '

    --under the If statement??

    I am not sure what @sql is doing in the query? Thanks for your help

  4. #4
    Join Date
    Oct 2009
    Posts
    27
    Run the following script

    DECLARE @sql VARCHAR(MAX)
    SELECT @sql

    The result is "NULL"

    But after the following assignment

    SELECT @sql = ''

    The result is blank string.

    If we didn't assign the blank string to that @sql variable and if we add something to that variable it result NULL value.

    SELECT @sql = @sql + 'some more text'

    Result the NULL value.
    Because of this the assignment as follows,

    SELECT @sql = ''

    Are you understood now. Run the scripts in query window, so that you can understand.


    SQL Server Programmers and Consultants
    Microsoft SQL Server Consultants and Developers - SQL Programmers Chicago

  5. #5
    Join Date
    Dec 2009
    Posts
    4
    Thanks, I played with the query and have understood it. Really appreciate your help.

Posting Permissions

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