How to tell the mappings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to tell the mappings

Author

Topic

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-11 : 06:57:08

There exists a table that has been created from production. But somehow I need to create another table but using the same attributes like the last table. I would I know looking at the last table , what attributes/fields were used as I need the same fields to be in my new table. I looked in the old table and it had names different from that of prod table.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-11 : 07:04:07

quote:
Originally posted by Joshrinn

There exists a table that has been created from production. But somehow I need to create another table but using the same attributes like the last table. I would I know looking at the last table , what attributes/fields were used as I need the same fields to be in my new table. I looked in the old table and it had names different from that of prod table.

There is no systematic way to do this that I can think of. If it is a database with a small number of tables you may be able to look at the data types and sample the data in the database and your new table to make educated guesses. Or if the names are indicative of where they came from, that might be useful as well.

Another thing to keep in mind is that the data in the new table may not be a one-to-one mapping from the original tables. It may have been computed quantities.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 15:30:52

quote:
Originally posted by Joshrinn

There exists a table that has been created from production. But somehow I need to create another table but using the same attributes like the last table. I would I know looking at the last table , what attributes/fields were used as I need the same fields to be in my new table. I looked in the old table and it had names different from that of prod table.

One way to start analysis is:-
check for any procedures which involves the production as well as this table and specifically look for any insert/update logics. Look at column list against which values are inserted from production. this should give you mapping columns.
Also search for any SSIS packages which is using this table as destination and production table as source. To get this information you can use the below logic and read properties from the package

http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/18/collecting-information-about-your-ssis-packages-ssis-nugget.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-14 : 22:26:28

Hi Visakh
any clue about the answer I asked last time. Still haven't been able to solve that issue an I'm approaching the deadline. I think I confused you last time. There are three tables used in the report. The report has two columns. The report shows the display for one whole year. It has to refresh itself every 3 months with all the new customers that have given their business to the company. I do not need to worry about the bottom column as it is the forecast. The top column of report shows the data from June 2011 and the quarter ends on sept 2011. So all the customers on that period has to be frozen and the new customers that have come will be refreshed on the next quarter. Please help me with this. I couldn't provide you with the report query here and I'm sorry for that. I need to have the logic ready very quick.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-14 : 22:42:51

quote:
Originally posted by Joshrinn

Hi Visakh
any clue about the answer I asked last time. Still haven't been able to solve that issue an I'm approaching the deadline. I think I confused you last time. There are three tables used in the report. The report has two columns. The report shows the display for one whole year. It has to refresh itself every 3 months with all the new customers that have given their business to the company. I do not need to worry about the bottom column as it is the forecast. The top column of report shows the data from June 2011 and the quarter ends on sept 2011. So all the customers on that period has to be frozen and the new customers that have come will be refreshed on the next quarter. Please help me with this. I couldn't provide you with the report query here and I'm sorry for that. I need to have the logic ready very quick.

can you remind me that thread?
Cant get that question on top of my head now.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-14 : 22:47:56

What I said on that thread was a misinformation and that's why I wrote the description of the persisting problem here in this thread. The report has to have the top column refreshed every quarterly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-14 : 22:55:29

quote:
Originally posted by Joshrinn

What I said on that thread was a misinformation and that's why I wrote the description of the persisting problem here in this thread. The report has to have the top column refreshed every quarterly.

can you post screenshot or atleast sample layout of how report has to come?
what do you mean top column has to refesh? are you trying to cross tab and create new columns for each elapsed quarter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-14 : 22:57:35

@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-14 : 23:10:44

quote:
Originally posted by Joshrinn

Could you email me at joshrinn1@gmail.com. I could send you some info regarding it if that's not an issue with you. It's tough to provide here that's why

for others sake and to enable you to get better visibility of question please post details here
You can upload the screenshots to shared server and post the links here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-14 : 23:15:23

How do I do that? Like uploading The screen shots to shared folder?I can understand what you are trying to say:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-14 : 23:17:16

quote:
Originally posted by Joshrinn

How do I do that? Like uploading The screen shots to shared folder?I can understand what you are trying to say:)

post it in some uploading sites as images and post link here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-15 : 22:36:55

http://i48.tinypic.com/25i18gn.jpg>
Lemme know if that works

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-15 : 22:47:14

ok. this seems like a straight forward task to me. Use a calendar table to generate months between the required dates and populate a temporary table with result.
then use this table to left join to your table and get data for required period if present. then apply pivot over data to get it in required format or use matrix container in report with column group as month

see similar calendar function here

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-15 : 22:54:05

ok so what about query that already exists in my report? there is a code that already pulls the data from the tables? can I follow this one instead. Sorry a little slow to understand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-15 : 23:05:55

quote:
Originally posted by Joshrinn

ok so what about query that already exists in my report? there is a code that already pulls the data from the tables? can I follow this one instead. Sorry a little slow to understand

thats what i told. you need to use calendar table as base and add your query by mean of left join to that .

ie


SELECT MonthDate,required fields
FROM dbo.CalendarTable(@Start,@nd,0,1) f
LEFT JOIN {your current code here}
ON...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-15 : 23:08:48

Thanks Visakh you are awesome. If I still have issues tomorrow I'll reply on this post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-16 : 00:17:15

np...you're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-16 : 11:13:10

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='CalendarTable' AND ROUTINE_SCHEMA='dbo' AND
ROUTINE_TYPE='FUNCTION')

DROP FUNCTION dbo.CalendarTable
GO
go

CREATE FUNCTION dbo.CalendarTable(
@StartDate datetime
,@Enddate datetime
,@MonthEnd bit=0
)
RETURNS @CALENDAR TABLE(
Date datetime
,MonthEnd bit
)
AS
BEGIN
;WITH Calendar_CTE
(Date,MonthEnd)
AS
(
SELECT
@Startdate
,CASE WHEN DATEPART(dd,@StartDate)=1 THEN 1 ELSE 0
END

UNION ALL

SELECT
DATEADD(mm,1,Date)
,CASE WHEN DATEPART(dd,Date)=1
THEN 1 ELSE 0
END

FROM Calendar_CTE
WHERE DATEADD(mm,1,Date)<=@Enddate
)

INSERT INTO @CALENDAR
SELECT Date,MonthEnd
FROM Calendar_CTE
WHERE (MonthEnd=1
OR @MonthEnd=0)
OPTION (MAXRECURSION 0)
RETURN
END
I tweaked the query a lil bit and I got a table valued structure as desired. But the dates were in a weird fashion. When you run the query you can tell

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-16 : 20:17:36

tell me what do you mean by weird fashion.How else do you want it to come?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources