How can i reprogram this? It's taking forever to run.

I need to rewrite a sp. I'm pasting the major part of the sp down here. Also
,
if possible, can you tell me which columns to index? Thank you.
DECLARE @.procAsset int,
@.procStartDate datetime,
@.procEndDate datetime
DECLARE assetdate_cur CURSOR FOR
SELECT DISTINCT ITAssetObjectID, CAST(DATEPART(month, UsageStartDateTime)
AS VARCHAR(2)) + '/' + CAST(DATEPART(day, UsageStartDateTime) AS VARCHAR(2)
)
+ '/' + CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0' AS
StartDate
FROM mapITAsset2ExecutablesUsage
WHERE IsSummarized = 0
ORDER BY ITAssetObjectID, StartDate
OPEN assetdate_cur
FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.procEndDate = DATEADD(day, 1, @.procStartDate)
--Delete old data for the day from the table
DELETE FROM mapITAsset2ExecutablesUsageSummary WHERE ITAssetObjectID =
@.procAsset AND RunDate = @.procStartDate
INSERT INTO mapITAsset2ExecutablesUsageSummary
(ITAssetObjectID, ExecutableRepositoryID, UserID, RunCount, RunTime,
RunDate)
SELECT @.procAsset, ExecutableRepositoryID, UserID, SUM(Occur), CASE
WHEN SUM(Secs) IS NULL THEN 0
ELSE SUM(Secs)
END, @.procStartDate
FROM
(
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*) AS
Occur, SUM(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
@.procEndDate
AND (UsageEndDateTime < @.procEndDate OR UsageEndDateTime IS NULL)
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
UNION ALL
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
SUM(DATEDIFF(second, UsageStartDateTime, @.procEndDate))
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
@.procEndDate
AND UsageEndDateTime >= @.procEndDate
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
UNION ALL
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*), 24*60*60
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime >=
@.procEndDate
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
UNION ALL
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
SUM(DATEDIFF(second, @.procStartDate, UsageEndDateTime))
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime <
@.procEndDate AND UsageEndDateTime >= @.procStartDate
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
) AS Totals
GROUP BY ExecutableRepositoryID, UserID
FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
END
CLOSE assetdate_cur
DEALLOCATE assetdate_cur
This sp actually summarizes thes data but it just takes forever to
run...(more than 10 hours)
Can someone give me a hint on how can i reprogram this? Thank youYou've got to get rid of the cursor, bottom line. Take out the subquery in
your INSERT INTO...SELECT statement. Put that data into a temp table and
link to that if you need to instead. If you post the DDL statements that
define your tables, I can help you better.
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:E02337CF-71FF-4871-8F3C-60CDFC79C4B6@.microsoft.com...
>I need to rewrite a sp. I'm pasting the major part of the sp down here.
>Also,
> if possible, can you tell me which columns to index? Thank you.
>
> DECLARE @.procAsset int,
> @.procStartDate datetime,
> @.procEndDate datetime
> DECLARE assetdate_cur CURSOR FOR
> SELECT DISTINCT ITAssetObjectID, CAST(DATEPART(month, UsageStartDateTime)
> AS VARCHAR(2)) + '/' + CAST(DATEPART(day, UsageStartDateTime) AS
> VARCHAR(2))
> + '/' + CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0'
> AS
> StartDate
> FROM mapITAsset2ExecutablesUsage
> WHERE IsSummarized = 0
> ORDER BY ITAssetObjectID, StartDate
>
> OPEN assetdate_cur
> FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.procEndDate = DATEADD(day, 1, @.procStartDate)
> --Delete old data for the day from the table
> DELETE FROM mapITAsset2ExecutablesUsageSummary WHERE ITAssetObjectID =
> @.procAsset AND RunDate = @.procStartDate
> INSERT INTO mapITAsset2ExecutablesUsageSummary
> (ITAssetObjectID, ExecutableRepositoryID, UserID, RunCount, RunTime,
> RunDate)
> SELECT @.procAsset, ExecutableRepositoryID, UserID, SUM(Occur), CASE
> WHEN SUM(Secs) IS NULL THEN 0
> ELSE SUM(Secs)
> END, @.procStartDate
> FROM
> (
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*) AS
> Occur, SUM(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
> @.procEndDate
> AND (UsageEndDateTime < @.procEndDate OR UsageEndDateTime IS NULL)
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> UNION ALL
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
> SUM(DATEDIFF(second, UsageStartDateTime, @.procEndDate))
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
> @.procEndDate
> AND UsageEndDateTime >= @.procEndDate
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> UNION ALL
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*), 24*60*60
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime >=
> @.procEndDate
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> UNION ALL
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
> SUM(DATEDIFF(second, @.procStartDate, UsageEndDateTime))
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime <
> @.procEndDate AND UsageEndDateTime >= @.procStartDate
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> ) AS Totals
> GROUP BY ExecutableRepositoryID, UserID
>
> FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
> END
> CLOSE assetdate_cur
> DEALLOCATE assetdate_cur
>
>
> This sp actually summarizes thes data but it just takes forever to
> run...(more than 10 hours)
> Can someone give me a hint on how can i reprogram this? Thank you|||Couple more things:
1. You may also want to consider having a clustered index on
UsageStartDateTime since you are doing a lot of range selection using
operators such as >, >=, <, and <=.
2. You may want to consider using T-SQL extended arguments like
READ_ONLY, OPTIMISTIC locking, etc for your cursor.|||I've acdtually used a temp table as a replacement for the cursor but it has
not given me the expected results. I've taken the initial query and stored i
t
in a temp table. I have then joined that temp table to the map... table and
done all my querying. But the group by stuff throws me off because when i do
selects, I've to groupby ITAssetObjectID and @.procStartDate/StartDate which
I
dont want to do.
SELECT DISTINCT ITAssetObjectID,
CAST(DATEPART(month, UsageStartDateTime) AS VARCHAR(2)) + '/' +
CAST(DATEPART(day, UsageStartDateTime) AS VARCHAR(2)) + '/' +
CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0' AS StartDat
e
,CAST(DATEPART(month, dateadd(day,1,UsageStartDateTime)) AS VARCHAR(2)) +
'/' + CAST(DATEPART(day, dateadd(day,1,UsageStartDateTime)) AS VARCHAR(2))
+ '/' + CAST(DATEPART(year, dateadd(day,1,UsageStartDateTime))AS VARCHAR(4))
+ ' 0:0:0' AS EndDate
into ##SummarizeITAsset3
FROM mapITAsset2ExecutablesUsage (nolock)
WHERE IsSummarized = 0
ORDER BY ITAssetObjectID, StartDate
delete mia2eus FROM mapITAsset2ExecutablesUsageSummary mia2eus join
##SummarizeITAsset3 SITA
on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
where mia2eus.RunDate = SITA.StartDate
SELECT mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID, count(*) AS
Occur, sum(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
into ##Totals
FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
SITA
on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
WHERE mia2eus.UsageStartDateTime >= SITA.StartDate And
mia2eus.UsageStartDateTime < SITA.EndDate
AND (mia2eus.UsageEndDateTime < SITA.EndDate OR mia2eus.UsageEndDateTime IS
NULL)
GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
--2004
--union all
insert into ##Totals
SELECT mia2eus.ITAssetObjectID, mia2eus.ExecutableRepositoryID,
mia2eus.UserID, COUNT(*) AS Occur, SUM(DATEDIFF(second, UsageStartDateTime,
UsageEndDateTime)) AS Secs
FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
SITA
on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
WHERE mia2eus.UsageStartDateTime >= SITA.StartDate AND
mia2eus.UsageStartDateTime < SITA.EndDate
AND mia2eus.UsageEndDateTime >= SITA.EndDate
GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
.
.
.
.
I'm having problems with the above select into's and the insert into's. How
would you do those and the groupby's? Please help with that. Thank you.
The DDL's are as follows.
CREATE TABLE [dbo].[mapITAsset2ExecutablesUsage] (
[ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ITAssetObjectID] [bigint] NOT NULL ,
[ExecutableRepositoryID] [int] NOT NULL ,
[UserID] [int] NULL ,
[ProcessID] [int] NOT NULL ,
[UsageStartDateTime] [datetime] NOT NULL ,
[UsageEndDateTime] [datetime] NULL ,
[ServerUpdateTime] [datetime] NULL ,
[IsSummarized] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[mapITAsset2ExecutablesUsageSummary] (
[ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ITAssetObjectID] [bigint] NOT NULL ,
[ExecutableRepositoryID] [int] NOT NULL ,
[UserID] [int] NULL ,
[RunCount] [int] NOT NULL ,
[RunTime] [int] NOT NULL ,
[RunDate] [datetime] NOT NULL
)
GO|||Sorry I don't have time to test this myself right now, but maybe what I have
will get you going in a better direction. I've tried to write things based
on your first procedure you posted:
--First, create a View that joins the mapITAsset2ExecutablesUsage table to
itself based on the ITAssetObjectID.
--This will filter out the IsSummarized records and give you a time
computation for each join result.
--I left out the parsing of the StartDate field since I couldn't tell why
you had that in there in the first place.
--I based the CASE statement on the way you calculated the EndDate field in
your cursor and your UNION statements. I'm sure you could clean it up
better.
CREATE VIEW vw_mapITAsset2ExecutablesUsage
AS
SELECT
M1.ITAssetObjectID,
M1.ExecutableRepositoryID,
M1.UserID,
M1.UsageStartDateTime,
CASE
WHEN M2.UsageStartDateTime >= M1.UsageStartDateTime AND
M2.UsageStartDateTime < DATEADD(day, 1, M1.UsageStartDateTime) AND
(M2.UsageEndDateTime < DATEADD(day, 1, M1.UsageStartDateTime) OR
M2.UsageEndDateTime IS NULL) THEN DATEDIFF(second, M2.UsageStartDateTime,
M2.UsageEndDateTime)
WHEN M2.UsageStartDateTime >= M1.UsageStartDateTime AND
M2.UsageStartDateTime < DATEADD(day, 1, M1.UsageStartDateTime) AND
M2.UsageEndDateTime >= DATEADD(day, 1, M1.UsageStartDateTime) THEN
DATEDIFF(second, M2.UsageStartDateTime, DATEADD(day, 1,
M1.UsageStartDateTime))
WHEN M2.UsageStartDateTime < M1.UsageStartDateTime AND M2.UsageEndDateTime
>= DATEADD(day, 1, M1.UsageStartDateTime) THEN 86400
WHEN M2.UsageStartDateTime < M1.UsageStartDateTime AND M2.UsageEndDateTime
< DATEADD(day, 1, M1.UsageStartDateTime) AND M2.UsageEndDateTime >=
M1.UsageStartDateTime THEN DATEDIFF(second, M1.UsageStartDateTime,
M2.UsageEndDateTime)
END AS Secs
FROM mapITAsset2ExecutablesUsage M1
INNER JOIN mapITAsset2ExecutablesUsage M2 ON M1.ITAssetObjectID =
M2.ITAssetObjectID
WHERE M1.IsSummarized = 0 AND M2.IsSummarized = 0
---
--Next, your procedure to populate the mapITAsset2ExecutablesUsageSummary
table should be as simple as...
TRUNCATE TABLE mapITAsset2ExecutablesUsageSummary
INSERT INTO mapITAsset2ExecutablesUsageSummary (
ITAssetObjectID,
ExecutableRepositoryID,
UserID,
RunCount,
RunTime,
RunDate)
SELECT M.ITAssetObjectID,
M.ExecutableRepositoryID,
M.UserID,
COUNT(M.ITAssetObjectID),
SUM(Secs),
M.UsageStartDateTime
FROM vw_mapITAsset2ExecutablesUsage M
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID,
M.UsageStartDateTime
--Here you get both the count on rows and the summary of the time in seconds
in the same statement.
--
Again, I may not have understood in the time I had your query needs, but I
hope this helps you out better. Keep in mind you can also add indexes to
the View to assist in your performance to the final query.
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:1052AF0E-B7D3-47B8-9DA9-69C6E04A4651@.microsoft.com...
> I've acdtually used a temp table as a replacement for the cursor but it
> has
> not given me the expected results. I've taken the initial query and stored
> it
> in a temp table. I have then joined that temp table to the map... table
> and
> done all my querying. But the group by stuff throws me off because when i
> do
> selects, I've to groupby ITAssetObjectID and @.procStartDate/StartDate
> which I
> dont want to do.
>
> SELECT DISTINCT ITAssetObjectID,
> CAST(DATEPART(month, UsageStartDateTime) AS VARCHAR(2)) + '/' +
> CAST(DATEPART(day, UsageStartDateTime) AS VARCHAR(2)) + '/' +
> CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0' AS
> StartDate
> ,CAST(DATEPART(month, dateadd(day,1,UsageStartDateTime)) AS VARCHAR(2)) +
> '/' + CAST(DATEPART(day, dateadd(day,1,UsageStartDateTime)) AS
> VARCHAR(2))
> + '/' + CAST(DATEPART(year, dateadd(day,1,UsageStartDateTime))AS
> VARCHAR(4))
> + ' 0:0:0' AS EndDate
> into ##SummarizeITAsset3
> FROM mapITAsset2ExecutablesUsage (nolock)
> WHERE IsSummarized = 0
> ORDER BY ITAssetObjectID, StartDate
> delete mia2eus FROM mapITAsset2ExecutablesUsageSummary mia2eus join
> ##SummarizeITAsset3 SITA
> on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
> where mia2eus.RunDate = SITA.StartDate
>
>
> SELECT mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID, count(*)
> AS
> Occur, sum(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
> into ##Totals
> FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
> SITA
> on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
> WHERE mia2eus.UsageStartDateTime >= SITA.StartDate And
> mia2eus.UsageStartDateTime < SITA.EndDate
> AND (mia2eus.UsageEndDateTime < SITA.EndDate OR mia2eus.UsageEndDateTime
> IS
> NULL)
> GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
> --2004
> --union all
> insert into ##Totals
> SELECT mia2eus.ITAssetObjectID, mia2eus.ExecutableRepositoryID,
> mia2eus.UserID, COUNT(*) AS Occur, SUM(DATEDIFF(second,
> UsageStartDateTime,
> UsageEndDateTime)) AS Secs
> FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
> SITA
> on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
> WHERE mia2eus.UsageStartDateTime >= SITA.StartDate AND
> mia2eus.UsageStartDateTime < SITA.EndDate
> AND mia2eus.UsageEndDateTime >= SITA.EndDate
> GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
> .
> .
> .
> .
> I'm having problems with the above select into's and the insert into's.
> How
> would you do those and the groupby's? Please help with that. Thank you.
> The DDL's are as follows.
>
> CREATE TABLE [dbo].[mapITAsset2ExecutablesUsage] (
> [ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [ITAssetObjectID] [bigint] NOT NULL ,
> [ExecutableRepositoryID] [int] NOT NULL ,
> [UserID] [int] NULL ,
> [ProcessID] [int] NOT NULL ,
> [UsageStartDateTime] [datetime] NOT NULL ,
> [UsageEndDateTime] [datetime] NULL ,
> [ServerUpdateTime] [datetime] NULL ,
> [IsSummarized] [int] NOT NULL
> )
> GO
> CREATE TABLE [dbo].[mapITAsset2ExecutablesUsageSummary] (
> [ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [ITAssetObjectID] [bigint] NOT NULL ,
> [ExecutableRepositoryID] [int] NOT NULL ,
> [UserID] [int] NULL ,
> [RunCount] [int] NOT NULL ,
> [RunTime] [int] NOT NULL ,
> [RunDate] [datetime] NOT NULL
> )
> GO
>|||Hey Random. I have put this in QA. There are a few problems I have with this
.
I'm thinking the view is correct but the group by clause is messing a lot of
things up. I'm receiving the correct format for almost everything.
-I need to remove the time field from the RunDate or make it 00:00:00
because everything for a day is to be summarized.
-The Runcount is somehow screwed. All the runcounts for a particular
ITAssetObjectID is the same regardless of its ExecutableRepositoryID, UserID
(I think I have found a fix as it's another column that it needs to count
on, it's not ITAssetObjectID, it's ProcessID.
This are the problems for now.|||Hey Random, The second point in my previous post is also a problem. Even
when I do a count(ProcessID) for the RunCount column it gives me the same
counts for every ITassetObjectId and ExecutableRepositoryID.
It's quite urgent for me to get this to work. Thank you for all your help...|||Tejas;
Is there any way you can email me some sample data? Maybe a lot of INSERT
statements for your mapITAsset2ExecutablesUsage table and an idea of what
the result data should look like in mapITAsset2ExecutablesUsageSummary?
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:DFA772AC-9CCA-46AB-A434-C1C06F8BB7E5@.microsoft.com...
> Hey Random. I have put this in QA. There are a few problems I have with
> this.
> I'm thinking the view is correct but the group by clause is messing a lot
> of
> things up. I'm receiving the correct format for almost everything.
> -I need to remove the time field from the RunDate or make it 00:00:00
> because everything for a day is to be summarized.
> -The Runcount is somehow screwed. All the runcounts for a particular
> ITAssetObjectID is the same regardless of its ExecutableRepositoryID,
> UserID
> (I think I have found a fix as it's another column that it needs to count
> on, it's not ITAssetObjectID, it's ProcessID.
> This are the problems for now.|||Yes, I can. Can you give me youe email address or email me at
parikht@.gmail.com? Thank you.

how can i merge both columns?

I have a column called email_address in table: mailing_list and another column called member_email in table: members and I want to do a SELECt to list the result of both columns in a new column

Create a UNION between the two tables.

Something like:

SELECT ml.eMail_Address

FROM Mailing_List ml

UNION

SELECT m.Member_EMail

FROM Members m

|||

what would be the output column if I use SqlDataReader?

also how can I get the select count(*) of it?

|||

You 'could' just execute the query and see what you get.

As written, there will be one column, named: eMail_Address

SELECT @.@.ROWCOUNT immediately after the query should proved the count.

|||

You can also do:

select *

from (

SELECT ml.eMail_Address

FROM Mailing_List ml

UNION

SELECT m.Member_EMail

FROM Members m ) as emailAddresses

Then you can count them, or do whatever with them, treating this derived table as any other. The first name in the UNION operator will be the name of the column in the result set.

The one thing I wanted to note is that you should always name things the same sort of way. If it is email_address in one place, it should be email_address in another. member_email could mean something else without context and knowledge of the data, while email_address is quite clear.

Naming it member_email_address would be acceptable, as an extension to explain whose email address it is.

|||

Louis,

I agree wholy with your remarks about consistency in naming. Any column containing an email address 'should' be names 'eMailAddress'. As you indicate, anything else is opening the door for confusion.

But it seems redundent to go with your alternative suggestion, member_email_address, since the column would be known as Members.eMailAddress and therefore wouldn't be confused with MailingList.eMailAddress. (I also don't believe in unnecessary underscores separating words either.) I guess I put adding a table name prefix to a column name right up there in the same category as prefixing table names with 'tbl'. Totally wasted keystrokes that offer no added value.

|||

Yeah, I agree with you. That was just an out of context naming suggestion, meaning that the name member_email_address is an acceptable name. I agree wholeheartedly that you would name it email address in the member table, if it was directly referencing the member. But if there was an email that was specifically a email address that was specific to their membership, and it wasn't clear from the name email_address, then member_email_address is a good name.

To me, naming is such a monumental pain, mostly because it is one of the most important steps in the process. A funky name causes a ripple effect of confusion to future users/programmers/support persons/etc. Thanks for clearing that one up!

how can i merge both columns?

I have a column called email_address in table: mailing_list and another column called member_email in table: members and I want to do a SELECt to list the result of both columns in a new column

Create a UNION between the two tables.

Something like:

SELECT ml.eMail_Address

FROM Mailing_List ml

UNION

SELECT m.Member_EMail

FROM Members m

|||

what would be the output column if I use SqlDataReader?

also how can I get the select count(*) of it?

|||

You 'could' just execute the query and see what you get.

As written, there will be one column, named: eMail_Address

SELECT @.@.ROWCOUNT immediately after the query should proved the count.

|||

You can also do:

select *

from (

SELECT ml.eMail_Address

FROM Mailing_List ml

UNION

SELECT m.Member_EMail

FROM Members m ) as emailAddresses

Then you can count them, or do whatever with them, treating this derived table as any other. The first name in the UNION operator will be the name of the column in the result set.

The one thing I wanted to note is that you should always name things the same sort of way. If it is email_address in one place, it should be email_address in another. member_email could mean something else without context and knowledge of the data, while email_address is quite clear.

Naming it member_email_address would be acceptable, as an extension to explain whose email address it is.

|||

Louis,

I agree wholy with your remarks about consistency in naming. Any column containing an email address 'should' be names 'eMailAddress'. As you indicate, anything else is opening the door for confusion.

But it seems redundent to go with your alternative suggestion, member_email_address, since the column would be known as Members.eMailAddress and therefore wouldn't be confused with MailingList.eMailAddress. (I also don't believe in unnecessary underscores separating words either.) I guess I put adding a table name prefix to a column name right up there in the same category as prefixing table names with 'tbl'. Totally wasted keystrokes that offer no added value.

|||

Yeah, I agree with you. That was just an out of context naming suggestion, meaning that the name member_email_address is an acceptable name. I agree wholeheartedly that you would name it email address in the member table, if it was directly referencing the member. But if there was an email that was specifically a email address that was specific to their membership, and it wasn't clear from the name email_address, then member_email_address is a good name.

To me, naming is such a monumental pain, mostly because it is one of the most important steps in the process. A funky name causes a ripple effect of confusion to future users/programmers/support persons/etc. Thanks for clearing that one up!

sql

How can I make reports and hide columns at runtime?

Hello. I did a report that has many many columns. I should give the user the
possibility to choose which columns to use with my asp.net program. Is that
possible to do? The only columns that must be shows are the ones that the
user has choosed.
Is that possible. ! !Please help me with links, code, books anythin
neccesary.Luis,
This is possible, simply create a boolean parameter for each column you want
to show/hide (e.g. ShowOfficeName) then select the column in the datagrid
and set the visibility | Hidden property to an expression of
= Not Parameters!ShowOfficeName.Value
Hope this helps,
Brian
"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in message
news:OLwPSwAeFHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hello. I did a report that has many many columns. I should give the user
> the
> possibility to choose which columns to use with my asp.net program. Is
> that
> possible to do? The only columns that must be shows are the ones that the
> user has choosed.
> Is that possible. ! !Please help me with links, code, books anythin
> neccesary.
>

How can I make reports and hide columns at run time with my aspnet app?

Hello. I did a report that has many many columns. I should give the user the
possibility to choose which columns to use with my asp.net program. Is that
possible to do? The only columns that must be shows are the ones that the
user has choosed.

Is that possible. ! !Please help me with links, code, books anythin
neccesary.You can parameterize the report and set the visibilty property on the column to be dependent on the parameter value. If you want to hide mlutiple columns, you will need multiple parmeters or, in SQL 2005, you can have a multivalued parameted. The only downside to this approach is that the body will not shrink with the hidden columns. This can cause problems when printing / exporting the report.

How can I make changes to my Publisher tables e.g change columns ,

I have a transactional replication with one publisher and one subscriber.
This is working fine. Now my developers need to make changes to the
production database. They would like to change columns, add new indexes, etc.
What is the bet way to do this without much down time?
George Gopie
freaking developers. You can use sp_repladdcolumn or sp_repldropcolumn for
changing columns. To add new indexes use sp_addscriptexec for subscriptions
deployed via unc's.
If your environment's schema is volatile (i.e. changing all the time) you
should look at log shipping or SQL Server 2005.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"georgeg" <ggg@.hotamil.com> wrote in message
news:B980F6D1-CF64-4A7D-B513-9715D427A898@.microsoft.com...
> I have a transactional replication with one publisher and one subscriber.
> This is working fine. Now my developers need to make changes to the
> production database. They would like to change columns, add new indexes,
etc.
> What is the bet way to do this without much down time?
> --
> George Gopie
>

How can I make a Matrix Transposition in SQL ?

Can we do matrix transpose (rows become columns and columns become rows) in standard SQL?

1 2 3
4 5 6
7 8 9

changes to

1 4 7
2 5 8
3 6 9

how about the situation when no of rows <> no of column ?

let's consider the no of rows it's fixed and known before running the SQL statement.

thanks.

What do you mean standard SQL? Here is a 2005 version that will rotate your data (it is not as elegant without CTE's, UNPIVOT and PIVOT, but it can be done if you need it:

The basic idea used was to add a key for the rotate, and then do an UNPIVOT followed by a PIVOT:

set nocount on
create table pivotTest
(
pkey varchar(10) primary key,
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)

insert into pivotTest (pkey, col1, col2, col3)
select 'a','1','2','3'
union all
select 'b','4','5','6'
union all
select 'c','7','8','9'
go

select *
from pivotTest
go

This returns:

pkey col1 col2 col3
- - - -
a 1 2 3
b 4 5 6
c 7 8 9

First take the set and flatten it out:

with breakdown as( --cte instead of temp table or derived table
--unpivot
select pkey, cast(name as varchar(20)) as name, value
from ( select pkey, col1, col2, col3
from pivotTest) p
UNPIVOT
(value for name in (col1, col2, col3)) as unpvt)
select *
from breakdown

returns:

pkey name value
- -- -
a col1 1
a col2 2
a col3 3
b col1 4
b col2 5
b col3 6
c col1 7
c col2 8
c col3 9

Then rotate it with pivot on the pkey values (see --section repivot)

with breakdown as(
--unpivot
select pkey, cast(name as varchar(20)) as name, value
from ( select pkey, col1, col2, col3
from pivotTest) p
UNPIVOT
(value for name in (col1, col2, col3)) as unpvt)

--repivot
select cast(name as varchar(10)), a,b,c
from
(select name, pkey,value
from breakdown) as rotated
PIVOT
(
MAX(value)
for pkey in (a,b,c)) as pvt --this was ,,

name a b c
- - - -
col1 1 4 7
col2 2 5 8
col3 3 6 9

--clean up

drop table pivotTest
go

|||

Here is a Standard SQL Version ;)

--Use sample data provided by Louis

SELECT id, MIN(CASE WHEN P.pkey = 'a' THEN col1 END) AS Col1,
MIN(CASE WHEN P.pkey = 'b' THEN col1 END) AS Col2,
MIN(CASE WHEN P.pkey = 'c' THEN col1 END) AS Col3
FROM
(SELECT 'Col 1' as id,pkey, col1 FROM pivotTest
UNION
SELECT 'Col 2', pkey, col2 FROM pivotTest
UNION
SELECT 'Col 3',pkey, col3 FROM pivotTest) P
GROUP BY id
ORDER BY id

Regards
Roji. P. Thomas

|||You can use UNION ALL in the SELECT statement to avoid the distinct operation which will perform better also.|||

I know you specified "with standard SQL", but it is worth noting that if you do matrix operations using SQL Server 2005, it might be worth investigating if CLR UDTs would be a good fit.

Along with simpler programmability, you would most likely get better performance, too!

|||

I kind of doubt that it would be that much simpler, and I don't even know about better performance (though that one is probably much more likely)

If you want to build it, I would certainly be willing to help out by giving it a run for its money and build a large enough test case to see :)

|||Well, there are several linear algebra packages available for C# on the web - I haven't tried them myself, but they might be worth checking out.

how can I get which columns were updated in trigger on update

Hi,

I'm using sql-2005.

I want to update several columns in different tables whenever an update is happend on specific table. I have a trigger for that which update all the relevant places when it fires.

I want to do this update only if certains columns were changed, otherwise - do anything, to reduce performance.

How Can I know which columns were updated inside the trigger? The tables has many columns and only if 2 of them were changed then I need to update other tables.

Many thanks,

Nira.

use Inserted & Deleted table on your Trigger to compare wheather your action columns are updated or not.

Suppose I have table Table1 with 3 columns, Id, col1, col2, col3

if i need to find from trigger wheather col1 & col3 are updated or not,

Declare @.Col1Flag as Bit, @.Col2Flag as Bit

Select

Col1Flag = Case When Ins.Col1 <> Del.Col1 Then 1 Else 0 End Col1_Updated

,Col2Flag = Case When Ins.Col3 <> Del.Col3 Then 1 Else 0 End Col3_Updated

From Inserted as Ins

Join Deleted as Del on Ins.Id = Del.Id;

|||

Many thanks for you quick response

Though maybe there is some build-in way to do that but that's definitely a simple good way.

Thx

How can I get modified data using timestamp columns

I am putting together an SQL script that is pulling recently modified data from 3 tables and INSERTing that data into another table.

All 3 of my input tables have a timestamp column and I have the previous values for these 3 timestamp columns at the time my SQL script was run previously. So, using the timestamp column values that I had from the previous run of my SQL script and the current timestamp columns that exist in my 3 tables, I am able to derive any recently modified rows.

So, here are my 3 input tables:

Items (has a timestamp column) and has several million rows.

Attributes1 (has a timestamp column) and has a million rows.

Attributes2 (has a timnestamp column) and has a million rows.

The Attributes1 and Attributes2 tables have attributes that describe the items in the Items table. I want to INSERT the Items rows with all of their attributes into a fourth table (that doesn't need a timestamp column).

The kicker is if any attribute changes in the Attributes1 and/or Attributes2 tables, I want to completely resummarize the entire item in the fourth table.

So, I have 3 INSERT/SELECTs in my SQL Script so that I can pickup any combination of modified data in my 3 input tables.

INSERT INTO Table4

.......

SELECT

.....

FROM Items

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Items.TimestampColumn BETWEEN a AND b

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes1

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Attributes1.TimestampColumn BETWEEN c AND d

AND (the Items row is not already in Table4)

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes2

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Attributes2.TimestampColumn BETWEEN e AND f

AND (the Items row is not already in Table4)

This SQL takes a whole long time to run (more than an hour).

I would like to consense my SQL into a single INSERT/SELECT.

Does anybody know of an SQL technique that I haven't thought of...

TIA

Will this work:

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes1

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE (Attributes1.TimestampColumn BETWEEN a AND b

OR Attributes1.TimestampColumn BETWEEN c AND d

OR Attributes1.TimestampColumn BETWEEN e AND f)

AND (the Items row is not already in Table4)

Alternatively, you could try doing a UNION on the select statements to get them into one derived table (if the items table is empty before the first query is run, you can drop the NOT EXISTS as the UNION will remove duplicate rows), and then insert into the table in one go from the derived table (UNION statement). The latter may be quicker if the items table is empty to begin with.