Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    Jakarta, Indonesia
    Posts
    3

    Red face Unanswered: First Date in the Month

    I have a question about creating view for these coloum and have a lot trouble, before that I describe my tables :

    Table A :
    Code as nvarchar
    Ammount1 as money
    Date as datetime -> transaction date
    primary key are Code and Date1

    Table B :
    Code as nvarchar
    Ammount2 as money
    Date as datetime -> transaction date
    primary key are Code and Date2

    I like joining this table and create table/view that result :

    Code | Amount1 Today | Amount2 Today | Amount1 Last Year | Amount1 Month to date (Sum from first day month till today) | Amount2 month to date | Amount1 Last Year month to date

    The parameter only for @Date.

    First I have problem to get queries specially for first date in the month, I used CAST but it doesn't work, then I used CONVERT and CAST still doesn't work.

    Anybody help me ?
    Thx a lot.
    ad1k4r4

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    first date of the month for getDate...

    Code:
    select cast('01/' + '0'+cast(month(getDate()) as varchar) + '/' + cast(year(getDate()) as varchar) as datetime)
    replace getDate with @Date and you should be right.... I think...

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I like this method because it is more concise:

    select convert(char(7), getdate(), 120) + '-01'
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    hehehe I knew there must be a better way,... that's why I'm not a db developer.

    Is there a way to do it with a 3 character month?? I perfer 3 char month names because then you don't get any confusion with date time settings etc....

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm sure there is. Look up the formatting parameters for the CONVERT function. You can use the technique of converting the result to a shortened CHAR value whenever the results of the CONVERT function are consistent in the length of their output.

    I understand you desire to use three character month names to make the result more easily readable by carbon based processors, but formatting style "120" is more universally recognized by silicon based processors. Personally, I would refer any more complex formatting to the user interface.

    The other big advantage of style "120" is that the result: YYYY-MM-DD sorts correctly even as a string.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    actually the reason I like the 3 char month is because you can never rely on people to set up servers or accounts with the correct language settings.... and depending on what settings you have 01-04-2004 could be recorded in the system as 1 April or 4 Jan where as with either set up 1-Apr-2004 is always 1 April.

    The database will then hold the value in whatever format it likes and reformating it on the way out is very much the role of whatever interface you want to implement.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, I'd start with:
    PHP Code:
    CREATE PROCEDURE dbo.patp
      
    @pdAsof DATETIME
    AS

    DECLARE @
    dFirst DATETIME  --  First of the month

    SELECT 
    @dFirst Convert(CHAR(8), @pdAsOf121) + '01'

    SELECT
       Coalesce
    (a.codeb.code) AS code
    ,  a.amount1
    ,  b.amount2
    ,  (SELECT Sum(c.amount1)
          
    FROM tableA AS c
          WHERE  c
    .code Coalesce(a.codeb.code)
             AND 
    c.[date] = DateAdd(year, -1Coalesce(a.[date], b.[date])) AS amount1_last_year
    ,  (SELECT Sum(d.amount1)
          
    FROM tableA AS d
          WHERE  d
    .code Coalesce(a.codeb.code)
             AND 
    d.date BETWEEN @dFirst AND
                
    Coalesce(a.[date], b.[date])) AS amount1_mtd
       FROM tableA 
    AS a
       FULL OUTER JOIN tableB 
    AS b
          ON 
    (b.code a.code
          
    AND b.[date] = a.[date])

    RETURN 
    That's only part of the solution, but let's see if that part does what you want before we get too crazy!

    -PatP

Posting Permissions

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