Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2014
    Posts
    18

    Unanswered: Syntax and Concepts

    Needing a little help with syntax concepts... It seems I keep running into situations requiring me to reference the same table and same columns two or more times in a query, once with one type of reference, and again with another.

    For example, looking at Table1 (T1) and using Col1 & Col2, grouped by Table2 (T2) Col1. I need to query SUM(T1.Col1) and SUM(T1.Col2) for a specific one-month timeframe, but then SUM(T1.Col1) and SUM(T1.Col2) for a different timeframe.

    Heres what Im going for (hopefully my mock results line up when this posts!):

    T2.Col1 T1.Col1 T1.Col2 T1.Col1 T1.Col2
    ----------- ----------- -------------- -------------- -----------
    Group1 MTD Sum MTD Sum YTD Sum YTD Sum
    Group2 MTD Sum MTD Sum YTD Sum YTD Sum
    ...



    But heres what I often get:

    T2.Col1 T1.Col1 T1.Col2
    -------- --------- ----------
    Group1 MTD Sum MTD Sum
    Group1 YTD Sum YTD Sum
    Group2 MTD Sum MTD Sum
    Group2 YTD Sum YTD Sum
    ...


    I have also had this:

    T2.Col1 T1.Col1 T1.Col2 T1.Col1 T1.Col2
    ----------- -------------- -------------- -------------- ---------------
    Group1 MTD Sum MTD Sum Total All Groups Total All Groups
    Group2 MTD Sum MTD Sum Total All Groups Total All Groups
    ...


    Im hoping to find what Im missing conceptually so I can apply it to all queries going forward.

    I suspect it has to do with my JOINS and/or use of UNION, but Im not sure how else to conceptualize referring to the same columns multiple times with different constraints.

    Any thoughts?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    How about case?

    Code:
    select sum(case when t2.col1 = 'MTD' then...
                           when t2.col1= 'YTD' then...
    Dave

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Im having a hard time following you. Can you provide some dummy data for T1 and T2 and the desired outcome using said dummy data?

  4. #4
    Join Date
    Mar 2014
    Posts
    18
    @dav1mo - I don't believe CASE will work since I need MTD and YTD totals for all line items in the results.

    @clawlan - Looks like my dummy data didn't line up as nicely as I had planned. Not sure how to express this as I can't seem to get it to line up squarely in a post such that it can be easily read. Any ideas how to do that? Let's try it this way:

    For each type of customer (T2.Col1), I want the MTD gross collected, MTD fee collected, YTD gross collected, and YTD fee collected. Gross (T1.Col1) and fee (T1.Col2) collected are in two columns but the problem is that I need to reference those same columns twice... once for the MTD figures, and once for the YTD figures... so the same columns from the same table need to be referenced with two different date ranges in the same query.

    Does that help clarify?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're getting into geeky territory:
    Code:
    --  ptp  20140401  Demo running totals
    
    --  Setup the scenario
    
    DECLARE @t TABLE
       (asof            DATE            NOT NULL
    ,  bucks            DECIMAL(10, 2)  NOT NULL
       )
    
    INSERT INTO @t
       SELECT DateAdd(day, v.number, '2000-01-01'), v.number
          FROM master.dbo.spt_values AS v
    	  WHERE  'P' = v.type
    
    --  Here's where the work happens!
    
    SELECT asof
    ,  bucks
    ,  Sum(bucks) OVER (
          PARTITION BY Convert(CHAR(7), asof, 121) 
    	  ORDER BY asof 
    	  RANGE UNBOUNDED PRECEDING) AS mtd
    ,  Sum(bucks) OVER (
    	  PARTITION BY Convert(CHAR(4), asof, 121) 
    	  ORDER BY asof 
    	  RANGE UNBOUNDED PRECEDING) AS ytd
       FROM @t
       ORDER BY asof
    I'm throwing spaces into the next two lines to keep them visible. If I took the spaces out, it would create a second code block like the one above that uses a monospaced font to make it easy to line things up. In VBulletin, the markers are:

    [ c o d e ]
    [ / c o d e ]

    Remove the spaces from the lines above to mark the beginning and end of a code block. The text inside the code block is monospaced so that it is easy to line up!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I do not understand what you want to do. The fiction is that each alias "effectively" creates a new table with the alias name.

    Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

    CREATE TABLE Something_Report_Periods
    (something_report_name CHAR(10) NOT NULL PRIMARY KEY
    CHECK (something_report_name LIKE <pattern>),
    something_report_start_date DATE NOT NULL,
    something_report_end_date DATE NOT NULL,
    CONSTRAINT date_ordering
    CHECK (something_report_start_date <= something_report_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantages are that it will sort with the ISO-8601 data format required by Standard SQL and it is language independent. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Celko:

    I assumed that the MTD and YTD were based on a calendar year, and calendar years aren't exactly flexible... Calendar periods aren't subject to the arbitrary changes that corporate calendars can be.

    I find your championing a particular dialect extension like zero convention used by MySQL amusing. You normally slice "non-standard" extensions to ribbons. I don't have an opinion on this one, but i'm generally allergic to "magic numbers" and using a zero as a wildcard feels like a magic number to me.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I assumed that the MTD and YTD were based on a calendar year, and calendar years aren't exactly flexible... Calendar periods aren't subject to the arbitrary changes that corporate calendars can be.
    I would go for fiscal years. I did a lot of US federal calendar stuff that starts in October

    I find your championing a particular dialect extension like zero convention used by MySQL amusing. You normally slice "non-standard" extensions to ribbons.
    It is being proposed for ISO-8601 extension as a way of doing date ranges, along with "yyyyQ[1-4]" for quarters, etc. Hey, sometimes I am ahead of the curve

    I like to pass these strings to a presentation layer in the best format.

Posting Permissions

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