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)

Joining different datasets with parameters

Hi all,

lets say i have one table with colour ids and colour names, and another table with broad-leafed trees and different colour id fields for root, leafs and trunk:

Table colours:
integer field colour_id, varchar field colour_name
Table trees:
varchar field tree_type, integer field leaf_colour_id,
integer field trunk_colour_id, integer field root_colour_id

one way to get the fields would be:

select T.tree_type, C1.colour_name as leaf_colour_name,
C2.colour_name as trunk_colour_name, C3.colour_name as root_colour_name
from trees as T
inner join colours as C1 on C1.colour_id = leaf_colour_id
inner join colours as C2 on C2.colour_id = trunk_colour_id
inner join colours as C3 on C3.colour_id = root_colour_id;

But i would like to do that with some kind of inline function like:
colourName := select colour_name from colours where colour_id = @.colourId;
and then:
select tree_type, colourName(leaf_colour_id), colourName(trunk_colour_id), colourName(root_colour_id) from trees;

Is that possible? Any comments welcome.

Thanks,
haraldIn Sql Server, you can create a User Defined Function (Scalar) which will produce this. It will make code more readable but will slow it down a bit.
See the templates in SQL Server, and this article.
It would be something like:
CREATE FUNCTION fnGetColourName(@.ColourID as int)
RETURNS int
as
BEGIN
DECLARE @.Value as int
SET @.Value = (SELECT MAX(Colour_name)
FROM Colours
WHERE colour_id = @.ColourID
END
RETURN @.Value
SELECT dbo.fnGetColourName(leaf_colour_id) as leafColour
FROM trees
Alternatively, you can use the Custom Code section of the report to create a function that will do the same thing, see examples from Bryant Likes's blog here.|||

Hi wavemash,

thanks for your reply, now my sql statement is more readable

Thanks,
harald

Join the Result of xp_logininfo with a table

Hello group!

i have a table "group_code" wich relates the names of
nt-(domain)-groups to codes. now i want use the stored procedure
xp_logininfo (asking for the group-membership of the current user) to
join the result to "group_code". then i must use the new result (the
code) to join against other tables.

i know now, that i cant join results of SPs against tables. may be that
a UDF with a table result is the correct approach. but i have no idea
how to wrap the xp_logininfo in a UDF.

a other way can be to do in a UDF the same thing like the xp_logininfo.
then this UDF should deliver at least the nt-(domain)-groups in wich
the current-user is a member.

Is there anybody who can give me the code for that?
many thanx in advance.

Karl(Karl.Auer@.gmail.com) writes:
> i have a table "group_code" wich relates the names of
> nt-(domain)-groups to codes. now i want use the stored procedure
> xp_logininfo (asking for the group-membership of the current user) to
> join the result to "group_code". then i must use the new result (the
> code) to join against other tables.
> i know now, that i cant join results of SPs against tables. may be that
> a UDF with a table result is the correct approach. but i have no idea
> how to wrap the xp_logininfo in a UDF.
> a other way can be to do in a UDF the same thing like the xp_logininfo.
> then this UDF should deliver at least the nt-(domain)-groups in wich
> the current-user is a member.

I don't think you can do this with a UDF. You can do:

CREATE TABLE #temp (...)
INSERT #temp (...)
EXEC master..xp_logininfo

But you cannot do this in a UDF, since you cannot create temp
tables in a UDF, and you cannot do INSERT EXEC on a table variable
if memory serves.

As for how #temp in the example should be created, see the definition
of the columns for xp_logininfo in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Join Table Key to Multiple Table Names

Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:

I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.

That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.

Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.

ThxWouldn't it be a lot less trouble to have one table for stock, one table for jobs, and one table to show job-stock-usage? That way you could have a practical infinity of jobs and stocks with only three tables.

This is a fundamental database design process called normalization. It is the key to maintaining your sanity as your projects grow!

-PatP|||I totally agree...I don't like this design whatsoever but its all I have to work with because I didn't design it and it's the way things are being done right now.|||I believe that particular schema is taken directly from Chapter 6, page 142 of the ever popular and best-selling book, "WORLD'S WORST DATABASE DESIGNS".

You are going to have to use dynamic SQL to solve this. Essentially, you will construct your SQL statement as a string concatenating the value of jobID in as the table name, and then execute the string.

Developers like that ought to be shot.|||thx for the reply blindman. I was leaning that way but I'm fairly new to using ms sql. I'm currently reading up on dynamic sql and seeing how it works. I need to generate a view out of this somehow.

Thx again|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)|||-----
lol|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)I don't know that it has ever been officially published, it is more of a "work in progress" kind of thing. Every time they think they're ready to publish, somebody runs in yelling "You've got to see this one" and they go right back to editing!

-PatP|||73% of the database designs in that book, WORLD'S WORST DATABASE DESIGNS, were written by php programmers, who design tables like that all the time

18% of them were written by people who've spent too much time at dbdebumph.com and have drunk the koolaid -- not a null in sight!!

9% were written by oracle developers who thought that storing a whole nested table inside a field was a neat idea and supports their concept of object-oriented encapsulation|||Man, you NAILED it with #3!!! I'll buy you a round (12 pack or a bottle of your choice) whenever you're in town!!! Though I see so much of #1 that it's not even funny any more...I wish images from Unreal Tornament were real sometimes...|||thank you, thank you, i'll be here all week, try the veal and don't forget to tip your waitress

:cool:|||no sooner do i say it, and another example of a design in the first category pops up: this thread (http://forums.devshed.com/t199186/s.html)

happens all the time|||This is SO ironic...I just declined an offer from a real estate marketing company which excercised a similar design "strategy" ... They even asked me at the interview how I would resolve this situation... Of course my answer was to FIRE the designer of the current database first ;)