SQL SELECT reference a field in a CASE statement

I am looking to reference a field I am creating with the CASE statement in lines 10 - 21.  I want to calculate the duration of package_assignment_timestamp (lines 18,19) between form_download_timestamp (lines 10, 11).  I tried using their aliases but I am getting the column does not exist error message.

SELECT
    public.assets.first_name || ' ' || public.assets.last_name AS team_member,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'Corporate'
        ELSE public.project_markets.market
                              END,
    closeout.packages.name AS package_name,
    events.event.name AS event_name,
    case
        when event.id = 1 then min(events.event_log.timestamp) end as form_download_timestamp,
     case
        when event.id = 1 then DATE_PART('day', now()::timestamp - min(events.event_log.timestamp)::timestamp) end as form_download_duration,
    case
        when event.id = 2 then min(events.event_log.timestamp) end as form_upload_timestamp,
    case
        when event.id = 2 then DATE_PART('day', now()::timestamp - min(events.event_log.timestamp)::timestamp) end as form_download_duration,
    case
        when event.id = 3 then min(events.event_log.timestamp) end as package_assignment_timestamp,
    case
        when event.id = 3 then DATE_PART('day', now()::timestamp - min(events.event_log.timestamp)::timestamp) end as form_download_duration,
    NOW() as today
FROM
    events.event
INNER JOIN
    events.event_log
ON
    (
        events.event.id = events.event_log.event_id)
INNER JOIN
    public.assets_user_xref
ON
    (
        events.event_log.user_id = public.assets_user_xref.user_id)
INNER JOIN
    public.assets
ON
    (
        public.assets_user_xref.assets_id = public.assets.id)
FULL OUTER JOIN
    public.unitek_pcs
ON
    (
        public.assets.pc6 = public.unitek_pcs.pc6)
FULL OUTER JOIN
    public.project_pc_market_xref
ON
    (
        public.unitek_pcs.id = public.project_pc_market_xref.pc_id)
FULL OUTER JOIN
    public.project_markets
ON
    (
        public.project_pc_market_xref.market_id = public.project_markets.id)
INNER JOIN
    public.project_instance_people
ON
    (
        public.assets.id = public.project_instance_people.asset_id)
INNER JOIN
    closeout.package_instances
ON
    (
        public.project_instance_people.project_instance_id = closeout.package_instances.project_id)
INNER JOIN
    closeout.packages
ON
    (
        closeout.package_instances.package_id = closeout.packages.id)
WHERE
    events.event_log.event_id IN (3,
                                  2,
                                  1) 
group by events.event_log.timestamp, public.assets.first_name, public.assets.last_name, public.project_markets.market, closeout.packages.name, events.event.name, events.event.id  ;

Open in new window

szadrogaAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
one column (col 2) has no alias, some columns have the same alias (I added 1 2 3), these need to be sorted out; but for this question:

You are going to have to "push down" you current query one level to enable access to column aliases for this calculation, something like this:
select
        team_member
      , whatever
      , package_name
      , event_name
      , form_download_timestamp
      , form_download_duration1
      , form_upload_timestamp
      , form_download_duration2
      , package_assignment_timestamp
      , form_download_duration3
      , today

, do your column difference calculation here using aliases

from (
      SELECT
               PUBLIC.assets.first_name || ' ' || PUBLIC.assets.last_name AS team_member
             , CASE WHEN PUBLIC.project_markets.market IS NULL THEN 'Corporate' ELSE PUBLIC.project_markets.market END AS whatever
             , closeout.packages.NAME AS package_name
             , events.event.NAME AS event_name
             , CASE WHEN event.id = 1 THEN min(events.event_log.TIMESTAMP) END AS form_download_timestamp
             , CASE WHEN event.id = 1 THEN DATE_PART('day', now()::TIMESTAMP - min(events.event_log.TIMESTAMP)::TIMESTAMP) END AS form_download_duration1
             , CASE WHEN event.id = 2 THEN min(events.event_log.TIMESTAMP) END AS form_upload_timestamp
             , CASE WHEN event.id = 2 THEN DATE_PART('day', now()::TIMESTAMP - min(events.event_log.TIMESTAMP)::TIMESTAMP) END AS form_download_duration2
             , CASE WHEN event.id = 3 THEN min(events.event_log.TIMESTAMP) END AS package_assignment_timestamp
             , CASE WHEN event.id = 3 THEN DATE_PART('day', now()::TIMESTAMP - min(events.event_log.TIMESTAMP)::TIMESTAMP) END AS form_download_duration3
             , NOW() AS today
      FROM events.event
      INNER JOIN events.event_log ON (events.event.id = events.event_log.event_id)
      INNER JOIN PUBLIC.assets_user_xref ON (events.event_log.user_id = PUBLIC.assets_user_xref.user_id)
      INNER JOIN PUBLIC.assets ON (PUBLIC.assets_user_xref.assets_id = PUBLIC.assets.id)
      FULL JOIN PUBLIC.unitek_pcs ON (PUBLIC.assets.pc6 = PUBLIC.unitek_pcs.pc6)
      FULL JOIN PUBLIC.project_pc_market_xref ON (PUBLIC.unitek_pcs.id = PUBLIC.project_pc_market_xref.pc_id)
      FULL JOIN PUBLIC.project_markets ON (PUBLIC.project_pc_market_xref.market_id = PUBLIC.project_markets.id)
      INNER JOIN PUBLIC.project_instance_people ON (PUBLIC.assets.id = PUBLIC.project_instance_people.asset_id)
      INNER JOIN closeout.package_instances ON (PUBLIC.project_instance_people.project_instance_id = closeout.package_instances.project_id)
      INNER JOIN closeout.packages ON (closeout.package_instances.package_id = closeout.packages.id)
      WHERE events.event_log.event_id IN (3, 2, 1)
      GROUP BY
               events.event_log.TIMESTAMP
             , PUBLIC.assets.first_name
             , PUBLIC.assets.last_name
             , PUBLIC.project_markets.market
             , closeout.packages.NAME
             , events.event.NAME
             , events.event.id
      ) IQ
;

Open in new window

This query is subject to several questions at once, I suggest you get the basic query stable before adding more to it.
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
hello,

You can not use alias in the case in the same SQL query

Either you should use the real column name or try another query above the main then you can use alias
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You can't use an alias in one SELECT column in another, as the query engine (I think) processes the entire SELECT clause together.

Perhaps a workaround would be to throw the line 10-11 part into some kind of subquery, and then the main query will have all columns, joined on the subquery, and can use the subquery alias columns.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
szadrogaAuthor Commented:
can an example for using a subquery be provided.  I am looking for the time difference between the 2 alias fields package_assignment_timestamp and form_download_timestamp.

I am not too familiar with joining subqueries...
0
 
szadrogaAuthor Commented:
i used your suggestion and I am getting NULL values for the duration.  My thought is because the 2 timestamps are not in the same row within the dataset so the calculation is null - timestamp or timestamp - null and NOT timestamp - timestamp. here is my query.

i have also attached the result data set from the subquery.

SELECT
fname,
lname,
market,
package_name,
form_upload - form_download as duration

FROM
(SELECT
    public.assets.first_name as fname,
    public.assets.last_name as lname,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'Corporate'
        ELSE public.project_markets.market
    END as market,
    closeout.packages.name as package_name,
    MIN(CASE
        WHEN events.event_log.event_id = 3
        THEN events.event_log.timestamp
    END) AS package_assignment,
    MIN(CASE
        WHEN events.event_log.event_id = 1
        THEN events.event_log.timestamp
    END) AS form_download,
    MIN(CASE
        WHEN events.event_log.event_id = 2
        THEN events.event_log.timestamp
    END) AS form_upload
FROM
    events.event
INNER JOIN
    events.event_log
ON
    (
        events.event.id = events.event_log.event_id)
INNER JOIN
    public.assets_user_xref
ON
    (
        events.event_log.user_id = public.assets_user_xref.user_id)
INNER JOIN
    public.assets
ON
    (
        public.assets_user_xref.assets_id = public.assets.id)
FULL OUTER JOIN
    public.unitek_pcs
ON
    (
        public.assets.pc6 = public.unitek_pcs.pc6)
FULL OUTER JOIN
    public.project_pc_market_xref
ON
    (
        public.unitek_pcs.id = public.project_pc_market_xref.pc_id)
FULL OUTER JOIN
    public.project_markets
ON
    (
        public.project_pc_market_xref.market_id = public.project_markets.id)
INNER JOIN
    public.project_instance_people
ON
    (
        public.assets.id = public.project_instance_people.asset_id)
INNER JOIN
    closeout.package_instances
ON
    (
        public.project_instance_people.project_instance_id = closeout.package_instances.project_id)
INNER JOIN
    closeout.packages
ON
    (
        closeout.package_instances.package_id = closeout.packages.id)
WHERE
    events.event_log.event_id IN (3,
                                  2,
                                  1)
GROUP BY public.assets.first_name, public.assets.last_name, public.project_markets.market, events.event.name, events.event_log.event_id, packages.name

ORDER BY assets.last_name, package_name ASC) as duration_table;

Open in new window

closeout-adoption-report.xlsx
0
 
PortletPaulfreelancerCommented:
This question is about how to use an alias in a calculation.

The subsequent issue of aligning the fields into one row is on a different question
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.