The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005
Debit = All positive amount
Credit = All negative amount
Net Change = Total Credit - Total Debit in Period 01
Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
I have 2 tables, each with one ID field, a separate Date and Time fields and a number of other fields. The tables contain duplicates on the ID field. I want to do a UNION keeping only the record with the latest Date and Time.
This would work: SELECT MyTab.myKeyField, Max(MyTab.myDate) AS myDate FROM (SELECT myKeyField, myDate from Table1 union SELECT myKeyField, myDate from Table2) AS MyTab GROUP BY MyTab.myKeyField
But is only taking care of Date, not Time (some records have the same date but different times) The other problem is, when I add more fields, I have to include them in the GROUP BY clause, and this way I end up with duplicates (because some other fields have different values)
Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query. Can this be done? Please guide me on this...
I have a table that I am using in a package to create an extract from. In that table is an address field called "Street" that is 255 characters in length. My table also has 3 additional fields called address_1, address_2 and address_3 that are each 50 characters in length because that is the requirement for my extract. I need to split the address field up in such a way that if it is longer than 50 characters, it backs up to the first space in the address prior to character #50, puts that info in street1, then from that cut off point used in street1, puts the next 50 up to the prior blank space in street2, then the remainder in street3. Where the extract will be used only has three 50 character fields so if the data runs more than 150 characters, the street3 data will just have to be truncated. No way around that, but I don't anticipate any address getting close to that long. Although doing such a split would be much easier using SQL, the solution requirement is that it be done in the package, not using SQL to do so.
I'm assuming I need to use a "derived column transformation" in my data flow. But, I can't figure out how to do what I need to do with a derived column transformation.
Example of info in an address: 123 Chicamauga Avenue South, Across the Street from International Center Square, Apartment Number 17650 Tokiwa-machi Machida
position 1-50: 123 Chicamauga Avenue South, Across the Street fro
Therefore, Street1 would need to get: 123 Chicamauga Avenue South, Across the Street
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
Whats the best way to do the following?Field1 in Table1 contains numbers and characters seperated by #Examples: aaa#01, kjhkjhjh#21 and jlkjlkj#123How can I create two new fields in Table1, one containing what is tothe left of the # and the other what is to the right?Regards,Ciarán
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.
Here's a question for the SQL gurus out there: I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be 8060 8060,4450 8060,4123,3245 Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example CODE1=8060, CODE2=4450, CODE3=NULL. I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field? Any help very greatly appreciated. Thanks, Simon.
I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually.
In SQL SErver 2008, I have a text column. I need to display either 2nd word in the text column or 1st word in the text column based on certain conditions.
How shall i display either 2nd word or 1st word from a text field.
I need to split a field based off it's length. Â Initial thought was to take Left(field, characters), and Right(field, characters), but now I see I am duplicating the data as I am capturing too much of the data in. Â However, what I actually need to achieve is in field1 capture the left 1000 characters, and in field 2 take from character 1001 to the end. Â How can I update my syntax so that field2 will ONLY capture from character 1001 to end?
field1 = COALESCE(CASE WHEN DATALENGTH(exitinterviewnotes) > 998 THEN LEFT(CAST(exitinterviewnotes AS VARCHAR(MAX)), 1000) ELSE exitinterviewnotes END,''), field2 = COALESCE(CASE WHEN DATALENGTH(exitinterviewnotes) > 998 THEN RIGHT(CAST(exitinterviewnotes AS VARCHAR(MAX)), 1000) END,'')
I have a database field which consists of a long string of values with some separator.
Ex:Â Injection^!@$#Medication
in my report i have to split the string as
Injection Medication
till here i am able to display the results but in addition to that i have to display check box against these values like below
and this string can contain any no of values ex: if we have 4 values are separated within a string then i have to display 4 check boxes against 4 results.
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
SELECT DENSE_RANK() OVER (ORDER BY clroot.Ctgry1.Descr ASC) AS Row1, Row_Number() OVER (partition BY clroot.Ctgry1.Descr ORDER BY clroot.Ctgry1.Descr, T1.Descr ASC) AS Row2, left(t1.ID,4)+right(t1.levelid,4) AS ERPID,T1.ID AS Ctgry1ID, clroot.Ctgry1.ID AS ParentID, T1.LevelID, clroot.Ctgry1.Descr AS Category, T1.Descr AS SubCategory,
I am setting up a database that will receive a lot of data from twoseparate telephone centers, the log table will in a short time haveover 1 million lines, and I was wondering if I should use 1 identifyfield or two:case 1:[Id] [int] IDENTITY (1, 1) NOT NULL[ServerId] [int] NOT NULLcase 2:[Id] [varchar(20)] IDENTITY NOT NULLWhere in case 1 I would just use a combination of Id and ServerId toidentify the line, where in case 2 I would have the Id field a varcharthat would look something like A-000001, A-000002 for server 1 andB-000001, B-000002 for server 2Which solution will be faster when searching for a record when thewill have over 1 million lines?
I'm struggling with this. I'd like to perform a joined query from two or more tables and take the same field from several rows of one table into seperate fields of a single row in a new table.
I'm developing a report in RS 2000 that uses two datasets. The first dataset provides data to a drill-down report on the top part of the screen and the second small dataset provides some data for additional summary information on the bottom of the screen. I'm getting a runtime error "An unexpected error occurred in Report Processing. The expression referenced a non-existing field in the fields collection." Of course, I've edited every expression I can think of: text boxes, data grid, parameters, filters, drill-down lists... I think I've gone through every object on the report but I can't isolate the bad expression. Can anyone suggest a better way to debug this type of error? It compiles fine, but fails in runtime. thanks.
I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like: John Bob Number1
The only way around it now is that I store Other_Name in Table1.
I have a table with eight (8) fields, including the primary key (rfpid). Three of the fields are foreign keys, which take their values form lookup tables. They are int fields (pmid, sectorid, officeid). One of the fields in this table is based on putting together the descriptive field in the lookup table for sector (tblsector). The two other fields to be part of this string are the rfpname and rfpid. This creates the following string:
The words rfp, proposals are words that have to be part of string;
the slashes are to also appear.
current_year would be defaulting to datepart = year (2008)
The part that has the last two digits of the current year then the underscore and then the rfpid should be connected by an underscore to the rfpname. I am at a loss and would greatly appreciate any help.
I have a table with two sets of fields, one for PRIMARY Mail Address and the other for ALTERNATE Mail Address. Both sets are five fields long. I will call them PRIMARY & ALTERNATE for this discussion. I want to select the PRIMARY fields if ALTERNATE is either null or zero, and the ALTERNATE fields if data is present.
I have used this syntax but it gives a table with both the PRIMARY & SECONDARY records.
SELECTÂ Â Â TXPRCL, TXALTR, TXANAM FROMÂ Â Â dbo.PCWEBF21 WHEREÂ Â Â (TXALTR > 0) UNION SELECTÂ Â Â TXPRCL, TXTAXP, TXTNAM FROMÂ Â Â dbo.PCWEBF21 WHEREÂ Â Â TXALTR = 0
Would a Select Case work? something like
Select PRIMARY when ALTERNATE = 0, and ALTERNATE when ALTERNATE > 0 or is not null.