Kongta
asked on
wrong results due to date-format?
I do have the tbl dbo.GFArchiv where I have millions of rst including dbo.GFArchiv.DatumBerechnu ng 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.DatumBerechnu ng.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.DatumBerechnu ng which are not yet included in dbo.GF_V1_Investmentratio to perform faster. Trying
SELECT DISTINCT dbo.GFArchiv.DatumBerechnu ng
FROM dbo.GFArchiv LEFT OUTER JOIN
dbo.GF_V1_Investmentratio ON CONVERT(smalldatetime, CONVERT(char(10), dbo.GFArchiv.DatumBerechnu ng, 104), 104) > CONVERT(smalldatetime,
CONVERT(char(10), dbo.GF_V1_Investmentratio. Datum, 104), 104)
brings back all dates of dbo.GFArchiv.DatumBerechnu ng and not only > dbo.GF_V1_Investmentratio. Datum
any idea where the bug is?
SELECT DISTINCT dbo.GFArchiv.DatumBerechnu
FROM dbo.GFArchiv LEFT OUTER JOIN
dbo.GF_V1_Investmentratio ON CONVERT(smalldatetime, CONVERT(char(10), dbo.GFArchiv.DatumBerechnu
CONVERT(char(10), dbo.GF_V1_Investmentratio.
brings back all dates of dbo.GFArchiv.DatumBerechnu
any idea where the bug is?
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.DatumBere chnung) FROM dbo.GFArchiv
)
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.
(
Select MAX(dbo.GFArchiv.DatumBere
)
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
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)
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.
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.
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.Datu m
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
GFArchiv.DatumBerechnung GF_V1_Investmentratio.Datu
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.DatumBerechnu ng, 104), 104) > (SELECT MAX(CONVERT(smalldatetime, CONVERT(char(10), bo.GF_V1_Investmentratio.D atum, 104), 104))
FROM dbo.GF_V1_Investmentratio)
SELECT *
FROM dbo.GFArchiv
WHERE convert(smalldatetime, CONVERT(char(10), dbo.GFArchiv.DatumBerechnu
FROM dbo.GF_V1_Investmentratio)
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)
should be a lot faster as you do not need to do all the converts.
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
)
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
)
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
rgds
Kongta
FROM dbo.GFArchiv
WHERE DateAdd(dd, 0, DateDiff(dd, 0, dbo.GFArchiv.DatumBerechnu
(SELECT MAX(DateAdd(dd, 0, DateDiff(dd, 0, dbo.GF_V1_Investmentratio.