Solved

SQL grouping - one line per record

Posted on 2014-09-23
6
100 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

773 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