En SQL Nød at knække

Det skal iøvrigt nævnes, at der ønskes en SQL-sætning der kan eksekveres på Microsofts JET sprog. Data kommer fra et Excel regneark der bliver åbnet som en database.

reffak>What about the actual date, does this have any say in the SQL or is it JUST the time?

which program are you working from VB, Access?

Terry : My last comment concearning time, is a bit misleading. I meant to say, that the perfomance of the solution isn't important. Moreso the selection of the proper dates.

The solution should be working on Access, however, it is really an excel spreadsheet being opened by the JET engine. This does present some limitations:

-You cannot define stored procedures
-You cannot define views

However, I still think it should be possible.

Kind Regards,

reffak>I understood your last comment. But I want to know if the DATE also needs tobe taken into concideration in the SQL. You only mention time (kl. 14.58) in your examples and NOT date!

Also which program are you working with, this MAY have influence on the solution!

Alright, now I know what you mean.
And yes, the date has to be accounted for. Observations are processed for each month. Here is a sample file to be processed:

http://www.hghardware.dk/sdm_0112.xls

I am working with Visual Studio .NET..

reffak>This may be possible to solve in SQL but Jet does have its limitations. This is NOT a simple SQL so it will more than likely take more than one SQL and a bit of coding to solve. I was looking at a solution in Access but that seems out of the question now.
I havent worked with VS .NET! If it was possible to import the sheet into Access then I may be able to help!

Bare lige 3 dumme spørgsmål:

  - Hvilken af tiderne 15:45 og 16:15 er tættest på 16:00, hvis denne ikke er representeret?
  - Hvorfor aflæser du ikke "bare" hver hele time?
  - Hvorfor bruger du ikke gennemsnitsfunktioner, hvor du grupperer på dato og time?  Som i: SELECT Int(Tidspunkt) AS Dato, Hour(Tidspunkt) AS HeleTime, Avg(Aflæsning) AS Aflægning1 FROM Tabel GROUP BY Int(Tidspunkt), Hour(Tidspunkt);

hi proaccess>Yes it would be easier if readings were made at FIXED intervals. I had made a query in Access calculating the difference in minutes between each reading and the hour and also for the previous hour and the next hour and making this into a UNION JOIN. So for 14:58 I would get the following

Time  Hour  Diff
14:58  13    118
14:58  14    58
14:58  15    2

If I did this for all times I could then find the smallest value for each hour. But I was using DataPart and DateDiff and I dont think these will be supported through JET.

proaccess:
-I tilfælde af lige nærme værdier vælges den seneste
-Jeg kan ikke være sikker på, at der er foretaget en opsamling hver hele time, som terry også påpeger.
-Gennemsnitsfunktioner er en rigtigt godt ide, men i 95% af tilfældene vil en hel timeværdi være tilgængelig. Derfor vil det påføre målingerne en unødig støj i langt de fleste tilfælde.

Terry:
DatePart and DateDiff are supported by JET, so your solution would be valid.

Så er der en løsning:

først sammenkæder du dit regneark med tabel-navnet: [113442156]
derefter laver du følgende forespørgsel, kaldet [113442156_Mindste afvigelse pr time]

SELECT Int([F1]) AS Dato, Hour((([F1]-Int([F1]))*24+0.5)/24) AS HeleTime, Min(Abs(([F1]-Int([F1]))-TimeSerial(Hour((([F1]-Int([F1]))*24+0.5)/24),0,0))) AS Afvigelse
FROM 113442156
GROUP BY Int([F1]), Hour((([F1]-Int([F1]))*24+0.5)/24);

Derefter bruger du følgende forespørgsel, for at finde de relevante resultater:
SELECT [113442156].*
FROM 113442156, [113442156_Mindste afvigelse pr time]
WHERE ((([113442156_Mindste afvigelse pr time].Dato)=Int([F1])) AND (([113442156_Mindste afvigelse pr time].HeleTime)=Hour((([F1]-Int([F1]))*24+0.5)/24)) AND (([113442156_Mindste afvigelse pr time].Afvigelse)=Abs(([F1]-Int([F1]))-TimeSerial(Hour((([F1]-Int([F1]))*24+0.5)/24),0,0))));

BEMÆRK: hvis der er en måling 14 minutter før og een 15 minutter efter et timeslag, så er det den 14 minutter før, som vælges...

Udarbejdet efter dit oprindelige spørgsmål!

Tak for det :-)