September 2, 2011 at 1:55 pm
I am new to SQL Server so please forgive the crudity of the code. I am trying
to insert dates from one temp table and integers from other temp tables into
one master temp table named #FullDataGrid. the code I have is below. when I
run the code without the INNER JOIN, it runs fine but of course I am not
inserting all of my data. however, when I run it with an INNER JOIN, I
receive the folloiing error:
Invalid object name '#DateCreated.CreatedDate'
What am I doing wrong?
Create TABLE #CaseSlipList
(
DocID int,
TicketNbr int,
Date datetime,
StatusType
char(10)
);
Insert into #CaseSlipList
select dbo.toc.tocid as DocID,'', (SELECT DATEADD(D, 0, DATEDIFF(D, 0, dbo.
toc.created))) as Date, 'Created'
from dbo.toc
where dbo.toc.pset_id=91;
Insert into #CASESlipList
Select dbo.propval.tocid as DocID,'', (SELECT DATEADD(D, 0, DATEDIFF(D, 0,
dbo.propval.date_val))) as Date, 'Setup'
from dbo.propval
where dbo.propval.prop_id='211';
Insert into #CASESlipList
Select dbo.propval.tocid as DocID,'', dbo.propval.date_val as Date, 'Coding'
from dbo.propval
where dbo.propval.prop_id='212';
Insert into #CASESlipList
Select dbo.propval.tocid as DocID,'', dbo.propval.date_val as Date,
'Posting'
from dbo.toc
INNER Join dbo.propval
on toc.tocid = propval.tocid
where toc.pset_id = 91 and dbo.propval.prop_id='180';
Update #CASESlipList
set TicketNbr = dbo.propval.str_val
from dbo.propval
where dbo.propval.prop_id='29' and docid=dbo.propval.tocid;
Create Table #DateCreated
(
CreatedDate datetime,
CreatedTotal int
);
Insert into #DateCreated
select date,count(statusType)
from #CASESlipList
where statustype='Created'
group by date;
Create Table #DateSetup
(
SetupDate datetime,
SetupTotal int
);
Insert into #DateSetup
select date,count(statusType)
from #CASESlipList
where statustype='Setup'
group by date;
Create Table #Datecoding
(
CodingDate datetime,
CodingTotal int
);
Insert into #DateCoding
select date,count(statusType)
from #CASESlipList
where statustype='Coding'
group by date;
Create Table #DatePosting
(
PostingDate datetime,
PostingTotal int
);
Insert into #DatePosting
select date,count(statusType)
from #CASESlipList
where statustype='Posting'
group by date;
-- Section creating master list of dates for FullDataGrid
Create Table #MasterDate
(
Date datetime
);
Insert into #Masterdate
select CreatedDate from #DateCreated
Union
select SetupDate from #DateSetup
Union
select CodingDate from #DateCoding
Union
select PostingDate from #DatePosting
;
Create Table #FullDataGrid
(
Date datetime,
CreatedTotal int,
SetupTotal int,
CodingTotal int,
PostingTotal int
);
Insert into #FullDataGrid
Select '', createdtotal,'','',''
from #datecreated
INNER JOIN #DateCreated.CreatedDate
on #MasterDate.Date = #DateCreated.CreatedDate;
September 2, 2011 at 2:08 pm
Thanks for posting all the CREATE TABLE statements, but as a general rule of thumb, you should make sure that your code can run as-is in a standalone environment. I tried executing it and of course, errors were thrown since you are also referencing local tables which you didn't provide definitions for.
However, you're in luck - in this case I don't need those to answer your question 😛
Your problem is in the syntax of your INNER JOIN at the end:
Select '', createdtotal,'','',''
from #datecreated
INNER JOIN #DateCreated.CreatedDate
on #MasterDate.Date = #DateCreated.CreatedDate;
That should read:
Select '', createdtotal,'','',''
from #datecreated
INNER JOIN #MasterDate
on #MasterDate.Date = #DateCreated.CreatedDate;
The value that follows the INNER JOIN is always the name of the table that is being joined to, and then the join conditions, EG:
INNER JOIN [NAME_OF_TABLE_TO_BE_JOINED] ON [NAME_OF_TABLE_TO_BE_JOINED].[FIELD_NAME] = [NAME_OF_TABLE_TO_JOIN_TO].[FIELD_NAME]
September 2, 2011 at 2:09 pm
just a copy paste error...you left the column name attached to teh table name:
Insert into #FullDataGrid
Select '', createdtotal,'','',''
from #datecreated
INNER JOIN #DateCreated.CreatedDate
on #MasterDate.Date = #DateCreated.CreatedDate;
Lowell
September 2, 2011 at 3:35 pm
Many thanks, kramaswamy! Works like a charm.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply