Hello to all,
I need to execute a very difficult query and I need your help.
The target is create a scheduled export everyday 11.00 AM / 3.00 PM / 9.00 PM.
I have a big DB, but I need just 4 columns.
So I use a simple selection:
SELECT idPrat, CodRichiestaCRM, Telefono, convert(VARCHAR(19), DataOraContatto, 21) as Data
FROM Lav_CollaudiVodaf_Storico
WHERE DataOraContatto >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
AND DataOraContatto < DATEADD(DAY, 1, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
This is for select all the rows of today. Now the question become a little bit complicated.
I need to get maximum 3 records with the same IdPrat, and I'm trying to find a subquery for this thing, but the very complicated part is here:
I must get maximum 3 record all the day, but there is a chanche that I have 3 record in the export of the 11.00 AM, 3 in the 3 PM and 3 at the 9pm, so I must check how many records with the same idPrat are present in the past export.
Example:
11 AM IdPrat
66
66
66
77
77
Export all.
11 AM IdPrat
66
66
66
66
77
77
Export the first the last and the median IdPrat 66
3pm IdPrat
66
77
77
Check how many rows with the same IdPrat, insert if there aren't 3 rows with the same IdPrat, update with the last record in order of time.
So the same for the export at 9PM
I know, I wrote very bad, and is very twisted.
a link with csv for the records of the table.
https://drive.google.com/file/d/0Byhec8pWx4CrbDZiZFlNbkg2X1E/view?usp=sharing