Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2013
    Posts
    4

    Unanswered: incrementing dates & displaying range as column

    I'm pretty new to SQL and honestly don't have a great handle on it, so with that said:

    I'm using SQL Server 2000 sp2

    I have created a view that gives me customer info from which I need to create a view and or table that gives me a 24 monthly columns of the sum of each account_number monthly revenues (going back 24 months from this month)

    The columns I'm pulling from are these:

    Customer_Name
    Account_number
    First_insert_date
    Order_net_price

    Here's what I have so far:
    ----------------------------
    SELECT TOP 100 PERCENT Account_number, Customer_Name, SUM(Order_net_price) AS 'CM - 24'
    FROM dbo.Customer_Feed
    WHERE (First_insert_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 24, 0)) AND (First_insert_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 23,
    0))
    GROUP BY Account_number, Customer_Name
    ORDER BY Account_number
    ------------------------------

    I've basically hacked out a way to get the monthly totals for each account.
    What I would like to do is so be able to repeat the query but increment the date range 1 month until reaching the present or last FULL month and display these sums in individual columns named CM - n (where CM means current month and 'n' is the # of months back from current.

    I'm stuck at how to make this query run over again the 23 other times I need it to and display those months sum totals in individual columns.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> I'm pretty new to SQL and honestly don't have a great handle on it, <<

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    Now we have to type and guess at what you meant. Your narrative and “almost code” do not help much. The customer names would be in the Accounts table, not with orders! If your report needs it, join afterwards. Is this more like what you HAVE?

    CREATE TABLE Orders
    (account_nbr CHAR(15) NOT NULL,
    order_date DATE NOT NULL,
    PRIMARY KEY (account_number, order_date)
    order_net_price DECIMAL (12,2) NOT NULL
    CHECK (order_net_price >= 0.00));

    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 Month_Report_Periods
    (month_report_name CHAR(10) NOT NULL PRIMARY KEY
    CHECK (month_report_name LIKE '[12][0-9][0-9][0-9]-[0-3][0-9]-00'),
    month_report_start_date DATE NOT NULL,
    month_report_end_date DATE NOT NULL,
    CONSTRAINT date_ordering
    CHECK (month_report_start_date <= month_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 advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'

    First get a view with monthly totals

    CREATE VIEW Monthly_Sales_Summary
    AS
    SELECT O.account_nbr, SUM(O.order_net_price) AS net_price_tot, R.month_name
    FROM Monthly_Report_Periods AS R, Orders AS O
    WHERE O.order_date
    BETWEEN month_report_start_date
    AND month_report_end_date;

    then put out any set of months you want.

    SELECT *
    FROM Monthly_Sales_Summary
    WHERE month_name BETWEEN @in_start_month AND @in_end_month;

  3. #3
    Join Date
    Mar 2013
    Posts
    4
    While I appreciate your reply, my "almost code" seems to be code enough for SQL to display a view with 3 columns: Account_name, Account_number, and the sum of all the months revenues for Order_net_price.

    Maybe I'm asking too simple a question here, but all I want to do is take the "almost code" I have and just add some logic that will increment the month, whether it's declaring a variable and incrementing it or adding to what I've already got.

    I would love to spend the time to learn more about SQL, but I don't have that kind of time and am looking for some help and guidance on a solution. I know it's not eloquent what I'm trying to do, I just need it to work as it's a one time thing I need as we're moving some information to a new CRM system.

    Also, if someone is willing to take a little time and explain the logic, that would be great.

    Thanks again.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    What about moving your WHERE condition to individual cases . . . as per this air code:

    Code:
    SELECT  Account_number
            ,Customer_Name
            ,SUM(
                case
                    when First_insert_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 24, 0)) AND (First_insert_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 23, 
    0) then Order_net_price)
                    else 0
                end
            ) AS 'CM - 24'
            ,SUM(
                case
                    when First_insert_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 23, 0)) AND (First_insert_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 22, 
    0) then Order_net_price)
                    else 0
                end
            ) AS 'CM - 23'
            ,SUM(
                case
                    when First_insert_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 22, 0)) AND (First_insert_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 21, 
    0) then Order_net_price)
                    else 0
                end
            ) AS 'CM - 22'
    
    ,etc.
    ,etc.
    ,etc.
    ,etc.
    
    FROM    dbo.Customer_Feed
    GROUP
    BY      Account_number
            ,Customer_Name
    ORDER
    BY      Account_number
    Since it it early in the morning here and I haven't had my coffee yet, I can't recall if you can use the AND within a CASE, but if you can't, you will simply have to separate that second condition into its own CASE.
    Last edited by PracticalProgram; 03-20-13 at 08:58.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can take this query and apply a PIVOT to it or, better yet, call it from a reporting tool such SSRS or Crystal Reports in order to pivot it for display:

    Code:
    SELECT	Account_number, 
    		Customer_Name,
    		DATEDIFF(MONTH, mm, GETDATE()) as MonthLag,
    		SUM(Order_net_price) AS SumPrice 
    FROM	dbo.Customer_Feed
    WHERE	DATEDIFF(MONTH, mm, GETDATE()) < 24
    GROUP BY Account_number, 
    		Customer_Name,
    		DATEDIFF(MONTH, mm, GETDATE())
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by blindman View Post
    ... Better yet, call it from a reporting tool such ssrs or crystal reports in order to pivot it for display:
    + 1
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2013
    Posts
    4
    In response to Blindman:
    I did just a straight copy/paste into my sql server 2k design view and ran the query and got this error as a result: " Invalid column name 'mm' "
    I'm challenged, so all the monkeying around I could do yielded nothing.

    In Response to PracticalProgram:
    I used design view with just the first CASE statement and got this Query Definitions Differ message: " Error in list of function arguments: ')' not recognized.
    Unable to parse query text. "
    Running the query just gave an incorrect syntax near ')' error.

    Any help would be appreciated.

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I may have accidentally deleted a parenthesis, so just go through the code and make sure the parenthesis match-up.

    Taking a quick look at it, I can see that they do not, but this is something you should be able to find.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Mar 2013
    Posts
    4
    GOT IT, Ken!

    You're right, some of the "()" didn't match up. Works like a charm. A little more code than I anticipated in the query, but I'm not complaining. There's gonna be a few very very happy people on the top floor come the end of this week. Maybe they'll unchain me for a few minutes next week!


    SELECT TOP 100 PERCENT Account_number, Customer_Name

    , SUM(CASE WHEN First_insert_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 24, 0) AND First_insert_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 23, 0) THEN Order_net_price ELSE 0 END) AS 'CM - 24'

    ,SUM(CASE WHEN First_insert_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 23, 0) AND First_insert_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 22, 0) THEN Order_net_price ELSE 0 END) AS 'CM - 23'

    ,SUM(CASE WHEN First_insert_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 22, 0) AND First_insert_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 21, 0) THEN Order_net_price ELSE 0 END) AS 'CM - 22'

    , etc, etc, etc

    FROM dbo.Customer_Feed
    GROUP BY Account_number, Customer_Name
    ORDER BY Account_number

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    BTW, get rid of that "TOP 100 PERCENT"--not doing anything for you.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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