Hey all,
I have a table of errors, where many of the records are the same error, with times very close to each other. I want to be able to group by the error number and time. But the time grouping is a special case, it should return the MAX(Time) within the records that are less than 1 minute apart. So if my records were:
i.e. Morning 00:00 to 11:59 Afternoon 12:00 to 17:59 Evening 18:00 to 23.59
I can do the group By Day Name ok Like Below
Code:
SELECT COUNT(ISNULL(createdDate, 1)) AS DayCount, ISNULL(DATENAME(dw, createdDate), 'No Date Set') AS DayOftheWeek FROM dbo.MyTable GROUP BY ISNULL(DATENAME(dw, createdDate), 'No Date Set')
its just the grouping by time ranges im having trouble with..
I have column which stores People count based on department, Now I want to keep them in the batch of 1000, If the running summary of (No of people) from departments reached 1000 then it should start sum(no of people) from 0 to 1000
Is there any running summary kind of function which can start sum record with in range of 0-1000
For Ex. My Data stored like this Dept People Count CSE 200 IT 250 EEE 312 ECE 214 MEC 337
Batch Grouping Dept People Count BatchSum CSE 200 200 IT 250 450 EEE 312 762 ECE 214 976 MEC 337 337 (Note here since its crossing 1000, its resetting and starting summary)
I implemented this with While Loop & if condition, But its very slow, is there any other way to achieve it in better way.
My scenario is: a person has many events, all based on a date. I need to aggregate the person to show min and max dates for a period, the period being defined as ending when there is not an event following the next date.
Currently I report our monthly fees broken down into 4 weeks per month by using 4 separate datasets with the following code
SELECT SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT FROM Practice.ibvSalesByJob INNER JOIN Practice.idvJobType
[Code] ....
The second dataset then has the date code changed to
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/08/' + Cast(@DateYear AS char(4)) + ' 00:00:01' AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:00'
The third
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:01' AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:00'
The fourth
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:01'
Now I was hoping so that I could report the above data in one chart and do an expression on the category and group the dates so I would just have one dataset like below but four separate columns saying Week 1, 2 3 and 4 and then the sum filtered in line.
SELECT SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT FROM Practice.ibvSalesByJob INNER JOIN Practice.idvJobType
[Code] ....
If I could somehow with SQL tie in all 4 separate datasets and display them as Week 1, 2, 3 and 4 underneath the dataset and selectable.An even simpler solution maybe just understanding how charts work, I can get it so I display the 4 separate weeks in the chart however I can't get the bottom line (Category Group) to display Week 1, 2, 3 and 4.
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I'm running a pre-defined script which used to work fine on a file which was resupplied every month. below is the script used and the error message. looking at the error I assume that there is a rouge record within the file but have looked in Textpad and cannot find it.
UPDATE [matching].[dbo].[hot_nov] SET [AOV] = (CAST([Demand] AS DECIMAL)/CAST([Orders] AS INT)) WHERE [Demand] <> '';
UPDATE [matching].[dbo].[hot_nov] SET [POST2] = left([PostCode], PATINDEX('%[0-9]%', [PostCode] + '1') - 1) ; UPDATE [matching].[dbo].[hot_nov] SET [POST4] = left([PostCode],LEN([PostCode])-4); UPDATE [matching].[dbo].[hot_nov] SET [POST6] = left([PostCode],LEN([PostCode])-2);
error message
(1000 row(s) affected) Msg 245, Level 16, State 1, Line 181 Conversion failed when converting the varchar value '2014-09-03 00:00:00' to data type int.
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
I have a need to select rows where values in a column are within a range AND ALSO the first row that is highest (MAX) but just UNDER the range. For example:
NAME VAL Jim 4 John 5 Tom 6 Julie 7 Rich 8 Rob 9
RANGE TO SELECT is 6-8 (inclusive)
Rows with a value in the range are returned (6, 7, & 8) as well as the row with a highest value that is JUST BELOW the range (5)
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year Month Date
And the following row groups:
Region Product SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
select top 15 count(*) as cnt, state from table group by state order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned. DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20) SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central' SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts FROM log_directory_contacts GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.
SELECT Node_ID,Day,Operation, AA,BB FROM (SELECT CASE WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA, CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation
[code]....
So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....
Hi,I was hoping someone may be able to help me with a tricky T-SQLproblem.I need to come up with a SELECT statement that does basically thefollowing:Select RCRD_REFNO, MAX(MODIF_DTTM) as MODIF_DTTM from[StageDb].[dbo].tblPersonInfo group by RCRD_REFNOHowever, I need to select ONLY the TOP 1 of each group (i.e. only 1record for each unique RCRD_REFNO). The problem is of course that if Iadd 'top 1' after select, it only brings back 1 record full stop,rather than 1 for each group!Now, I have previously come up with a similar query that DOES do thissuccessfully, but it relies on a criteria (such as a unique identifier)-unfortunately, the nature of the table I'm using for this currentjob means that it actually doesn't have a primary key, as it'ssimply a staging area for raw data, and can even have completelyidentical records in it. I think the only way I'm going to be able todo it is to literally use the 'TOP' command somehow, but am notsure how to adapt the above to implement it...I'd be very gratefulfor any advice.Many thanks
Here is my Table Structure ( from Oracle database) Team | Customer Code | Amount | Credit Limit 1 , a, 100, 1000 1 , a , 200, 1000 1 , b, 100, 100 1, b, 1000, 100 1, b, 2000, 100 2, a, 100, 2000
For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount. Here I want to present Team Customer Code Amount Credit Limit Exceed 1 a 300 1000 0 1 b 3100 100 3000 Team Total 3300 3000 2 a 100 2000 0 Team Total 100 0 Total 3400 3000
BUT it turn out.. Team Customer Code Amount Credit Limit Exceed 1 a 300 1000 0 1 b 3100 100 3000 Team Total 3300 2300 ( Problem here a ) 2 a 100 2000 0 Team Total 100 0 ( Problem here a ) Total 3400 2400 ( Problem here b)
I Grouped the Custoer Code and Team I can preform the sum however I can't Do the Exceed total becoz the value should be iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0) but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount And the finial total it turns out 2400 (3400 - 1000)
I have tried use the coding to sum up the exceed but I found that the group total is sumup first than the sum up the detail :
Team Customer Code Amount Credit Limit Exceed 1 a 300 1000 0 1 b 3100 100 3000 Team Total 3300 0 2 a 100 2000 0 Team Total 100 3000 ( The Total from Team 1 ! ) Total 3400 0 ( Problem here b)
this situration , I can't change the query statement I can do the good result for CR report but for reporting service 2005, I can't to the first report result Any one can help me ?? thank you
need to migrate a cluster with an AG dtabases to new data center cluster with AG.
I was wondering if is possible to do mirroring on top of the AG configuration? or what other options could be to migrate a cluster that has 3 nodes and setup the ag databases to a new datacenter.
partition with single file group or multiple file group which one best.
we have some report running from partition table, few reports don't have any partition Key and after creating 400 partition with 400 file group it is slow.what is best practices to crate 400 file group or single file group.
Hi all, I have a Report and I have used Table in the report to define 5 levels of Sub Groupings: Group1, Group2, Group3, Group4, grpResourceBehavior.
The Sample data which comes is: Group1 Group2 Group3 Group4 ResourceBehavior BgtHrs BgtAmt A1 B1 C1 D1 NML 12 12 A1 B1 C1 D1 ODC 0 12
Now, to calculate a field "Cost", I have to use following condition. If the ResourceBehavior is NML, then Cost = BgtAmt/BgtHrs But, if the ResourceBehavior is ODC, then COst = BgtAmt/(SUm of BgtHrs for the resourcebehavior NML)
I am not able to calculate the Sum of the BgtHrs for different ResourceBehavior. Also, I need this field calculation for each and every group as well, and this makes it all the more complicated.
I have a report, which SUMS a value and groups on another field (campaign). What I need to be able to do, is take the SUMMED value from the 1st group, and subtract from this value, the value of the SUMMED value of the second group.
Example:
CAMPAIGN VALUE CAMPAIGN A 5,000 CAMPAIGN B 1,100
I'd like to be able to subtract 1,100 from 5,000 - but can't find out how this can be done....don't forget these are grouped item's not just rows from the dataset.
I am fairly new to SSRS, so please go easy on me if this is something simple (i hope it is!)
Hi, I use group by myItem, that displays rows for each value of myItem. I want at the end of each group for each specific myItem, I want an extra row to tell me the count of the rows for that group.
How do i do that please. Here s what I need, for example for this set of rows:
I need 2 extra rows, one at the end of the items 711056 telling me there are 4 rows for the item 711056 and one row at the very end telling me there are: 2 rows for the item: 711057
