Forum Replies Created

Viewing 15 posts - 2,146 through 2,160 (of 2,171 total)

  • RE: Any ideas why this query is running so slow?

    And the WHERE clause could be somewhat easier to optimize with

     

    SELECT DISTINCT dbo.cs_ArticleTracking.id,

      dbo.cs_ArticleTracking.taskProblem,

      dbo.cs_ArticleTracking.dateCreated,

      cs_users-firstname + ' ' + cs_users.lastname AS fullname

    FROM  dbo.cs_ArticleCategories

    INNER JOIN dbo.cs_ArticleTracking ON dbo.cs_ArticleCategories.parent = dbo.cs_ArticleTracking.parentcategory AND dbo.cs_ArticleCategories.child = dbo.cs_ArticleTracking.childcategory AND...

    N 56°04'39.16"
    E 12°55'05.25"

    May 25, 2006 at 9:46 am

    #640106

  • RE: How to use relative path in BULK INSERT

    Try calling API

     

    ExpandEnvironmentVariable

    N 56°04'39.16"
    E 12°55'05.25"

    May 23, 2006 at 1:42 pm

    #639591

  • RE: Excel & OpenRowset

    Connection to Excel using Jet Engine

    To connect to Excel, one can use OleDb objects that will treat Excel as a database, and then the required information can be easily fetched...

    N 56°04'39.16"
    E 12°55'05.25"

    May 22, 2006 at 12:34 am

    #639160

  • RE: Two SELECT...GROUP BY queries into one query

    Hi Joe!

    I have read your books on SQL for smarties over and over again! Never thought I met you online.

     

    Sorry to say there is a glitch in your query.

    Suppose...

    N 56°04'39.16"
    E 12°55'05.25"

    May 19, 2006 at 2:21 pm

    #639089

  • RE: Get the max out of a series of periods

    • Are your dates stored as datetimes/smalldatetimes or varchar?
    • Is @FromDate smaller/earlier/older than @ToDate?

    I run the queries against a test set of 10 000 rows of DATETIME and INT and they tooks...

    N 56°04'39.16"
    E 12°55'05.25"

    May 19, 2006 at 2:07 pm

    #639087

  • RE: Get the max out of a series of periods

    This code should be used for fixed daterange (always x virtual days in the calendar for the range)

    DECLARE  @FromDate DATETIME,

             @ToDate DATETIME,

             @DateRange INT,

             @SKU VARCHAR(1)

    N 56°04'39.16"
    E 12°55'05.25"

    May 19, 2006 at 8:35 am

    #639017

  • RE: Get the max out of a series of periods

    Should the range be as followed?

     

    01-01-2006..............A.........20

    01-01-2006..............B.........20

    02-01-2006..............A.........30

    02-01-2006..............A.........40

    02-01-2006..............B.........10

    04-01-2006..............A.........20

    04-01-2006..............A.........50

    05-01-2006..............A.........40

    05-01-2006..............A.........10

    Now suppose my daterange is 2 days, so in the above table i need to get the total for :

    01-01-2006 - 02-01-2006 (2days) Sum for...

    N 56°04'39.16"
    E 12°55'05.25"

    May 19, 2006 at 8:23 am

    #639012

  • RE: Get the max out of a series of periods

    Here we go again...

    DECLARE  @FromDate DATETIME,

             @ToDate DATETIME,

             @DateRange INT,

             @SKU VARCHAR(1)

    SELECT   @FromDate = '2004-01-15',-- This is the starting date in the range

             @ToDate...

    N 56°04'39.16"
    E 12°55'05.25"

    May 19, 2006 at 8:18 am

    #639008

  • RE: Get the max out of a series of periods

    The data is not stored as a date range. See example.

    "I have a table with the following data (date, value)"

    N 56°04'39.16"
    E 12°55'05.25"

    May 19, 2006 at 8:14 am

    #639003

  • RE: Get the max out of a series of periods

    Or more specific

    DECLARE  @FromDate DATETIME,

             @ToDate DATETIME,

             @DateRange INT

    SELECT   @FromDate = '2004-01-15',-- This is the starting date in the range

             @ToDate = '2008-02-20',-- This...

    N 56°04'39.16"
    E 12°55'05.25"

    May 19, 2006 at 8:12 am

    #639002

  • RE: Get the max out of a series of periods

    You mean like this?

     

    DECLARE  @FromDate DATETIME,

             @ToDate DATETIME,

             @DateRange INT

    SELECT   @FromDate = '2004-01-15',

             @ToDate = '2008-02-20',

             @DateRange = 20

    SELECT   MIN(Date)...

    N 56°04'39.16"
    E 12°55'05.25"

    May 19, 2006 at 8:10 am

    #639000

  • RE: Distinct vs Group By

    The greatest factor is proper indexes.

    I run the two questions against our production server with 90+ million rows.

    DISTINCT took 66 seconds.

    GROUP BY took 8 seconds.

     

    N 56°04'39.16"
    E 12°55'05.25"

    May 19, 2006 at 3:28 am

    #638928

  • RE: Make a Crosstab relational?!

    Based on the original posting, the query would be someting like

    SELECT AggregateGroup, 1 FiscalMonth, Jan Amount FROM MySourceTable

    UNION ALL

    SELECT AggregateGroup, 2, Feb FROM MySourceTable

    UNION ALL

    SELECT AggregateGroup, 3, Mar FROM MySourceTable

    UNION ALL

    SELECT AggregateGroup, 4, Apr FROM MySourceTable

    UNION...

    N 56°04'39.16"
    E 12°55'05.25"

    May 18, 2006 at 1:28 pm

    #638854

  • RE: Two SELECT...GROUP BY queries into one query

    Here is the real query with some optimization techniques...

    SELECT   SUM(CASE WHEN DT.type = 'r' THEN DT.Items ELSE 0 END) Registrations,

             SUM(CASE WHEN DT.type = 'c' THEN DT.Items ELSE 0...

    N 56°04'39.16"
    E 12°55'05.25"

    May 18, 2006 at 12:18 pm

    #638834

  • RE: GEOCODE Street corner --- brain stopped

    Heureka! I've found it!

    Here is a "one query"-solution...

    SELECT DISTINCT CONVERT(SMALLMONEY, x1 + ((x4 - x3) * (y1 - y3) - (y4 - y3) * (x1 - x3)) * (x2...

    N 56°04'39.16"
    E 12°55'05.25"

    May 18, 2006 at 8:51 am

    #638761

    • Viewing 15 posts - 2,146 through 2,160 (of 2,171 total)