SQL grouping - one line per record

I have the following query below and it returns accurate results, but I need to have all the timestamps appear in one line per user, market and package_name.  For some reason, if its the same package, user and market it will put the timestamps for package_assignment, form_download, form_upload if the values exist.  

I am looking to find the durations between these timestamps so i need to have them in one line correct?  Unless there is a way to use a FOR parameter?

SELECT
    public.assets.first_name,
    public.assets.last_name,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'Corporate'
        ELSE public.project_markets.market
    END,
    CASE
        WHEN events.event_log.event_id = 3
        THEN MIN(events.event_log.timestamp)
    END AS package_assignment,
    CASE
        WHEN events.event_log.event_id = 1
        THEN MIN(events.event_log.timestamp)
    END AS form_download,
    CASE
        WHEN events.event_log.event_id = 2
        THEN MIN(events.event_log.timestamp)
    END AS form_upload,
    closeout.packages.name as package_name
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;

Open in new window

szadrogaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please give us a screen shot of the above query's return set, and a mockup of what you are trying to pull off.
It's difficult to get our heads around T-SQL that we can't execute ourselves..

Thanks in advance.
0
szadrogaAuthor Commented:
please see attached.  I want one row to contain all the dates (if available).  I need to do some calcs based on their differencesoutput.pngmockup.png
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give the below a whirl.  Keep in mind that you'll need to identify how you want to handle a situation where there are more than one date entered for a given 'first_name, last_name, market, package_assignment' - the below code uses the max date, but other options are min, first, ??.
SELECT first_name, last_name, market, package_assignment, Max(form_download) as form_download, max(form_upload) as form_upload, package_name
FROM your_table
GROUP BY first_name, last_name, market, package_assignment
ORDER BY first_name, last_name, market, package_assignment

Open in new window

0
szadrogaAuthor Commented:
i tried your suggestion but no luck.  I am using the MIN() function on the timestamp field.  Also "package_assignment" is an alias and it would not let me GROUP BY that field.

any other thoughts?
0
PortletPaulfreelancerCommented:
Oh so close!  You need to remove events.event_log.event_id from the GROUP BY clause (see line 36 below)

Then you need to place the whole case expression inside the MIN() functions  (see lines 6,7,8)
SELECT
      public.assets.first_name
    , public.assets.last_name
    , CASE WHEN public.project_markets.market IS NULL THEN 'Corporate' ELSE public.project_markets.market END
-- changes   here 
    , 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

    , closeout.packages.name                                                                AS package_name
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 (1, 2, 3)
GROUP BY
      public.assets.first_name
    , public.assets.last_name
    , public.project_markets.market
    , events.event.name
-- change here                      -----------------------------<<<<<<<<<<<<<<<<<<
    , packages.name
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.