Can Fields Span TableGroups Independantly Of Columns
Jan 31, 2006
Hi,
I'm trying to add a heading to my report using Table and TableGroups. But I don't want my group headings to line up with the detail rows. This seems like a common requirement in my mind but I can't figure out how to do it.
I have one table namely consumer with approx 50 columns.
I have created one same table with audit prefix including 2 more column for action n timestamp fields.
My question is that if user change only 10 column data at a time: i want to add only that particular column data rather to add entire row. Currently I am adding entire row in audit table but now scenario is change to update only updated column data.
hi need help from the wonderful people here i am working on project and convert a OLD DOS program to SQL server on the web this is the resalt i need to show
I've called a resultset from SQL Server using an SQL Selection. I need to iterate over that entire result set (200+ columns/fields) and all I need are the random numbers contained in any of the rows/columns. I don't want to have to name each field/column and then use an if > 0 statement.Isn't there some way to generically loop through the column's by index or something instead of their field name so I can just use an integer loop to walk the dataset? I know there is I've done it about 5 years ago. The question is how do you do it in C#?SqlConnection thisConn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLQuery"].ConnectionString); SqlCommand thisCmd = new SqlCommand("Command String", thisConn); thisCmd.CommandText = "Select * from SelectionsByCountry where [" + DropDownList1.SelectedItem.ToString() + "] > '0'"; thisConn.Open(); SqlDataReader thisReader = null; thisReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection); while (thisReader.Read()) { DropDownList2.Items.Add(thisReader["System"].ToString().Trim());/*** There are 200+ columns left I want to walk over using a loop structure of some sort. How do I do that?*/ }- Rex
Not nice but a work around: is there a way to get the amount of fields or rows from a table or query within MS SQL, to pick out the lastfield-4 for example ?
I've been busy all night searching and reading trying to figure out how I can do the following.
I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far...
SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???
How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB?
I am new to database programming and was curious how others solve theproblem of storing encrypted in data in db table columns and thensubsequently searching for these records.The particular problem that I am facing is in dealing with (privacy)critical information like credit-card #s and SSNs or business criticalinformation like sales opportunity size or revenue in the database. Therequirement is that this data be stored encrypted (and not in theclear). Just limiting access to tables with this data isn't sufficient.Does any database provide native facilities to store specific columns asencrypted data ? The other option I have is to use something like RC4 toencrypt the data before storing them in the database.However, the subsequent problem is how do I search/sort on these columns? Its not a big deal if I have a few hundred records; I couldpotentially retrieve all the records, decrypt the specific fields andthen do in process searches/sorts. But what happens when I have (say) amillion records - I really don't want to suck in all that data and workon it but instead use the native db search/sort capabilities.Any suggestions and past experiences would be greatly appreciated.much thanks,~s
I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?
I have a database where user events are recorded quite frequently. I'd like to be able to get a count of the 'good' events that happen in each 5 second period. Unfortunately I don't know how to display and group by a time range.
Here is the query I would like to change: SELECT count(*), clientTime FROM dbo.V_COMBINED WHERE (sessionId = '122b') AND (type = N'sys_goodaction') AND (paraName = 'value') GROUP BY clientTime
It returns records like: 1 |2006-02-16 23:21:05.250 1 |2006-02-16 23:21:05.267 1 |2006-02-16 23:21:06.470
I'd like it to return records like: 5 |2006-02-16 23:21:06 - 23:21:10 3 |2006-02-16 23:21:11 - 23:21:15 4 |2006-02-16 23:21:16 - 23:21:20
Anyone know how I could do this? Is it even possible?
I have to compare two time duration for a resource booking system. for example if a resource is booked for 9.00am to 2.00am ,it must not be booked during that duration and other coinciding times e.g 1.00pm to 2.00 pm should not be booked or even 9.00 am to 5.00pm should not be booked.
Some advice appreciated on the following, thanks! I have this query: select field1, field2, astartdate, anenddate from atable where astartdate BETWEEN '9/11/2007 12:00:00 AM' AND '9/18/2007 11:59:59 PM'
I need the results to be like the following: field1,field2,anenddate-astartdate <<that's minus
I need a query to pull the data from Sql server. my requirement is i need to pull the data from multiple columns, in that there are three email fields are there like email1, email2, email3. i need query to retreive the data from table first it search for email in the above 3 fields if any one of the fields contains the record the it display as Main mail id.
I've been busy all night searching and reading trying to figure out how I can do the following.
I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far...
SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???
How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB?
I am not sure how to go about doing this. I have a record that has a start time of 1 am and a stop time of 9pm (same day for simplicity) and I want to know how many hours during a peek time and how many were not.
For example, the application starts and inserts into the data base the start time of 1am and then the user stops the app at 9pm. Lets say the peek hours are 1pm to 7pm. I know i can do a date diff function to get how long the app ran for but how can I get the amount of hours it ran during the peek time? I know there has to be some mathematical solution to this but it is escaping me at the moment. I want to do this over many records so a a cte or pivot table is the end solution for performance.
I need to find the total StatusDateTime for each TicketId I need to find the average StatusDateTime for all TicketIDs Ex. TicketId, "T10001", has 4 records based on the Seq column.
By using this, I should be able to find the amount of time between the first Seq and the last Seq to get a total time span for Ticket.
Expanding on this, I should be able to add up all of the Ticket's calculated time spans and divide by the number of tickets to get the average time span.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO
[code]....
I am trying to display all records older then 5 Mondays span..so far I got this and it is working fine but it is excluding the records with Id 2 and 4 (Sundays) I would like to include these records as well.
I need to calculate the amount of time between each visit. I am pulling the Row Number for my visits and now I need the date span that goes between each day. I also need a new column that returns a Yes or a No if the date span exceeds 3 years.
SELECT ROW_NUMBER ( ) OVER ( PARTITION BY pv.PatientProfileId ORDER BY pv.Visit ASC ) AS RN , CONVERT ( VARCHAR ( 20 ) , pv.Visit , 101 ) AS Visit , pv.TicketNumber , vstatus.Description AS VisitStatus , doc.ListName AS Doctor
Looking for returning multiple entries from a time span. I have a date, start-time, end-time and duration. I need the start-times separated in a list. It's fine if temp tables are needed - I have that clearance.
ID - INT Machine - TINYINT StartTime - DATETIME EndTime - DATETIME
What I am trying to do is figure out how much time is used for production per day. The problem is, there are production runs that run over midnight and possible multiple days without ending. For example, if I have the following data:
I need a formula to calculate the time (let's say in minutes) between two dates/times. The problem is that I have to exclude the time between 06 PM and 06 AM and also exclude the time in the weekend (Saturday and Sunday). I will use this in a couple of reports made in Reporting Services. If anyone have an algoritm that could be modified for this and is willing to share this I would be very grateful. Many thanks! /Per Lissel
Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu". can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like: UPDATE "TABLENAME" SET DATAFIELD1 = DATAFIELD2 WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
Hi people My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?