November 9, 2009 at 7:12 am
My table and data as follow,
**************************************************
declare @tPosiHdr TABLE (
TID int not null,
BusN varchar (20) NOT NULL,
TTime varchar(6) not null,
strPosi varchar(6)not null,
DDate datetime not null
)
insert into @tPosiHdr values(1,'ADR3344','120000','00','09/01/2009')
insert into @tPosiHdr values(2,'WNR4474','130000','00','09/02/2009')
insert into @tPosiHdr values(3,'WKM1955','190000','00','09/04/2009')
insert into @tPosiHdr values(4,'WKM1955','150000','00','10/13/2009')
insert into @tPosiHdr values(5,'TXM4478','130000','00','10/16/2009')
insert into @tPosiHdr values(6,'TXM1925','170000','00','11/02/2009')
insert into @tPosiHdr values(7,'KTX1955','150000','00','11/09/2009')
declare @tPosiDtl_200909 TABLE(
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into @tPosiDtl_200909 values(1,'0','1')
insert into @tPosiDtl_200909 values(2,'0','1')
insert into @tPosiDtl_200909 values(3,'1','2')
declare @tPosiDtl_200910 TABLE(
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into @tPosiDtl_200910 values(4,'0','1')
insert into @tPosiDtl_200910 values(5,'1','2')
declare @tPosiDtl_200911 TABLE(
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into @tPosiDtl_200911 values(7,'1','2')
insert into @tPosiDtl_200911 values(8,'0','1')
insert into @tPosiDtl_200911 values(9,'1','2')
**************************************************
so far, i've this,
select t1.TID,t1.BusN,t1.TTime,t1.DDate,
convert(varchar(4),datepart(yyyy,t1.DDate)) + right('00'+convert(varchar(2),datepart(mm,t1.DDate)),2) as whichTable,
t2.KT,t2.HPT
from @tPosiHdr t1
left outer join
(select TID,KT,HPT
from @tPosiDtl_200909)t2
on t1.TID=t2.TID
my result will
1 ADR3344 120000 2009-09-01 00:00:00.000 200909 0 1
2 WNR4474 130000 2009-09-02 00:00:00.000 200909 0 1
3 WKM1955 190000 2009-09-04 00:00:00.000 200909 1 2
4 WKM1955 150000 2009-10-13 00:00:00.000 200910 NULL NULL
5 TXM4478 130000 2009-10-16 00:00:00.000 200910 NULL NULL
6 TXM1925 170000 2009-11-02 00:00:00.000 200911 NULL NULL
7 KTX1955 150000 2009-11-09 00:00:00.000 200911 NULL NULL
Actually, i want my SQL is dynamic. If whichTable=200909, the join will be on @tPosiDtl_200909, if whichTable=200910, the join will be on @tPosiDtl_200910, and so on.
So my result will be,
1 ADR3344 120000 2009-09-01 00:00:00.000 200909 0 1
2 WNR4474 130000 2009-09-02 00:00:00.000 200909 0 1
3 WKM1955 190000 2009-09-04 00:00:00.000 200909 1 2
4 WKM1955 150000 2009-10-13 00:00:00.000 200910 0 1
5 TXM4478 130000 2009-10-16 00:00:00.000 200910 1 2
6 TXM1925 170000 2009-11-02 00:00:00.000 200911 0 1
7 KTX1955 150000 2009-11-09 00:00:00.000 200911 1 2
how my dynamic SQL looks like?
DECLARE @sql as nvarchar(4000)
SET @sql = ' ????'
EXEC sp_executesql @sql
I'm stuck. Me really looking for help.
November 9, 2009 at 8:23 am
This is really quick and probably dirty. I am trying to come up with a better way to determine the joins cause right now you will need a UNION ALL for each table. Is there a reason each month is split into it's own table? Just wondering if you could get this data from a different source to make it easier.
select t1.TID,t1.BusN,t1.TTime,t1.DDate,
convert(varchar(4),datepart(yyyy,t1.DDate)) + right('00'+convert(varchar(2),datepart(mm,t1.DDate)),2) as whichTable,
t2.KT,t2.HPT
from @tPosiHdr t1
left outer join
(select TID,KT,HPT, '2009' AS [year], 9 AS [MONTH]
from @tPosiDtl_200909
UNION ALL
select TID,KT,HPT, '2009' AS [year], 10 AS [MONTH]
from @tPosiDtl_200910
UNION ALL
select TID,KT,HPT, '2009' AS [year], 11 AS [MONTH]
from @tPosiDtl_200911
)t2
on t1.TID=t2.TID
AND DATEPART(yyyy,t1.ddate) = t2.[year]
AND DATEPART(mm,t1.ddate) = t2.[MONTH]
November 9, 2009 at 11:33 am
Matt Wilhoite (11/9/2009)
This is really quick and probably dirty. I am trying to come up with a better way to determine the joins cause right now you will need a UNION ALL for each table. Is there a reason each month is split into it's own table? Just wondering if you could get this data from a different source to make it easier.
select t1.TID,t1.BusN,t1.TTime,t1.DDate,
convert(varchar(4),datepart(yyyy,t1.DDate)) + right('00'+convert(varchar(2),datepart(mm,t1.DDate)),2) as whichTable,
t2.KT,t2.HPT
from @tPosiHdr t1
left outer join
(select TID,KT,HPT, '2009' AS [year], 9 AS [MONTH]
from @tPosiDtl_200909
UNION ALL
select TID,KT,HPT, '2009' AS [year], 10 AS [MONTH]
from @tPosiDtl_200910
UNION ALL
select TID,KT,HPT, '2009' AS [year], 11 AS [MONTH]
from @tPosiDtl_200911
)t2
on t1.TID=t2.TID
AND DATEPART(yyyy,t1.ddate) = t2.[year]
AND DATEPART(mm,t1.ddate) = t2.[MONTH]
Yes. Each month split into it's own table. If all data into 1 table, the row will be huge. I'm looking the dynamic solution without UNION ALL. If UNION ALL, the table need to be hardcoded.
November 9, 2009 at 12:05 pm
I have a solution that works using a loop and dynamic SQL. But this has some stipulations with it. I changed the table variables to be temp tables as the dynamic part won't work with table variables. Also, you would have to change the @i counter and possible nest another while loop if you are doing this for more than 1 year.
Edit: One more thing is that I don't know how well this will scale.
Let me know what you think
CREATE TABLE #tPosiHdr (
TID int not null,
BusN varchar (20) NOT NULL,
TTime varchar(6) not null,
strPosi varchar(6)not null,
DDate datetime not null
)
insert into #tPosiHdr values(1,'ADR3344','120000','00','09/01/2009')
insert into #tPosiHdr values(2,'WNR4474','130000','00','09/02/2009')
insert into #tPosiHdr values(3,'WKM1955','190000','00','09/04/2009')
insert into #tPosiHdr values(4,'WKM1955','150000','00','10/13/2009')
insert into #tPosiHdr values(5,'TXM4478','130000','00','10/16/2009')
insert into #tPosiHdr values(6,'TXM1925','170000','00','11/02/2009')
insert into #tPosiHdr values(7,'KTX1955','150000','00','11/09/2009')
CREATE TABLE #tPosiDtl_200909 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into #tPosiDtl_200909 values(1,'0','1')
insert into #tPosiDtl_200909 values(2,'0','1')
insert into #tPosiDtl_200909 values(3,'1','2')
CREATE TABLE #tPosiDtl_200910 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into #tPosiDtl_200910 values(4,'0','1')
insert into #tPosiDtl_200910 values(5,'1','2')
CREATE TABLE #tPosiDtl_200911 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into #tPosiDtl_200911 values(7,'1','2')
insert into #tPosiDtl_200911 values(8,'0','1')
insert into #tPosiDtl_200911 values(9,'1','2')
CREATE TABLE #temp (
tid INT,
BusN VARCHAR(20),
TTime VARCHAR(6),
DDate DATETIME,
whichTable VARCHAR(6),
KT VARCHAR(20),
HPT VARCHAR(6)
)
DECLARE @sql VARCHAR(1000)
DECLARE @i INT
DECLARE @iChar VARCHAR(10)
SET @i = 9
SET @iChar = NULL
WHILE @i < 12
BEGIN
IF @i < 10
BEGIN
select @iChar = '0'+CONVERT(CHAR(1),@i)
end
ELSE
SET @iChar = @i
select @sql = 'insert into #temp
select t1.TID,t1.BusN,t1.TTime,t1.DDate,
convert(varchar(4),datepart(yyyy,t1.DDate)) + right(''00''+convert(varchar(2),datepart(mm,t1.DDate)),2) as whichTable,
t2.KT,t2.HPT
from #tPosiHdr t1
left outer join
(select TID,KT,HPT
from #tPosiDtl_2009'+@iChar+'
)t2
on t1.TID=t2.TID
where datepart(mm,t1.ddate) = '+CONVERT(VARCHAR,@i)
EXEC (@sql)
SET @i = @i + 1
END
SELECT * FROM #temp
DROP TABLE #temp, #tPosiHdr, #tPosiDtl_200909, #tPosiDtl_200910, #tPosiDtl_200911
November 9, 2009 at 4:12 pm
Matt Wilhoite (11/9/2009)
I have a solution that works using a loop and dynamic SQL. But this has some stipulations with it. I changed the table variables to be temp tables as the dynamic part won't work with table variables. Also, you would have to change the @i counter and possible nest another while loop if you are doing this for more than 1 year.Edit: One more thing is that I don't know how well this will scale.
Let me know what you think
CREATE TABLE #tPosiHdr (
TID int not null,
BusN varchar (20) NOT NULL,
TTime varchar(6) not null,
strPosi varchar(6)not null,
DDate datetime not null
)
insert into #tPosiHdr values(1,'ADR3344','120000','00','09/01/2009')
insert into #tPosiHdr values(2,'WNR4474','130000','00','09/02/2009')
insert into #tPosiHdr values(3,'WKM1955','190000','00','09/04/2009')
insert into #tPosiHdr values(4,'WKM1955','150000','00','10/13/2009')
insert into #tPosiHdr values(5,'TXM4478','130000','00','10/16/2009')
insert into #tPosiHdr values(6,'TXM1925','170000','00','11/02/2009')
insert into #tPosiHdr values(7,'KTX1955','150000','00','11/09/2009')
CREATE TABLE #tPosiDtl_200909 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into #tPosiDtl_200909 values(1,'0','1')
insert into #tPosiDtl_200909 values(2,'0','1')
insert into #tPosiDtl_200909 values(3,'1','2')
CREATE TABLE #tPosiDtl_200910 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into #tPosiDtl_200910 values(4,'0','1')
insert into #tPosiDtl_200910 values(5,'1','2')
CREATE TABLE #tPosiDtl_200911 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into #tPosiDtl_200911 values(7,'1','2')
insert into #tPosiDtl_200911 values(8,'0','1')
insert into #tPosiDtl_200911 values(9,'1','2')
CREATE TABLE #temp (
tid INT,
BusN VARCHAR(20),
TTime VARCHAR(6),
DDate DATETIME,
whichTable VARCHAR(6),
KT VARCHAR(20),
HPT VARCHAR(6)
)
DECLARE @sql VARCHAR(1000)
DECLARE @i INT
DECLARE @iChar VARCHAR(10)
SET @i = 9
SET @iChar = NULL
WHILE @i < 12
BEGIN
IF @i < 10
BEGIN
select @iChar = '0'+CONVERT(CHAR(1),@i)
end
ELSE
SET @iChar = @i
select @sql = 'insert into #temp
select t1.TID,t1.BusN,t1.TTime,t1.DDate,
convert(varchar(4),datepart(yyyy,t1.DDate)) + right(''00''+convert(varchar(2),datepart(mm,t1.DDate)),2) as whichTable,
t2.KT,t2.HPT
from #tPosiHdr t1
left outer join
(select TID,KT,HPT
from #tPosiDtl_2009'+@iChar+'
)t2
on t1.TID=t2.TID
where datepart(mm,t1.ddate) = '+CONVERT(VARCHAR,@i)
EXEC (@sql)
SET @i = @i + 1
END
SELECT * FROM #temp
DROP TABLE #temp, #tPosiHdr, #tPosiDtl_200909, #tPosiDtl_200910, #tPosiDtl_200911
Dear Mister,
I want to make it column whichTable is a variable on @iChar, so my statement
....
from #tPosiDtl_2009'+@iChar+'
become
....
#tPosiDtl_'+@iChar+'
Can you give me the syntax?
Now, You give me a hope 🙂
November 9, 2009 at 5:35 pm
Well, I have already done a loop and thats just a nicer word for cursor. I don't have access to a test environment right now but I will write one up tomorrow morning. If you want to get started you can do something like this: write a cursor for the distinct values of year and month from the main table. Then loop through those values changing the @sql variable as you go and inserting into the #temp table. I will post a more formal answer in the morning.
November 9, 2009 at 9:57 pm
You may get rid of dynamic SQL problems by converting "Excel-style" tables to more appropritate form:
CREATE VIEW dbo.tPosiDtl_2009
-- view which contains all records for the whole year
AS
select TID, KT, HPT, '200901' WhichTable
from tPosiDtl_200901
UNION ALL
select TID, KT, HPT, '200902' WhichTable
from tPosiDtl_200902
UNION ALL
select TID, KT, HPT, '200903' WhichTable
from tPosiDtl_200903
UNION ALL
select TID, KT, HPT, '200904' WhichTable
from tPosiDtl_200904
UNION ALL
......... -- rest of the months here
select TID, KT, HPT, '200909' WhichTable
from tPosiDtl_200909
UNION ALL
select TID, KT, HPT, '200910' WhichTable
from tPosiDtl_200910
UNION ALL
select TID, KT, HPT, '200911' WhichTable
from tPosiDtl_200911
UNION ALL
select TID, KT, HPT, '200912' WhichTable
from tPosiDtl_200912
GO
-- Now use this view as a table:
select t1.TID, t1.BusN, t1.TTime, t1.DDate,
t2.whichTable, t2.KT, t2.HPT
from tPosiHdr t1
left outer join tPosiDtl_2009 t2 on t1.TID = t2.TID
_____________
Code for TallyGenerator
November 10, 2009 at 1:53 am
Matt Wilhoite (11/9/2009)
Well, I have already done a loop and thats just a nicer word for cursor. I don't have access to a test environment right now but I will write one up tomorrow morning. If you want to get started you can do something like this: write a cursor for the distinct values of year and month from the main table. Then loop through those values changing the @sql variable as you go and inserting into the #temp table. I will post a more formal answer in the morning.
Let's refresh, my table not table variables anymore
Let's say, My table structure and data sample as follow,
**************************************************
CREATE TABLE tPosiHdr (
TID int not null,
BusN varchar (20) NOT NULL,
TTime varchar(6) not null,
strPosi varchar(6)not null,
DDate datetime not null
)
insert into tPosiHdr values(1,'ADR3344','120000','00','09/01/2009')
insert into tPosiHdr values(2,'WNR4474','130000','00','09/02/2009')
insert into tPosiHdr values(3,'WKM1955','190000','00','09/04/2009')
insert into tPosiHdr values(4,'WKM1955','150000','00','10/13/2009')
insert into tPosiHdr values(5,'TXM4478','130000','00','10/16/2009')
insert into tPosiHdr values(6,'TXM1925','170000','00','11/02/2009')
insert into tPosiHdr values(7,'KTX1955','150000','00','11/09/2009')
CREATE TABLE tPosiDtl_200909 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into tPosiDtl_200909 values(1,'0','1')
insert into tPosiDtl_200909 values(2,'0','1')
insert into tPosiDtl_200909 values(3,'1','2')
CREATE TABLE tPosiDtl_200910 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into tPosiDtl_200910 values(4,'0','1')
insert into tPosiDtl_200910 values(5,'1','2')
CREATE TABLE tPosiDtl_200911 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into tPosiDtl_200911 values(7,'1','2')
insert into tPosiDtl_200911 values(8,'0','1')
insert into tPosiDtl_200911 values(9,'1','2')
**************************************************
so far, i've this,
select t1.TID,t1.BusN,t1.TTime,t1.DDate,
convert(varchar(4),datepart(yyyy,t1.DDate)) + right('00'+convert(varchar(2),datepart(mm,t1.DDate)),2) as whichTable,
t2.KT,t2.HPT
from tPosiHdr t1
left outer join
(select TID,KT,HPT
from tPosiDtl_200909)t2
on t1.TID=t2.TID
my result will
TID| BusN |TTime | DDate | whichTable| KT | HPT
--------------------------------------------------------------------
1 ADR3344 120000 2009-09-01 00:00:00.000 200909 0 1
2 WNR4474 130000 2009-09-02 00:00:00.000 200909 0 1
3 WKM1955 190000 2009-09-04 00:00:00.000 200909 1 2
4 WKM1955 150000 2009-10-13 00:00:00.000 200910 NULL NULL
5 TXM4478 130000 2009-10-16 00:00:00.000 200910 NULL NULL
6 TXM1925 170000 2009-11-02 00:00:00.000 200911 NULL NULL
7 KTX1955 150000 2009-11-09 00:00:00.000 200911 NULL NULL
I want to make it my SQL is dynamic.
The rule is
-- If whichTable=200909, the join table will be on tPosiDtl_200909
-- If whichTable=200910, the join table will be on tPosiDtl_200910, and so on.
So my result will be,
TID| BusN |TTime | DDate | whichTable| KT | HPT
--------------------------------------------------------------------
1 ADR3344 120000 2009-09-01 00:00:00.000 200909 0 1
2 WNR4474 130000 2009-09-02 00:00:00.000 200909 0 1
3 WKM1955 190000 2009-09-04 00:00:00.000 200909 1 2
4 WKM1955 150000 2009-10-13 00:00:00.000 200910 0 1
5 TXM4478 130000 2009-10-16 00:00:00.000 200910 1 2
6 TXM1925 170000 2009-11-02 00:00:00.000 200911 0 1
7 KTX1955 150000 2009-11-09 00:00:00.000 200911 1 2
It's possible my SQL will be dynamic?
I'm thinking of using @tblTemp and loop of While End.
DECLARE @tblTemp TABLE (
tid INT,
BusN VARCHAR(20),
TTime VARCHAR(6),
DDate DATETIME,
whichTable VARCHAR(6),
KT VARCHAR(20),
HPT VARCHAR(6)
)
WHILE statement here ...
i dont know how to get @whichTable here ...
select @sql = 'insert into @tblTemp
select t1.TID,t1.BusN,t1.TTime,t1.DDate,
convert(varchar(4),datepart(yyyy,t1.DDate)) + right(''00''+convert(varchar(2),datepart(mm,t1.DDate)),2) as whichTable,
t2.KT,t2.HPT
from tPosiHdr t1
left outer join
(select TID,KT,HPT
from tPosiDtl_'+@whichTable+'
)t2
on t1.TID=t2.TID
EXEC (@sql)
-- SQL coding here ..
END
As a result, me easily can query as
SELECT * FROM @tblTemp
November 10, 2009 at 6:14 am
Ok, I think this will work. You can replace all of the table names with your actual tables except for the #temp table. This will loop through each month and year in the #tPosiHdr table and find the corresponding detail table for it.
CREATE TABLE #tPosiHdr (
TID int not null,
BusN varchar (20) NOT NULL,
TTime varchar(6) not null,
strPosi varchar(6)not null,
DDate datetime not null
)
insert into #tPosiHdr values(1,'ADR3344','120000','00','09/01/2009')
insert into #tPosiHdr values(2,'WNR4474','130000','00','09/02/2009')
insert into #tPosiHdr values(3,'WKM1955','190000','00','09/04/2009')
insert into #tPosiHdr values(4,'WKM1955','150000','00','10/13/2009')
insert into #tPosiHdr values(5,'TXM4478','130000','00','10/16/2009')
insert into #tPosiHdr values(6,'TXM1925','170000','00','11/02/2009')
insert into #tPosiHdr values(7,'KTX1955','150000','00','11/09/2009')
CREATE TABLE #tPosiDtl_200909 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into #tPosiDtl_200909 values(1,'0','1')
insert into #tPosiDtl_200909 values(2,'0','1')
insert into #tPosiDtl_200909 values(3,'1','2')
CREATE TABLE #tPosiDtl_200910 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into #tPosiDtl_200910 values(4,'0','1')
insert into #tPosiDtl_200910 values(5,'1','2')
CREATE TABLE #tPosiDtl_200911 (
TID int not null,
KT varchar (20) NOT NULL,
HPT varchar(6) not null
)
insert into #tPosiDtl_200911 values(7,'1','2')
insert into #tPosiDtl_200911 values(8,'0','1')
insert into #tPosiDtl_200911 values(9,'1','2')
CREATE TABLE #temp (
tid INT,
BusN VARCHAR(20),
TTime VARCHAR(6),
DDate DATETIME,
whichTable VARCHAR(6),
KT VARCHAR(20),
HPT VARCHAR(6)
)
DECLARE @sql VARCHAR(1000)
DECLARE @tableYear VARCHAR(10)
DECLARE @tableMonth VARCHAR(2)
DECLARE whichTable CURSOR FOR
SELECT DISTINCT CONVERT(VARCHAR,DATEPART(yyyy,t1.ddate)),
right('00'+convert(varchar(2),datepart(mm,t1.DDate)),2)
FROM #tPosiHdr t1
OPEN whichTable
FETCH NEXT FROM whichTable INTO @tableYear, @tableMonth
WHILE @@FETCH_STATUS = 0
BEGIN
select @sql = 'insert into #temp
select t1.TID,t1.BusN,t1.TTime,t1.DDate,
convert(varchar(4),datepart(yyyy,t1.DDate)) + right(''00''+convert(varchar(2),datepart(mm,t1.DDate)),2) as whichTable,
t2.KT,t2.HPT
from #tPosiHdr t1
left outer join
(select TID,KT,HPT
from #tPosiDtl_'+@tableYear+@tableMonth+'
)t2
on t1.TID=t2.TID
where datepart(yyyy,t1.ddate) = '+@tableYear+'
and right(''00''+convert(varchar(2),datepart(mm,t1.DDate)),2) = '+ @tableMonth
--PRINT @sql
EXEC (@sql)
FETCH NEXT FROM whichTable INTO @tableYear, @tableMonth
END
SELECT * FROM #temp
CLOSE whichTable
DEALLOCATE whichTable
DROP TABLE #temp, #tPosiHdr, #tPosiDtl_200909, #tPosiDtl_200910, #tPosiDtl_200911
November 10, 2009 at 12:19 pm
Kindest Matt Wilhoite,
Your direction give me an accurate resultset. 😀
TQVM.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply