Tracker
HOME    TRACKER    MS SQL Server
 SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.

# Calculating Weekly Averages...

## Hi. I want to be able to calculate weekly averages of our report data. Here is a sample from our EmplyeeTrends table: EmployeeID ReportDate TotalCases 2 1/1/2007 77 2 1/2/2007 63 3 1/1/2007 56All the report data is produced daily. However, my boss wants to be able to see weekly/monthly averages for each employee. Is this possible? Thanks so much for your help!

Related Messages:
Calculating Averages On Integer Fields
Yesterday we ran into a very surprising problem: a colleague had developed a view which calculated a numbero of domain functions on an integer field (sum, count, standard deviation, average).

We were amazed to find that the view always returned an integer value for the average (not for the std dev, though). This was obviously wrong. We even tried changing the function to Sum(fld)/Count(fld) where "fld" was the fieldname, but we still got this strange curious result.

I got around the problem using the CONVERT function to change the integer value into a floating (e.g. Avg(CONVERT(FLOAT(5),fld)), but this is so strange that I'm sure we'll have the same problem again and again.

Now, can anyone tell me why the SQL engine is so incredibly stupid to presume that the average of a series of integer values is necessarily an integer???

Calculating Weekly Totals
I have a table with columns: Date (SmallDateTime), Sales (Decimal)

Is there a SELECT way to compute weekly totals, similar to the GROUP BY clause?

Or is using a WHILE loop a practical way?

Thank you.

SQL Averages
Is there anyway to find an average date value in SQL? it may sound like a strange question. Im on the bench as to whether it is possible

Averages In Matrix
I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1

1
2
3
4
5
6
7
8
9
10
11
12
13
Total

2004
80
60
70
95
70
85
60
90
110
55
90
90
50
1005

2005
5

5
10
20
5

5
5
5
5

10
75

The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1

1
2
3
4
5
6
7
8
9
10
11
12
13
Total
AVG

2004
80
60
70
95
70
85
60
90
110
55
90
90
50
1005
77.31

2005
5
0
5
10
20
5
0
5
5
5
5
0
10
75
5.77

-93.8%
-100.0%
-92.9%
-89.5%

-71.4%
-94.1%
-100.0%
-94.4%
-95.5%
-90.9%
-94.4%
-100.0%
-80.0%

Averages Of Time By Month
Hello all, I have the query below that is returning the difference in time between two scans, I now need to take that time and break it out by month and than break that out by shift and get an average for the 2 shifts for the month. Below is the query with some sample data. Your help is greatly appreciated. THANKS!!!

SELECT LoadID, DATEDIFF(MINUTE, mi, ma) AS DiffMinutes
FROM SELECT LoadID, MIN(ProcessDate) AS mi, MAX(ProcessDate) AS ma
FROM [Scan Detail]
WHERE (ScanSequence = 2 AND LoadSequence = 2) OR
(ScanSequence = 3 AND LoadSequence = 13)

80485
308755
226816
1609613
1477910
319006
364355
399487
218020
115616
339479
2838715
1960913
395079
288289
81946
3965310
84897
60017

Running Sums Of Averages
Hi,

I have a table with a userid and duration field. I need to contrast and individual against a group of users from the table. I can get the users cumulative hours by date in the select clause.

I am having a problem producing the group's sum of averages in the main query.I have tried it many ways and just cant get it to work. I need to be able to display something like this below:

Date GV IV
1/1/07 .45 .37
1/2/07 .56 .45
1/3/07 .68 .59
--
So Far I have this:

SELECT

D1.Date,

GV=ROUND(CAST(SUM(D2.Duration) AS FLOAT) / 3600,2),
IV=(SELECT ROUND(CAST(SUM(DurationAll)AS FLOAT) / 3600,2)

FROM IR_UserDaily WHERE UserID=@UserID AND Date<=D1.Date)

FROM

( SELECT Date,Duration=AVG(DurationAll)

FROM IR_UserDaily

WHERE

UserID IN(SELECT FilterID FROM IR_Filter WHERE ReportID=@RID)AND(Date BETWEEN @LowDate and @HighDate)

GROUP BY Date
) AS D1
INNER JOIN

( SELECT Date,Duration=AVG(DurationAll)

FROM IR_UserDaily

WHERE

UserID IN(SELECT FilterID FROM IR_Filter WHERE ReportID=@RID)AND(Date BETWEEN @LowDate and @HighDate)

GROUP BY Date

) AS D2
ON D2.Date < = D1.Date
WHERE (D1.Date BETWEEN @LowDate and @HighDate) AND (D2.Date BETWEEN @LowDate and @HighDate)
GROUP BY D1.Date,D1.Duration
ORDER BY D1.Date

The problem is that the avg function is returning the avg for all values up to the inner join condition, I think.
Can someone help me here I know it should be simple.

Matrix And Column And Row Based Averages Without Using Avg()

A

A

A

A

B

B

Avg.

Name

1

2

3

4

1

2

xxxxxxxxxxx

12/16

90

100

85

10/8

100

--

xxxxxxxxxxx

20/16

93

5/8

100

--

xxxxxxxxxxx

16/16

89

90

16/8

95

--

Avg.

--

--

--

--

--

--

--

Greetings,

I have a matrix that looks similar to the table above with two row groups and one column group. Does anyone know a way to manually do column and row based averaging without using the avg() function -as the data is not numerical?
Thanks

How To Get Weekly Average?
I have a view call view_jobComponent and it contain the following fields: date_complete, job_tag_no, part_model, component_id, component_description, component_qty
If I want to get the average component_qty in a certain time frame (weekly, monthly, qtrly, bi-annually, annually .etc) in a certain time period.
For example, I want to get the weekly average of component_qty for the year of 2005, how should my sql query like?

Weekly Server Crash
Been trying to send this all week...

-----Original Message-----
From: Driggers, John
To: 'SQL Discussions'
Sent: 10/27/99 9:10 AM
Subject: FW: Weekly server hang

I also see the one below prior to another crash....going through tech
net now...but not seeing anything that reflects the messages below. The
results from searching on "Exception_Access_Violation" I'm not sure
apply in my case...also looks like I have at least two causes of crashes
(how can one interpret the below statements???)

Thanks, John
--------------------

99/10/24 10:38:00.06 spid10 EXCEPTION_ACCESS_VIOLATION raised,
attempting to create symptom dump
99/10/24 10:38:00.06 spid10 Initializing symptom dump and stack dump
facilities
99/10/24 10:38:02.61 spid10 ***BEGIN STACK TRACE***
99/10/24 10:38:02.61 spid10 0x00404CD9 in SQLSERVR.EXE,
rm_ods_handler() + 0x0329
99/10/24 10:38:02.64 spid10 0x00405571 in SQLSERVR.EXE, st_do_enlist()
+ 0x00C1
99/10/24 10:38:02.64 spid10 0x004071CA in SQLSERVR.EXE,
CDTCState::init() + 0x033A
99/10/24 10:38:02.65 spid10 0x005A70A3 in SQLSERVR.EXE,
lddb_fixdbosuid() + 0x0423
99/10/24 10:38:02.68 spid10 0x005A6CC2 in SQLSERVR.EXE,
lddb_fixdbosuid() + 0x0042
99/10/24 10:38:02.68 spid10 0x005963CB in SQLSERVR.EXE, textalloc() +
0x04CB
99/10/24 10:38:02.71 spid10 0x00463F4B in SQLSERVR.EXE, agghaving() +
0x004B
99/10/24 10:38:02.71 spid10 0x00409829 in SQLSERVR.EXE, opencheck() +
0x0089
99/10/24 10:38:02.71 spid10 0x00427B09 in SQLSERVR.EXE,
tbswritecheck() + 0x0969
99/10/24 10:38:02.71 spid10 0x00250FED in opends60.dll
99/10/24 10:38:02.71 spid10 0x0025055B in opends60.dll
99/10/24 10:38:02.71 spid10 0x002414D1 in opends60.dll
99/10/24 10:38:02.71 spid10 0x00241384 in opends60.dll
99/10/24 10:38:02.71 spid10 0x10219D84 in MSVCRT40.dll
99/10/24 10:38:02.71 spid10 0x77F04F3E in KERNEL32.dll
99/10/24 10:38:02.71 spid10 ***END STACK TRACE***

************************************************** **********************
*************

Cindy, nothing in the NT logs but found this in the SQL logs:

99/10/25 09:25:15.45 spid71 EXCEPTION_ACCESS_VIOLATION raised,
attempting to create symptom dump
99/10/25 09:25:15.45 spid71 Initializing symptom dump and stack dump
facilities
99/10/25 09:25:20.45 spid71 ***BEGIN STACK TRACE***
99/10/25 09:25:20.46 spid71 0x00404CD9 in SQLSERVR.EXE,
rm_ods_handler() + 0x0329
99/10/25 09:25:20.52 spid71 0x005725C1 in SQLSERVR.EXE, stuff() +
0x0241
99/10/25 09:25:20.54 spid71 0x0056D35F in SQLSERVR.EXE, ncrid_update()
+ 0x057F
99/10/25 09:25:20.57 spid71 0x0051DD35 in SQLSERVR.EXE, prRESOURCE() +
0x0055
99/10/25 09:25:20.57 spid71 0x00464C65 in SQLSERVR.EXE, genbuiltin() +
0x0445
99/10/25 09:25:20.59 spid71 0x00427B09 in SQLSERVR.EXE,
tbswritecheck() + 0x0969
99/10/25 09:25:20.62 spid71 0x00250FED in opends60.dll
99/10/25 09:25:20.62 spid71 0x0025055B in opends60.dll
99/10/25 09:25:20.62 spid71 0x002414D1 in opends60.dll
99/10/25 09:25:20.62 spid71 0x00241384 in opends60.dll
99/10/25 09:25:20.62 spid71 0x10219D84 in MSVCRT40.dll
99/10/25 09:25:20.62 spid71 0x77F04F3E in KERNEL32.dll
99/10/25 09:25:20.62 spid71 ***END STACK TRACE***

This proceeds my 'crashes', which it looks scary enough to do the trick!

Any idea what could be causing this exception?

Thanks, John

ps. someone else mentioned backup software - we use BackupExec and I
have a sched. task that dumps one of the databases to a network drive 2x
day. But these are running throughout the week...looking over the logs I
really don't see a correlation...(ie. these same processes run on days
that no crash occurs and successfully later in the day that the crashes
do occur (some hours earlier).

-----Original Message-----
From: Gross, Cindy [mailto:CindyGross@hmhs.com]
Sent: Monday, October 25, 1999 2:14 PM
To: SQL 6.5 Discussions
Subject: RE: Weekly server hang

Did you check the SQL Server errorlog (sometimes things are written here
that don't go to the event viewer) and the NT event viewer (application
and
system)?

You could try turning on SQL Trace to see if you can capture a "bad"
query
but depending on how SQL goes down it may not be captured.

If you are auditing successful logons you could take a look to see if
there
is any pattern in who logs in just before SQL restarts.

Any chance someone is actually stopping it on purpose? Or maybe a
program
that is stopping it (maybe a backup system trying to backup the device
files

Cindy Gross
SQL Server MCP
Texas Health Resources
http://members.tripod.com/cindygross/sqlsrvr.htm

> -----Original Message-----
> From:Driggers, John [SMTP:John_Driggers@spspay.com]
> Sent:Monday, October 25, 1999 12:26 PM
> To: SQL 6.5 Discussions
> Subject: Weekly server hang
>
> List-Unsubscribe: <mailto:leave-mssql-13928C@ls.swynk.com>
> List-Software: Lyris Server version 3.0
> List-Subscribe: <mailto:subscribe-mssql@ls.swynk.com>
> List-Owner: <mailto:owner-mssql@ls.swynk.com>
> X-URL: <http://www.swynk.com/sysapps/sql.asp>
> X-List-Host: swynk.com discussion lists <http://www.swynk.com>
> Reply-To: "SQL 6.5 Discussions" <mssql@ls.swynk.com>
> X-Message-Id: <06F417B00B8CD1119BA400008322DF6D03515E78@spsgex01>
> Sender: bounce-mssql-14964@ls.swynk.com
> Precedence: bulk
>
> This one is bugging the tar out of me. Running SQL 6.5 sp5a, NT4 sp4
on
> the
> server. Either on the weekend or Mon. mornings (happened all 3 days
this
> past week) the SQL service stops on the server. This is during low
usage
> times. The box is a DELL 4300 dual 450 w 512 RAM (250 dedicated to
SQL).
> All
> other services on the server are ok, except for SQL.
>
> I'm thinking maybe a bad query hitting the server (I've seen this
happen
> before) but the programmers claim there is nothing special about these
> time
> periods that something "unique" would be happening. After I restart
the
> server it may not happen until the next week (this past weekend being
an
> exception). I thought maybe I had a memory leak but running perf.
monitor
> before a crash once revealed 99+% data cache, available proced. crash,
low
> CPU usage, low swapping....anything else I could check?
>
> Maybe reinstalling the sp5a? Any suggestions on things to try would be
> most
> appreciated...
>
> Thanks, John
>
>
> ------
> FAQ: http://www.swynk.com/faq/sql/sqlserverfaq.asp
> Please post SQL Server 7.0 questions to the SQL 7 list
> http://ls.swynk.com for list server signup/maint options
> You are subscribed as cindygross@hmhs.com
> Archives: http://www.swynk.com/sitesearch/search.asp
> To unsubscribe send a blank email to leave-mssql-13928C@ls.swynk.com

------
FAQ: http://www.swynk.com/faq/sql/sqlserverfaq.asp
Please post SQL Server 7.0 questions to the SQL 7 list
http://ls.swynk.com for list server signup/maint options
You are subscribed as John_Driggers@spspay.com
Archives: http://www.swynk.com/sitesearch/search.asp
To unsubscribe send a blank email to leave-mssql-13928C@ls.swynk.com

Should You Do Scheduled Weekly Reboots?
We do a 4:00AM weekly reboot of every SQL 7.0 server on NT4 scheduled as a SQL Agent job using the os command "shutdown servername /l /c /y /r". The problem is some of the SQL servers recognize the NT shutdown command and gracefully exit out of SQL first with the SQL log entry "SQL Server terminating because of system shutdown", then closes all databases files. Most of the other SQL server do not list any SQL log messages and sometimes list startup error messages indicating a dirty shutdown.

Does anyone else reboot their SQL servers with a scheduled job? If so, how do you perform this task cleanly?

Thanks,
D

Weekly Select Statement
Hi.

I need some help. Does anyone know how to create a select statetement that will generate a list of records that have been timestamped (datetime) on the week of the system date (getdate())? Assuming that Monday is the start of the week and Sunday is the lastday of the week.

Need Weekly Wise Data
Hi,

Need to write a query which would select data based on weekly wise based on the column Date.And week starts from Monday.
Hence would reqiure a function or a query which would select only those records which would fall under respective Weeks.

Help!!!

Regards,
Navneet

Daily/Weekly Checks
What daily/weekly checks do you guys currently perform on your servers and databases?

I recently ran across with an article from SQLServerCentral that listed a couple of daily checks that I'm thinking about implementing on my environment, and some of them are:
DB Missing Recent Backup - Report
DB Missing Recent Log Backup - Report
Drives Low on Disk Space - Report
Error Log Messages Report - Report
Instance Recently Restarted - Report
Job Failures - Report
Large Databases Log File - Report

Verify is SQL Agent Service is running
Check Disk Space Available

Since I'm going to spend some time on this, I was wondering if there's anything else that you guys have in place or any other 'nice to have' that you guys also might have, so I don't leave anything behind...

Thanks!

---
http://www.ssisdude.blogspot.com/

Getting Count On Weekly Basis
Hi All
I am using following code for my application
SELECT
convert(varchar(10),acr._eventTime,120) [Date],
sum(case r.Severity WHEN 'Warning' THEN 1 end)[Warning],
isnull(sum(case r.Severity WHEN 'OK' THEN 1 end),0)[OK],
FROM ActiveCheckIssueResult r
GROUP BY convert(varchar(10),acr._eventTime,120)

it gives me Result as,

Date warning OK
--------- --------- -----
1/1/1008 1 1
2/1/2008 0 2

i want count on weekly basis
Could any one help me?

Reg: Weekly - Monthly Report
Hi All,

I am Maran. Am facing the problem to retrieve the following format of output using the sql query. Is it possible 2 solve this.. I tried this, but i am unable to.

Input values:

Start Date: 2/17/2008
End Date : 5/8/2008

Output Format:

2/17/08 - 2/29/08 (Partial Month) 12
3/1/08 - 3/31/08 (Full month) 0
4/1/08 - 4/30/08 (Full month) 22
5/1/08 - 5/8/08 (Full month) 10

I want the above format of the monthly report. I really could use some help on this. thanks.

~ Maran

Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028

Trying To Call A Function On A Weekly Timer
I'm not sure this is the place for this question, but not sure where else to go.  I've written asp.net  code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?

Sp_add_jobschedule Puzzle For Special Weekly Job....
Hi:
@freq_type = 8 for weekly @freq_interval = 1 for Sunday.

I need to script to create a job which will run every Sunday 6 pm, to backup one prod database to a new backup device. EX. database name is DBTest, and the 7/20/2003 (Sunday) running result should be backup_DBTest_2003_07_20.bak.
and next Sunday's should be backup_DBTest_2003_07_27.bak.
(if this way, the backup device will be created at the begining of the job running...)

this way the backup would not be overwritten. Is there a way to dynamically generate 54 weeks job script from this coming Sunday?

Or to make the backup result as backup_DBTest_2003_week27.bak, backup_DBTest_2003_week28.bak....

thanks
David

Display Weekly , Monthly Report
hello friends!

I want to display the reports in weekly format suppose

today is sept 27 2006, so i know from datepart(weekday,..) its value is 4 and end of this week is sept 30 2006 and again next week will start like that....also search should be monthly...

my report looks like
Weekly Report (09/27 - 10/12)
Week====09/27-09/30======10/01-10/07======10/08-10/12
Sales======50===============100===============80

like that my output looks like

T.I.A

Percentage Calculation For Weekly Total
I have a table which is supposed to show weekly totals for calls by individual categories for a month.

Hence i have week numbers as 24 25 26 27 and 28 for a particular month which can be expanded to show individual days of the week as Columns

As rows I have individual categories as Data restore, Metrics, etc.

I need to calculate percentage vaues for these rows.

eg. Data restore is what percentage of the total for all categories for a particular day when the column is expanded and what percentage of numbers for teh total week when the column is collapsed.

I am unable to calculate this percentage.

My regular code when I have to calculate percentage for a single week when the day numbers are distinct does not work here.

CAST(CAST(SUM(TakenInSLAContQueue) AS FLOAT)/CAST(SUM(TakenInSLAContQueue+TakenNotInSLAContQueue+QueueEscalated+AbnInSLAContQueue+AbnNotInSLAContQueue+Other+Voicemail+TransferredExternal+ExpiredDeleted+CompletedInQueue) AS FLOAT)*100 AS DECIMAL(10,2)) AS Percent_Total

Looking forward to suggestions,

thanks,

Kiran

Aggregate Daily Reports Into Weekly, Etc.
Dear Forum Community,

I am new to this forum (actually to forums in general).  I apologize for the long post, but I feel that someone must have done all this before and perhaps there are better approaches, so I felt I had to explain my objectives.

I have written a Daily(date) report for all the pertinent data in our production database.   We want this data to persist for two years and be easily accessible.  We also want other reports to use this data as a datasource rather than the production data because the production database is periodically purged of old data that may be of interest to these reports.  So I am using SSRS not only as a reporting tool but also a sort of historical database.

To persist the daily report I added a Yesterday report that includes the Daily(date) as a subreport.  The Yesterday report uses yesterday's date and has no paramters so it can be set up to run as a snapshot and be stored in the history.  Daily(date) is set to use the cache which expires after 7 reports.  So far so good.

I have now created a Weekly(weeknumber) report that includes 7 Daily(date) subreports and a LastWeek report that is schedule to run weekly as a snapshot and is stored in the history.  I am hoping that because the Daily(date) reports have already been run by the Yesterday report, their datasets will still be in the cache and this will not cause a refresh from the production database.  Am I right?

Basically, I want to keep access to the production database to a minimum, not store (much) duplicate data in the Report Server database and yet still have quick and easy access to the data going back 2 years even though it has long since been purged from the production database.

I considered to use the 7 daily reports as a datasources for the weekly report so that I could aggregate the data each week and expire the daily reports (and again for months, quarters and years).  However, I was unable to figure out how to use a report as a datasource.

My questions come down to these...

Will the approach I am pursuing work?

Is it the best approach?

If a report includes subreports that have already been run individually with the same parameters, will the cached subreport be used.

How do you use an existing reports as datacsources for an aggregated report?
I look forward to hearing your suggestions.

Dave

Move Staging Data To Weekly Partition
I have a process that loads about 500,000 records on a daily basis and normally takes somewhere in the neighborhood of 5-10 minutes to complete. First I use a SSIS Package to load the data into a staging table, and then call a Stored Procedure that determines which weekly table the data belongs in, and executes dynamic SQL to move the data from the stage table to the final table. It's working pretty well except that the Monday load (Sunday's data) is taking around 10 hours to run, instead of the normal 10 minutes. The only difference I see is that when it is copying data from the stage table, Monday's load will be adding the first rows to the week's partition. I have one clustered index on the table, and data from the staging table should be in the same order. There is also a Non-Clustered index on the ID field that is added as part of the insert. Does anyone have any suggestions for things that I can look at? It makes no sense to me, why an empty table would be 60 times slower...

Purge Records From Table In A Weekly Schedule
Hello all,

I hope someone can help me with a big problem... I'm using Citrix Resource Management Services with a SQL 2000 database. Their are 15 citrix servers which are all reporting to the SQL database.

The database is expanding very quickly and is becoming slower and slower.

My question is: I want to schedule a purge of old records on a friday afternoon, like this:

WEEK 1 - MON / FRI
WEEK 2 - MON / FRI (Friday's purge records week1)
WEEK 3 - MON / FRI (Friday's purge records week2)
etc...

Is this possible? if yes how do i do this !??!

Thank you very much for any info!!

Daan

How To Create Weekly Partitioned Tables Automatically
Hi,

I need help in looking at sample script to perform automatic creation for weekly partitioned tables and also update the partitioned view accordingly.

Any helps are very much appreciated!

Thanks,
G

Transaction Report By Weekly----Plz Help To Write Query

Hi Guys,
I am generating Transaction Activity report,which should get data by weekly.Report shold look like this.

W1
W2
W3
W4
W5
W6

OKC
79
38
50
76
35
47

NFL
0
0
45
43
33
28

LA

5

12

10

0

0

10

Total
79
38
95
119
68
75

Iam passing 3 parameters @startdate,@enddate,@Market. when i select one Market(OKC/NFL/LA), report generating properly, but when i passing 3 markets values(OKC,NFL,LA), iam getting wrong report,report format is not correct.I used Cross tab for generating this report.Result lam getting like this.

W37
W38
W39
W40
W41
W42
W43
W44
W45

OKC
80

OKC

38

OKC

95

OKC

119

OKC

68

OKC

75

OKC

74

OKC

70

OKC

59

OKC

OKC

LA

And i wrote query like this,

select m.Market_name as Market,'W'+datename(ww,ut.creation_date) as Week,count(ut.transaction_id) as Count
from POS.DSC_TRANSACTION_STATUS_VL ts inner join POS.DSC_USER_TRANSACTION ut
on ts.transaction_status=ut.transaction_status inner join POS.RETAIL_LOCATION rl
on ut.rl_number=rl.rl_number inner join POS.BILLING_MARKETS bm
on rl.bm_code=bm.bm_code inner join dbo.Market m
on bm.market_id=m.market_id
where (ut.creation_date between @startdate and @enddate) and m.market_name IN(@Market)
group by m.Market_name,ut.creation_date
order by m.Market_name desc

Could you please some one help me to get this correctly.

Thanks
San

DELETING 100 Million From A Table Weekly SQl SERVER 2000
DELETING 100 million from a table weekly SQl SERVER 2000Hi AllWe have a table in SQL SERVER 2000 which has about 250 million recordsand this will be growing by 100 million every week. At a time the tableshould contain just 13 weeks of data. when the 14th week data needs tobe loaded the first week's data has to be deleted.And this deletes 100 million every week, since the delete is taking lotof transaction log space the job is not successful.Can you please help with what are the approaches we can take to fixthis problem?Performance and transaction log are the issues we are facing. We trieddeletion in steps too but that also is taking time. What are thedifferent ways we can address this quickly.Please reply at the earliest.ThanksHarish

Generate Reports From Database Information And Email It Weekly
Hello there,

Im alittle stuck

what im trying to find out is:
MS SQL 2000

1. How to Generate Reports from selected Database information

2. then email that report weekly.

but is there away to create the reports directly in Enterprise manager?

any pointers would be great

Update Data Of LocalDB From Remote DB Weekly Basis
Hi All

Can any one solve my problem?

Requirements:
We have Our Local DataBase(SQL Server) Guess eg. DBLocal
We have a requirements to update this DBLocal Table Data with Other Database DBRemote(SQL Server) table data.
This Task is Schedule in a Week. Means We needs to update this DBLocal Table Every Week from DBRemote Data.

Both Side we have only one table. Means Source is One Table and Destination is Table One.

Right Now I have a connection string for DBRemote(SQL Server) and SQL Statement for getting Data from DBRemote(SQL Server).

can any one tell me what I need to do for achive this requirement?

please provide me link also; from there i can get enough information for my requirements.

Regards

How To Write A Stored Procedure To Report Weekly Sales For The Last 5 Weeks
Hi, Is there a way to write a stored procedure to get weekly report for 5 weeks?I currently use a stored procedure with 5 select statement to get the result for each week, but I was wondering it there is a way to do that with only one statementthanks

Select Daily, Monthly, Weekly, Quarterly And Yearly Values For Graph Report

Hi

I am very new to analysis services and using MDX.

I want to select data from a cube using an MDX statement and show the data on a graph report.

I want to select the daily, weekly, monthly and quarterly descriptions all in one column to make it easy to represent it on the report.

Then set the 'Date' Column to the x-axis and the Value column to the y-axis.

The user also must have the option to not show certain periods (Switch of daily and weekly)

My MDX works when I select from the SQL Management Studio but as soon as I copy the MDX over to the SSRS Report Designer is splits the daily, weekly, monthly, quarterly and yearly values into seperate columns which makes it very difficult to report on.

----
Code

SELECT NON EMPTY { ([Measures].[ValueAfterLogic])} ON COLUMNS,

NON EMPTY { [KPI Values].[KPI Name].[KPI Name].ALLMEMBERS * ORDER(

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Day Of Month] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Week Of Year Name] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Month] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Quarter Of Year Name] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[YEAR] ELSE NULL END,

[Measures].[ValueAfterLogic],DESC)

}

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM

(SELECT ( {[KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}] } ) ON COLUMNS

FROM [Workplace])

WHERE ( [KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}])

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The case statement will take paramter values when finished

----------------end of code portion

Is this possible or is it suppose to 'split' the columns when moving to SSRS.

Dev environment - SQL 2008 Feb CTP, VS 2008

How To Display Data At Bottom Of Report By Daily, Weekly, Monthly, SYTD Using Ssrs Report

i have  student billbale information assume what ever data it. i need to to dispaly total amount for the student at

Bottom Of Report By Daily, Weekly, Monthly, SYTD . take any example, i want to know formula.

Jacks v

The Multi-part Identifier &"Weekly.Tester_ID&" Could
Hi, I used the following query to insert data into Mon_Day column when it is Monday morning from 7am to 6pm where the data should match the respective TesterID. I received "The multi-part identifier "Weekly.Tester_ID" could not be bound." error when i executed. Please advice..

declare @Weekday bit, @hour int
select @Weekday = case datepart(dw,getdate())
when 1 then 1 when 7 then 1 else 0 end ,@hour= datepart(hh,getdate())

if (@Weekday=2 and @hour between 7 and 18)
begin

Delete from Weekly --- Delete the old data

Insert Into Weekly(Mon_Day) --- Insert new data into Mon_Day column
SELECT EngTime FROM ALD --- from column EngTime from table ALD
where Weekly.Tester_ID = ALD.TesterID ---where TesterID of ALD matches TesterID of Weekly

end

Weekly Update Part Of Database With Data From Original Database
Hi!

I have an original database that I want to copy once to another database. Then I want to update the data weekly with the data of the original database. I dont change any tables or columns in that part of the database. Just some tables more in the new database (than the tables from the original database) with some references to the tables that have to be updated weekly.
How can I do this? (if you know what I mean... it's a sort of a datawarehouse where different sources come together.. that part that represents the original database is just a part of the datawarehouse. That part is exactly the same structure as the original database.)

Thanks,

Sandra

T-SQL And Calculating A Sum
I want to sum up a column, but only summing up the top 25 quantites and then I want to subtract the sum of all quantities from the total of the top 25...how would this be done with t-sql?

Calculating PK
SQL7

I am interested in your opinion on the creation of primary keys in a table - simple ones that is. It would be the replacement for an Identity column.

Some say calculate the value off a lookup table, others say use an external object like c++ dll reading/incrementing from a flat file.

Using an identity column reduces portability of the table.

Thoughts ?

Craig

Help !!!calculating Age
Hi,
What the best way to calculate the age given two datetimes.
I tried using the DATEDIFF(yyy,startdate,enddate) but that seems to only compare the year and not look at the specific day.

I am looking for something that would return the following output

birthday= 12/6/1973
EndDate = 11/30/2001
then the age should be 27

If birthday = 12/6/1973
enddate = 12/11/1973
then the age should be 28

Zoey

Calculating RFM

I'm in the process of developing a Kimball-method CRM data warehouse and I'd like to have Recency, Frequency, Monetary "scores" at the customer level.  Am I better off creating these in the relational database and feeding them to SSAS or have SSAS do the aggregations and assign the "scores"?

Also, has anyone located any SSIS transform scripts that would calculated RFM scores?  This is probably a pretty easy task in VB.net but I haven't gone down that road yet.

Steve

Calculating The 3rd Value By The First 2's Selection
Hi,
First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this.
Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later.
How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me.
Skills Table

CatID
SkillID
Skills
SL_ID

1
4
Skill 1
3

2
5
Skill 2
2

2
6
Skill 3
2

3
7
Skill 4
4

3
8
Skill 5
4

4
9
Skill 6
NULL

4
10
Skill 7
NULL

5
11
Skill 8
3

6
12
Skill 9
3

7
13
Skill 10
4

8
14
Skill 11
1

9
15
Skill 12
3

9
16
Skill 13
3

10
17
Skill 14
6

11
18
Skill 15
1
Skills Catagory

CATID
Org
Skillcat

1
Org1
Skill Category 1

2
Org2
Skill Category 2

3
Org3
Skill Category 3

4
Org4
Skill Category 4

5
Org5
Skill Category 5

6
Org6
Skill Category 6

7
Org7
Skill Category 7

8
Org8
Skill Category 8

9
Org9
Skill Category 9
Service Line Table

SL_ID
Service_line

1
Service Line 1

2
Service Line 2

3
Service Line 3

4
Service Line 4

5
Service Line 5

6
Service Line 6

7
Service Line 7

Dash

Calculating ID Numbers
I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into  the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this?  Thanks for any help you provide.

Calculating Ages
When users register with my site they give me their DoB, how can I work out an age from it. I was thinkin about doing it from the year, but that isnt very accurate. Can anyone help? thanks si! ps Id only want to work out how many years old they are. si!

Calculating The Sum Of Columns...
It may be simple question, but how can i get the sum of column? with conditions like getting the some of clicks one user send and so...

Calculating Dates
I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.

the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.

For Example

If the Finalsuit is 12/01/2000
then the renewal would be 12/31/2002
the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?

Can someone out there help please

Help With Calculating Dates.
Hi,

I'm fairly new to MS SQL, but I'm trying to query the time elapse between two dates. Example below...

Select
dbo.res.book_date,
dbo.res.arrive_date,
Cast (dbo.translog.systemdate as datetime)
From
dbo.res
Where
dbo.res.resid > '500'

The third line is where I need help with a statement. I need the elapse time. Ex. if the book date is 20050820100000 and the arrive date is 20050820120000, I would like the third Select statement to return 2:00 hours or something similar. Thanks.

Calculating Time
Hello,
I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!

Ordernum OrderCreate OrderShipDate TimeZ
67890 5/5/2005 11:575/6/2005 15:33 EST
35789 5/5/2005 13:575/5/2005 14:19 MST
44465 5/5/2005 13:58 5/5/2005 21:58 CST
87654 5/5/2005 18:00 5/7/2005 21:58 PST

Calculating Avg Rowsize In My Db...
Hi,

I'm using SQL Server 7.0 SP3.

I need to calculate the average size of a record in my database to assist in estimating growth.

Is there a quick and/or easy way of doing this?

Darrin

Calculating Median In SQL
Hi All,
I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this;

ServerName | ExecTime
-----------------------
server1 | 0.07
server2 | 0.17
server1 | 0.27
server1 | 0.37
server2 | 0.47
server1 | 0.57
server1 | 0.67
server2 | 0.77

My SQL below gives me

ServerName | ExecTime
-----------------------
server1 | 0.37

Where as i want

ServerName | ExecTime
-----------------------
server1 | 0.37
server2 | 0.47

Here is my SQL, hope someone can modify it and thanks in advance.

Code:

SELECT DISTINCT instance, exec_time AS median
FROM (SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time) sub_a
ORDER BY 1 DESC) sub_1
UNION ALL
SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time DESC) sub_b
ORDER BY 1) sub_2)

Calculating Quartiles
I am trying to make a query that will calculate min, max, median, and the first and third quartiles as well as the average for the number of days that a call assignment is open. I need these returns to be grouped according to team name. I can find the min max and average, but I have no idea how to go about finding the quartiles or the median, my code is below.

Code:

SELECT P.PrimaryTeamName, AVG(CONVERT([INT], CONVERT(datetime, C.ClosedDate + ' ' + C.ClosedTime, 120) - CONVERT(datetime,
C.RecvdDate + ' ' + C.RecvdTime, 120), 1)) AS Average, MIN(CONVERT([INT], CONVERT(datetime, C.ClosedDate + ' ' + C.ClosedTime, 120)
- CONVERT(datetime, C.RecvdDate + ' ' + C.RecvdTime, 120), 1)) AS Minimum, MAX(CONVERT([INT], CONVERT(datetime,
C.ClosedDate + ' ' + C.ClosedTime, 120) - CONVERT(datetime, C.RecvdDate + ' ' + C.RecvdTime, 120), 1)) AS Maximum
FROM HEAT.dbo.CallLog C, HEAT.dbo.Profile P
WHERE C.CustID = P.CustID AND (C.CallStatus = 'Closed') AND (P.CustType <> 'services') AND (P.PrimarySupportGroupID = 'ATS') AND (C.ClosedDate <> '') AND
(CONVERT([INT], CONVERT(datetime, C.ClosedDate + ' ' + C.ClosedTime, 120) - CONVERT(datetime, C.RecvdDate + ' ' + C.RecvdTime, 120), 1) >= 0)
GROUP BY P.PrimaryTeamName

I need these results for a box plot chart that I am generating. Any information that anyone could give me would be extremely helpful and I would really appreciate it.

thank you,
court

Calculating Row Size
Hi, If anyone could exlain these calculations (it's about calculating row size):

null_bitmap=2+((number_of_columns+7)/8) ?
variable_size=2+(num_variable_columns*2)+max_varchar_size ?

Calculating A Percentage
My site has members. Each member has a certain goal, an amount that they are hoping to raise. Here's my tables:

---------------------
Members
---------------------
ID | Goal
---------------------

---------------------
Donors
---------------------
Amount | MemberID
---------------------

I'm trying to get the percentage of money raised compared to their goal. Basically 100*(SUM(Amount)/Goal). Here's my stored procedure:

CREATE PROCEDURE sproc_GetGoalPercentage
(
@memberid int
)
AS
SELECT(SUM(D.Amount)/M.Goal)*100
FROMMembers AS M INNER JOIN Donors AS D
ON M.ID = D.MemberID
WHEREM.ID = @memberid
GROUP BY M.Goal
RETURN

All it does is return 0.