Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    16

    Unanswered: Running the query just once for 4 columns results?

    SELECT emp, site, sum(Num_Reprints) as NumTimes_CashReprint,
    COUNT (*) as Numdays_CashReprint
    FROM
    (
    Select al.fastlane_login_username AS emp, al.fac_idu AS site,to_char(al.attempt_date_time,'YYYY-MM-DD') AS Dates,
    COUNT(*) as NUM_REPRINTS
    FROM Table1
    WHERE ( /*(al.fac_idu = 'S04422')--:siteid)
    AND */UPPER (audit_action) IN ('REPRINT BUTTON')
    AND (attempt_date_time >=
    '1-july-2012'--TO_DATE (:dtstart, 'mm/dd/yyyy hh24:mi:ss')
    )
    AND (attempt_date_time <= '31-july-2012'--TO_DATE (:dtend, 'mm/dd/yyyy hh24:mi:ss')
    )
    and (pm.payment_method_desc = 'Cash')
    )
    --AND ((al.attempt_date_time - th.time_out) * 1440) > 4.3
    GROUP by al.fastlane_login_username , al.fac_idu, to_CHAR(al.attempt_date_time,'YYYY-MM-DD')
    )
    GROUP BY emp, site
    ORDER by sum(Num_Reprints) desc;

    ------
    The above query runs fine, but if I want to display the results with a 4.5minute timetrend I would have to read the "AND ((al.attempt_date_time - th.time_out) * 1440) > 4.3" statement.

    If I want to just display total results, then I would have to ignore the "AND ((al.attempt_date_time - th.time_out) * 1440) > 4.3" statement.

    How can I incorporate the query where it will just display BOTH results at the sametime without needing to "ignore" that statement.

    I need it to display:

    emp-site-numtimes_cashreprints-numdays_cashreprint-numtime_timetrend-numdays-timetrend

    by running the query once ... rather than twice to see both results

    Thanks,
    Last edited by SQLNoob8; 09-07-12 at 18:27.

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Try this query (not tested):
    Code:
    SELECT emp, site, 
           sum(Num_Reprints) as NumTimes_CashReprint,
           COUNT (*) as Numdays_CashReprint,
           sum( CASE WHEN statement = '>4.3' THEN Num_Reprints ELSE 0 END ) NumTimes_CashReprint_gt43,
           sum( CASE WHEN statement = '>4.3' THEN 1 ELSE 0 END ) Numdays_CashReprint_gt43
    FROM
    (
    	Select al.fastlane_login_username AS emp, al.fac_idu AS site,to_char(al.attempt_date_time,'YYYY-MM-DD') AS Dates,
            CASE WHEN ((al.attempt_date_time - th.time_out) * 1440) > 4.3 THEN '>4.3' ELSE '<=4.3' END statement,
    	COUNT(*) as NUM_REPRINTS
    	FROM pos_clnt_auditlogging al INNER JOIN pos_ticket_header th
    	ON al.ticket_id = th.ticket_header_id_client
    	AND al.fac_idu = th.fac_idu
    	INNER JOIN pos_payment_method_lkp pm
    	ON th.payment_method_id = pm.payment_method_id
    	WHERE ( /*(al.fac_idu = 'S04422')--:siteid)
    	AND */UPPER (audit_action) IN ('REPRINT BUTTON')
    	AND (attempt_date_time >='1-july-2012'--TO_DATE (:dtstart, 'mm/dd/yyyy hh24:mi:ss')
    	)
    	AND (attempt_date_time <= '31-july-2012'--TO_DATE (:dtend, 'mm/dd/yyyy hh24:mi:ss')
    	)
    	and (pm.payment_method_desc = 'Cash')
    	)
    	--AND ((al.attempt_date_time - th.time_out) * 1440) > 4.3
    	GROUP by al.fastlane_login_username , al.fac_idu, to_CHAR(al.attempt_date_time,'YYYY-MM-DD'),
                  CASE WHEN ((al.attempt_date_time - th.time_out) * 1440) > 4.3 THEN '>4.3' ELSE '<=4.3' END
    )
    GROUP BY emp, site
    ORDER by sum(Num_Reprints) desc;

  3. #3
    Join Date
    Jun 2012
    Posts
    16
    Thanks so much!! I didn't think anyone would come up with something

    I'll have to review it once I get home to understand the logic of it .. to explain the results.

    THANKS!

Posting Permissions

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