Joining tables

Hi
I would like to join two tables, one containig the names of loaded datafiles
and the date of the loaded datafile i a field called RealDate. The other
tabel is a table containg a list of all the "missing" datafiles which for
some reason wasn't loaded into the database.
The result I would like is a resultset where I get all the loaded
datafilenames as well as all the missing datafilesnames, the later must have
a flag set so I can tell them apart, the field siteid can be used for that
where e.g. -1 or -3 flags indicates not loaded and 1 or 3 flags loaded.
V2Statistik definition
CREATE TABLE [dbo].[Statistik] (
[SiteID] [int] NOT NULL ,
[TicketFileName] [char] (12) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[Realdate] [datetime] NULL ,
[Loaddate] [datetime] NULL ,
[TotalRecords] [int] NOT NULL ,
[SkippedRecords] [int] NOT NULL
) ON [PRIMARY]
a result set could look like this
1 38566.txt 2005-08-02 2005-08-25 16:14:36.863 82462 31435
-1 38572.txt 2005-08-08 0 0 0
-1 38589.txt 2005-08-25 0 0 0
3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
-3 38592.txt 2005-08-28 0 0 0
What I have managed so far is this (the join is not correct)
DECLARE @.DateTable TABLE (RealDate DATETIME)
DECLARE @.RealDate DATETIME
SET @.RealDate = '01/01/2005'
WHILE @.RealDate BETWEEN '01/01/2005' AND GETDATE()
BEGIN INSERT INTO @.DateTable (RealDate) VALUES (@.RealDate) SET @.RealDate =
DATEADD(DAY, 1, @.RealDate) END
select * from V2Statistik where siteid=1
join '?
SELECT RealDate FROM @.DateTable dt
WHERE NOT EXISTS (SELECT * FROM V2Statistik stk WHERE stk.RealDate =
dt.RealDate)
I not sure how to join these to different tables.
Can you help?
HenryHenry,
Either you failed to include the table structure for the "other table" that
lists the missing datafiles, or I don't understand where that data is. And
I'm not sure whether [Statistik] is the first table you mention, since you
don't explain LoadDate.
If you can post more information, it would help - specifically, it would
help to post the source data that would give the result you want. You
posted the result you want, which is good, but it's impossible to suggest
a query that will give it to you without knowing how your source data
is stored.
Probably, you need something like a UNION ALL query
select ..., Realdate, Loaddate, ...
from <your first table, which contains information on loaded files>
union all
select ..., Realdate, NULL, ...
from <your second table, which contains information on not-loaded files>
The two parts of the union must have the same column structure, so you
will want to put NULL in the select list for Loaddate in the second query.
You can add a column to indicate whether the file was loaded or not, but
I don't think that's necessary, since you can tell if a file was loaded or
not by looking at whether Loaddate is NULL or not.
I don't see any need for a calendar table here, but more information may
make your requirements clearer.
Steve Kass
Drew University
Henry wrote:

>Hi
>I would like to join two tables, one containig the names of loaded datafile
s
>and the date of the loaded datafile i a field called RealDate. The other
>tabel is a table containg a list of all the "missing" datafiles which for
>some reason wasn't loaded into the database.
>The result I would like is a resultset where I get all the loaded
>datafilenames as well as all the missing datafilesnames, the later must hav
e
>a flag set so I can tell them apart, the field siteid can be used for that
>where e.g. -1 or -3 flags indicates not loaded and 1 or 3 flags loaded.
>V2Statistik definition
>CREATE TABLE [dbo].[Statistik] (
> [SiteID] [int] NOT NULL ,
> [TicketFileName] [char] (12) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
> [Realdate] [datetime] NULL ,
> [Loaddate] [datetime] NULL ,
> [TotalRecords] [int] NOT NULL ,
> [SkippedRecords] [int] NOT NULL
> ) ON [PRIMARY]
>
>a result set could look like this
>1 38566.txt 2005-08-02 2005-08-25 16:14:36.863 82462 31435
>-1 38572.txt 2005-08-08 0 0 0
>-1 38589.txt 2005-08-25 0 0 0
>3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
>1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
>-3 38592.txt 2005-08-28 0 0 0
>What I have managed so far is this (the join is not correct)
>DECLARE @.DateTable TABLE (RealDate DATETIME)
>DECLARE @.RealDate DATETIME
>SET @.RealDate = '01/01/2005'
>WHILE @.RealDate BETWEEN '01/01/2005' AND GETDATE()
>BEGIN INSERT INTO @.DateTable (RealDate) VALUES (@.RealDate) SET @.RealDate =
>DATEADD(DAY, 1, @.RealDate) END
>
>select * from V2Statistik where siteid=1
>join '?
>SELECT RealDate FROM @.DateTable dt
>WHERE NOT EXISTS (SELECT * FROM V2Statistik stk WHERE stk.RealDate =
>dt.RealDate)
>
>I not sure how to join these to different tables.
>Can you help?
>Henry
>
>
>|||I think we are missing a table with the "missing stuff" in it.
Do you know the names of the files to be loaded into the database in
advance? I would assume so, if you have set up proper system for a
data warehouse load.|||Hi
Sorry that I'm unclear about this.
I have one table which is a log of all the tables which has been
successfully loaded into the database.
SiteID, TicketFileName and RealDate are the important fields at present.
It looks like this
CREATE TABLE [dbo].[V2Statistik] (
[SiteID] [int] NOT NULL ,
[TicketFileName] [char] (12) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[Realdate] [datetime] NULL ,
[Loaddate] [datetime] NULL ,
[TotalRecords] [int] NOT NULL ,
[SkippedRecords] [int] NOT NULL
) ON [PRIMARY]
I have another table where I simpely shows the missing files from a given
date, that is files which for some reasons could not be read or loaded into
the database.
That table is a date table purpolated with all dates from the given date.
DECLARE @.DateTable TABLE (RealDate DATETIME)
I then select all dates where realdate does not exist in the V2statistik
(previously called statistik by mistake)
The result set from a select * from V2statistik would look like this
1 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
1 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
3 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
3 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
The result set from DateTable would look like this
2005-01-01
2005-01-02
2005-01-03
...
2005-10-23
I would like a result set of the joined or unioned tables that looks like
this
1 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
1 38589.txt 2005-08-25 0 0 0
1 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
3 38566.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
3 38589.txt 2005-08-25 0 0 0
3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
3 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
So I would like to select * from V2Statistik and "merge" it with dates from
the DateTable where the date doesnt exist in V2statistik, the merged
(missing dates) row should have the
Loaddate set to 0 (zero, null, nil) I have changed strategy on the I think
it's easier.
But I see a problem since the realdate will occur as many times as there are
sites (siteid) siteid + realdate is the unique primary key.
Does this clarify it?
regards
Henry|||On Mon, 24 Oct 2005 14:16:58 +0200, henry wrote:
(snip)
>I would like a result set of the joined or unioned tables that looks like
>this
>1 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
>1 38589.txt 2005-08-25 0 0 0
>1 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
>1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
>3 38566.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
>3 38589.txt 2005-08-25 0 0 0
>3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
>3 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
(snip)
Hi Henry,
I think that the query below is quite close (*) to what you want. It's
too bad that you didn't post any INSERT statements to give me working
data, so I couldn;t test it - but check if it suits your need.
(*) I deliberately left the filename column NULL for the missing files,
for two reasons:
a. logical (if the file is missing, you obviously can't tell the name)
b. attempting to find the numeric part of another file name, increasing
that with the result of a DATEDIFF function and adding back the .txt
part, though possible, would be extremely messy, and it would make the
query very vulnerable for malformed filenames.
SELECT s.SiteID, v.TicketFileName, d.RealDate,
COALESCE(v.LoadDate, 0) AS LoadDate,
COALESCE(v.TotalRecords, 0) AS TotalRecords,
COALESCE(v.SkippedRecords, 0) AS SkippedRecords
FROM (SELECT DISTINCT SiteID
FROM V2Statistik) AS s
CROSS JOIN @.DateTable AS d
LEFT JOIN V2Statistik AS v
ON v.SiteID = s.SiteID
AND v.RealDate = d.RealDate
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo
Thanks for your help ;o)
However, when I use that script the I don't get any output at all, only
"The command(s) completed successfully."
Which of cause is a positiv thing, not syntax error.
I can send the data as a seperate post with CSV data (don't realy know how
to make the insert data) the table structure is already posted above, do you
think you can use that?
This the complete script so far.
DECLARE @.DateTable TABLE (RealDate DATETIME)
DECLARE @.RealDate DATETIME
SET @.RealDate = '01/01/2005'
WHILE @.RealDate BETWEEN '01/01/2005' AND GETDATE()
BEGIN INSERT INTO @.DateTable (RealDate) VALUES (@.RealDate) SET @.RealDate =
DATEADD(DAY, 1, @.RealDate) END
SELECT s.SiteID, v.TicketFileName, d.RealDate,
COALESCE(v.LoadDate, 0) AS LoadDate,
COALESCE(v.TotalRecords, 0) AS TotalRecords,
COALESCE(v.SkippedRecords, 0) AS SkippedRecords
FROM (SELECT DISTINCT SiteID
FROM V2Statistik) AS s
CROSS JOIN @.DateTable AS d
LEFT JOIN V2Statistik AS v
ON v.SiteID = s.SiteID
AND v.RealDate = d.RealDate
Cheers
Henry|||On Wed, 26 Oct 2005 17:10:19 +0200, Henry wrote:

>Hi Hugo
>Thanks for your help ;o)
>However, when I use that script the I don't get any output at all, only
>"The command(s) completed successfully."
>Which of cause is a positiv thing, not syntax error.
Hi Henry,
Whether syntax error or incorrect results - a bug is a bug, and in dire
need of squashing. (In fact, syntax errors are often EASIER to locate
and correct).

>I can send the data as a seperate post with CSV data (don't realy know how
>to make the insert data) the table structure is already posted above, do yo
u
>think you can use that?
I possibly could, if I could afford to spend a few hours playing around
with bcp or trying to import through Excel to SQL Server - but there is
only so much time I can spend in these groups, and I like to help as
many people as I can in that limited time.
Below is a link to a script that will generate INSERT statements from
the data currently in your table. See if that helps you (and if not,
then you can always manually type the INSERT statements for five or ten
rows of sample data).
http://vyaskn.tripod.com/code.htm#inserts
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)