T-SQL in vfp 8 to find same value 1

[bharons]

Good morning experts..
request correction for the T-sql coding error below.
it is give me notification that windowed function can be use in select and order clause.

Local lcSQL1, lcSQL2
*norekening = crsSampleData.norekening
norekening = 1111125010021
TEXT TO m.lcSQL1 noshow
create table zonaku (norekening decimal(14), date1 smalldatetime, date2 smalldatetime, payment1 decimal (14))
INSERT INTO zonaku VALUES (1111125010021, CONVERT(VARCHAR, '12/05/2024', 103), CONVERT(VARCHAR, '12/16/2024', 103), 650000)
INSERT INTO zonaku VALUES (1111125010021, CONVERT(VARCHAR, '02/11/2025', 103), CONVERT(VARCHAR, '01/16/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010021, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '02/16/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010021, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/16/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010021, CONVERT(VARCHAR, '03/06/2025', 103), CONVERT(VARCHAR, '04/16/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010021, CONVERT(VARCHAR, '05/05/2025', 103), CONVERT(VARCHAR, '05/16/2025', 103), 650000)
ENDTEXT
lnResult = SQLExec(Thisform.nHandle, m.lcSQL1)

TEXT TO m.lcSQL2 noshow
select norekening, date1, date2, payment1,
datediff(day,date1, date2) as difference,
datediff(month, date1,date2) as difference1,
MAX(datediff(day, date1, date2)) over(partition by norekening) AS difference2
from dbo.zonaku where norekening = ?m.norekening and
datediff(day,date1, date2) as difference =
MAX(datediff(day, date1, date2)) over(partition by norekening)
group by norekening, date1, date2, payment1
ENDTEXT
lnResult = SQLExec(Thisform.nHandle, m.lcSQL2, 'mutation')

If m.lnResult < 0
    Aerror(aWhy)
    Messagebox(aWhy[2])
    SQLDisconnect(0)
    Return .F.
Else
endif

Thank you for your advice and direction.

 

[Paul Gibson]

I'm not 100% clear on what you are trying to achieve as you've not described your aim of the SELECT. My guess is that you are attempting to a) calculate the day differences and the month differences and then b) pull out just the row where the day difference is the largest per norekening.

If that's not what you are looking for you can probably ignore the rest of this reply

Are you using a SQL engine that supports Common Table Expressions (CTEs)? Sometimes, if a query is quite complicated to do in a oner I like to split it up into CTEs to make things a lot clearer. To that end, I have a query that will do what I've guessed you are trying to achieve, it is using CTE's to split it up into that logical process workflow, i.e. the first CTE adds those datediff columns to calculate the days and months differences, the second CTE then ranks each row based on the day difference, partitioning by what looks to be your main foreign key and then the final SELECT only shows any rows ranked 1, i.e. the maximum date difference per norekening:

Apologies if that is not what you are looking for .... or if you can't use CTEs

WITH cteWithDifference AS (
    SELECT norekening, date1, date2, payment1,
    datediff(day,date1, date2) as difference,
    datediff(month, date1,date2) as difference1
    FROM [zonaku]
    WHERE norekening = 1111125010021
), cteRanked AS (
    SELECT cte.*
    ,ROW_NUMBER() OVER(PARTITION BY norekening ORDER BY difference DESC) AS [Rank]
    FROM [cteWithDifference] cte
)
SELECT cte.*
FROM [cteRanked] cte
WHERE cte.[Rank] = 1

EDIT: I made a small mistake, I put ORDER BY difference1 instead of ORDER BY difference, I have edited the above to correct that. The mistake would have got the max month-difference row, not the max day-difference row. That was an easy mistake to make when working quickly because the column names are not particularly good - it is advisable to stay away from using column names that have 1 or 2 or 3 or any numeric in the name unless it 100% makes sense to do so. Very clear column names makes everything else very clear and reduces the chances of mistakes.

My sample is hard-coded for the value 1111125010021 but you can sort that out yourself and you can tidy up anything else where I've left it too simple or made assumptions.

 

[Paul Gibson]

A small refinement to my previous suggestion is that if you don't like the two-CTE solution, I realised it would be fine to merge the two CTEs into one, which makes it look like this:

WITH cteWithDifferenceAndRanked AS (
    SELECT norekening, date1, date2, payment1,
    datediff(day, date1, date2) as difference,
    datediff(month, date1, date2) as difference1
    ,ROW_NUMBER() OVER(PARTITION BY norekening ORDER BY datediff(day, date1, date2) DESC) AS [Rank]
    FROM [zonaku]
    WHERE norekening = 1111125010021
)
SELECT cte.*
FROM [cteWithDifferenceAndRanked] cte
WHERE cte.[Rank] = 1

Obviously, doing it that way still relies on being able to use CTEs but I think that query looks fairly clean and easy to understand what it is doing.

If anyone has got a good suggestion for doing that without a CTE and it still being very clear, I look forward to increasing my knowledge.

 

[Paul Gibson]

I'm a bit dumb sometimes

Obviously, the easiest way to do that without a CTE and it's still fairly clear, is to just encapsulate one query inside the other, as such:

SELECT rnk.*
FROM (
    SELECT norekening, date1, date2, payment1,
    datediff(day, date1, date2) as difference,
    datediff(month, date1, date2) as difference1
    ,ROW_NUMBER() OVER(PARTITION BY norekening ORDER BY datediff(day, date1, date2) DESC) AS [Rank]
    FROM [zonaku]
    WHERE norekening = 1111125010021
) rnk
WHERE rnk.[Rank] = 1

As soon as I merged the two-CTEs into one that should have been clear to me. So in theory, your TEXT ... ENDTEXT block could be written as:

TEXT TO m.lcSQL2 noshow
SELECT rnk.*
FROM (
    SELECT norekening, date1, date2, payment1,
    datediff(day, date1, date2) as difference,
    datediff(month, date1, date2) as difference1
    ,ROW_NUMBER() OVER(PARTITION BY norekening ORDER BY datediff(day, date1, date2) DESC) AS [Rank]
    FROM [zonaku]
    WHERE norekening = ?m.norekening
) rnk
WHERE rnk.[Rank] = 1
ENDTEXT

...and I think that should work for you.... if I understand your aim correctly in the first place.

 

[bharons]

I'm a bit dumb sometimes

Obviously, the easiest way to do that without a CTE and it's still fairly clear, is to just encapsulate one query inside the other, as such:

SELECT rnk.*
FROM (
    SELECT norekening, date1, date2, payment1,
    datediff(day, date1, date2) as difference,
    datediff(month, date1, date2) as difference1
    ,ROW_NUMBER() OVER(PARTITION BY norekening ORDER BY datediff(day, date1, date2) DESC) AS [Rank]
    FROM [zonaku]
    WHERE norekening = 1111125010021
) rnk
WHERE rnk.[Rank] = 1

As soon as I merged the two-CTEs into one that should have been clear to me. So in theory, your TEXT ... ENDTEXT block could be written as:

TEXT TO m.lcSQL2 noshow
SELECT rnk.*
FROM (
    SELECT norekening, date1, date2, payment1,
    datediff(day, date1, date2) as difference,
    datediff(month, date1, date2) as difference1
    ,ROW_NUMBER() OVER(PARTITION BY norekening ORDER BY datediff(day, date1, date2) DESC) AS [Rank]
    FROM [zonaku]
    WHERE norekening = ?m.norekening
) rnk
WHERE rnk.[Rank] = 1
ENDTEXT

...and I think that should work for you.... if I understand your aim correctly in the first place.

thanks Mr. Gibbs.. above your code i implement to my form.. and give me the answer that i want..

 

[bharons]

thanks Mr. Gibbs.. above your code i implement to my form.. and give me the answer that i want..

one more question,
I get all the records from your coding, the problem is why all the records appear if the difference and difference2 have a value of 0 (zero).
and can I get 1 row from the difference and difference2 with a value of 0 (zero)

 

[Paul Gibson]

My last query still only pulls one record "per group" even if all the days differences in their data set equals zero. That's the point of using the ROW_NUMBER() OVER(...) column to rank.

To prove this, I took you sample and made a new group for norekening = 1111125010022, where every single row would result in the days difference being zero, as such:

INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)

I then ran my query again, I just took out the WHERE clause, for me because I only have the two different norekening values in it, as such:

SELECT rnk.*
FROM (
    SELECT norekening, date1, date2, payment1,
    datediff(day, date1, date2) as difference,
    datediff(month, date1, date2) as difference1
    ,ROW_NUMBER() OVER(PARTITION BY norekening ORDER BY datediff(day, date1, date2) DESC) AS [Rank]
    FROM [zonaku]
) rnk
WHERE rnk.[Rank] = 1

The result was, exactly as I expected, only one row per group, i.e. one row per norekening:



...so I'm not sure what you mean when you say:

> why all the records appear if the difference and difference2 have a value of 0 (zero).

...I've just proved that they don't. When you say:

> and can I get 1 row from the difference and difference2 with a value of 0 (zero)

...that seems to be exactly what my query does.

Perhaps you need to be a lot clearer what what you mean, what you are looking for, what you have in your data, what you are getting back and what you need it to be. We're having to do a lot of guessing which is probably why, what I think I'm showing isn't 100% what you are looking for.

 

[bharons]

My last query still only pulls one record "per group" even if all the days differences in their data set equals zero. That's the point of using the ROW_NUMBER() OVER(...) column to rank.

To prove this, I took you sample and made a new group for norekening = 1111125010022, where every single row would result in the days difference being zero, as such:

INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)
INSERT INTO zonaku VALUES (1111125010022, CONVERT(VARCHAR, '03/05/2025', 103), CONVERT(VARCHAR, '03/05/2025', 103), 650000)

I then ran my query again, I just took out the WHERE clause, for me because I only have the two different norekening values in it, as such:

SELECT rnk.*
FROM (
    SELECT norekening, date1, date2, payment1,
    datediff(day, date1, date2) as difference,
    datediff(month, date1, date2) as difference1
    ,ROW_NUMBER() OVER(PARTITION BY norekening ORDER BY datediff(day, date1, date2) DESC) AS [Rank]
    FROM [zonaku]
) rnk
WHERE rnk.[Rank] = 1

The result was, exactly as I expected, only one row per group, i.e. one row per norekening:

View attachment 2417

...so I'm not sure what you mean when you say:

> why all the records appear if the difference and difference2 have a value of 0 (zero).

...I've just proved that they don't. When you say:

> and can I get 1 row from the difference and difference2 with a value of 0 (zero)

...that seems to be exactly what my query does.

Perhaps you need to be a lot clearer what what you mean, what you are looking for, what you have in your data, what you are getting back and what you need it to be. We're having to do a lot of guessing which is probably why, what I think I'm showing isn't 100% what you are looking for.

whoops.. sorry that is one account.. I will give you several accounts with a certain amount of activity..