| Author |
 Topic  |
|
|
Joshrinn
Posting Yak Master
118 Posts |
 Posted - 05/11/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/11/2012 : 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
India
47189 Posts |
Posted - 05/11/2012 : 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 - 05/14/2012 : 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
India
47189 Posts |
Posted - 05/14/2012 : 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 - 05/14/2012 : 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
India
47189 Posts |
Posted - 05/14/2012 : 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 - 05/14/2012 : 22:57:35
|
| @gmail.com |
Edited by - Joshrinn on 05/14/2012 23:16:38 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 05/14/2012 : 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 - 05/14/2012 : 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
India
47189 Posts |
Posted - 05/14/2012 : 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 05/15/2012 : 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 - 05/15/2012 : 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
India
47189 Posts |
Posted - 05/15/2012 : 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 - 05/15/2012 : 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
India
47189 Posts |
Posted - 05/16/2012 : 00:17:15
|
np...you're wc
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Joshrinn
Posting Yak Master
118 Posts |
Posted - 05/16/2012 : 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
India
47189 Posts |
Posted - 05/16/2012 : 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/
|
|
|
| |
Topic |
|
How to tell the mappings
