Professional Web Applications Themes

Horizontal Query - Macromedia ColdFusion

I need to output a series of dates from a query. The date count (month) must line up under that prespective month. In a nutshell: 24 colums jan-Dec twice. (Month) count underneath each colum header. Is that confusing?...

  1. #1

    Default Horizontal Query

    I need to output a series of dates from a query. The date count (month) must
    line up under that prespective month.
    In a nutshell:
    24 colums jan-Dec twice. (Month) count underneath each colum header.

    Is that confusing?

    mostlySimple Guest

  2. #2

    Default Re: Horizontal Query

    Yes.

    By "series of dates" what do you mean? If you have each month twice, is the
    date range two years? How do you associate the count with the month? By the
    resultset field name? By its position in the query result? Do you always have
    24 records in your result set?

    More questions than answers, but it's necessary in order to understand exactly
    what it is that you want to do.



    philh Guest

  3. #3

    Default Re: Horizontal Query

    Sorry I rushed that initial statement. So here's a more detailed spec.

    -User inputs 1 date
    -The report will show Individuals on the left side and months across the top
    (maximum of 24 months or 2 year window)
    -The data under each month will be the count of Individuals with ?A Date?
    equal to the calendar month
    -But individual dates to left (dates less than the 1 date input) of the 1 date
    input will represent a different date field from database than the date results
    to the right (dates greater than the 1 date input).

    I initially tried to take the results of a query pick it apart using arrays
    came close just couldn't spilt which date sets to show from left or right of
    date input. I don't know how to write a query to produce results in a tabular
    format.

    Hope that was a little clearer than the first time around. Thanks in advance
    for any help you may provide.
    \
    webs


    mostlySimple Guest

  4. #4

    Default Re: Horizontal Query

    And my apologies for rushing my reply.

    I really need to see your data structure before I can recommend a solution.
    Also, what is the relationship between
    the input date and the data? Are you returning a range of dates based on the
    input date, or what?

    philh Guest

  5. #5

    Default Re: Horizontal Query

    Heres a query used to pull the data:

    <cfquery name="t">
    Select t1.date1,t1.date1,t2.fname,t2.lname
    FROM table1 t1, table2 t2_referral ar
    WHERE t1.userID = t2.userID
    </cfquery>

    mostlySimple Guest

  6. #6

    Default Re: Horizontal Query

    <cfquery name="t">
    Select t1.date1,t1.date1,t2.fname,t2.lname
    FROM table1 t1, table2 t2_referral ar
    WHERE t1.userID = t2.userID
    </cfquery>

    Why are you returning t1.date1 twice?
    What is the meaning of "table2 t2_referral ar"? I don't see the alias "t2"
    defined here.

    What you need is a cross-tab query. You need to count the dates by calendar
    month, so you need to use the COUNT() function to return the number of records
    per user per date. Then, you need to turn this query on its side (horizontal)
    to show the counts by calendar month.

    Search these Fora for the term "cross-tab". There are a number of solutions
    posted here. Or search for the same term at [url]http://www.sqlteam.com[/url].

    HTH,

    philh Guest

  7. #7

    Default Re: Horizontal Query

    philh ,

    I looked for a solution with little success. I just can't seem to get the date
    count correct under each Month colum. I'm fried at this point and no solution
    for my customer...

    Thanks for your time

    mostlySimple Guest

Similar Threads

  1. Dynamic Menu - Horizontal with sub options horizontal
    By FER. in forum Macromedia Dynamic HTML
    Replies: 5
    Last Post: May 11th, 02:37 PM
  2. horizontal scroll Bar
    By Gianluca in forum Adobe Flash, Flex & Director
    Replies: 7
    Last Post: April 19th, 01:49 PM
  3. Horizontal pallets?
    By DR in forum Adobe Indesign Windows
    Replies: 0
    Last Post: April 10th, 11:46 PM
  4. horizontal scrollbar
    By chriske911 in forum Microsoft Access
    Replies: 0
    Last Post: July 16th, 04:49 PM
  5. Horizontal Looper for MX
    By Paul in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 14th, 03:01 PM

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