Solved

SQL grouping - one line per record

Posted on 2014-09-23
6
92 Views
Last Modified: 2014-10-08
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

0
Comment
Question by:szadroga
  • 2
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40339494
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
 

Author Comment

by:szadroga
ID: 40339809
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40339854
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
 

Author Comment

by:szadroga
ID: 40346191
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40347088
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now