January 28, 2016 at 1:32 pm
Need help with this query (only 1 out of DUP account rows need to be returned (the one with an earlier date in PTPFORMDATE field)
The attached PDF shows the result of the query in green (with the Account_nbr as its last line). The actual query is the bottom one, which returns a set of such dups accouts (where only PTPFORMDATE (jk.formdate) is what makes the rows different). We need only one row returned for EACH ACCOUNT (no dup account #s should be presennt in the resultset), the row where the DATE in PTPFORMDATE (jk.formdate) is EARLIER.
only The earliest-dated row of 2 or more dup rows should be returned.
PDF attached. and the TXT of the actual query
[highlight="#ffff11"]THANKS in advance for a possible solution ! [/highlight]
(just need to know where in the query to apply modification to, and what modification, if any..)
Likes to play Chess
January 28, 2016 at 1:53 pm
You could use ROW_NUMBER in a cte and then select those rows where RowNum = 1.
It is hard to figure out from your posted query exactly what you are trying to do but you have a couple issues in there. The first if udf_Today. What is that scalar function doing and do you really need it? The second is that you are doing date math without being explicit. You have your function - 4. You should be explicit there and use DateAdd. Also you have distinct and a group by in the outer query. There is no need for both distinct and a group by.
Totally guessing here since we don't have tables, sample data or desired output but I think this should be close.
with SortedData as
(
SELECT distinct
jk.Account_nbr,
jk.PTPDate,
minptp.ptpamount,
convert(datetime, convert(varchar(10), jk.formdate, 101)) as ptpformdate,
jk.status
, ROW_NUMBER() over(partition by jk.Account_nbr order by jk.formdate) as RowNum
FROM TB_PTP_HISTORY jk
INNER JOIN
(
SELECT --distinct the distinct is pointless. You have a group by already
min(ptpdate) AS mindate
, Account_nbr
, max(ptpamount) as ptpamount
FROM tb_ptp_history
where ptpdate >= DATEADD(day, -4, dbo.udf_Today()) --The scalar function seems potentially unnessecary.
and status = 'Pending'
GROUP BY Account_nbr
) AS minptp ON jk.Account_nbr = minptp.Account_nbr
AND jk.ptpdate = minptp.mindate
where jk.status not in ('kept','broken','cancelled')
)
select Account_Nbr
, PTPDate
, ptpamount
, ptpformdate
, status
from SortedData
where RowNum = 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2016 at 3:11 pm
Thank you !!! You are the man.
I really appreciate your help.
Question:
are you actually really coaching some dumber folks like myself in SQL server remotely (I mean for pay) ?
V
Likes to play Chess
January 28, 2016 at 3:19 pm
VoldemarG (1/28/2016)
Thank you !!! You are the man.I really appreciate your help.
Question:
are you actually really coaching some dumber folks like myself in SQL server remotely (I mean for pay) ?
V
LOL. No SSCoach is my "level". Notice on your posts you are listed as "SSC Rookie"? I wish I got paid for the time I spend helping people on the forums...oh wait...I do get paid. I get paid by making myself more knowledgeable with sql server and the good feeling of helping people that need it. I wish I could also figure out how to get some money for this but I don't think that is going to happen. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2016 at 3:48 am
you mean you actually are not doing SQL/databases/programming for living?!
how can that be...
Likes to play Chess
January 29, 2016 at 6:18 am
The reason i asked about that was because there are a couple of folks who i work with who specifically asked me if I know a SQL Coach who could remotely provide certain ongoing education and problem solving approaches.
Likes to play Chess
January 29, 2016 at 7:13 am
VoldemarG (1/29/2016)
you mean you actually are not doing SQL/databases/programming for living?!how can that be...
I didn't say that, I just said I don't get paid for providing assistance on the forums. I am actually a developer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply