T-SQL (SS2K8) :: Increase Amount By Fixed Maximum Spread Over Several Rows
Oct 1, 2014
I have an issue where I have multiple rows of data and I need to reduce a dollar amount by a fixed maximum. I am going to throw some code in here to give a rudimentary idea of the data and what the final result should be.
I need to run an update so that the result of the following query:
select LineNum, Code, Amt, MaxAmt from
@tblLooks like this:
LineNum Code Amt MaxAmt
----------- ---- --------------------- ---------------------
1 AA 10.00 50.00
2 AA 20.00 50.00
3 AA 20.00 50.00
(3 row(s) affected)
I have tried cursors but got unexpected results or the MaxAmt always defaulted to the original even if I updated it. This seems like a simple problem but I have been banging my head against the wall for 2 days now. I've written some pretty complicated updates with less effort than this and I must have some mental block that is keeping me from figuring this out.
I need to generate a csv file from another csv file, seems to be simple but let's go the trick thing:
Needs to have maximum 1000 lines, if I reach to this, I need to create another csv and fill that new one.
Exemplifying:
I have a csv file called fileA and this has 2000 lines and another csv called fileB with 1500 lines.
I need to loop a folder and get the fileA, create an output called FileAOutput and start to fill that, if I reach to 1000 lines, I need to create a FileAOutput_2 and fill the other 1000 lines...so I'll go to fileB and do the same thing, but in the second case, I'll have 500 lines in the second output.
I am receiving an error from my ODBC driver “Maximum number of DBPROCESSes already allocated.”
I confirmed that there are 25 connections and that this is the default. This is caused by error message 10029, SQLEDBPS, when the maximum number of simultaneously open DBPROCESS structures exceeds the current setting. I would like to increase this maximum.
I have found only two ways to do this. One is using dbsetmaxprocs using C and the other is using SqlSetMaxProcs using Visual Basic. My problem is that I am interfacing to SQL Server using a third party tool that is doing the lower level programming.
Is there some way that I can increase the maximum number of DB processes for all databases that are part of the SQL Server 7 environment, or can I set this value using a program that is called from a stored procedure?
Any ideas in this area will be greatly appreciated.
I have a table with 3 columns (ID Int , Name Varchar(25), Course Varchar(20))
My source data looks like below
ID     Name       Course 1       A              Java 1       A              C++ 2       B               Java 2       B              SQL Server 2       B               .Net 2       B                SAP 3       C                Oracle
My Output should look like below...
ID     Name      Course(1)    Course(2)        Course(3)    Course(4) Â
1       A                Java           C++ 2       B                Java           SQL Server .Net            SAP 3       C                Oracle
Basically need t-sql to Convert non fixed rows to non fixed columns...
Rule: IF each ID and Name have more than 1 course then show it in new columns as course(1) course(2)..Course(n)
Create SQL:
Create table Sample (ID Int null , Name Varchar(25) null, Course Varchar(20) null)
Insert SQL:
INSERT Sample (ID, Name, Course) Â Â Â Â Â Â Â Â Â VALUES (1,'A','Java'), Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â (1,'A','C++'), Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â (2,'B','Java'), Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â (2,'B','SQL Server'), Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â (2,'B','.Net'), Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â (2,'B','SAP'), Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â (3,'C','Oracle')
I need to create an output from a T-SQL query that picks a numeric variable and uses the print function to output with leading zeroes if it is less than three characters long when converted to string. Â For example if the variable is 12 the output should be 012 and if the variable is 3 the output should be 003.
Presently the syntax I am using is PRINT STR(@CLUSTER,3) .  But if @CLUSTER which is numeric is less than three characters I get spaces in front.
hi i'm having this error on my application"cannot allocate more connection.connect pool is at maximum increase max pool size" the proble is when i do testing this error does not apply it only Appears when the application is been used by many people How can I resolve this? Thanks
I need to divide amount in joint account. So if joint account has 2 account holders & amount is 35622.15 then one person should have 17811.08 and other person should have 17811.07
If I used below query it just give me 17811.08 for both account holders so when we sum it it's one penny extra.
I am trying to figure out how to pivot a temporary table. I have a table which starts with a date but the number of columns and columns names will vary but will be type INT (Data, col2,col3,col4………….n)
So it could look like
Date , TS-Sales, Budget , Toms sales 01-Jan-14,100,120,300 02-Jan-14,80,150,300 03-Jan-14,100,20,180
What I want is to be able to sum al the columns but without knowing the name and the amount columns to start with this is a manually processes. How could I automate this?
I'm writing a query that will be used in Jasper Ireports, but prefer to have the values done ahead of time using SQL rather than relying on the report to do the lifting.The fields are pretty straight forward, only the display is where I have a question.
Fields Used: PERIOD ('MON-yyyy') and VALUE
The results must start with the CURRENT PERIOD (AUG-2014) in one column and the VALUE for the current period multiplied by 1/12 (VALUE*(1/12)).The next column should return the VALUE for CURRENT PERIOD - 1 (JUL-2014) and multiply by 2/12 (VALUE*(2/12))
This should continue for the last 11 months and would end with OCT-2013 with the value being multiplied (VALUE*(11/12)).Is the easiest solution to this a CASE statement looking at PERIOD then PERIOD minus one month, minus two months...etc?
How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4, Now my query look likes this:
Select col1, col2,col3,col4, (col1 + col2) as addcol1, (col2 + col3) as addcol2, (col3 + col4) as addcol3, Max(addcol1,addcol2,addcol3) as maxvalue from Table A
I am getting error as max accepts one argument, I cannot use case statement as table is already bulky and it will make my query more expensive.
I have a routine that generates an HTML email and sends it just fine, but one of the columns ends up with 4 decimal places for a column datatype of money. How can I get the script to output only 2 decimal places for the amount column from the select statement?
please help to select these rows from these tables my tables aretable1table1Id groupId table2id price1 1 1 102 1 3 10003 1 4 5004 2 1 55 2 3 10006 2 2 2000table2table2id name1 hello2 test3 test14 test2ok i want to select maximum priced row from table1 grouped by groupidwith table2id and table2.namemy out put isgroupid price table2id table2.name1 1000 3 test12 2000 2 testif i do :select groupid,max(table1.price) as price from table1 group by pricethis will give me the max priced row from table1but when i join them with the table2 it gives me all rowslikeSelect groupid,max(price) as price,table2id,table2.name from table1inner join on table2group by groupid,table2id,table2.nameit gives me all rows cause i had to group by table2.id and table2.namebut i can't take it out cause it give me no aggrigated value errori can't figure out any other way, please helpSQL Server 2000thankseric
--drop table #temp create table #temp (id int, idvalue int) insert into #temp(id,idvalue) select 1095,75
[code]...
I need to take the id value from maximum's id, and compare the rest id value from the table. i need to check the diffrence , if diffrence is more than 18, then i need to raise the flag as failure otherwise the whole test is success. i need to take 63 and compare rest 69,65,61,75.check the diffrence less than 18 or not.
TotalSelected.Value = SqlDataSource1.SelectCommand = "SELECT COUNT(*) FROM tblNews";
the reason i am tring to do this is so if i can find out the amount of rows before sqldatasource selects for details view then i can make the sqldataesource select depends on total minus 5 so e.g. if total 200 then - 5 so i can select bottom 195 so it misses top 5 for details view any1 any ideas? Thanks Andy,
Hello,I have experienced that some of my tables occupies an extremely large amountof pages but with few rows. An example is a table with 37 rows over 22000pages !. The columns are simple integer and char. I fixed the problem byintroducing a clustered index. Now it only uses 1 page. But can anyoneexplain this behaviour in SQLServer 2000 ?regards Jakob Mathiasen
Create table code ( id identity(1,1) code parentcode internalreference)
There are other columns but I have omitted them for clarity.
The clustered index is on the ID.
There are indexes on the code, parentcode and internalreference columns.
The problem is the table stores a parentcode with an internalreference and around 2000 codes which are children of the parentcode. I realise the table is very badly designed, but the company love orms!!
The table currently holds around 300 millions rows.
The application does the following two queries to find the first internalreference of a code and the last internal refernce of a code:
--Find first internalrefernce SELECT TOP 1 ID, InternalReference FROM code WHERE ParentCode = 'M222' Order By InternalReference
-- Find last ineternalreference SELECT TOP 1 ID, InternalReference FROM code WHERE ParentCode = 'M222' Order By InternalReference DESC
These queries are running for a very long time, only because of the sort. If I run the query without the sort, then they return the results instantly, but obviously this doesn't find the first and last internalreference for a parentCode.
I realize the best way to fix this is to redesign the table, but I cannot do that at this time.
Is there a better way to do this so that two queries which individually run very slowly, can be combined into one that is more efficient?
1. PhoneID is nothing but the participant in the call. PhoneID = 1 is twice from above. Which means 2 particpants (Same call )with 2 numbers with their callstarttime and callendtime. Similarly for PhoneID =2, there are 4 participants. And the list goes on for a day and then for a month.
2. For example a phone call P1 with 2 participants is going on for a particular day. We should not consider the same phone call having 2 participants involved. So that the concurrency would be 2. We have to ignore that here.
3. Only to be considered is other Phone calls for that day. Lets say P1 having call with 2 participants, P2 having some 4 participants which fall in the time period of P1. Then we should consider P1 and P2 the common period
4. In order to find number of concurrent calls happened for a day basing on callstarttime and callendtime. What would be the query?
5. Should consider the Timeperiod or the bucket with 1 hour as the period.
6. A Phone Call P1, Phone Call P2, should have matching (common) time ( keeping all the scenarios) is required for this query.
Result for Concurrent calls for a day should be like below. Should get all the concurrent connections happened for a particular day.
Date|TimePeriod/Bucket(hr part)|Concurrentconnections| Jan-01-2015|01 to 02|3 Jan-01-2015|11 to 12|2 Jan-02-2015|04 to 05|5 Jan-02-2015|12 to 13|13 ........
ii) So once the above is achieved.
Have to find the Maximum concurrent connections for day from the above.
For below Maximum Concurrent connections are '3' Date|TimePeriod/Bucket(hr part)|Concurrentconnections| Jan-01-2015|01 to 02|3 Jan-01-2015|11 to 12|2
Hence the Result for Maximum Concurrent Connections would be
Date|TimePeriod/Bucket(hr part)|MaxConcurrentconnections| Jan-01-2015|01 to 02|3 Jan-02-2015|12 to 13|13 .............
We are running merge replication, SQL Server 2005 Enterprise with SQL Mobile 2005 (Windows Mobile 5) subscribers. Partitions are filtered on HOST_ID.
Ocassionally we experience a situation where a subscriber experiences an unusually long synchronization duration, and upon examining Replication Monitor, it appears that twice the number of rows, or X the number of rows (up to 7 times the number of rows) that should have been inserted are recorded as synchronized for the session: once the normal amount as inserts and once the normal amount as updates. This occurs for all tables in the subscription. This occurs on a first time synchronization to an empty subscriber database where there should be only inserts taking place.
I have examined the snapshot partition folders for these users on the file system and they appear to be identical in size and content as other subscribers. Checking the last partition snapshot job run and other characteristics for the the subscriber in question, everything appears to be the same as other subscribers functioning normally.
The HOST_ID for us is an employee ID used to filter employee specific data. I have seen this happen if the subscriber changes the value for the HOST_ID used in filtering, after the mobile database has already been populated (2 employees attempt to use the same mobile device and database). But, we have seen this happen recently where the HOST_ID was apparantly never changed midstream.
This just started happening recently. The only modification around the same time frame was the implementation of a custom business logic handler/custom conflict resolver that performs like "Latest Wins" but has logic added to update the a last-update datetime column for selected transaction tables at time of synchronization, so that an SSIS job can detect the changed rows for copying incremental database changes to another application database. This all seems to be working perfectly.
A table has a column of int type. I need to select a fixed number of rows for each value. For example, if data in that column (c) are 5, 6, 7, and the number I want to select is 2, then I need 2 rows from c=5, 2 from c=6, and 2 from c=7. How to write that query? Any idea?
I need to show the total amount of rows in a specific table?
The query is as follows:
As part of the planning process to expand the database that supports Northwind operations, the IT manager would like to know how many rows are currently in specific tables so that he can conduct capacity planning.
The results needed include two columns, TableName( containing all the tables in the database and Rows, which contain the total amount of all the rows per table).
I have a table with 30 rows. When the data is got from the DB these 30 rows may or may not be filled but they should be visualized. How can I do this? I mean if only 10 rows are filled with data the other 20 to be visible.
I want if cell content grows than the whole row to change its height. Is it possible?
I created a package that seems to work fine with a small amount of data. When I run the package however with more data (as in production) the merge join output is limites to 9963 rows, no matter if I change the number of input rows.
Situation as follows.
The package has 2 OLE DB Sources, in which SQL-statements have been defined in order to retrieve the data.
The flow of source 1 is: retrieving source data -> trimming (non-key) columns -> sorting on the key-columns.
The flow of source 2 is: retrieving source data -> deriving 2 new columns -> aggregating the data to the level of source 1 -> sorting on the key columns.
Then both flows are merged and other steps are performed.
If I test with just a couple of rows it works fine. But when I change the where-clause in the data source retrieval, so that the number of rows is for instance 15000 or 150000 the number of rows after the merge join is 9963.
When I run the package in debug-mode the step is colored green, nevertheless an error is displayed:
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Merge Join" (4703) failed with error code 0xC0047020. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
To be honest, a few more errormessages appear, but they don't seem related to this issue. The package stops running after some 6000 rows have been written to the destination.
I have a problem. I want to know if the time which is needed for creating an index increases proportional to the amount of rows. example: if creating an index on a table which 10.000 rows takes 15 seconds. does creating an index on a table with 20.000 rows take 30 seconds , 40.000 rows 60 seconds and so on... or does it take longer like 10.000 rows 15 second, 20.000 rows 40 seconds, 40.000 rows 80 seconds.
Dear Experts, i've one database with around 1400 tables. is there any possibilities to know at a time what is the count(*) in each table? actually i need tables which are having maximum data.
my expected result is like this table num_rows table1 20000 table2 10000
like this thank you very much
Vinod Even you learn 1%, Learn it with 100% confidence.
I need to count and display the number of records which have GradeTitle="SHO". I'm only starting to use BI development studio and all attempts at using the built in aggregate functions have failed.
Also, the report I wish to create has a fixed number of columns and a fixed number of rows as the info being displayed is really only counting values in the DB. I tried using Table but multiple rows were created.
I'd appreciate if anyone could point me in the right direction, as searching this forum turned out to be pretty fruitless for me.
I have a dataset with 2 columns, a rownumber and a servername - eg
rownumber servername
1 server1
2 server2
....
15 server15
I want to display the servernames in a report so that you get 3 columns - eg
server1 | server2 | server3
server4 | server5 | server6
...
server13 | server14 | server15
I have tried using multiple tables and lists and filtering the data on each one but this then makes formating very hard - i either end up with a huge gap between columns or the columns overlap
I have also tried using a matrix control but cant find a way to do this.
Does anybody know an easy way to do this? The data comes from sql 2005 so i can use a pivot clause on the dataset if somebody knows a way to do it this way. The reporting service is also RS2005
;WITH ctePreAgg AS ( select top 500 act_reference "ActivityRef", row_number() over (partition by act_reference order by act_reference) as rowno, t3.s_initials "Initials" from mytablestuff order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
I have a DB running for a while, but it reached 32767 rows and stopped.. The DB is only 20MB big. Where can i ajdust this setting to a bigger number of rows?
i want to break 2 by 2 rows in reportceiling(rownumber(nothing)/2).i used this expression in row group. URL....but i want to use this expression in matrix and that matirx is with in list . so i  am getting error . how to use rownumber in list.here i used list to break the page wise id
I have a table which dont any Identity column. The data in the table has duplicate values, some rows are totally identical. I just want to select the row which has maximum Row Id/Row number if I am getting multiple rows in my select statement.
HelloI am running a DTS (Sql 2000) and transfering data from an SqlServerdatabase to an Ingres database.I am transfering 153k rows.Execution fails with the error message:"The number of failing rows exceeded the maximum specified"The number of rows shouldn't be a problem because in another DTS I copya lot more rows.Also the width of the row shouldn't be a problem, I'm transfering only 3int4 fields and 1 char(5) field.Anyone have an idea what the problem could be ?ThanksDavid Greenberg
