Professional Web Applications Themes

DATENAME/DATEPART question - Microsoft SQL / MS SQL Server

Hello, Thanks for the help with the first error message. Query yzer seems to accept the query now. However, the result set shows: April 2003 August 2003 1. Is it possible to change these dates into: 04/03 08/03 The chronological order would be respected in this case. 2. Is it possible to show all months within the determined period even if the invoices were "0"? In this case, I could make a graph about monthly invoicing per lot? This is my new query: (object: return sum of monthly invoices per lot) SELECT L.DESCRIPTION_LOT AS [LOT], DATENAME (month, F.DATEFACTURE) + ' ...

  1. #1

    Default DATENAME/DATEPART question

    Hello,

    Thanks for the help with the first error message. Query
    yzer seems to accept the query now.

    However, the result set shows:
    April 2003
    August 2003

    1. Is it possible to change these dates into:
    04/03
    08/03
    The chronological order would be respected in this case.

    2. Is it possible to show all months within the determined
    period even if the invoices were "0"? In this case, I
    could make a graph about monthly invoicing per lot?

    This is my new query:
    (object: return sum of monthly invoices per lot)

    SELECT
    L.DESCRIPTION_LOT AS [LOT],
    DATENAME (month, F.DATEFACTURE) + ' ' + CONVERT(char(4),
    DATEPART (yyyy, F.DATEFACTURE)) AS [MOIS],
    SUM (CC.MONTANT_TOTAL) AS [TOTAL FACTURE]

    FROM
    TDLOT AS L,
    TDCOMMANDECORRECTIVE CC,
    TDDETAILCOMMANDECORRECTIVE DCC,
    TDFACTURES AS F

    WHERE
    (L.IDLOT = CC.IDLOT)
    AND (CC.IDCOMMANDECORRECTIVE = DCC.IDCOMMANDECORRECTIVE)
    AND (DCC.IDFACTURE = F.IDFACTURE)
    AND (F.DATEFACTURE BETWEEN '04/01/2001' AND '04/30/2004')

    GROUP BY
    L.DESCRIPTION_LOT ,
    DATENAME (month, F.DATEFACTURE) + ' ' + CONVERT(char(4),
    DATEPART (yyyy, F.DATEFACTURE))

    UNIONSELECT
    L.DESCRIPTION_LOT,
    DATENAME (month, F.DATEFACTURE) + ' ' + CONVERT(char(4),
    DATEPART (yyyy, F.DATEFACTURE)) ,
    SUM (CP.MONTANT_TOTAL

    FROM
    TDLOT AS L,
    TDCOMMANDEPREVENTIVE CP,
    TDDETAILCOMMANDEPREVENTIVE DCP,
    TDFACTURES AS F

    WHERE
    (L.IDLOT = CP.IDLOT)
    AND (CP.IDCOMMANDEPREVENTIVE = DCP.IDCOMMANDEPREVENTIVE)
    AND (DCP.IDFACTURE = F.IDFACTURE)
    AND (F.DATEFACTURE BETWEEN '04/01/2001' AND '04/30/2004')

    GROUP BY
    L.DESCRIPTION_LOT ,
    DATENAME (month, F.DATEFACTURE) + ' ' + CONVERT(char(4),
    DATEPART (yyyy, F.DATEFACTURE))ORDER BY [LOT], [MOIS]

    ORDER BY
    [LOT],
    [MOIS]
    Andy Guest

  2. #2

    Default Re: DATENAME/DATEPART question

    ....
    ORDER BY lot, MIN(f.datefacture)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: DATENAME/DATEPART question

    New error message:

    Error message:
    ORDER BY items must appear in the select list if the
    statement contains a UNION operator.


     
    Andy Guest

  4. #4

    Default Re: DATENAME/DATEPART question

    Hi Andy,

    To answer your second question:
    You can use the GROUP BY ALL clause to get all the months including the ones
    for which there are no invoices for a particular combination of year, month
    and description.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Andy" <com> wrote in message
    news:09b401c35b50$f590ba80$gbl... 


    Jacco Guest

Similar Threads

  1. How to SELECT DatePart as a new Variable
    By Gahiggidy in forum Macromedia ColdFusion
    Replies: 0
    Last Post: June 30th, 06:25 PM
  2. Replies: 2
    Last Post: October 16th, 03:57 PM
  3. DatePart query
    By David in forum ASP
    Replies: 3
    Last Post: September 17th, 01:11 PM
  4. DATEPART error message
    By Andy in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 09:57 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139