SQL 和 Google Charts - 给定一系列日期,缩放日期范围

发布于 2024-10-31 19:23:24 字数 407 浏览 2 评论 0原文

我正在使用 Google Charts 创建一些图表,效果很好。我遇到的唯一问题是当我的日期范围很大时。我的 X 轴显示一系列日期。 1 到 2 周就可以了(仅限工作日,所以我要么有 5 个或 10 个日期),但是当我开始进入月份范围时,所有日期都相互重叠,并且不可读。理想情况下,对于超过 2 周的任何事情,我希望获得 10 个最重要的日期。例如,如果我的系列是从 6/1 - 7/15 的所有日期,我希望图表上的 X 轴显示如下内容:

6/1 | 6/5 | 6/10 | 6/15 | 6/20 | 6/25 | 6/30 | 7/5 | 7/10 | 7/15

使用 Google 图表,您必须指定一系列数据,因此我不能只提供开始和结束日期并使其适当缩放。仅供参考 - 我正在使用 SQL Server 存储过程来获取数据,因此我希望能够计算数据库端的日期。

I'm using Google Charts to create some graphs and it's working well. The only problem I'm having is when I have a large date range. My X axis displays the series of dates. It's fine for 1 and 2 weeks (only weekdays, so I either have 5 or 10 dates) however when I start to get up into the month(s) range, all the dates overlap one another and it's unreadable. Ideally, for anything over 2 weeks, I'd like to get the 10 most significant dates. So for example, if my series is all the dates from 6/1 - 7/15, I'd like the X axis on the graph to display something like:

6/1 | 6/5 | 6/10 | 6/15 | 6/20 | 6/25 | 6/30 | 7/5 | 7/10 | 7/15

With Google Charts, you have to specify your series of data, so I can't just provide a start and end date and have it scale appropriately. FYI - I'm using a SQL Server stored proc to obtain my data so I'd like to be able to calculate the dates on the database side.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

善良天后 2024-11-07 19:23:24

好的,我理解您的要求,这是一段代码,它将形成一个用“;”分隔的字符串。您可以从 SQL Server SP 返回它!

begin
    declare @dt1 as smalldatetime
    declare @dt2 as smalldatetime
    declare @intDays as int
    declare @curDt as smalldatetime
    declare @interV as int
    declare @outText as varchar(1000)

    set @dt1 = '6/1/2011'
    set @dt2 = '7/15/2011'
    set @interv = 10

    set @intdays = datediff(dd,@dt1,@dt2) / @interv
    set @curDt = @dt1
    set @outText = ''
    while (@curdt < @dt2)
    begin
        set @outText = @outText + CONVERT(VARCHAR(8), @curdt, 1) + ';'
        set @curdt = dateadd(dd, @interv, @curdt)
    end
    if @curdt >= @dt2
        set @outText = @outText + CONVERT(VARCHAR(8), @dt2, 1) + ';'

    set @outText = substring(@outText, 1, len(@outText)-1)

    print @outtext
end

@dt1 和 @dt2 是开始日期和结束日期,您可以将其替换为本地变量。 @outText 是带有值的字符串。

如果您有任何疑问或者您希望将其作为函数/过程,请告诉我!

Ok I understand your requirement and here is a piece of code which will form a String separated by ";" which you can return from the SQL Server SP!

begin
    declare @dt1 as smalldatetime
    declare @dt2 as smalldatetime
    declare @intDays as int
    declare @curDt as smalldatetime
    declare @interV as int
    declare @outText as varchar(1000)

    set @dt1 = '6/1/2011'
    set @dt2 = '7/15/2011'
    set @interv = 10

    set @intdays = datediff(dd,@dt1,@dt2) / @interv
    set @curDt = @dt1
    set @outText = ''
    while (@curdt < @dt2)
    begin
        set @outText = @outText + CONVERT(VARCHAR(8), @curdt, 1) + ';'
        set @curdt = dateadd(dd, @interv, @curdt)
    end
    if @curdt >= @dt2
        set @outText = @outText + CONVERT(VARCHAR(8), @dt2, 1) + ';'

    set @outText = substring(@outText, 1, len(@outText)-1)

    print @outtext
end

@dt1 and @dt2 are the start and end dates, which you can replace with your local variables. @outText is the string with values.

Let me know if you have any questions or you want this as a function/procedure!

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文