Solved

limiting a report to a date range

Posted on 2006-11-17
17
287 Views
Last Modified: 2012-06-21
Hello, I am trying to make a report retunr only results based on a date entered by the user.

i am using regular asp, and sql server 2000.

i made a coupe of post in the asp forum, but it was recommended to make another post here.

i have no expertise on this subject, and i really need help.

currently this report works, i just need to add the search by date range functionality.
the stored procedure has code to pull numbers for today, this month and this year.

i don't need this columns though.

i am postnig my code for the stored proc. as well as for my asp page.

i apologize because this is a long post. please let me know if this needs a higher point value, and if
to get help for this problem is a feasible idea.

thank you in advanced,

itortu.
0
Comment
Question by:itortu
  • 11
  • 6
17 Comments
 

Author Comment

by:itortu
ID: 17968754
asp code:

<%@ Language=VBScript %>
<%Option Explicit
  Response.Expires = -1
%>
<!--#include file="includes/Database.asp"-->
<%
'Globals
Const PAGE_NAME  = "LOL_Stats.asp"

Dim strSortField
Dim sDate
Dim eDate



Initialize
Main
CleanUp

private function BuildTableHeaders

      '/// Build table headers (two rows)
      Dim strGroup, strStation, strSubsMonth, strSubsDay, strSubsYear, strSubsTotal, strDMATotal, strDMAMonth, strDMADay, strDMAYear, strDMA

      'Build links for sorting
      strDMA       = "<a href=""" & PAGE_NAME & "?SortField=dma"" title=""Click to sort by DMA"">DMA</a>"
      strGroup     = "<a href=""" & PAGE_NAME & "?SortField=group"" title=""Click to sort by Group"">Group</a>"
      strStation   = "<a href=""" & PAGE_NAME & "?SortField=host"" title=""Click to sort by Station"">Station</a>"
      strSubsDay   = "<a href=""" & PAGE_NAME & "?SortField=subsday"" title=""Click to sort by subs Today"">Downloads</a>"
      strDMADay    = "<a href=""" & PAGE_NAME & "?SortField=dmaday"" title=""Click to sort by DMA% change Today"">DMA% " & "</a>"

      'Don't display a link for the currently sorted column
      select case strSortField
            case "dma"
                  strDMA = "DMA"
            case "group"
                  strGroup = "Group"
            case "host"
                  strStation = "Station"
            case "subsday"
                  strSubsDay = "Downloads"
            case "dmaday"
                  strDMADay = "DMA% "
            case else
                  strStation = "Station"
      end select

      'Construct the table rows
      BuildTableHeaders = "<tr>" & _
                                    "<td class=""Header2"" width=""6%"">" & strStation & "</td>" & _
                                    "<td class=""Header2"" width=""24%"">" & strGroup & "</td>" & _
                                    "<td class=""Header2"" width=""6%"">" & strDMA & "</td>" & _
                                    "<td class=""Header2"" width=""4%"">&nbsp;</td>" & _
                                    "<td class=""Header2"" width=""4%"">" & strSubsDay & "</font></td>" & _
                                    "<td class=""Header2"" width=""2%"">&nbsp;</font></td>" & _
                                    "<td class=""Header2"" width=""9%"">" & strDMADay & "</td>" & _
                                    "</tr>" & vbCrLf
end function

private function BuildTableRow(ByVal strGroup, ByVal strHost, ByVal lngTotal, ByVal dblTotalDMA, lngDay, ByVal dblDayDMA, ByVal lngMonth, ByVal dblMonthDMA, ByVal lngYear, ByVal dblYearDMA, ByVal lngDMA, ByRef strCellColor1, ByRef strCellColor2)
      '/// Build single table row
      GetCellColors strCellColor1, strCellColor2

      if lngDay = "" or isnull(lngDay) then
                lngDay = 0
      end if

      if dblDayDMA = "" or isnull(dblDayDMA) then
        dblDayDMA = 0
      end if

      BuildTableRow =      "<tr>" & _
                              "<td width=""6%"" class=""" & strCellColor1 & """>" & strHost & "</td>" & _
                              "<td width=""24%"" class=""" & strCellColor1 & """>" & strGroup & "</td>" & _
                              "<td width=""6%"" class=""" & strCellColor1 & """ align=""right"">" & FormatNumber((lngDMA), 0) & "</td>" & _
                              "<td width=""4%"" class=""" & strCellColor1 & """>&nbsp;</td>" & _
                              "<td width=""4%"" class=""" & strCellColor2 & """ align=""right"">" & FormatNumber((lngDay), 0) & "</td>" & _
                              "<td width=""2%"" class=""" & strCellColor2 & """>&nbsp;</td>" & _
                              "<td width=""9%"" class=""" & strCellColor2 & """>" & FormatPercent(dblDayDMA) & "</td>" & _
                              "</tr>" & vbCrLf
end function

private function BuildTableRows(ByRef lngTotal, ByRef lngDay, ByRef lngMonth, ByRef lngYear, ByRef lngNumStations, ByRef lngDMA)
      '/// Build all table rows
      Dim RS, lngCurrentGroup, lngCurrentHost, lngCurrentTotal, lngCurrentDay, lngCurrentMonth, lngCurrentYear, lngCurrentDMA, strCellColor1, strCellColor2
      Dim dblTotalDMA, dblYearDMA, dblMonthDMA, dblDayDMA

    Set RS = db.Execute("spLOL_date_range'" & strSortField & "'")

      with RS
            while not .EOF
                  if Response.IsClientConnected then
                        lngCurrentGroup = Trim(.Fields("HostGroup"))
                        lngCurrentHost  = Trim(.Fields("Host"))
                        lngCurrentDay   = .Fields("Day")
                        lngCurrentDMA   = .Fields("DMA")
                        dblDayDMA       = .Fields("DayDMA")
                        Response.Write vbCrLf & vbTab & vbTab & vbTab & vbTab & BuildTableRow(lngCurrentGroup, lngCurrentHost, lngCurrentTotal, dblTotalDMA, lngCurrentDay, dblDayDMA, lngCurrentMonth, dblMonthDMA, lngCurrentYear, dblYearDMA, lngCurrentDMA, strCellColor1, strCellColor2)
                        UpdateTotals lngCurrentTotal, lngCurrentDay, lngCurrentMonth, lngCurrentYear, lngCurrentDMA, lngTotal, lngDay, lngMonth, lngYear, lngDMA, lngNumStations
                        .MoveNext
                  else
                        .Close
                        Set RS = Nothing
                        CleanUp
                        Response.End
                  end if
            wend
            .Close
      end with
      Set RS = Nothing
end function

private function BuildTotalsRow(ByVal lngTotal, ByVal lngDay, ByVal lngMonth, ByVal lngYear, ByVal lngNumStations, ByVal lngDMA)
      '/// Build column totals row
      BuildTotalsRow =     "<tr>" & _
                                    "<td width=""6%"" class=""Header"">" & lngNumStations & "</td>" & _
                                    "<td width=""24%"" class=""Header"">&nbsp;</td>" & _
                                    "<td width=""6%"" class=""Header"" align=""right""><b>" & FormatNumber((lngDMA),0) & "</b></td>" & _
                                    "<td width=""4%"" class=""Header"">&nbsp;</td>" & _
                                    "<td width=""4%"" class=""Header"" align=""right""><b>" & FormatNumber((lngDay), 0) & "</b></td>" & _
                                    "<td width=""2%"" class=""Header"">&nbsp;</td>"
                                  If lngDMA = 0 Then
                                      BuildTotalsRow = BuildTotalsRow & "<td width=""9%"" class=""Header"">" & 0 & "</td>"
                                  Else
                                      BuildTotalsRow = BuildTotalsRow & "<td width=""9%"" class=""Header"">" & FormatPercent(lngDay / lngDMA) & "</td>"
                                  End If



    BuildTotalsRow = BuildTotalsRow & "</tr>" & vbCrLf

end function

private sub CleanUp
      '/// Destroy database object
      if IsObject(db) then
            if not db Is Nothing then
                  CloseDatabase db
            end if
      end if
end sub

private function FormatPercent(ByVal dblData)
      '/// Return dblData as a string in the form: 9.999%
      FormatPercent = FormatNumber(dblData * 100, 3) & "%"
end function

private sub GetCellColors(ByRef strCellColor1, strCellColor2)
      '/// Toggle between two sets of colors for table cells
      if strCellColor1 = "Gray1" then
            'Switch to dark row colors
            strCellColor1 = "Gray2"
            strCellColor2 = "DarkGray1"
      else
            'Switch to light row colors
            strCellColor1 = "Gray1"
            strCellColor2 = "DarkGray2"
      end if
end sub

private sub Initialize
      '/// Create database object and parse querystring
      Set db = OpenDatabase

      'Column to sort by
      strSortField = LCase(Trim(Request.QueryString("SortField")))
end sub

private sub Main
      '/// Build the page
      Dim lngTotal, lngDay, lngMonth, lngYear, lngNumStations, lngDMA 'Column totals
      %>
      <html>
            <head>
                  <title>:LIVE Online Download Report</title>
                  <LINK REL="stylesheet" TYPE="text/css" HREF="includes/Stylesheets/StationReports.css">
            </head>

            <body>

                  <h2 align="center">:LIVE Online Download Report</h2>
                  <h4 align="center"><%=FormatDateTime(Date(),1)%></h4>

                  <table width="100%" border="1" cellpadding="4" cellspacing="0">
      <%
      'Display column headers
      Response.Write BuildTableHeaders

      'Display rows (one per station)
      Response.Write BuildTableRows(lngTotal, lngDay, lngMonth, lngYear, lngNumStations, lngDMA)

      'Display totals
      Response.Write BuildTotalsRow(lngTotal, lngDay, lngMonth, lngYear, lngNumStations, lngDMA)
      %>
                  </table>
                  <form name="myform" method="POST">
                      <div align="center">
                      <br><br>
                        <label for="sdate">Start Date:</label><input type="text" size="10" name="sdate" id="sdate">
                        <label for="edate">End Date:</label><input type="text" size="10" name="edate" id="edate">
                        <input type="submit" value="Send" name="btn_submit"><br>
                      </div>
            </form>
            </body>
      </html>
      <%
end sub

private sub UpdateTotals(ByVal lngCurrentTotal, ByVal lngCurrentDay, ByVal lngCurrentMonth, ByVal lngCurrentYear, ByVal lngCurrentDMA, ByRef lngTotal, ByRef lngDay, ByRef lngMonth, ByRef lngYear, ByRef lngDMA, ByRef lngNumStations)
      '/// Add new row values to running column totals
      lngTotal       = lngTotal + lngCurrentTotal
      lngDay         = lngDay + lngCurrentDay
      lngMonth       = lngMonth + lngCurrentMonth
      lngYear        = lngYear + lngCurrentYear
      lngNumStations = lngNumStations + 1
      lngDMA         = lngDMA + lngCurrentDMA
end sub
%>
0
 

Author Comment

by:itortu
ID: 17968768
stored procedure:

ALTER    PROCEDURE "spLOL_date_range"(@SortField NVARCHAR(50)=NULL)
AS
BEGIN
      SET NOCOUNT ON

      DECLARE @CurrentDate DATETIME
      DECLARE @StartDay DATETIME
      DECLARE @EndDay DATETIME
      DECLARE @StartMonth DATETIME
      DECLARE @EndMonth DATETIME
      DECLARE @StartYear DATETIME
      DECLARE @EndYear DATETIME
      DECLARE @HostGroup NVARCHAR(200)
      DECLARE @Host NVARCHAR(50)
      DECLARE @Total INT
      DECLARE @TotalDMA FLOAT
      DECLARE @Day INT
      DECLARE @DayDMA FLOAT
      DECLARE @Month INT
      DECLARE @MonthDMA FLOAT
      DECLARE @Year INT
      DECLARE @YearDMA FLOAT
      DECLARE @DMA INT
      DECLARE @ErrorFlag BIT
      DECLARE @Result TABLE      (
                        HostGroup NVARCHAR(200),
                        Host NVARCHAR(50),
                        Total INT,
                        TotalDMA FLOAT,
                        [Day] INT,
                        DayDMA FLOAT,
                        [Month] INT,
                        MonthDMA FLOAT,
                        [Year] INT,
                        YearDMA FLOAT,
                        DMA INT
                        )

      -- Calculate start/end dates: a "day" is defined as 2:00AM-2:00AM CST.
      SET @CurrentDate = GETDATE()
      SET @StartDay    = (SELECT DATEADD(HOUR, 2, CAST(CAST(DATEPART(MONTH, @CurrentDate)AS VARCHAR(2)) + '/' + CAST(DATEPART(DAY, @CurrentDate)AS VARCHAR(2)) + '/' + CAST(DATEPART(YEAR, @CurrentDate)AS VARCHAR(4))AS DATETIME)))
      SET @EndDay      = (SELECT DATEADD(DAY, 1, @StartDay))
      SET @StartMonth  = (SELECT DATEADD(HOUR, 2, CAST(CAST(DATEPART(MONTH, @CurrentDate)AS VARCHAR(2)) + '/1/' + CAST(DATEPART(YEAR, @CurrentDate)AS VARCHAR(4))AS DATETIME)))
      SET @EndMonth    = (SELECT DATEADD(MONTH, 1, @StartMonth))
      SET @StartYear   = (SELECT DATEADD(HOUR, 2, CAST('1/1/' + CAST(DATEPART(YEAR, @CurrentDate)AS VARCHAR(4))AS DATETIME)))
      SET @EndYear     = (SELECT DATEADD(YEAR, 1, @StartYear))

      -- Retrieve/calculate stats for each launched station
      DECLARE host_cursor CURSOR
      FOR
            SELECT StationName
            FROM [xxx.xxx.x.xx].stationreport.dbo.lol_launched_stations
            WHERE StationName in (SELECT Host
            FROM LiveOnline.dbo.LiveOnlineStats
            WHERE (StatsDate >= '01/04/2005' AND StatsDate <= @CurrentDate))

      OPEN host_cursor
      
      FETCH NEXT FROM host_cursor INTO @Host
      WHILE (@@FETCH_STATUS <> -1)
      BEGIN
            IF (@@FETCH_STATUS <> -2)
            BEGIN      
                  SET @DMA       = (SELECT Population FROM myweather.dbo.Host WHERE CallString = @Host)
                  SET @HostGroup = (SELECT hg.Name FROM [xxx.xxx.x.xx].stationreport.dbo.host_group_value hg, myweather.dbo.Host h WHERE hg.GroupID = h.GroupID AND h.CallString = @Host)
                  SET @Total     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1)
                          IF @DMA = 0    
                  BEGIN
                        SET @TotalDMA  = 0      
                        SET @Day       = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND CONVERT(varchar(12), actionenddate, 102) = CONVERT(varchar(12), getdate() - 1, 102))
                        SET @DayDMA    = 0      
                        SET @Month     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND MONTH(actionenddate) = MONTH(getdate()) AND YEAR(actionenddate) = YEAR(getdate()))
                        SET @MonthDMA  = 0      
                        SET @Year      = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND YEAR(actionenddate) = YEAR(getdate()))
                        SET @YearDMA   = 0      
                  END
                  ELSE
                  IF @DMA <> 0
                  BEGIN
                        SET @TotalDMA  = (CAST(@Total AS FLOAT) / CAST(@DMA AS FLOAT))
                        SET @Day       = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND CONVERT(varchar(12), actionenddate, 102) = CONVERT(varchar(12), getdate() - 1, 102))
                        SET @DayDMA    = (CAST(@Day AS FLOAT) / CAST(@DMA AS FLOAT))
                        SET @Month     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND MONTH(actionenddate) = MONTH(getdate()) AND YEAR(actionenddate) = YEAR(getdate()))
                        SET @MonthDMA  = (CAST(@Month AS FLOAT) / CAST(@DMA AS FLOAT))
                        SET @Year      = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND YEAR(actionenddate) = YEAR(getdate()))
                        SET @YearDMA   = (CAST(@Year AS FLOAT) / CAST(@DMA AS FLOAT))
                  END

                  INSERT INTO @Result (HostGroup, Host, Total, TotalDMA, Day, DayDMA, Month, MonthDMA, Year, YearDMA, DMA)
                  VALUES(@HostGroup, @Host, @Total, @TotalDMA, @Day, @DayDMA, @Month, @MonthDMA, @Year, @YearDMA, @DMA)

                  IF (@@ERROR <> 0)
                  BEGIN
                        SET @ErrorFlag = 1
                  END
            END                        
            FETCH NEXT FROM host_cursor INTO @Host
      END
      
      CLOSE host_cursor
      DEALLOCATE host_cursor

      IF (@ErrorFlag <> 0)
      BEGIN
            PRINT 'Error!'
      END
      ELSE
      BEGIN
            -- Sort the results by the field specified in @SortField.  
                -- If @SortField is invalid or wasn't specified then sort by month.
            IF @SortField = 'subsmonth'
            BEGIN
                  SELECT * FROM @Result ORDER BY [Month] DESC
            END
            ELSE
            IF @SortField = 'dmamonth'
            BEGIN
                  SELECT * FROM @Result ORDER BY MonthDMA DESC
            END
            ELSE
            IF @SortField = 'subsday'
            BEGIN
                  SELECT * FROM @Result ORDER BY [Day] DESC
            END            
            ELSE
            IF @SortField = 'dmaday'
            BEGIN
                  SELECT * FROM @Result ORDER BY DayDMA DESC
            END            
            ELSE
            IF @SortField = 'subsyear'
            BEGIN
                  SELECT * FROM @Result ORDER BY [Year] DESC
            END            
            ELSE
            IF @SortField = 'dmayear'
            BEGIN
                  SELECT * FROM @Result ORDER BY YearDMA DESC
            END            
            ELSE
            IF @SortField = 'substotal'
            BEGIN
                  SELECT * FROM @Result ORDER BY Total DESC
            END            
            ELSE
            IF @SortField = 'dmatotal'
            BEGIN
                  SELECT * FROM @Result ORDER BY TotalDMA DESC
            END            
            ELSE
            IF @SortField = 'host'
            BEGIN
                  SELECT * FROM @Result ORDER BY Host
            END            
            ELSE
            IF @SortField = 'group'
            BEGIN
                  SELECT * FROM @Result ORDER BY HostGroup
            END            
            ELSE
            IF @SortField = 'dma'
            BEGIN
                  SELECT * FROM @Result ORDER BY DMA DESC
            END            
            ELSE
            BEGIN
                  SELECT * FROM @Result ORDER BY [Month] DESC
            END
      END

END
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17970809
check for @date in the stored procedure thats the change i made

ALTER    PROCEDURE "spLOL_date_range"(@SortField NVARCHAR(50)=NULL, @date datetime = null)
AS
BEGIN
     SET NOCOUNT ON

     DECLARE @CurrentDate DATETIME
     DECLARE @StartDay DATETIME
     DECLARE @EndDay DATETIME
     DECLARE @StartMonth DATETIME
     DECLARE @EndMonth DATETIME
     DECLARE @StartYear DATETIME
     DECLARE @EndYear DATETIME
     DECLARE @HostGroup NVARCHAR(200)
     DECLARE @Host NVARCHAR(50)
     DECLARE @Total INT
     DECLARE @TotalDMA FLOAT
     DECLARE @Day INT
     DECLARE @DayDMA FLOAT
     DECLARE @Month INT
     DECLARE @MonthDMA FLOAT
     DECLARE @Year INT
     DECLARE @YearDMA FLOAT
     DECLARE @DMA INT
     DECLARE @ErrorFlag BIT
     DECLARE @Result TABLE     (
                    HostGroup NVARCHAR(200),
                    Host NVARCHAR(50),
                    Total INT,
                    TotalDMA FLOAT,
                    [Day] INT,
                    DayDMA FLOAT,
                    [Month] INT,
                    MonthDMA FLOAT,
                    [Year] INT,
                    YearDMA FLOAT,
                    DMA INT
                    )

     -- Calculate start/end dates: a "day" is defined as 2:00AM-2:00AM CST.
     SET @CurrentDate = GETDATE()
     SET @StartDay    = (SELECT DATEADD(HOUR, 2, CAST(CAST(DATEPART(MONTH, @CurrentDate)AS VARCHAR(2)) + '/' + CAST(DATEPART(DAY, @CurrentDate)AS VARCHAR(2)) + '/' + CAST(DATEPART(YEAR, @CurrentDate)AS VARCHAR(4))AS DATETIME)))
     SET @EndDay      = (SELECT DATEADD(DAY, 1, @StartDay))
     SET @StartMonth  = (SELECT DATEADD(HOUR, 2, CAST(CAST(DATEPART(MONTH, @CurrentDate)AS VARCHAR(2)) + '/1/' + CAST(DATEPART(YEAR, @CurrentDate)AS VARCHAR(4))AS DATETIME)))
     SET @EndMonth    = (SELECT DATEADD(MONTH, 1, @StartMonth))
     SET @StartYear   = (SELECT DATEADD(HOUR, 2, CAST('1/1/' + CAST(DATEPART(YEAR, @CurrentDate)AS VARCHAR(4))AS DATETIME)))
     SET @EndYear     = (SELECT DATEADD(YEAR, 1, @StartYear))

     -- Retrieve/calculate stats for each launched station
     DECLARE host_cursor CURSOR
     FOR
            SELECT StationName
            FROM [xxx.xxx.x.xx].stationreport.dbo.lol_launched_stations
            WHERE StationName in (SELECT Host
            FROM LiveOnline.dbo.LiveOnlineStats
            WHERE (StatsDate >= @date AND StatsDate <= @CurrentDate))

     OPEN host_cursor
     
     FETCH NEXT FROM host_cursor INTO @Host
     WHILE (@@FETCH_STATUS <> -1)
     BEGIN
          IF (@@FETCH_STATUS <> -2)
          BEGIN    
               SET @DMA       = (SELECT Population FROM myweather.dbo.Host WHERE CallString = @Host)
               SET @HostGroup = (SELECT hg.Name FROM [xxx.xxx.x.xx].stationreport.dbo.host_group_value hg, myweather.dbo.Host h WHERE hg.GroupID = h.GroupID AND h.CallString = @Host)
               SET @Total     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1)
                         IF @DMA = 0    
               BEGIN
                    SET @TotalDMA  = 0    
                    SET @Day       = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND CONVERT(varchar(12), actionenddate, 102) = CONVERT(varchar(12), getdate() - 1, 102))
                    SET @DayDMA    = 0    
                    SET @Month     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND MONTH(actionenddate) = MONTH(getdate()) AND YEAR(actionenddate) = YEAR(getdate()))
                    SET @MonthDMA  = 0    
                    SET @Year      = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND YEAR(actionenddate) = YEAR(getdate()))
                    SET @YearDMA   = 0    
               END
               ELSE
               IF @DMA <> 0
               BEGIN
                    SET @TotalDMA  = (CAST(@Total AS FLOAT) / CAST(@DMA AS FLOAT))
                    SET @Day       = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND CONVERT(varchar(12), actionenddate, 102) = CONVERT(varchar(12), getdate() - 1, 102))
                    SET @DayDMA    = (CAST(@Day AS FLOAT) / CAST(@DMA AS FLOAT))
                    SET @Month     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND MONTH(actionenddate) = MONTH(getdate()) AND YEAR(actionenddate) = YEAR(getdate()))
                    SET @MonthDMA  = (CAST(@Month AS FLOAT) / CAST(@DMA AS FLOAT))
                    SET @Year      = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND YEAR(actionenddate) = YEAR(getdate()))
                    SET @YearDMA   = (CAST(@Year AS FLOAT) / CAST(@DMA AS FLOAT))
               END

               INSERT INTO @Result (HostGroup, Host, Total, TotalDMA, Day, DayDMA, Month, MonthDMA, Year, YearDMA, DMA)
               VALUES(@HostGroup, @Host, @Total, @TotalDMA, @Day, @DayDMA, @Month, @MonthDMA, @Year, @YearDMA, @DMA)

               IF (@@ERROR <> 0)
               BEGIN
                    SET @ErrorFlag = 1
               END
          END                    
          FETCH NEXT FROM host_cursor INTO @Host
     END
     
     CLOSE host_cursor
     DEALLOCATE host_cursor

     IF (@ErrorFlag <> 0)
     BEGIN
          PRINT 'Error!'
     END
     ELSE
     BEGIN
          -- Sort the results by the field specified in @SortField.  
                -- If @SortField is invalid or wasn't specified then sort by month.
          IF @SortField = 'subsmonth'
          BEGIN
               SELECT * FROM @Result ORDER BY [Month] DESC
          END
          ELSE
          IF @SortField = 'dmamonth'
          BEGIN
               SELECT * FROM @Result ORDER BY MonthDMA DESC
          END
          ELSE
          IF @SortField = 'subsday'
          BEGIN
               SELECT * FROM @Result ORDER BY [Day] DESC
          END          
          ELSE
          IF @SortField = 'dmaday'
          BEGIN
               SELECT * FROM @Result ORDER BY DayDMA DESC
          END          
          ELSE
          IF @SortField = 'subsyear'
          BEGIN
               SELECT * FROM @Result ORDER BY [Year] DESC
          END          
          ELSE
          IF @SortField = 'dmayear'
          BEGIN
               SELECT * FROM @Result ORDER BY YearDMA DESC
          END          
          ELSE
          IF @SortField = 'substotal'
          BEGIN
               SELECT * FROM @Result ORDER BY Total DESC
          END          
          ELSE
          IF @SortField = 'dmatotal'
          BEGIN
               SELECT * FROM @Result ORDER BY TotalDMA DESC
          END          
          ELSE
          IF @SortField = 'host'
          BEGIN
               SELECT * FROM @Result ORDER BY Host
          END          
          ELSE
          IF @SortField = 'group'
          BEGIN
               SELECT * FROM @Result ORDER BY HostGroup
          END          
          ELSE
          IF @SortField = 'dma'
          BEGIN
               SELECT * FROM @Result ORDER BY DMA DESC
          END          
          ELSE
          BEGIN
               SELECT * FROM @Result ORDER BY [Month] DESC
          END
     END

END
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17970812
change this in asp code3
  Set RS = db.Execute("spLOL_date_range'" & strSortField & "','" & strSortField & "'")
0
 

Author Comment

by:itortu
ID: 17979798
hello, for the asp code

why do i use the strSortField variable two times?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17985331
>>why do i use the strSortField variable two times?
sorry not the strSortField the other one is the date parameter

Set RS = db.Execute("spLOL_date_range'" & strSortField & "','" & DateParam & "'")
0
 

Author Comment

by:itortu
ID: 17987540
hello imran_fast,

first of all, thank you for your help. i truly appreciate it.

I am still having some doubts about how to incorporate your solution to work along the asp code.
i mean, i added a form on the page with 2 input fields, one for the start date, and one for the end date
besides that, i don't know how to get the dates entered, and then pass the values to the stored procedure execute.

do you think you can take a look at the asp code, and tell me how to do this?

thank you.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17987602
Hi,

If you are entering start date and end date then you have to change the asp code as well as the sp

Asp Code
========
Set RS = db.Execute("spLOL_date_range'" & strSortField & "','" &  StartDateParam &  "','" & EndDateParam & "'")


stored procedure
==============
ALTER    PROCEDURE "spLOL_date_range"(@SortField NVARCHAR(50)=NULL, @startDate datetime = null, @EndDate datetime = null)
AS
BEGIN
     SET NOCOUNT ON

     DECLARE @CurrentDate DATETIME
     DECLARE @StartDay DATETIME
     DECLARE @EndDay DATETIME
     DECLARE @StartMonth DATETIME
     DECLARE @EndMonth DATETIME
     DECLARE @StartYear DATETIME
     DECLARE @EndYear DATETIME
     DECLARE @HostGroup NVARCHAR(200)
     DECLARE @Host NVARCHAR(50)
     DECLARE @Total INT
     DECLARE @TotalDMA FLOAT
     DECLARE @Day INT
     DECLARE @DayDMA FLOAT
     DECLARE @Month INT
     DECLARE @MonthDMA FLOAT
     DECLARE @Year INT
     DECLARE @YearDMA FLOAT
     DECLARE @DMA INT
     DECLARE @ErrorFlag BIT
     DECLARE @Result TABLE     (
                    HostGroup NVARCHAR(200),
                    Host NVARCHAR(50),
                    Total INT,
                    TotalDMA FLOAT,
                    [Day] INT,
                    DayDMA FLOAT,
                    [Month] INT,
                    MonthDMA FLOAT,
                    [Year] INT,
                    YearDMA FLOAT,
                    DMA INT
                    )

     -- Calculate start/end dates: a "day" is defined as 2:00AM-2:00AM CST.
     SET @CurrentDate = GETDATE()
     SET @StartDay    = (SELECT DATEADD(HOUR, 2, CAST(CAST(DATEPART(MONTH, @CurrentDate)AS VARCHAR(2)) + '/' + CAST(DATEPART(DAY, @CurrentDate)AS VARCHAR(2)) + '/' + CAST(DATEPART(YEAR, @CurrentDate)AS VARCHAR(4))AS DATETIME)))
     SET @EndDay      = (SELECT DATEADD(DAY, 1, @StartDay))
     SET @StartMonth  = (SELECT DATEADD(HOUR, 2, CAST(CAST(DATEPART(MONTH, @CurrentDate)AS VARCHAR(2)) + '/1/' + CAST(DATEPART(YEAR, @CurrentDate)AS VARCHAR(4))AS DATETIME)))
     SET @EndMonth    = (SELECT DATEADD(MONTH, 1, @StartMonth))
     SET @StartYear   = (SELECT DATEADD(HOUR, 2, CAST('1/1/' + CAST(DATEPART(YEAR, @CurrentDate)AS VARCHAR(4))AS DATETIME)))
     SET @EndYear     = (SELECT DATEADD(YEAR, 1, @StartYear))

     -- Retrieve/calculate stats for each launched station
     DECLARE host_cursor CURSOR
     FOR
            SELECT StationName
            FROM [xxx.xxx.x.xx].stationreport.dbo.lol_launched_stations
            WHERE StationName in (SELECT Host
            FROM LiveOnline.dbo.LiveOnlineStats
            WHERE (StatsDate >= @startDate AND StatsDate <= @endDate))

     OPEN host_cursor
     
     FETCH NEXT FROM host_cursor INTO @Host
     WHILE (@@FETCH_STATUS <> -1)
     BEGIN
          IF (@@FETCH_STATUS <> -2)
          BEGIN    
               SET @DMA       = (SELECT Population FROM myweather.dbo.Host WHERE CallString = @Host)
               SET @HostGroup = (SELECT hg.Name FROM [xxx.xxx.x.xx].stationreport.dbo.host_group_value hg, myweather.dbo.Host h WHERE hg.GroupID = h.GroupID AND h.CallString = @Host)
               SET @Total     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1)
                         IF @DMA = 0    
               BEGIN
                    SET @TotalDMA  = 0    
                    SET @Day       = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND CONVERT(varchar(12), actionenddate, 102) = CONVERT(varchar(12), getdate() - 1, 102))
                    SET @DayDMA    = 0    
                    SET @Month     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND MONTH(actionenddate) = MONTH(getdate()) AND YEAR(actionenddate) = YEAR(getdate()))
                    SET @MonthDMA  = 0    
                    SET @Year      = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND YEAR(actionenddate) = YEAR(getdate()))
                    SET @YearDMA   = 0    
               END
               ELSE
               IF @DMA <> 0
               BEGIN
                    SET @TotalDMA  = (CAST(@Total AS FLOAT) / CAST(@DMA AS FLOAT))
                    SET @Day       = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND CONVERT(varchar(12), actionenddate, 102) = CONVERT(varchar(12), getdate() - 1, 102))
                    SET @DayDMA    = (CAST(@Day AS FLOAT) / CAST(@DMA AS FLOAT))
                    SET @Month     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND MONTH(actionenddate) = MONTH(getdate()) AND YEAR(actionenddate) = YEAR(getdate()))
                    SET @MonthDMA  = (CAST(@Month AS FLOAT) / CAST(@DMA AS FLOAT))
                    SET @Year      = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND YEAR(actionenddate) = YEAR(getdate()))
                    SET @YearDMA   = (CAST(@Year AS FLOAT) / CAST(@DMA AS FLOAT))
               END

               INSERT INTO @Result (HostGroup, Host, Total, TotalDMA, Day, DayDMA, Month, MonthDMA, Year, YearDMA, DMA)
               VALUES(@HostGroup, @Host, @Total, @TotalDMA, @Day, @DayDMA, @Month, @MonthDMA, @Year, @YearDMA, @DMA)

               IF (@@ERROR <> 0)
               BEGIN
                    SET @ErrorFlag = 1
               END
          END                    
          FETCH NEXT FROM host_cursor INTO @Host
     END
     
     CLOSE host_cursor
     DEALLOCATE host_cursor

     IF (@ErrorFlag <> 0)
     BEGIN
          PRINT 'Error!'
     END
     ELSE
     BEGIN
          -- Sort the results by the field specified in @SortField.  
                -- If @SortField is invalid or wasn't specified then sort by month.
          IF @SortField = 'subsmonth'
          BEGIN
               SELECT * FROM @Result ORDER BY [Month] DESC
          END
          ELSE
          IF @SortField = 'dmamonth'
          BEGIN
               SELECT * FROM @Result ORDER BY MonthDMA DESC
          END
          ELSE
          IF @SortField = 'subsday'
          BEGIN
               SELECT * FROM @Result ORDER BY [Day] DESC
          END          
          ELSE
          IF @SortField = 'dmaday'
          BEGIN
               SELECT * FROM @Result ORDER BY DayDMA DESC
          END          
          ELSE
          IF @SortField = 'subsyear'
          BEGIN
               SELECT * FROM @Result ORDER BY [Year] DESC
          END          
          ELSE
          IF @SortField = 'dmayear'
          BEGIN
               SELECT * FROM @Result ORDER BY YearDMA DESC
          END          
          ELSE
          IF @SortField = 'substotal'
          BEGIN
               SELECT * FROM @Result ORDER BY Total DESC
          END          
          ELSE
          IF @SortField = 'dmatotal'
          BEGIN
               SELECT * FROM @Result ORDER BY TotalDMA DESC
          END          
          ELSE
          IF @SortField = 'host'
          BEGIN
               SELECT * FROM @Result ORDER BY Host
          END          
          ELSE
          IF @SortField = 'group'
          BEGIN
               SELECT * FROM @Result ORDER BY HostGroup
          END          
          ELSE
          IF @SortField = 'dma'
          BEGIN
               SELECT * FROM @Result ORDER BY DMA DESC
          END          
          ELSE
          BEGIN
               SELECT * FROM @Result ORDER BY [Month] DESC
          END
     END

END
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:itortu
ID: 17987731
ok i am going to work on it, and will let you know if i was able to do finish it. thank you very much.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17987779
Good Luck!
0
 

Author Comment

by:itortu
ID: 17988007
wel,, i added the changes to the asp code, and to the stored proc.
nothing broke, and i was able to submit the page, the problem, is that
i am getting the same numbers all the time no matter what dates i enter.

on the asp page i am using only 5 fields:

Station       Group  DMA  Downloads  DMA%

this select is on the asp code: (just to give you the idea of whcih fileds are used from the stored proc.)

select case strSortField
    case "dma"
        strDMA = "DMA"
    case "group"
      strGroup = "Group"
    case "host"
      strStation = "Station"
    case "subsday"
        strSubsDay = "Downloads"
    case "dmaday"
      strDMADay = "DMA% "
    case else
        strStation = "Station"
end select

as i mentioned before, i have no expertise on sql server or asp, i am just trying to work with what i have, and with help from EE (otherwise I would be in big desperation)
by looking at the stored procedrue code, i see that dates are calculate for day, month, and year totals, my question is this one, is there a way to eliminate from the stored procedure month and year, and leave only the day total numbers, and make these based on the passed dates?

i will take a screen shot of the page and uploaded, i'll post the link in a minute.

let me know if you think you will be able to help me a litte bit more.

thanks a lot.

itortu.
0
 

Author Comment

by:itortu
ID: 17988077
0
 

Author Comment

by:itortu
ID: 17988089
sorry for blurring some of the names on the report jpeg image i just want to avoid any type of complications here on EE.

i hope u understand.

thanks.
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 500 total points
ID: 17993988
Try this

ALTER    PROCEDURE "spLOL_date_range"(@SortField NVARCHAR(50)=NULL, @startDate datetime = null, @EndDate datetime = null)
AS
BEGIN
     SET NOCOUNT ON

     DECLARE @CurrentDate DATETIME
     DECLARE @StartDay DATETIME
     DECLARE @EndDay DATETIME
     DECLARE @StartMonth DATETIME
     DECLARE @EndMonth DATETIME
     DECLARE @StartYear DATETIME
     DECLARE @EndYear DATETIME
     DECLARE @HostGroup NVARCHAR(200)
     DECLARE @Host NVARCHAR(50)
     DECLARE @Total INT
     DECLARE @TotalDMA FLOAT
     DECLARE @Day INT
     DECLARE @DayDMA FLOAT
     DECLARE @Month INT
     DECLARE @MonthDMA FLOAT
     DECLARE @Year INT
     DECLARE @YearDMA FLOAT
     DECLARE @DMA INT
     DECLARE @ErrorFlag BIT
     DECLARE @Result TABLE     (
                    HostGroup NVARCHAR(200),
                    Host NVARCHAR(50),
                    Total INT,
                    TotalDMA FLOAT,
                    [Day] INT,
                    DayDMA FLOAT,
                    [Month] INT,
                    MonthDMA FLOAT,
                    [Year] INT,
                    YearDMA FLOAT,
                    DMA INT
                    )



     -- Retrieve/calculate stats for each launched station
     DECLARE host_cursor CURSOR
     FOR
            SELECT StationName
            FROM [xxx.xxx.x.xx].stationreport.dbo.lol_launched_stations
            WHERE StationName in (SELECT Host
            FROM LiveOnline.dbo.LiveOnlineStats
            WHERE (StatsDate between @startDate AND @endDate))

     OPEN host_cursor
     
     FETCH NEXT FROM host_cursor INTO @Host
     WHILE (@@FETCH_STATUS <> -1)
     BEGIN
          IF (@@FETCH_STATUS <> -2)
          BEGIN    
               SET @DMA       = (SELECT Population FROM myweather.dbo.Host WHERE CallString = @Host)
               SET @HostGroup = (SELECT hg.Name FROM [xxx.xxx.x.xx].stationreport.dbo.host_group_value hg, myweather.dbo.Host h WHERE hg.GroupID = h.GroupID AND h.CallString = @Host)
               SET @Total     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1)
                         IF @DMA = 0    
               BEGIN
                    SET @TotalDMA  = 0    
                    SET @Day       = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND  actionenddate  between @startDate AND @endDate)
                    SET @DayDMA    = 0    
                    SET @Month     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND  actionenddate  between @startDate AND @endDate)
                    SET @MonthDMA  = 0    
                    SET @Year      = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND   actionenddate  between @startDate AND @endDate)
                    SET @YearDMA   = 0    
               END
               ELSE
               IF @DMA <> 0
               BEGIN
                    SET @TotalDMA  = (CAST(@Total AS FLOAT) / CAST(@DMA AS FLOAT))
                    SET @Day       = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND actionenddate  between @startDate AND @endDate)
                    SET @DayDMA    = (CAST(@Day AS FLOAT) / CAST(@DMA AS FLOAT))
                    SET @Month     = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND  actionenddate  between @startDate AND @endDate)
                    SET @MonthDMA  = (CAST(@Month AS FLOAT) / CAST(@DMA AS FLOAT))
                    SET @Year      = (SELECT SUM(actioncount)FROM usage.dbo.subscriberhostactioncount WHERE host = @host AND actionid = 2 AND countdurationdays = 1 AND  actionenddate  between @startDate AND @endDate)
                    SET @YearDMA   = (CAST(@Year AS FLOAT) / CAST(@DMA AS FLOAT))
               END

               INSERT INTO @Result (HostGroup, Host, Total, TotalDMA, Day, DayDMA, Month, MonthDMA, Year, YearDMA, DMA)
               VALUES(@HostGroup, @Host, @Total, @TotalDMA, @Day, @DayDMA, @Month, @MonthDMA, @Year, @YearDMA, @DMA)

               IF (@@ERROR <> 0)
               BEGIN
                    SET @ErrorFlag = 1
               END
          END                    
          FETCH NEXT FROM host_cursor INTO @Host
     END
     
     CLOSE host_cursor
     DEALLOCATE host_cursor

     IF (@ErrorFlag <> 0)
     BEGIN
          PRINT 'Error!'
     END
     ELSE
     BEGIN
          -- Sort the results by the field specified in @SortField.  
                -- If @SortField is invalid or wasn't specified then sort by month.
          IF @SortField = 'subsmonth'
          BEGIN
               SELECT * FROM @Result ORDER BY [Month] DESC
          END
          ELSE
          IF @SortField = 'dmamonth'
          BEGIN
               SELECT * FROM @Result ORDER BY MonthDMA DESC
          END
          ELSE
          IF @SortField = 'subsday'
          BEGIN
               SELECT * FROM @Result ORDER BY [Day] DESC
          END          
          ELSE
          IF @SortField = 'dmaday'
          BEGIN
               SELECT * FROM @Result ORDER BY DayDMA DESC
          END          
          ELSE
          IF @SortField = 'subsyear'
          BEGIN
               SELECT * FROM @Result ORDER BY [Year] DESC
          END          
          ELSE
          IF @SortField = 'dmayear'
          BEGIN
               SELECT * FROM @Result ORDER BY YearDMA DESC
          END          
          ELSE
          IF @SortField = 'substotal'
          BEGIN
               SELECT * FROM @Result ORDER BY Total DESC
          END          
          ELSE
          IF @SortField = 'dmatotal'
          BEGIN
               SELECT * FROM @Result ORDER BY TotalDMA DESC
          END          
          ELSE
          IF @SortField = 'host'
          BEGIN
               SELECT * FROM @Result ORDER BY Host
          END          
          ELSE
          IF @SortField = 'group'
          BEGIN
               SELECT * FROM @Result ORDER BY HostGroup
          END          
          ELSE
          IF @SortField = 'dma'
          BEGIN
               SELECT * FROM @Result ORDER BY DMA DESC
          END          
          ELSE
          BEGIN
               SELECT * FROM @Result ORDER BY [Month] DESC
          END
     END

END
0
 

Author Comment

by:itortu
ID: 17996409
hi again imran_fast,

your last solution seems to be working nicely. the numbers change accordingly to the dates entered. excellent!

i have one little doubt i hope you don't mind....after submitting the form dates, and getting the results, when i click the headers to
change the sort order, the rows disappear; leaving the body of the report as it was open for the first time.

i added the stardate and end date variables to the header section thinking that was the problem, but it did nit help.

do you have any ideas on how to solve this little problem?

here is the headers part i changed:

'Build links for sorting
strDMA       = "<a href=""" & PAGE_NAME & "?SortField=dma&startdate=" & strStartDate & "&enddate=" & strEndDate & """ title=""Click to sort by DMA"">DMA</a>"
strGroup     = "<a href=""" & PAGE_NAME & "?SortField=group&startdate=" & strStartDate & "&enddate=" & strEndDate & """ title=""Click to sort by Group"">Group</a>"
strStation   = "<a href=""" & PAGE_NAME & "?SortField=host&startdate=" & strStartDate & "&enddate=" & strEndDate & """ title=""Click to sort by Station"">Station</a>"
strSubsDay   = "<a href=""" & PAGE_NAME & "?SortField=subsday&startdate=" & strStartDate & "&enddate=" & strEndDate & """ title=""Click to sort by subs Today"">Downloads</a>"
strDMADay    = "<a href=""" & PAGE_NAME & "?SortField=dmaday&startdate=" & strStartDate & "&enddate=" & strEndDate & """ title=""Click to sort by DMA% change Today"">DMA% " & "</a>"



Thank you greatly!
0
 

Author Comment

by:itortu
ID: 17996439
i think the problem is that the dates are lost when the headers are clicked, so my question is, how can i saved the dates
after submitting the form, so they can be used as needed on the stored proc.?

thanks!
0
 

Author Comment

by:itortu
ID: 17998298
hi again imran_fast,

the problem has been solved by adding this piece of code:

strStartDate = request.form ("sdate")
strEndDate   = request.form ("edate")


if request.form("sdate") <> "" then
  strStartDate = request.form("sdate")
  strEndDate   = request.form("edate")
else
  strStartDate = request.querystring("startdate")
  strEndDate   = request.querystring("enddate")
end if

now there is always a date to pass to the stored proc.

thank you very much for you help.

itortu
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now