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!



ADVERTISEMENT

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???

View Replies View Related

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.

View Replies View Related

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

View Replies View Related

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%
 


Thanks in Advance.

View Replies View Related

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)
GROUP BY LoadID) d

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

View Replies View Related

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.

View Replies View Related

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

View Replies View Related

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?

View Replies View Related

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
instead of the dumps)?

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

View Replies View Related

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

View Replies View Related

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.

Thanks in advance

View Replies View Related

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

View Replies View Related

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

I already have in place:
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/

View Replies View Related

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?

View Replies View Related

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

View Replies View Related

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? 

View Replies View Related

Sp_add_jobschedule Puzzle For Special Weekly Job....

Hi:
in sp_add_jobschedule
@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

View Replies View Related

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

View Replies View Related

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

 

 

 

View Replies View Related

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

View Replies View Related

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...

View Replies View Related

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

View Replies View Related

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

View Replies View Related

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 in advance
 
 
Thanks
San
 
 

View Replies View Related

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

View Replies View Related

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.

Ive heard about Visual Studio .NET 2003: Business Intelligence Projects

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

any pointers would be great

View Replies View Related

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.
 
Thanks in Advance
 
Regards
 

View Replies View Related

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

View Replies View Related

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.

 

 

Thans in advance

Dev environment - SQL 2008 Feb CTP, VS 2008

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved