[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Querying dynamic file name base on field content.

Hi,

Let's see table structure:

Table TDCHQRCVyyyy (Cheque Receive) where yyyy is year.

1. ChqId
2. ChqDate
3. ChqAmount
4. ChgDueDate

Table  TDINVPAYyyyy (Invoice Payment) where yyyy is year.

1. PayId.
2. PayDate.
3. ChqId.
4. ChqDate
5. Invoice.
6. InvoiceDate.
7. PayAmount.

SELECT A.PayId, A.ChqId, A.ChqDate, (SELECT  ChgDueDate FROM TDCHQRCVyyyy AS B WHERE A.ChqId = B.ChqId )
              A.Invoice, A.PayAmount
FROM TDINVPAY2007.

Note : TDCHQRCVyyyy is get from TDCHQRCV + YEAR(A.ChqDate)
 
This a very challenging query.

I don't know if it is possible.

Thank you.

 

0
emi_sastra
Asked:
emi_sastra
  • 5
  • 4
2 Solutions
 
BinuthCommented:
i think you need dynamic sql to solve this problem
try this sample
CREATE TABLE #TmpTDCHQRCV(ChqId INT,ChgDueDate DATETIME,chkYear INT)
 
DECLARE @SQL AS VARCHAR(MAX)
SET @SQL = NULL
 
SELECT
	@SQL = 
		CASE WHEN @SQL IS NULL THEN 'SELECT DISTINCT ' + CAST(A.ChqId AS VARCHAR) + ' AS ChqId,ChgDueDate,' + CAST(YEAR(A.ChqDate) AS VARCHAR) + ' AS chkYear FROM TDCHQRCV' + CAST(YEAR(A.ChqDate) AS VARCHAR)
			 ELSE @SQL + ' UNION ALL SELECT DISTINCT ' + CAST(A.ChqId AS VARCHAR) + ' AS ChqId,ChgDueDate,' + CAST(YEAR(A.ChqDate) AS VARCHAR) + ' AS chkYear FROM TDCHQRCV' + CAST(YEAR(A.ChqDate) AS VARCHAR) END
FROM( 
	SELECT 
		ChqId,
		ChqDate
	FROM 
		TDINVPAY2007
	) AS A
 
INSERT INTO #TmpTDCHQRCV
EXEC(@SQL)
 
 
SELECT
	A.PayId,
	A.ChqId,
	A.ChqDate,
	(SELECT ChgDueDate FROM #TmpTDCHQRCV AS B WHERE A.ChqId = B.ChqId AND chkYear = YEAR(A.ChqDate)),
    A.Invoice, 
	A.PayAmount
FROM TDINVPAY2007 AS A
 
 
DROP TABLE #TmpTDCHQRCV

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Dynamic SQL is the only way to do it, unless, there is a limit to the number of years, then you could do something like :

SELECT A.PayId, A.ChqId, A.ChqDate
           , case
                   when year(a.chqdate) = 2006 then (SELECT  ChgDueDate FROM TDCHQRCV2006 AS B WHERE A.ChqId = B.ChqId )
                   when year(a.chqdate) = 2005 then (SELECT  ChgDueDate FROM TDCHQRCV2005 AS B WHERE A.ChqId = B.ChqId )
                   when year(a.chqdate) = 2004 then (SELECT  ChgDueDate FROM TDCHQRCV2004 AS B WHERE A.ChqId = B.ChqId )
             else '19000101' end as A.Invoice
             , A.PayAmount
FROM TDINVPAY2007.


or as a join... and then select distinct, or use as a subquery.

What do you prefer ?
0
 
Mark WillsTopic AdvisorCommented:
think the above "else" line should be :  else '19000101' end as chngduedate, A.Invoice


Or, as a function might be interesting...


SELECT A.PayId, A.ChqId, A.ChqDate, dbo.udf_get_chngduedate(a.chqid,year(a.chqdate)) as chngduedate,  A.Invoice, A.PayAmount
FROM TDINVPAY2007

-- but create the function first...

create function udf_get_chngduedate(@chqid int, @year int)
returns datetime
as
begin

return (select isnull((case when @year = 2000 then (SELECT  ChgDueDate FROM TDCHQRCV2000 WHERE ChqId = @chqid)
                   when @year = 2001 then (SELECT  ChgDueDate FROM TDCHQRCV2001 WHERE ChqId = @chqid)
                   when @year = 2002 then (SELECT  ChgDueDate FROM TDCHQRCV2002 WHERE ChqId = @chqid)
                   when @year = 2003 then (SELECT  ChgDueDate FROM TDCHQRCV2003 WHERE ChqId = @chqid)
                   when @year = 2004 then (SELECT  ChgDueDate FROM TDCHQRCV2004 WHERE ChqId = @chqid)
                   when @year = 2005 then (SELECT  ChgDueDate FROM TDCHQRCV2005 WHERE ChqId = @chqid)
                   when @year = 2006 then (SELECT  ChgDueDate FROM TDCHQRCV2006 WHERE ChqId = @chqid)
                   when @year = 2007 then (SELECT  ChgDueDate FROM TDCHQRCV2007 WHERE ChqId = @chqid)
                   when @year = 2008 then (SELECT  ChgDueDate FROM TDCHQRCV2008 WHERE ChqId = @chqid)
                   when @year = 2009 then (SELECT  ChgDueDate FROM TDCHQRCV2009 WHERE ChqId = @chqid)
                   when @year = 2010 then (SELECT  ChgDueDate FROM TDCHQRCV2010 WHERE ChqId = @chqid)
               else '19000101'
               end),'19000101'))

end
GO

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
emi_sastraAuthor Commented:
Hi All,

Your both code are great.

Let me try first which one is suitable in practice.

Thank you very much for your help.
0
 
Mark WillsTopic AdvisorCommented:
Just in case... The create function is a once off event - don't have to do it every time, once it is there, it is done and then you can use it in place of a column name...


Thanks for the assist, and will see you around :)
0
 
emi_sastraAuthor Commented:
Hi mark_wills,

Yes, the function offer flexibility in calling column.

Since you get in again, I will ask more regarding this question.

Could we pass the return columns (one or more than one)?

create function udf_get_chngduedate(@chqid int, @year int, @returnFields char)
returns datetime  <----How about this?
as
begin

 when @year = 2001 then (SELECT  @returnFields FROM TDCHQRCV2001 WHERE ChqId = @chqid)

If we could do it then we just have one function to get the entire fields.

Thank you.

0
 
Mark WillsTopic AdvisorCommented:
Yes, could return a table instead of a variable, but then it would look a lot like that original query I posted with a big case statement inside the actual query.

Could encapsulate the query as a VIEW if you want it to look "cleaner", or, if the function had other things to do, then might be good in the function, but from what you have described so far, the function is really just a more convenient method to encapsulate that case statement.
0
 
emi_sastraAuthor Commented:
Ok.

Could encapsulate the query as a VIEW if you want it to look "cleaner".

I am happy to learn it. Should I post a new question for this?

Thank you.
0
 
Mark WillsTopic AdvisorCommented:
only if it goes beyond this... Besides - the dynamic SQL got the accepted answer on this one ;)

create view vw_INVPAY
as
SELECT
  A.PayId
, A.ChqId
, A.ChqDate
, dbo.udf_get_chngduedate(a.chqid,year(a.chqdate)) as chngduedate
,  A.Invoice
, A.PayAmount
FROM TDINVPAY2007 A

go

or, you can put that case statement in the above. Once created - a once off event - you can use it just like any other table (for selections - not for inserts or updates)

e.g. select * from vw_INVPAY where chngduedate > getdate() - 90

0
 
emi_sastraAuthor Commented:
Ok.

Thank you again for your help.

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now