January 12, 2017 at 4:57 pm
In the following t-sql 2012, I want to select records from atlltr table that have the
largest updateddate date (most current). The atlltr table can have 1 or more records where
the atlltr.updateddate can be the same. I have the following code that is not working correctly below.
The following code picks more records than what is the most current updateddate records.
;with CurStringTemplate as
(
SELECT stulink,stringtemplate,updateddate=MAX(updateddate)
FROM atlltr
WHERE (atlltr.language IS NOT NULL)
AND (atlltr.stringtemplate IS NOT NULL)
AND (atlltr.documenttype IS NOT NULL)
AND (atlltr.printeddate IS NOT NULL)
GROUP BY stulink,updateddate,stringtemplate
HAVING updateddate=MAX(updateddate)
)
SELECT
, RIGHT(REPLICATE(''0'', 7) + atlltr.stulink, 7) AS stulink
, atlltr.milestonecode
, atlltr.language
, CurStringTemplate.stringtemplate
, atlltr.documenttype
FROM atlltr atlltr
JOIN dbo.atlltrmile atlltrmile
ON atlltr.stulink= atlltrmile.stulink
AND atlltr.schoolyear= atlltrmile.SCHOOLYEAR
AND atlltr.schoolnum=atlltrmile.SCHOOLNUM
AND atlltr.milestonecode=atlltrmile.MILESTONE_CODE
AND atlltr.semester=atlltrmile.SEMESTER
AND atlltrmile.PRINTED = ''Y''
AND (atlltr.language IS NOT NULL)
AND (atlltr.stringtemplate IS NOT NULL)
AND (atlltr.documenttype IS NOT NULL)
AND (atlltr.printeddate IS NOT NULL)
JOIN CurStringTemplate
ON CurStringTemplate.stulink = atlltr.stulink
AND CurStringTemplate.updateddate = atlltr.updateddate
AND CurStringTemplate.stringtemplate = atlltr.stringtemplate
Thus would you tell me what I can do to solve my problem?
January 12, 2017 at 5:59 pm
dianerstein 8713 - Thursday, January 12, 2017 4:57 PMIn the following t-sql 2012, I want to select records from atlltr table that have the
largest updateddate date (most current). The atlltr table can have 1 or more records where
the atlltr.updateddate can be the same. I have the following code that is not working correctly below.
The following code picks more records than what is the most current updateddate records.;with CurStringTemplate as
(
SELECT stulink,stringtemplate,updateddate=MAX(updateddate)
FROM atlltr
WHERE (atlltr.language IS NOT NULL)
AND (atlltr.stringtemplate IS NOT NULL)
AND (atlltr.documenttype IS NOT NULL)
AND (atlltr.printeddate IS NOT NULL)
GROUP BY stulink,updateddate,stringtemplate
HAVING updateddate=MAX(updateddate)
)
SELECT
, RIGHT(REPLICATE(''0'', 7) + atlltr.stulink, 7) AS stulink
, atlltr.milestonecode
, atlltr.language
, CurStringTemplate.stringtemplate
, atlltr.documenttype
FROM atlltr atlltr
JOIN dbo.atlltrmile atlltrmile
ON atlltr.stulink= atlltrmile.stulink
AND atlltr.schoolyear= atlltrmile.SCHOOLYEAR
AND atlltr.schoolnum=atlltrmile.SCHOOLNUM
AND atlltr.milestonecode=atlltrmile.MILESTONE_CODE
AND atlltr.semester=atlltrmile.SEMESTER
AND atlltrmile.PRINTED = ''Y''
AND (atlltr.language IS NOT NULL)
AND (atlltr.stringtemplate IS NOT NULL)
AND (atlltr.documenttype IS NOT NULL)
AND (atlltr.printeddate IS NOT NULL)
JOIN CurStringTemplate
ON CurStringTemplate.stulink = atlltr.stulink
AND CurStringTemplate.updateddate = atlltr.updateddate
AND CurStringTemplate.stringtemplate = atlltr.stringtemplateThus would you tell me what I can do to solve my problem?
What about something like:
;WITH CurStringTemplate as
(
SELECT
stulink
, stringtemplate
, updateddate
, Rank() OVER (PARTITION BY stulink, stringtemplate ORDER BY updateddate DESC) Rnk
FROM atlltr
WHERE (atlltr.language IS NOT NULL )
AND (atlltr.stringtemplate IS NOT NULL)
AND (atlltr.documenttype IS NOT NULL )
AND (atlltr.printeddate IS NOT NULL )
)
SELECT
Right(REPLICATE('0', 7) + atlltr.stulink, 7) AS stulink
, atlltr.milestonecode
, atlltr.language
, CurStringTemplate.stringtemplate
, atlltr.documenttype
FROM
atlltr atlltr
JOIN
dbo.atlltrmile atlltrmile ON atlltr.stulink= atlltrmile.stulink
AND atlltr.schoolyear= atlltrmile.SCHOOLYEAR
AND atlltr.schoolnum=atlltrmile.SCHOOLNUM
AND atlltr.milestonecode=atlltrmile.MILESTONE_CODE
AND atlltr.semester=atlltrmile.SEMESTER
JOIN
CurStringTemplate ON CurStringTemplate.stulink = atlltr.stulink
AND CurStringTemplate.updateddate = atlltr.updateddate
AND CurStringTemplate.stringtemplate = atlltr.stringtemplate
WHERE CurStringTemplate.Rnk = 1
AND (atlltr.stringtemplate IS NOT NULL)
AND (atlltr.language IS NOT NULL)
AND (atlltr.documenttype IS NOT NULL)
AND atlltrmile.PRINTED = 'Y'
AND (atlltr.printeddate IS NOT NULL);
January 13, 2017 at 7:34 am
You are very nearly there. Try to think of the problem as two separate steps:
1) How do I find the last date in the system
2) How do I find the records with that last date
Your CTE is returning three pieces of information:
stulink
stringtemplate
updatedate
however the way it is written there may be more than one result as you will get a update date for every combination of stulink/stringtemplate
Joe's solution of adding an RANK() to the CTE and then using RANK = 1 as a filter in the second query gets the right result, but if you only want records on the last date irrespective of stulink and stringtemplate then simply remove them from the CTE and the join criteria
January 13, 2017 at 9:05 am
aaron.reese - Friday, January 13, 2017 7:34 AMYou are very nearly there. Try to think of the problem as two separate steps:
1) How do I find the last date in the system
2) How do I find the records with that last dateYour CTE is returning three pieces of information:
stulink
stringtemplate
updatedatehowever the way it is written there may be more than one result as you will get a update date for every combination of stulink/stringtemplate
Joe's solution of adding an RANK() to the CTE and then using RANK = 1 as a filter in the second query gets the right result, but if you only want records on the last date irrespective of stulink and stringtemplate then simply remove them from the CTE and the join criteria
I want both the values of stulink and stringtemplate. The stulink value is the unique identifier of a student and the value in stringtemplate is the value I want on the ssrs report. The date field is the only I can get to the stringtemplate values for the stulinks (students) that I want to appear on the report.
January 13, 2017 at 9:08 am
dianerstein 8713 - Thursday, January 12, 2017 4:57 PM
SELECT stulink,stringtemplate,updateddate=MAX(updateddate)FROM atlltr
WHERE (atlltr.language IS NOT NULL)
AND (atlltr.stringtemplate IS NOT NULL)
AND (atlltr.documenttype IS NOT NULL)
AND (atlltr.printeddate IS NOT NULL)
GROUP BY stulink,updateddate,stringtemplate
HAVING updateddate=MAX(updateddate)
I wanted to point out one issue with your CTE. I've highlighted the relevant areas.
By definition, the values for each of the GROUP BY expressions is INVARIANT within a group. You are taking the MAX of one of those invariant expressions. Because the value is invariant within each group, EVERY updateddate is equal to the MAX(updateddate), and also equal to MIN(updateddate) and AVG(updateddate).
Furthermore, since the HAVING clause is evaluated after the SELECT clause, and you are assigning the alias updateddate to MAX(updateddate), your HAVING clause is equivalent to HAVING MAX(updateddate) = MAX(updateddate).
Drew
PS: I inserted the IFCode tags in the quote, because it makes it easier to read.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 13, 2017 at 9:24 am
dianerstein 8713 - Friday, January 13, 2017 9:05 AMaaron.reese - Friday, January 13, 2017 7:34 AMYou are very nearly there. Try to think of the problem as two separate steps:
1) How do I find the last date in the system
2) How do I find the records with that last dateYour CTE is returning three pieces of information:
stulink
stringtemplate
updatedatehowever the way it is written there may be more than one result as you will get a update date for every combination of stulink/stringtemplate
Joe's solution of adding an RANK() to the CTE and then using RANK = 1 as a filter in the second query gets the right result, but if you only want records on the last date irrespective of stulink and stringtemplate then simply remove them from the CTE and the join criteria
I want both the values of stulink and stringtemplate. The stulink value is the unique identifier of a student and the value in stringtemplate is the value I want on the ssrs report. The date field is the only I can get to the stringtemplate values for the stulinks (students) that I want to appear on the report.
The way to think about this is that the PARTITION BY clause translates into English as "FOR EACH".
-- For each student (stulink) and comment (stringtemplate). Almost definitely NOT what you want.
Rank() OVER (PARTITION BY stulink, stringtemplate ORDER BY updateddate DESC) Rnk
-- For each student (stulink). Possibly, but it doesn''t match your English description.
Rank() OVER (PARTITION BY stulink ORDER BY updateddate DESC) Rnk-- Global (no for each) thus no partition. Matches your English description.Rank() OVER (PARTITION BY stulink ORDER BY updateddate DESC) Rnk
-- Global (no for each) thus no partition. Matches your English description.
Rank() OVER (ORDER BY updateddate DESC) Rnk
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 13, 2017 at 10:23 am
drew.allen - Friday, January 13, 2017 9:24 AMdianerstein 8713 - Friday, January 13, 2017 9:05 AMaaron.reese - Friday, January 13, 2017 7:34 AMYou are very nearly there. Try to think of the problem as two separate steps:
1) How do I find the last date in the system
2) How do I find the records with that last dateYour CTE is returning three pieces of information:
stulink
stringtemplate
updatedatehowever the way it is written there may be more than one result as you will get a update date for every combination of stulink/stringtemplate
Joe's solution of adding an RANK() to the CTE and then using RANK = 1 as a filter in the second query gets the right result, but if you only want records on the last date irrespective of stulink and stringtemplate then simply remove them from the CTE and the join criteria
I want both the values of stulink and stringtemplate. The stulink value is the unique identifier of a student and the value in stringtemplate is the value I want on the ssrs report. The date field is the only I can get to the stringtemplate values for the stulinks (students) that I want to appear on the report.
The way to think about this is that the PARTITION BY clause translates into English as "FOR EACH".
-- For each student (stulink) and comment (stringtemplate). Almost definitely NOT what you want.
Rank() OVER (PARTITION BY stulink, stringtemplate ORDER BY updateddate DESC) Rnk
-- For each student (stulink). Possibly, but it doesn''t match your English description.
Rank() OVER (PARTITION BY stulink ORDER BY updateddate DESC) Rnk-- Global (no for each) thus no partition. Matches your English description.Rank() OVER (PARTITION BY stulink ORDER BY updateddate DESC) Rnk
-- Global (no for each) thus no partition. Matches your English description.
Rank() OVER (ORDER BY updateddate DESC) RnkDrew
I am not certain what you are saying the correct sql to use is.
Rank() OVER (PARTITION BY stulink, stringtemplate ORDER BY updateddate DESC) Rnk
Do I want to use the above or something else? Would you show me the recommended sql to use and explain why that sql would woirk?
January 13, 2017 at 11:04 am
dianerstein 8713 - Friday, January 13, 2017 10:23 AMdrew.allen - Friday, January 13, 2017 9:24 AMdianerstein 8713 - Friday, January 13, 2017 9:05 AMaaron.reese - Friday, January 13, 2017 7:34 AMYou are very nearly there. Try to think of the problem as two separate steps:
1) How do I find the last date in the system
2) How do I find the records with that last dateYour CTE is returning three pieces of information:
stulink
stringtemplate
updatedatehowever the way it is written there may be more than one result as you will get a update date for every combination of stulink/stringtemplate
Joe's solution of adding an RANK() to the CTE and then using RANK = 1 as a filter in the second query gets the right result, but if you only want records on the last date irrespective of stulink and stringtemplate then simply remove them from the CTE and the join criteria
I want both the values of stulink and stringtemplate. The stulink value is the unique identifier of a student and the value in stringtemplate is the value I want on the ssrs report. The date field is the only I can get to the stringtemplate values for the stulinks (students) that I want to appear on the report.
The way to think about this is that the PARTITION BY clause translates into English as "FOR EACH".
-- For each student (stulink) and comment (stringtemplate). Almost definitely NOT what you want.
Rank() OVER (PARTITION BY stulink, stringtemplate ORDER BY updateddate DESC) Rnk
-- For each student (stulink). Possibly, but it doesn''t match your English description.
Rank() OVER (PARTITION BY stulink ORDER BY updateddate DESC) Rnk-- Global (no for each) thus no partition. Matches your English description.Rank() OVER (PARTITION BY stulink ORDER BY updateddate DESC) Rnk
-- Global (no for each) thus no partition. Matches your English description.
Rank() OVER (ORDER BY updateddate DESC) RnkDrew
I am not certain what you are saying the correct sql to use is.
Rank() OVER (PARTITION BY stulink, stringtemplate ORDER BY updateddate DESC) Rnk
Do I want to use the above or something else? Would you show me the recommended sql to use and explain why that sql would woirk?
Are you saying there is no solution to this problem?
January 13, 2017 at 2:24 pm
dianerstein 8713 - Friday, January 13, 2017 11:04 AMAre you saying there is no solution to this problem?
No, I'm saying that it's not clear which solution is correct. I offered three different options, but I suspect what you want is the last one.
This is why we ask people to post sample data and expected results (as listed in my signature). It can be very difficult to describe what you want in words, but giving sample data and the expected results guarantees that people know exactly what you want.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply