difference between given dates

Question

I have a sqlce database table that I am entering data every day.

Date          No      Meter_1     Meter_2    Meter_3
12-05-2010    1       1234        3456       4567
12-05-2010    2       3423        4567       0987
13-05-2010    1       2345        7654       7654
13-05-2010    2       7676        8765       5643
14-05-2010    1       2345        7654       7654
14-05-2010    2       7676        8765       5643
15-05-2010    1       2345        7654       7654
15-05-2010    2       7676        8765       5643

and I would like to find difference between given 2 dates with datetimepicker and display them in datagrid. Example: If I select Dates between 15th and 12th. Function has to subtract 12th meter values from 15th meter values. It was easy when i was dealing in Access with queries. But I can't do in VB.net. Any help will be highly appreciated.

Thanks to every one

Answer 1

You can write queries against SQLCe so you could continue doing it that way. Otherwise, you can use SqlCeDataReader to retrieve all the data you need from the database (for example any records from the 12th and 15th), cache them in some list or something if needed for easier processing, process the data as needed (subtracting etc) then just add the data in the list to your grid.

See here for a sample of using SqlCeDataReader.

Answer 2

Code which used for sql server is ok but in sqlce doesn't support for declarations. How can I fix to problem. sql server is too complicated for me.

    USE tempdb
GO

CREATE TABLE TableA
(
    [Date] datetime,
    [No] int,
    [Meter_1] int,
    [Meter_2] int,
    [Meter_3] int
)
GO

--add sample data
INSERT INTO TableA VALUES ('2010.05.12', 1, 1000, 2000, 3000);
INSERT INTO TableA VALUES ('2010.05.12', 2, 2000, 3000, 4000);
INSERT INTO TableA VALUES ('2010.05.13', 1, 3000, 4000, 4000);
INSERT INTO TableA VALUES ('2010.05.13', 2, 4500, 5000, 6000);
INSERT INTO TableA VALUES ('2010.05.14', 1, 5000, 6000, 7000);
INSERT INTO TableA VALUES ('2010.05.15', 1, 6500, 7000, 8000);
INSERT INTO TableA VALUES ('2010.05.15', 2, 6500, 7000, 8000);
GO

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2010.05.12'
SET @EndDate = '2010.05.15'


SELECT
    'Date range: ' + CONVERT(varchar, A.[date]) + ' - ' + CONVERT(varchar, B.[date]),
    A.[No] As 'No',
    B.[Meter_1] - A.[Meter_1] As 'MeterDiff1',
    B.[Meter_2] - A.[Meter_2] As 'MeterDiff2',
    B.[Meter_3] - A.[Meter_3] As 'MeterDiff3'
FROM 
    TableA A
JOIN
    TableA B ON A.[No] = B.[No]
WHERE
    A.[Date] = @StartDate
AND
    B.[Date] = @EndDate