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.
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
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}])
Currently, I have a report that takes two parameters: StartDate and EndDate.
I would like to schedule the report to run on a Daily, Weekly, Monthly or Yearly basis, but this doesn't work too well with StartDate and EndDate because the parameter is static. What is the most elegant way to implement this change?
I have a query that will generate records monthly based on the number of months that i calculate between two date feilds for a given requestid. How can i use the same query to generate records for weekly and bi weekly based on the receiveddate field that i use in the subtraction for calculating the number of months.
Also when inserting i have been adding a month for every record as i was generating monthly and now i would have to add week and 2 weeks to the receiveddate
SET NOCOUNT ON GO declare @num_of_times int declare @count int declare @frequency varchar(10) declare @num_of_times1 int
I m Maran. I am trying to write a SQL Query to retrieve the following report format. But I'm not sure how to go about it.
Input values:
Starting Date: 09/14/2007 End Date: 12/06/2007
Monthly Report :
Start Date - End Date - Number of companies 09/14/2007 - 09/30/2007 1 10/01/2007 - 10/31/2007 0 11/01/2007 - 11/30/2007 4 12/01/2007 - 12/06/2007 0
Please its very urgent, Plz do the needful help. Actually this same report format i was posted already and got some methodology, but its not satisfied my requirements :( :(
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.
I have created a database table in MSSQL 2000 like this
[empcode] [leave_date] [type] [reason] 100 2008-12-29 00:00:00.000 T Tour 100 2008-12-30 00:00:00.000 T Tour 101 2008-12-31 00:00:00.000 CL Casual Leave 102 2009-01-01 00:00:00.000 R Restricted holiday 100 2009-01-02 00:00:00.000 T Tour
This table contains only leave details.... but i need to create monthly attendance report such as below
empcode 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 ............. 100 P P P P S P T CL P P P S P P T ............ 101 P T R R S R R T CL P P S P P P.............. 102 P P P P S P P P P T T S CL P P P............
I would need to add a monthly based parameter to my reports. In my actual scenario i have 2 datetime parameters : "start date" - "end date", that denotes the time interval in days. Is there any standard way to customize these parameters to set an interval between months? In other words, i would need 2 parameters like these : "start month" (eg. august 2005) - "end month" (eg. march 2006), with the report filtering data among this given interval of months.
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
Hi Guys, I need some help and suggestion to rewrite one of my screens (using ASP.NET) which is using stored procedure. The processing on this screen is taking more than 3 minutes (which i know is totaly unacceptable). I am making use of cursors within the stored procedure (SQL Server 2005). I really intend to get rid of cursors as they have their performance hit. I have been told to rewrite this screen (or the stored procedure) so i need some help for SQL Gurus. Following are the details: 1. This is a Monthly Employee Attendance Report on a day by day basis for any given month (maximum 31 days in a month) 2. The values (for each day) have to be computed at runtime and not stored. e.g. Since an employee may have signed in/out several times in a day 3. There are around 500 employees data im dealing with 4. The user will select any given department and employee's data for the respective department has to be displayed for any given month 5. If the user selects [All Department], the entire 500 employees have to be displayed on the screen 6. This report will look like an excel report on the screen i.e. Employee's basic info and record of 31 days (maximum days in a month) are displayed in one row for each employee 7. This report involves are 7-8 tables. 7 tables are for employees basic info whereas one table has the attendance record Kindly give me your suggestion on writing the SQL stored procedure. I cannot use any other option such as a real Excel Sheet or anything. I need suggestion on how to write this monthly report. By the way, we dont intend to Cache the data since the report can be viewed at anytime of the day, so fresh data is required everytime. Also the data for 500 employees may be too much to be cached. Also in the attendance table, we are dealing with approximately half a million attendance records. Thanks and waiting for your suggestions...
but instead putting 31, I'd like to pull the totaldays from the actual_date using the Extract function so I try this
SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(to_date('05/01/13','dd/mm/rr'))) as AVGPERDAY, dd.month_number FROM sales_fact sf, date_dim dd WHERE dd.date_id = sf.date_id AND dd.month_number = 1 GROUP BY dd.month_number;
The result is nice, but now when I change the date with the dd.actual_date it gives error
SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(dd.actual_date)) as AVGPERDAY, dd.month_number FROM sales_fact sf, date_dim dd WHERE dd.date_id = sf.date_id AND dd.month_number = 1 GROUP BY dd.month_number; Error at Command Line : 1 Column : 53
Error report - SQL Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression"
I am using SQL Server 2005 with Reporting Services (Using the Visual side - not direct code)
I am having problems understanding the dates. eg where to put them,
I want a report that runs on the 1st day of the month for the previous month. I know you can set up something in subscriptions but then how do I get my report header to say from .......to...............
I have been through the AW reports but can't see what I need.
Happy if someone wants to direct me to somewhere that has date examples.
I have a daily report which each morning generates monthly information for the current month which was implemented in December. Everything was working correctly untill January 1st. On the 1st the report generated blank since it was suppose to generate 1-31 Dec but but the currently month was Jan, so it failed. How do I program it so if it is the 1st of a month generates the previous month but still would generate current month but while in the current month? Any help is appreciated.
SELECT GETDATE() - 1 AS rptdate, Errors.WTG_ID, lookup.Phase, Errors.STATUS_TYPE, Errors.STATUS_CODE, STATUS_CODES.STATUS_DEF, Errors.TIME_STAMP, Errors.ANSI_TIME, lookup.WTG_TYPE, Errors.POSITION FROM Errors INNER JOIN lookup ON Errors.WTG_ID = lookup.WTG_id RIGHT OUTER JOIN STATUS_CODES ON Errors.STATUS_CODE = STATUS_CODES.STATUS_CODE AND lookup.WTG_TYPE = STATUS_CODES.WTG_TYPE WHERE (STATUS_CODES.STATUS_DEF IS NOT NULL) AND (Errors.TIME_STAMP BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)) ORDER BY Errors.WTG_ID, Errors.TIME_STAMP, position
I need to calculate the success rate of our OS Patch deployments. the data from system is stored in SQL with corresponding states (installed, missing, ...)
I would need to provide monthly report that shows how successful the deployment was for particular patchgroup. I have the following 2 dummy tables (just used as example)
table5 ==> Table containing patch groups + patches table6 ==> Table with machines names, patches and patch state
select * from table5 pgrouppatch GROUP1PATCH1 GROUP1PATCH2 GROUP1PATCH3 GROUP1PATCH4
[Code] ....
Result would be pgroup install missing group1 80% 20% group2 50% 50% group3 55% 45%
Ideally I would like to do this in T-SQL but if necessary can also do this in .NET Function. Only mention this but both Patchgroup and machines are dynamic each month can be different.
below are the sql scripts to create tables and populate with data
CREATE TABLE [dbo].[table5]( [pgroup] [varchar](128) NOT NULL, [patch] [nvarchar](128) NOT NULL ) CREATE TABLE [dbo].[table6]( [machinename] [varchar](128) NOT NULL,
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?
-----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
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.
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!
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?
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...
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.
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?
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....
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.
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:
I have a weekly Maintenance Plan Reindex job that has failed because of a deadlock. My question seems simple enough and I'm ashamed to say I ought to know this answer, but here goes: Does the rest of a given job continue after such failures (this one was maybe 3/4 through the log) occur?
