Table with multiple field ID's with different values

Question

Table with multiple field ID's with different values

darren 75071

Grasshopper

Points: 19
More actions
August 9, 2016 at 1:55 pm

#320499

The measures table consist of the following fields id, user_id, measuretype_id, value, measuredate. Trying to select measuretype_id 8,9,12 and report the measures.`value`
Presently I am using MAX which pulls the information but the larger number. I need the last date entry and not MAX. Any help would be appreciated. Thanks,
SELECT
users.id,
users.first_name,
users.last_name,
businesses.`name` AS BusName,
businesstypes.`name` AS BusIndustry,
businessforms.`name` AS BusForm,
MAX(Case WHEN measures.measuretype_id = 9 THEN measures.`value` END) AS BusRevenue,
MAX(Case measures.measuretype_id WHEN 8 THEN measures.`value` END) AS BusFT_Emp,
MAX(Case measures.measuretype_id WHEN 12 THEN measures.`value` END) AS BusPT_Emp,
usertypes.`name` AS User_Status,
users.counselor_userid
FROM
users
INNER JOIN meetings ON users.id = meetings.user_id
LEFT JOIN measures ON users.id = measures.user_id
LEFT JOIN businesses ON users.id = businesses.user_id
LEFT JOIN businesstypes ON businesses.businesstype_id = businesstypes.id
Left JOIN usertypes ON usertypes.id = users.usertype_id
left JOIN businessforms ON businesses.businessform_id = businessforms.id
WHERE
meetings.meetingdate BETWEEN [$Begin Date] AND [$End Date]AND
measures.measuretype_id IN (8,9,12)
GROUP BY
users.id
ORDER BY
users.id ASC

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 77014 More actions · Answer 1

Please post sample data and expected results as in the first link in my signature.

That being said, if you want to find a value base on the MAX/MIN of another field, you should look at ROW_NUMBER if you want a subset of the results (usually 1) per partition. You should look at FIRST_VALUE/LAST_VALUE if you want to spread the value across the entire rowset.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

darren 75071 Grasshopper Points: 19 More actions · Answer 2

data set

iduser_idbusiness_idmeasuretype_idvaluemeasuredate

14382191931120007/24/2006 0:00

1439219193227/24/2006 0:00

1440219193327/24/2006 0:00

14412191934FALSE7/24/2006 0:00

14422191936FALSE7/24/2006 0:00

1443219193817/24/2006 0:00

1818621919311000005/20/2010 0:00

18187219193225/20/2010 0:00

18188219193325/20/2010 0:00

181892191935Homeowner5/20/2010 0:00

181902191936FALSE5/20/2010 0:00

18191219193815/20/2010 0:00

181922191931205/20/2010 0:00

181932191939600005/20/2010 0:00

1819421919310500005/20/2010 0:00

18195219193112505/20/2010 0:00

End Result I would like

idfirst_namelast_nameBusNameBusIndustryBusFormBusRevenueBusFT_EmpBusPT_EmpUser_Statuscounselor_userid

219CarolLewisModern Parents MagazineDesktop PublishingOther/Unknown6000010Active1

Max is pulling the data but it pulls as you know the largest number for the ID. Trying to pull the LASt entry on the ID. Not sure how to right the query. This is what I have now

SELECT

users.id,

users.first_name,

users.last_name,

businesses.`name` AS BusName,

businesstypes.`name` AS BusIndustry,

businessforms.`name` AS BusForm,

MAX(Case WHEN measures.measuretype_id = 9 THEN measures.`value` END) AS BusRevenue,

MAX(Case measures.measuretype_id WHEN 8 THEN measures.`value` END) AS BusFT_Emp,

MAX(Case measures.measuretype_id WHEN 12 THEN measures.`value` END) AS BusPT_Emp,

usertypes.`name` AS User_Status,

users.counselor_userid

FROM

users

INNER JOIN meetings ON users.id = meetings.user_id

LEFT JOIN measures ON users.id = measures.user_id

LEFT JOIN businesses ON users.id = businesses.user_id

LEFT JOIN businesstypes ON businesses.businesstype_id = businesstypes.id

Left JOIN usertypes ON usertypes.id = users.usertype_id

left JOIN businessforms ON businesses.businessform_id = businessforms.id

WHERE

meetings.meetingdate BETWEEN [$Begin Date] AND [$End Date]AND

measures.measuretype_id IN (8,9,12)

GROUP BY

users.id

ORDER BY

users.id ASC

drew.allen SSC Guru Points: 77014 More actions · Answer 3

I'm sorry, perhaps I should have been clearer. When I said to post data as specified in the link, I meant the method under the title The Correct Way to Post data, not the method under The Incorrect Way to Post Data.

If you want tested solutions to your question it helps if you can do as much of the upfront work as possible for people.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 77014 More actions · Answer 4

You want to do something like the following, which uses a CTE with a ROW_NUMBER and then filters based on the ROW_NUMBER.

;

WITH dataset AS (

SELECT *, ROW_NUMBER() OVER(PARTITION BY dataset.user_id ORDER BY dataset.measuredate DESC, dataset.value DESC) AS rn

FROM ( -- Replace the TABLE VALUES CONSTRUCTOR with your table

VALUES

(1438, 219, 193, 1, '12000', CAST('20060724' AS DATE)),

(1439, 219, 193, 2, '2', '20060724'),

(1440, 219, 193, 3, '2', '20060724'),

(1441, 219, 193, 4, 'FALSE', '20060724'),

(1442, 219, 193, 6, 'FALSE', '20060724'),

(1443, 219, 193, 8, '1', '20060724'),

(18186, 219, 193, 1, '100000', '20100520'),

(18187, 219, 193, 2, '2', '20100520'),

(18188, 219, 193, 3, '2', '20100520'),

(18189, 219, 193, 5, 'Homeowner', '20100520'),

(18190, 219, 193, 6, 'FALSE', '20100520'),

(18191, 219, 193, 8, '1', '20100520'),

(18192, 219, 193, 12, '0', '20100520'),

(18193, 219, 193, 9, '60000', '20100520'),

(18194, 219, 193, 10, '50000', '20100520'),

(18195, 219, 193, 11, '250', '20100520')

) dataset(id, user_id, business_id, measuretype_id, value, measuredate)

WHERE dataset.measuretype_id IN (8, 9, 12)

)

SELECT *

FROM dataset

WHERE rn = 1

NOTE: I have used a table value constructor. You will want to replace that with your table.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

-- untested, no data available (see Drew's post)

SELECT

users.id,

users.first_name,

users.last_name,

businesses.`name` AS BusName,

businesstypes.`name` AS BusIndustry,

businessforms.`name` AS BusForm,

x.BusRevenue,

x.BusFT_Emp,

x.BusPT_Emp,

usertypes.`name` AS User_Status,

users.counselor_userid

FROM users

INNER JOIN meetings ON users.id = meetings.user_id

--LEFT JOIN measures ON users.id = measures.user_id

OUTER APPLY (

SELECT

MAX(Case WHEN measures.measuretype_id = 9 THEN measures.`value` END) AS BusRevenue,

MAX(Case measures.measuretype_id WHEN 8 THEN measures.`value` END) AS BusFT_Emp,

MAX(Case measures.measuretype_id WHEN 12 THEN measures.`value` END) AS BusPT_Emp

FROM measures

WHERE users.id = measures.user_id

AND measures.measuretype_id IN (8,9,12)

) measures

LEFT JOIN businesses

ON users.id = businesses.user_id

LEFT JOIN businesstypes

ON businesses.businesstype_id = businesstypes.id

Left JOIN usertypes

ON usertypes.id = users.usertype_id

left JOIN businessforms

ON businesses.businessform_id = businessforms.id

WHERE meetings.meetingdate BETWEEN [$Begin Date] AND [$End Date]

AND measures.measuretype_id IN (8,9,12)

--GROUP BY users.id

ORDER BY users.id ASC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

darren 75071 Grasshopper Points: 19 More actions · Answer 6

Thanks for the quick replies.. I am working on the correct way to post. I apologize new to this.