I am trying to make a report and need some help please.
It is a helpdesk database. Jobs are logged, and then closed. Each of
these events is timestamped in the database in the date fields
“DateLogged” and “DateClosed”
I am trying to create a crosstab style report that will show each month
of the year along the top, and then the number of jobs logged and closed
during that month. The problem I am having is that when Crystal Groups
by the month, you have to specify a date field for the grouping. If I
select “DateLogged”, then the crosstab will accurately show all of the
jobs logged for that month, but is not correct for the jobs closed
during that month. The problem is that is counting the number of jobs
that were both logged AND closed during the grouped month.
Can anyone see how such a report is possible?
Furthermore, I would like to be able to calculate how many jobs were
open at the start of the month, as well as at the end of the month.
Thank you for your help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
helloi have to make helpdesk application for teh IT Department of my company,so if anyone can help by supporting me by database schema for HelpDesk application.thank you for the help
The RuleId are the Ids of the different server process that we monitor. The ResultId can be a 1 for Success, or 3 for Failure. Entries are only added during a falure, and the a recovery from the falure. Each entry will be a new record.
I need to get a query that will produce the following. The query will need to retrieve the first success for the RuleId after the failure since there can be many success and failure result in the table:
We do have plenty of information about index usage in DMVs and I was wondering if there was any way for us to tell which of the user-created statistics for table were in use.
I have a Excel cross-tab (multilevel column) report that need to be loaded into a database table. Currently, I am using a Excel macro that convert the columns into rows before loading into the database table. I was thinking whether there is a better way of doing this perhaps in SSIS or using XML.
Hello group.I have an issue, which has bothered me for a while now:I'm wondering why the column statistics, which SQL Server wants me tocreate, if I turn off auto-created statistics, are so important to theoptimizer?Example: from Northwind (with auto create stats off), I do the following:SELECT * FROM Customers WHERE Country = 'Sweden'My query plan show a clustered index scan, which is expected - no indexexists for Country. BUT, the query plan also shows, that the optimizer ismissing a statistic on Country, which tells me, that the optimizer wouldbenefit from knowing this.I cannot see why? (and I've been trying for a while now).If I create the missing statistics, nothing happens in the query plan (andwhy should it?). I could understand it, if the optimizer suggested an indexon Country - this would make sense, but if creating the missing index, queryanalyzer creates the statistics with an empty index, which seems to me to beless than usable.I've been thinking long and hard about this, but haven't been able to reacha conclusion :) It has some relevance to my work, because allowing theoptimizer to create missing statistics limits my options for designingindexes (e.g. covering) for some rather wide tables, so I'm thinking why notturn it off altogether. But I would like to know the consequences - hopesomebody has already delved into this, and knows a good explanation.RgdsJesper
What is the unit of the numbers you get in the Time Statistics-part when running a query in Microsoft SQL Server Management Studio with Client Statistics turned on?
Currently I get mostly 0´s, but if I try and *** up a query on purpose I can get it up to around 30... Is it milliseconds or som made up number based on clockcycles or... ?
I would also like to know if it´s possible to change the precision.
Hi all I'm transferring some Access queries to SQL server and the crosstabs don't want to work, can anyone shed any light on the query below.
Thanks.
TRANSFORM Min(tCompany.cCompanyName) AS CompanyName SELECT tProjContacts.ProjectID FROM tCompany INNER JOIN (tProjContacts INNER JOIN tCompanyType ON tProjContacts.CoTypeId = tCompanyType.CoTypeId) ON tCompany.CompanyID = tProjContacts.CompanyID WHERE (((tCompanyType.CoType) Like "*topo*" Or (tCompanyType.CoType) Like "*ground*")) GROUP BY tProjContacts.ProjectID PIVOT tCompanyType.CoType;
How often should I run statistics on a database and what should I use? The create statistics function? Right now we have not run statistics against the database in over 8 months. However, we do have the database setup so that it will autocreate and auto update statistics on each table. Is this the best way to keep statistics up to date or is it better to run a function that rebuilds them on a nightly/weekly basis. Our database does have a large amount of activity against 99% of it takes place between 7am-6pm so the rest of the day/night we have open to schedule things like this. Any suggestions? I am really just trying to get a handle on the best way to keep our database running smoothly and quickly.
I had run a stored procedure in my server that update statistics against all user defined tables in my database (MSSQL 7.0).
Since then I am getting errors in my ASP application where I am reffering to adovbs.inc.
Here is an example of errors I get.
Microsoft VBScript runtime error '800a0411'
Name redefined: 'adOpenForwardOnly'
/Essai/adovbs.inc, line 14
Below is the stored procedure I have run against the database.
Can anybody help tank you guys.
CREATE PROCEDURE update_all_stats AS /* This PROCEDURE will run UPDATE STATISTICS against ALL user-defined tables within this database. */ DECLARE @tablename varchar(30) DECLARE @tablename_header varchar(75) DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' OPEN tnames_cursor FETCH NEXT FROM tnames_cursor INTO @tablename WHILE (@@fetch_status <> -1)
BEGIN IF (@@fetch_status <> -2)
BEGIN SELECT @tablename_header = "Updating " + RTRIM(UPPER(@tablename)) PRINT @tablename_header EXEC ("UPDATE STATISTICS " + @tablename ) END FETCH NEXT FROM tnames_cursor INTO @tablename END PRINT " " PRINT " " SELECT @tablename_header = "************* NO MORE TABLES" + " *************" PRINT @tablename_header PRINT " " PRINT "Statistics have been updated FOR ALL tables." DEALLOCATE tnames_cursor
I am a neewbie to SQL 7 and having previously used Access for some time.. I have a table with a date field in a want to create a table with the values filling columns in another table by month ie April,may ,Jun and so on. Ia m using the Month function to get the month number but I dont know how to crosstab this into the relevant columns in the new table...I thinks DTS is the way and do a transformation on the month field but am struglling at the mo...Any help greatfully appreciated...
I need to accomplish the turning column data into row data via SQL. I can sorta get what I want with creating the report as a matrix report. However, I always seem to need one little thing to happen to shape my data as I need it. Anyway, here goes...
I have a table with 4 columns
UNIQUE | Code | FieldID | CustomField
The Code column is the customer code. FieldID numbers 1-100 and CustomField has string data.
My problem. I need to be able to choose ALL Customers [Code] where FieldID/s ="6", "7" & "8" and the CustomField rowdata corresponding to the FieldID data. BUT I then need to be able to use a daterange parameter on any row with a FieldID of "6" but that is not a datetime format it is in a string format.
Currently my SQL is:
SELECT [Unique ID], Code, [Field ID] AS IUdate, [Field ID] AS IUNote, [Field ID] AS IUReq, [Custom Field]
FROM dbo.[Customer Custom Field]
WHERE (Code = '07-8111')AND ([Field ID] = 6) OR (Code = '07-8111')AND ([Field ID] = 7) OR (Code =
'07-8111')AND ([Field ID] = 8)
However, everytime I try to run a daterange against, I get all kinds of data I don't need because the parameter is running against all the fields and NOT just the stringdate.
My idea is to convert the column data into row data (like a crosstab query) to sharpen it up for parameter ranges.
Is there something i am missing here? Can someone point me in the right direction?
Is there a way to write Crosstab query in SQL 7.0.
I have a Table which has partner, usernames and the city they are from, apart from other fields. I have another table which tracks the user activity on a day by number of visits to the web site. What i want is like this :-
Date Total Visits Atlanta Connecticut .... .... ....
I.E. I wan't the city names to appear as columns. The main catch here is that this data is for a given partner. So the city names could be different for different users from different partners. i.e. city names have to be generated on the fly by looking at the partner table and then by getting all its users and seeing what all are the cities involved.
I am trying to run a report off a crosstab query. The report calls for columns A - F, which are returned from the crosstab query. The only problem is that at times some of the columns are not returned by the query because there is no data associated with them. The report asks for column X, and no such columns exists in the query result; thus, an error is generated. My question is: how do I deal with this problem? I would like to avoid having to build the report from scratch. Is there some way that at runtime I could programatically tell the report to ignore these fields?
I have a table called Ideas and a table called Users_Ideas. In Users_Ideas, there can be up to four rows referencing Ideas with a foriegn key. I need to select all rows from Ideas and for each Idea display the users associated with it.
Total is the total number of alerts for the server between the two dates. Of course, there are many servers, and the number of alert names varies over time which means i can't use "case when...".
Is this possible??? Would be extremely thankful for any help!
I'm trying to port a dymanically renderred form from a .mdb to a .adp. In the Access .mdb. The form is a representation of a crosstab query with unknown column headings (hence, the need for the form to be dynamically rendered at runtime).
I tried to port the crosstab from the .mdb to a stored procedure, but SQL Server doesn't like TRANSFORM, and perhaps the PIVOT as well. How do you do this in sql server?:
TRANSFORM Count(d.CAR_INIT) AS CountOfCAR_INIT SELECT b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR FROM ((TSA_HS_MPCT_CNT a INNER JOIN TSA_HS_COMB2 b ON a.RECC_COMB_ID = b.COMB_ID) INNER JOIN TSA_HS_WKLD c ON b.WKLD_ID = c.WKLD_ID) INNER JOIN TSA_HS_OBJ_TRN d ON c.WKLD_ID = d.WKLD_ID GROUP BY b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR PIVOT d.LST_HMP_DTM + d.OBJ_DEP_TRN
Hi, I have tried with the solutions available in the links provided by Pootle,but still I think I have to go a long way.My main problem is with the second aggregate column of previous year. Let me put it again....
But I can't bring the previous year aggregate column ,i.e 2005 in the above case.
So can you suggest me a way or direction to acheive that?
Any help would be really appreciated. BTW,Pootle gave these links in my previous post... link1 (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21820764.html#16500817) Link2 (http://www.sqlteam.com/item.asp?ItemID=2955)
Hi I have table called MyTable that has 3 columns (City , Brand, Price) I want to select from this table in Cross Tab format i.e. My table has CityBrandPrice LondonDELL1227 LondonToshiba1100 LondonAcer1007 LondonHP1467 LondonIBM1193 SydneyToshiba2100 SydneyAcer2219 SydneyApple2589 SydneyVAIO2122 SydneyHP1929 SydneyIBM2877 TokyoToshiba7200 TokyoAcer5299 TokyoCOMPAQ9200 TokyoIBM8779 TokyoHP6286 ParisDELL1670 ParisApple1825 ParisVAIO1267 ParisHP1882 ParisCOMPAQ1636 ParisIBM1332 NewYorkDELL2000 NewYorkToshiba1288 NewYorkAcer2333 NewYorkApple2299 NewYorkVAIO2327
__________________________________
i want the select statment result to be like this DELLToshibaAcerHPIBMAppleVAIOCOMPAQ London12271100100714671193N/AN/AN/A SydneyN/A2100221919292877N/AN/AN/A TokyoN/A7200529962868779N/AN/A9200 Paris1670N/AN/A18821332182512671636 NewYork200012882333N/AN/A22992327N/A
City & Brand can be anything so i will not be able to hard code them in my Select.
Currently working on a Attendance System project . Iam storing the data in table against the employee code, date and status (basically a rowwise data).
Would require a query which can generate a cross-tab display with Employee Codes (on X axis), Dates (on Y-Axis) and with the Attendance Status.
The sample data for the same is as follows:
EmpCode Att_Date Att_Status ------- -------- ---------- 001 01/01/2007 P 001 01/02/2007 A .. ... .. 001 01/31/2007 P
002 01/01/2007 P . . . . . . . . .
Would require the output as..
EmpCode 01/01/2007 01/02/2007 ...... 01/31/2007 ------- ---------- ---------- ---------- 001 P A P 002 A P P . . . . . . . . . . . .
