Help! T-SQL, Calling multiple stored procs?

I have to load a webpage table up with values from a database as such:

The Webpage Expects a Final result set of rows with 5 columns each built as below:

For each Row Returned by StoredProc1

Write Columns 1-3 = StoredProc2

Write Column 4 = StoredProc3

Write Column 5 = StoredProc4

Next Row

But I am not sure how to write the conditionals to CALL the stored procs i wrote and loop by a return value from StoredProc1.

Any help is much appreciated!

THANKS!

I would prefer to create separate stored proc with entire logic encapsulated within it, if I were you. Loops are fast and typical in client languages, but in SQL it's better not to be overused.|||So create just one big stored proc to do it all?
|||Yes. And calling stored procs from within stored procs is not so fast.|||

Can you be a little more specific about what each stored proc does? I mean, if it is a set of procs you are writing, then I doubt this is the best way to do it. I would be more inclined to write one stored procedure with one SELECT statement that joins 3 larger sets together (perhaps in temp tables) on the common value from a fourth set.

select *

from QueryFromSP1

outer join (QueryFromSP2) as QueryFromSP2
on QueryFromSP1.key = QueryFromSP2.key

...

The queries could be temp tables, or function calls, depending on the need, but a cursor like you have suggested will probably be a dog in performance (and not one of those really fast dogs either. Think hound dog on a hot Mississippi morning dogs.)

HELP! Stored Procedure Problem

I need to pull distinct records in my SP, but if there are different values in some of the columns it grabs those records too. I tried a nested query but I get an error that it is returning more than one value and not allowed.

I can grab the 1st record for each cust I need like this in a view:

SELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Ad
FROM dbo.PropReportData
GROUP BY vcCustId, vcAdsource
ORDER BY vcCustId

and then Inner Join it in my SP like this:

CREATE PROCEDURE Reports_GetReportData
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT *
FROM dbo.vPrePull INNER JOIN
 dbo.PropReportData ON dbo.vPrePull.MinRecNo = dbo.PropReportData.siEntry
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GO

Now, the problem is this, the parameters are in my stored procedure which is parsed second. So I am not getting the unique data to pull from.

Ultimately what I need to use is below with something inside it that will do what the view above did:

CREATE PROCEDURE Reports_GetReportData2
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Ad,sientry,vcProspectName,
vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,vcComments
FROM dbo.PropReportData
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GROUP BY vcCustId, vcAdsource,sientry,vcProspectName,vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,
vcComments
ORDER BY vcCustId
GO

Got to have this done by C.O.B. Monday or I may not have a job.
Thanks.What you want seems doable, but some more info (structure of the tables, some sample data) would help.

Not that I feel pressured or anything here...|||I'm using it to create reports for my CRM application and I used the Reports Starter Kit as my base. I am displaying Each Ad Source the Prospect called in on and the Prospects Record data in a tabular report. In the footer of each Ad Source it gives a count of the total leads that came in on that Ad Source. The problem is this, when a change is made it creates another record for that prospect with a unique record number; this has to be for history purposes, because I have another report that display all record activity, (that one works). So when I display the Prospect records it has a duplicate, which I can cull out by using vcCustID, MIN(siEntry) this gives me the first record entry for that Prospect, BUT, if one of the fields that I am trying to display was changed (like Visit Date in the example below), another instance of the Prospect is displayed. So for example I have a count of 2 unique leads by Ad Source and they call back and change their Visit Date, it will display a count of 2 leads (which is correct) and display 3 records (wrong), showing that Prospect twice.

Example:
ApartmentGuide.com
Prospect Name.Telephone.Email.DesiredHome.Move-In Date.Status.Visit Date
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/23/2003
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/26/2003
Walker, Johnny 555-645-7895 drunk@.booze.com 1 x 1 10/23/2003 Visit Set 10/23/2003

Total Leads this Ad Source: 2

I am pulling the data from a single table called PropReportData that has just the info I need for reporting. This was necessary because the information necessary to create a report is in 9 different tables and the amount of executes necessary to do the inner joins caused major performance issues and after running an execution plan it just didn't seem feasible to continue in that direction.

The table has vcCustID(Unique), cApartmentSite(used to associate Client to Cust to), siEntry(Unique Record Number), Ad Source, etc.

The last Stored Procedure in my first post is what I need to work, it has the parameters in it I need to display client specific data, which uses cApartmentSite. The value is picked up from the UserLogin and put in session to be used with my parameters and a few other things.

Each Prospect record created by my Marketing Associates for that client has this value inserted in a field in the PropReportData table creating the Client to Prospect relationship. So when the client logs it only pulls their information based on the cApartmentSite value in the tables.

Let me know if you need more info.

Thanks.|||This seems to work:

CREATE PROCEDURE Reports_PainInTheButt
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT DISTINCT
 TOP 100 PERCENT dbo.PropReportData.vcCustId, MIN(DISTINCT dbo.PropReportData.siEntry) AS siEntry,
 MIN(DISTINCT dbo.PropReportData.vcAdsource) AS vcAdsource,
 MIN(DISTINCT dbo.PropReportData.vcProspectName) AS vcProspectName, MIN(DISTINCT dbo.PropReportData.vcPhone) AS vcPhone,
 MIN(DISTINCT dbo.PropReportData.vcEmail) AS vcEmail, MIN(DISTINCT dbo.PropReportData.vcDesiredHome) AS vcDesiredHome,
 MIN(DISTINCT dbo.PropReportData.vcMoveInDate) AS vcMoveInDate, MIN(DISTINCT dbo.PropReportData.vcStatus) AS vcStatus,
 MIN(DISTINCT dbo.PropReportData.vcVisitDate) AS vcVisitDate, MIN(DISTINCT dbo.PropReportData.vcComments) AS vcComments
FROM dbo.PropReportData
WHERE dbo.PropReportData.cApartmentSite = @.cApartmentSite AND dbo.PropReportData.vcAdsource = @.vcAdSource
GROUP BY dbo.PropReportData.vcCustId
ORDER BY dbo.PropReportData.vcCustId
GO

If you see any potential problems with this let me know, it's all I can come up with.

Thanks.

Help! Merge Replication and identity values

Hi,
I have a Merge replication set up between a SQL Server and many (around
150-200) SQL CE databases. I am using identity as primary keys in 2 of the
replicated tables which have a identity seed (set to 1) and range set (set
to 1000 with 10000 as publisher range and 80% threshold). All was going well
until there was a database schema change and I had to rebuild the
publication and re-initialize all subscribers (which was ok).
But after rebuilding the publication and re-initialization all subscribers,
the agent started giving out identity ranges that conflicted with current
values in the database. As some of you have faced similar situation and have
found workarounds. I used a script to manually change the "next seed" value
of the MSrepl_identity_range table to set the "next seed" identity value to
be the max value of the table...basically used UPDATE
distribution..MSrepl_identity_range SET next_seed = max value + range...so
on and so forth. This worked as far as giving each re-initialized
subscribers a new identity range. I have 2 questions regarding this:
* How can I set the publishers range? I manually updated the
MSrepl_identity_range to a higher value than the max value used for updating
the distribution..MSrepl_identity_range using the sql script. What effects
would this have?
* In Check Constraint tab of the replicated table (generating identity
values at subscriber), there is a check constraint value (value is:
[Table1_Col_Id] > 255452 and [Table1_Col_Id] < 400000) which is "Enforced
for Insert and Update" with constraint name like
"repl_identity_range_pub_1CC33D46_49FA_4A34_9722_7 F8D53C0B20A". I had to
uncheck them for the merge agent to be able to add new rows to the server.
Where can I get more info on how this constraint value is generated and how
is it used? Also what is the harm leaving the enforcement of constraint
uncheck?
Can anyone point me to a website where someone has successfully dealt with
this issue without manually setting the ranges?
Please help.
Thanks.
wow! thats a lot of SQL CE databases.
1) to set the identity range on the publisher the correct way to do this is
through the articles property, select the identity range tab. Sounds like
you have already being there. After setting the range on the publisher the
ranges should be parceled out to the Subscribers. As Subscribers come online
they'll get a range assigned to them.
There are instances where the range won't be incremented correctly. For
instance if you have a range size on the publisher of 100 and you update
more than the threshold or range size on the publisher in a batch, the range
adjustment won't be done until the batch is complete. If the batch is more
than 100 records you blow the range and get a constraint error.
In cases like this you have to automatically adjust the identity ranges or
do it manually by adjusting the range table and the corresponding
constraint.
Because of these "limitations" many DBA's elect to use the set it and forget
it approach, where they assign a range to the publisher and subscriber
manually which will not be exceeded in the lifetime of the project/solution.
The dangers of manually making the adjustment is you have to use consistent
values everywhere and you have to adjust the constraint correctly. Other
than that its pretty safe.
The constraint is created when you create the snapshot and adjusted with the
proc sp_MSreseed. This proc is completely undocumented. If you disable the
constraint you may run into problems depending on what is updating your
table. Disableing it for inserts and updates will be harmless if only
replication is making the changes, otherwise you may have problems, if the
identity range is blown and another subscriber/publisher uses it.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Deepak Ramakumar" <dramakumar@.strongtie.com> wrote in message
news:evu$7CnUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I have a Merge replication set up between a SQL Server and many (around
> 150-200) SQL CE databases. I am using identity as primary keys in 2 of the
> replicated tables which have a identity seed (set to 1) and range set (set
> to 1000 with 10000 as publisher range and 80% threshold). All was going
well
> until there was a database schema change and I had to rebuild the
> publication and re-initialize all subscribers (which was ok).
>
> But after rebuilding the publication and re-initialization all
subscribers,
> the agent started giving out identity ranges that conflicted with current
> values in the database. As some of you have faced similar situation and
have
> found workarounds. I used a script to manually change the "next seed"
value
> of the MSrepl_identity_range table to set the "next seed" identity value
to
> be the max value of the table...basically used UPDATE
> distribution..MSrepl_identity_range SET next_seed = max value + range...so
> on and so forth. This worked as far as giving each re-initialized
> subscribers a new identity range. I have 2 questions regarding this:
>
> * How can I set the publishers range? I manually updated the
> MSrepl_identity_range to a higher value than the max value used for
updating
> the distribution..MSrepl_identity_range using the sql script. What effects
> would this have?
> * In Check Constraint tab of the replicated table (generating identity
> values at subscriber), there is a check constraint value (value is:
> [Table1_Col_Id] > 255452 and [Table1_Col_Id] < 400000) which is "Enforced
> for Insert and Update" with constraint name like
> "repl_identity_range_pub_1CC33D46_49FA_4A34_9722_7 F8D53C0B20A". I had to
> uncheck them for the merge agent to be able to add new rows to the server.
> Where can I get more info on how this constraint value is generated and
how
> is it used? Also what is the harm leaving the enforcement of constraint
> uncheck?
>
> Can anyone point me to a website where someone has successfully dealt with
> this issue without manually setting the ranges?
>
> Please help.
> Thanks.
>
>
|||Hilary,
Thanks a lot for your response. Googling on sp_MSreseed gives only one result by Fiach Reid who details the stored procedure itself. I will not be doing any batch inserts so I guess sql should handle the identity ranges automatically, but if it doesn't then I will start seeing conflicts on the server again and may be by that time I could have gathered more info on sp_MSreseed.
Thanks,
Deepak.
Hilary Cotter" <hilaryk@.att.net> wrote in message news:u$aeWOoUEHA.3420@.TK2MSFTNGP12.phx.gbl...

> wow! thats a lot of SQL CE databases.
> 1) to set the identity range on the publisher the correct way to do this is
> through the articles property, select the identity range tab. Sounds like
> you have already being there. After setting the range on the publisher the
> ranges should be parceled out to the Subscribers. As Subscribers come online
> they'll get a range assigned to them.
> There are instances where the range won't be incremented correctly. For
> instance if you have a range size on the publisher of 100 and you update
> more than the threshold or range size on the publisher in a batch, the range
> adjustment won't be done until the batch is complete. If the batch is more
> than 100 records you blow the range and get a constraint error.
> In cases like this you have to automatically adjust the identity ranges or
> do it manually by adjusting the range table and the corresponding
> constraint.
> Because of these "limitations" many DBA's elect to use the set it and forget
> it approach, where they assign a range to the publisher and subscriber
> manually which will not be exceeded in the lifetime of the project/solution.
> The dangers of manually making the adjustment is you have to use consistent
> values everywhere and you have to adjust the constraint correctly. Other
> than that its pretty safe.
> The constraint is created when you create the snapshot and adjusted with the
> proc sp_MSreseed. This proc is completely undocumented. If you disable the
> constraint you may run into problems depending on what is updating your
> table. Disableing it for inserts and updates will be harmless if only
> replication is making the changes, otherwise you may have problems, if the
> identity range is blown and another subscriber/publisher uses it.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Deepak Ramakumar" <dramakumar@.strongtie.com> wrote in message
> news:evu$7CnUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> well
> subscribers,
> have
> value
> to
> updating
> how
>

HELP! Concatinated Values

Hello everyone,

I would really appreciate if someone could help me out with this one.

I need to execute a select statement which returns more than one row but I need the values from all the rows returned in a single string.

For example

SELECT * FROM USERS would produce

ID NAME
1 Jason
2 Mark
3 Whatever

I need the returned value to be a string with 'Jason,Mark,Whatever' as a returned result.

Any thoughts anyone?

RegardsI would look towards using a cursor in a stored procedure to loop through each row and concatenating the values one by one.

I'm no big fan of serverside cursors, even if they have their time and place too, so depending on the environment and application demands I would look into putting such logic in the middle tier or even client side.

Cheers,
Robert|||Originally posted by Rawbat
I would look towards using a cursor in a stored procedure to loop through each row and concatenating the values one by one.

I'm no big fan of serverside cursors, even if they have their time and place too, so depending on the environment and application demands I would look into putting such logic in the middle tier or even client side.

Cheers,
Robert

declare @.name varchar(40),@.result varchar(500)
declare Cursor1 cursor
for
SELECT * FROM USERS
open Cursor1
fetch from Cursor1 into @.name
Set @.result=@.name
while(@.@.fetch_status=0)
begin
set @.result = ','+@.name
fetch from Cursor1 into @.name
end

Help! - BCP / Bulk Copy

I need top update several tables with new values from an external text file. That would notrmally be a no brainer, (even for me) :) But, what is important this time around, is I can NOT fire the triggers, or every user would get notified of a 150,000 updates.

The text file contains 4 fields
KEYNUM|UpdateField1|UpdateField2|Class

Class will be either 1 or 2, if class = 1 then table 1 contains the record, if class = 2 then table 2 contains the record...

Any help, as always is greatly appreciatedHi,

By default BCP does not fire triggers, it will only fire triggers if you use the FIRE_TRIGGERS hint.

Does that change anything or help?|||Originally posted by bmalar
Hi,

By default BCP does not fire triggers, it will only fire triggers if you use the FIRE_TRIGGERS hint.

Does that change anything or help?

That Part I knew (but thanks). How can I use bcp to update selected records? (i.e. update records based on values in the KEYNUM field)|||Originally posted by GregCrossan
That Part I knew (but thanks). How can I use bcp to update selected records? (i.e. update records based on values in the KEYNUM field)

Sorry I didn't read your post correctly.. I thought you were inserting new records.

HELP!

hi!
I have a table who name is table0. its have a values like colum0: 01 colum1
: d01 and other table(this name is table1) have linked values to 01 and
d01. I want to update d01 value in table1, look from table0. I have
32000-35000 value in table0 and table1.So how can I do it?
Thanks,,,,,,
<m_guner18@.hotmail.com> wrote in message news:...
> hi!
> I have a table who name is table0. its have a values like colum0: 01
colum1
> : d01 and other table(this name is table1) have linked values to 01 and
> d01. I want to update d01 value in table1, look from table0. I have
> 32000-35000 value in table0 and table1.So how can I do it?
> Thanks,,,,,,
>
|||update table0
set colum0= 01, colum1= d01
from table0,table1
where table1.column0=table0.column0 and table1.column1=table0.column1
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"m_guner18@.hotmail.com" wrote:

> hi!
> I have a table who name is table0. its have a values like colum0: 01 colum1
> : d01 and other table(this name is table1) have linked values to 01 and
> d01. I want to update d01 value in table1, look from table0. I have
> 32000-35000 value in table0 and table1.So how can I do it?
> Thanks,,,,,,
>
>

Help writing a report query

OK, I have to write a SQL query for someone, for a report. There's financial values involved. There's three tables, essentially. Call them account names, account types, and transactions. I need to return the value of each transaction with the transactions, the sum of the transactions for the account, and the sum of all account transactions across all account types for the account name. I'm not sure how best to do this, I'm thinking of creating a temporary table at the bottom level ( where the actual data is ) and joining against it to do a SUM on the higher levels. To do that, I seem to need to do group by, which then means I need to specify all my non grouped column names, is that right ?

Can you post a bit more info, particularly a small sample set of data to work with and what you want the results to be? I am having a bit of trouble envisioning what you want.

|||

OK, it works something like this:

table ShareInfo

ShareInfoId int

CurrentPrice int

table SharePurchase

SharePurchaseId int

PortfolioId int

ShareInfoId int

SharesPurchased int

PricePerShare int

table SharePortfolio

PortfolioId int

and some other columns for names, etc. So, on the bottom level, I want to list all share purchases within a portfolio, and how their value has changed in each instance to toay. This data will be collapsible on the report, and the row that shows always will show the share name, and the total value of shares purchased, and amount lost/gained. This data is also collapsible, and on the top level, I show the Portfolio name, and the total value/total amount lost or gained on the portfolio to date.

|||

Hi you can use the ROLLUP operator,

Example,
Create table #Shares
(
PortfolioId int,
ShareId int,
NoOfShares int,
TimePurchased varchar(10),
SharesPerPrice int
)

Insert Into #Shares values(1,1,10,'10:00 AM',30);
Insert Into #Shares values(1,2,100,'10:00 AM',5);
Insert Into #Shares values(1,2,50,'11:00 AM',6);
Insert Into #Shares values(1,3,112,'10:00 AM',5);
Insert Into #Shares values(1,4,112,'10:00 AM',5);

Select
PortfolioId
,ShareId
,TimePurchased
,Sum(NoOfShares)
,Sum(SharesPerPrice)
,Sum(NoOfShares * SharesPerPrice)
from
#Shares
Group By PortfolioId, ShareId, TimePurchased With Rollup

OUTPUT:

PortfolioId ShareId TimePurchased NoOfShares SharesPerPrice Totalvalue
-- -- - -- -- --
1 1 10:00 AM 10 30 300
1 1 NULL 10 30 300
1 2 10:00 AM 100 5 500
1 2 11:00 AM 50 6 300
1 2 NULL 150 11 800
1 3 10:00 AM 112 5 560
1 3 NULL 112 5 560
1 4 10:00 AM 112 5 560
1 4 NULL 112 5 560
1 NULL NULL 384 51 2220
NULL NULL NULL 384 51 2220

If TimePurchased is NOT NULL then it is Down Level Data
If TimePurchased is NULL and ShareId is not null then it is One level Collopsed from the Down Level
If TimePurchased is NULL and ShareId is NULL and PortfolioId is not NULL then it is at Portfolio Level

|||

OK - that looks good, but how would I then populate my report from this ? I expected I'd return three tables, one for each level.

Thanks for helping...

|||

Here you can change the query as follow as,

Select
PortfolioId
,ShareId
,TimePurchased
,SUM(NoOfShares) NoOfShares
,SUM(SharesPerPrice) SharesPerPrice
,Sum(NoOfShares * SharesPerPrice) TotalValue
INTO #RESULT
from
Shares
Group By PortfolioId, ShareId, TimePurchased wITH ROLLUP
Select PortfolioId,ShareId,TimePurchased,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is not null

Select PortfolioId,ShareId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NOT NULL

Select PortfolioId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NULL And PortfolioId is Not Null

Help with writing Triggers

I have no experience with Triggers and need help to create 2. My goal is to
generate the average and Stard Devation for 2 values when they are inserted
or updated. In the first field I insert a count that I need to calculate th
e
mean and StdDev based on the values of the past 30 days. The other is the
same with the addition of the past 30 days based on wdays or wends.
I currently am doing this with a batch but it would be simpler to manage if
the rows values were calculated when the values are inserted or updated.
Below are the table design and the batch statement.
-- ========================================
===============================
CREATE TABLE [dbo].[RollingRecordCount] (
[CDATE] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NCOUNT] [bigint] NOT NULL ,
[RCOUNT] [bigint] NOT NULL ,
[IsWday] AS (convert(char(1),case when ((datepart(wday,[CDATE]) = 7
or datepart(wday,[CDATE]) = 1)) then 'N' else 'Y' end)) ,
[MeanNc] [float] NULL ,
[StdDevNc] [float] NULL ,
[MeanRc] [float] NULL ,
[StdDevRc] [float] NULL ,
[InsertModDate] [datetime] NULL
) ON [PRIMARY]
GO
========================================
====================================
======
UPDATE RollingRecordCount
SET MeanNc = (SELECT AVG(NCOUNT)
FROM RollingRecordCount
WHERE CDATE BETWEEN (@.startdate) AND (@.enddate)),
StdDevNc = (SELECT STDEVP(NCOUNT)
FROM RollingRecordCount
WHERE CDATE BETWEEN (@.startdate) AND (@.enddate)),
MeanRc = CASE
WHEN IsWday = 'Y'
THEN (SELECT AVG(RCOUNT)
FROM RollingRecordCount
WHERE ISWday = 'Y' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
ELSE (SELECT AVG(RCOUNT)
FROM RollingRecordCount
WHERE ISWday = 'N' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
END,
StdDevRc = CASE
WHEN IsWday = 'Y'
THEN(SELECT STDEVP(RCOUNT)
FROM RollingRecordCount
WHERE IsWday = 'Y' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
ELSE (SELECT STDEVP(RCOUNT)
FROM RollingRecordCount
WHERE IsWday = 'N' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
END
FROM RollingRecordCount
WHERE CDATE = @.enddateJim Abel (JimAbel@.discussions.microsoft.com) writes:
> I have no experience with Triggers and need help to create 2. My goal
> is to generate the average and Stard Devation for 2 values when they are
> inserted or updated. In the first field I insert a count that I need to
> calculate the mean and StdDev based on the values of the past 30 days.
> The other is the same with the addition of the past 30 days based on
> wdays or wends. I currently am doing this with a batch but it
> would be simpler to manage if the rows values were calculated when the
> values are inserted or updated. Below are the table design and the batch
> statement.
Hm, I'm not that this is good for a trigger. As I understand it,
you want the values to reflect the last 30 days. But what if nothing
happens during a day? The values should still change, shouldn't they?
Of course, it may be a fair assumption that data is inserted everyday.
But how often? Recalculating everytime may be expensive?
(Basically, I say this, because I'm just about to leave, and don't
have the time to compose a trigger right now.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I agree with Erland. A view could be used here. But it really is difficult t
o
be 100% sure without seeing the DDL and some sample data.
ML
http://milambda.blogspot.com/|||Sorry for the lack of details with this request. Here is some more
information. The 2 fields NCOUNT and RCOUNT are inserted once esch day. On
rare ocasions the counts that are entered had been calculated incorrectly at
the datasource and I need to manually edit the particular row and change the
value for one or both counts and then recalculate the means and StdDev of
that row based on the previous 30 days of that rows date. The other message
suggested using a view and that may work as well. I'm just trying to develo
p
something that takes as little management as possible, the goal being that I
need only to enter the NCOUNT and/or the RCOUNT and the mean and StdDev
columns can be autimatically generated without me needing to pull up a batch
script.
"Erland Sommarskog" wrote:

> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Hm, I'm not that this is good for a trigger. As I understand it,
> you want the values to reflect the last 30 days. But what if nothing
> happens during a day? The values should still change, shouldn't they?
> Of course, it may be a fair assumption that data is inserted everyday.
> But how often? Recalculating everytime may be expensive?
> (Basically, I say this, because I'm just about to leave, and don't
> have the time to compose a trigger right now.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||I added some more detail to Erlands message in a reply. I thought that the
script under my 2nd ====== line was the ddl of the existing batch less the
declaration of the Start and en date parameters. the end date is set by
getting the Max date after the dauly insert occurs and the start date is 30
days less. In the case of a manual update to a previously entered row the
end date is the CDATE value and the Start date is 30 days less.
If I am ubderstanding your suggestion to use a view the mean and StdDev
fields would be calculated in the view design and they would not be necessar
y
in the underlyung table, is this chat you're suggesting?
"ML" wrote:

> I agree with Erland. A view could be used here. But it really is difficult
to
> be 100% sure without seeing the DDL and some sample data.
>
> ML
> --
> http://milambda.blogspot.com/|||Exactly. But to be certain we'd have to see more DDL (table definition) and
sample data.
ML
http://milambda.blogspot.com/|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Sorry for the lack of details with this request. Here is some more
> information. The 2 fields NCOUNT and RCOUNT are inserted once esch day.
> On rare ocasions the counts that are entered had been calculated
> incorrectly at the datasource and I need to manually edit the particular
> row and change the value for one or both counts and then recalculate the
> means and StdDev of that row based on the previous 30 days of that rows
> date. The other message suggested using a view and that may work as
> well. I'm just trying to develop something that takes as little
> management as possible, the goal being that I need only to enter the
> NCOUNT and/or the RCOUNT and the mean and StdDev columns can be
> autimatically generated without me needing to pull up a batch script.
If rows are inserted once per day, it makes more sense. I assume then
that CDATE is the primary key in RollingRecordCount? Wittout a primary
key, it gets difficult.
Below is a trigger. Some remarks: I've replaced the sub-selects with
joins to a derived table. This is a proprietary syntax and not very
portable. On the other hand, on SQL Server this syntax usuaally gives
better performance. I did not include the computation of StdDevRc, but
left that as an exercise. :-) You can use MeanRc as a pattern. I also
added 1E0* in some places to force a conversion to float. It's meaningless
to store the means as float, if the result is integer only. (Which it is
if you say AVG(NCOUNT) without any conversion.
CREATE TRIGGER rolling_tri FOR INSERT, UPDATE AS
UPDATE RollingRecordCount
SET MeanNc = R1.MeanNc,
StdDevNc = R1.StdDevNc,
MeanRc = CASE IsWday
WHEN 'Y' THEN R1.MeanWDay
ELSE R1.MeanWEnd
END
FROM RollingRecordCount R
JOIN (SELECT i.CDATE, MeanNc = AVG(1E0 * R.NCOUNT),
STDEVP(1E0 * R.NCOUNT
MeanWDay = SUM(1E0 * R.RCOUNT) /
SUM(CASE R.ISWday WHEN 'Y' THEN 1 ELSE 0 END),
MeanWEnd = SUM(1E0 * R.RCOUNT) /
SUM(CASE R.ISWday WHEN 'N' THEN 1 ELSE 0 END)
FROM inserted i
JOIN RolleingRecordCount ON R.CDATE
BETWEEN i.CDATE - 30 AND i.CDATE
GROUP BY i.CDATE) AS R1 ON R.CDate = R1.CDate
In lieu of sample data, the code is untested.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx