November 13, 2012 at 10:07 am
Good Evening All,
Im having some frustrating problems with what seems initally to be a simple query,
SELECT Distinct(Customer), MAX(date), MAX(Status), JobType
FROM [Data_Warehouse].[dbo].[Issued_Jobs]
join dbo.job on [Job Ref] = Job_reference
Group by job_policy, jdt_jty_code
The issue is with "MAX(Status)",
what the function is doing is taking the maximum alphaetic character (the closest to Z) at all times, where i would like it to the last status which matches the "Max(date)"
Any help on this would be much appreciated
Thanks
November 13, 2012 at 10:44 am
When using a VARCHAR() or CHAR() column the MAX() on a field returns the highest letter (I think its based on the ASCII CHAR() value but i could be wrong, someone with more knowledge than me will have to answer to exactly how the sorting happens on varchar columns.)
Instead of using the VARCHAR convert it to an INT as a foreign key to a dimension table holding the description if you can change the schema (of course there may be code changes to deal with as well) The dimension table would look something like the following:
CREATE TABLE JobStatus (
JobStatusID INT PRIMARY KEY,
Description VARCHAR(32)
)
INSERT INTO JobStatus (JobStatusID, Description)
SELECT 1, 'CBS' UNION ALL
SELECT 2, 'CP' UNION ALL
SELECT 3, 'INP'
Then you can arrange your codes to correspond where the MAX(JobStatusID) returns what you are looking for.
EDIT Missed the OP is looking for the status code on the MAX(DATE) not the MAX(JobStatus).
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 13, 2012 at 10:52 am
Thanks for the reply CapnHector,
I understand completely where your coming from, however, if i were to create this table and then reference MAX(JobStatusID), would this again pose the same problem,
where the previous status may be 3 (INP), and the current status 2 (CP), would it not just return 3 (INP)?
November 13, 2012 at 11:13 am
Ok here is the answer to the actual question. What we have is the classic i need all the columns but only with the MAX(Date). There are 2 ways i see to do this First is with a row number ordered by the date decending and then select all rows where the row number is equal to 1:
WITH base AS (
SELECT CustomerID, EntryDate, StatusCode
FROM (VALUES (1,'2012-01-01','INP'),
(1,'2012-01-02','INP'),
(1,'2012-01-03','CP'),
(2,'2012-01-01','INP'),
(2,'2012-01-02','INP'),
(2,'2012-01-03','CPX')
)x(CustomerID, EntryDate, StatusCode)),
Ordered AS(
SELECT ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY EntryDate DESC) RN, *
FROM base)
SELECT *
FROM Ordered
WHERE RN = 1
The second is to first find the max date in a query containing only the customer id and date then join that to the original table where customerID and date equals the CustomerID and MaxDate
WITH base AS (
SELECT CustomerID, EntryDate, StatusCode
FROM (VALUES (1,'2012-01-01','INP'),
(1,'2012-01-02','INP'),
(1,'2012-01-03','CP'),
(2,'2012-01-01','INP'),
(2,'2012-01-02','INP'),
(2,'2012-01-03','CPX')
)x(CustomerID, EntryDate, StatusCode)),
MaxDate AS (SELECT CustomerID, MAX(EntryDate) AS MaxDate
FROM base
GROUP BY CustomerID)
SELECT *
FROM base b
INNER JOIN MaxDate m
ON b.CustomerID = m.CustomerID
AND b.EntryDate = m.MaxDate
Now there are other ways to accomplish this but any way i can think of takes 2 steps.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply