Link to home
Start Free TrialLog in
Avatar of Kongta
KongtaFlag for Switzerland

asked on

wrong results due to date-format?

I do have the tbl dbo.GFArchiv where I have millions of rst including dbo.GFArchiv.DatumBerechnung in datetime format and a tbl dbo.GF_V1_Investmentratio with Datum, formerly in smalldatetime changed into datetime but did not solve issue. I previously deleted all entries in dbo.GF_V1_Investmentratio  and feeded all new with  dbo.GFArchiv.DatumBerechnung.Datum which worked fine but took more then an hour to execute. Therefore, I would like to insert only the latest dates of  dbo.GFArchiv.DatumBerechnung  which are not yet included in dbo.GF_V1_Investmentratio to perform faster. Trying

SELECT DISTINCT dbo.GFArchiv.DatumBerechnung
FROM         dbo.GFArchiv LEFT OUTER JOIN
                      dbo.GF_V1_Investmentratio ON CONVERT(smalldatetime, CONVERT(char(10), dbo.GFArchiv.DatumBerechnung, 104), 104) > CONVERT(smalldatetime,
                      CONVERT(char(10), dbo.GF_V1_Investmentratio.Datum, 104), 104)

brings back all dates of dbo.GFArchiv.DatumBerechnung and not only > dbo.GF_V1_Investmentratio.Datum

any idea where the bug is?
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

SELECT DISTINCT dbo.GFArchiv.DatumBerechnung
FROM    dbo.GFArchiv
WHERE DateAdd(dd, 0, DateDiff(dd, 0, dbo.GFArchiv.DatumBerechnung) > 
(SELECT MAX(DateAdd(dd, 0, DateDiff(dd, 0, dbo.GF_V1_Investmentratio.Datum))) FROM dbo.GF_V1_Investmentratio)
If I am understanding you correctly, the above query I suggested will get you dates that are greater than the max date in the other table.
the way your query is set up wont work.

So you have data in the table and only want to insert data that has not been inserted yet by che3cking the date?  

Will this logic work:  Can you just get the highest date that exists in the table already and only get data to insert that are greater than that date?

If so you can do something like this:

Select DataFieldsToInsert
From dbo.GF_V1_Investmentratio
Where dbo.GF_V1_Investmentratio.Datum >=
    (
       Select MAX(dbo.GFArchiv.DatumBerechnung) FROM dbo.GFArchiv
    )
Hello Kongta, your query will join on your two table where the date in archiv is bigger than the date in investmentratio, it will bring you all the archiv where the date is after any date in investment ratio.

You should at least use a max date on investment ratio

SELECT *
FROM  dbo.GFArchiv 
WHERE (smalldatetime, CONVERT(char(10), dbo.GFArchiv.DatumBerechnung, 104), 104) > (SELECT MAX(CONVERT(smalldatetime,                       CONVERT(char(10), bo.GF_V1_Investmentratio.Datum, 104), 104))
FROM dbo.GF_V1_Investmentratio)

Open in new window

hooo i'm too late

Also if the date that you use in your table are not ordered, for exemple an invoice date, you can receive an invoice from a supplier later than another one but with a smallest date, then you could not use this date to retrieve your data because you could miss the one that are add to your system with a smaller date than your last import.

In this case I would suggest you to add a status field on your first table, Transfered as a int where the value could stand as 0 - Not Transfered, 1 - Transfert in process, 2 - Transfered.

Before importing your data you can do an update on your table to change all the 0 to 1, then Only import the 1, after that update the 1 as 2. It will prevent you missing data that people add to your system while transfering.
Avatar of Kongta

ASKER

tried all three but always got an error in the QueryAnalyzer,  anyhow seems that I haven't described for a clear understanding as english is not my motherlanguage. In the table GFArchiv, I do have dates back to 2002, where in GF_V1_Investmentratio I would like to add only the dates that are not open yet like

GFArchiv.DatumBerechnung                     GF_V1_Investmentratio.Datum
01.11.2008                                                 01.11.2008
02.11.2008                                                 02.11.2008
3.11.2008
4.11.2008
5.11.2008


in this case, I would like to add 3.11.-5.11.2008 into the GF_V1_Investmentratio table
ASKER CERTIFIED SOLUTION
Avatar of brad2575
brad2575
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry I forgot a Convert in mine, what is the error you got ?

SELECT *
FROM  dbo.GFArchiv
WHERE convert(smalldatetime, CONVERT(char(10), dbo.GFArchiv.DatumBerechnung, 104), 104) > (SELECT MAX(CONVERT(smalldatetime,                       CONVERT(char(10), bo.GF_V1_Investmentratio.Datum, 104), 104))
FROM dbo.GF_V1_Investmentratio)
Avatar of Kongta

ASKER

brad2575, just run your code but takes already few minutes, don't know yet what the outcome is, hope doesn't take 45' which would be no improvement in time
From your description this may just be simply this:
SELECT DatumBerechnung 
FROM GFArchiv
WHERE DatumBerechnung NOT IN (SELECT Datum FROM GF_V1_Investmentratio)

Open in new window

should be a lot faster as you do not need to do all the converts.

Avatar of Kongta

ASKER

ok, took me now 50', not faster at all. As GFArchiv has about 20'000 rst on each day, 01.11.2008 comes back 20'000 for example, I changed now into SELECT DISTINCT and hope for a faster query, just start run and wait
do you have an auto increment ID field in the GFArchiv.DatumBerechnung  table?

If so what you can do is save last ID you inserted into a lookup/temp table and use that in your query for the next day.

So say yesterday you inserted from the GFArchiv.DatumBerechnung  table and the last ID you inserted was 1001.  You save that ID in a temp/lookup table.  Then tomorrow when you go to do the insert again you just select all the data from the GFArchiv.DatumBerechnung  table that have an ID greater then 1001 (from the table) - say 100 new records (Id's from 1101 - 1201).  Then update that number in the temp/lookup table to 1201 (as this was the last inserted ID for today).

This would save having to do any date checks at all and would speed things up tremendously.

So if you did this your query would be something like this:
Select GFArchiv.DatumBerechnung
From GFArchiv
Where GFArchiv.AutoIncrementID  >
(
       Select LastInsertedID From TempLookupTable
    )
Avatar of Kongta

ASKER

ok brad, good input. let me have a try, anyhow as the initial problem has been solved, I will award you the points, I hope everybody here is ok with that, thx to all and have a nice weekend
rgds
Kongta