I have a data; descriptions (types of elements in my system) on rows, and results (numeric values) of different branches for each description on columns. I have one entry (one row) for each individual day (so i have year, month and day columns) I prepared a report for this data (using enable drilldown; year --> month --> day --> description and its numeric values on the row) But I have to add new descriptions using my own descriptions, like formulas... My descriptions and my new descriptions made of with old ones, should be listed one under the other
For ex; new_description = (descr1 + descr2) / descr7
I am building a website in asp.net 1.1 with vb.net 2003 which will have the standings of the teams in our baseball league. Below is the database table I have created.
ID(int) home_team (nvarchar) away_team(nvarchar) win_teampf(nvarchar) lose_teampf(nvarchar) 1 Elmwood Murdock 7 22 Louisville Manley 4 33 Manley Elmwood 9 8 ID is the primary key. What I am attempting to do is add each instance of Elmwood from the win column to output the total number of wins from Elmwood and do the same for Elmwood in the losing team to output the total number of losses. The result will look something like this: Elmwood: 1 Win 1 Loss. .500 Thanks for your reply.
CREATE PROCEDURE Zakl_AddNewRow @zakl_nazwa VARCHAR(25), @zakl_miasto VARCHAR(20), @zakl_ulica VARCHAR(30) AS INSERT INTO Zaklady (Zakl_Nazwa, Zakl_Miasto, Zakl_Ulica) VALUES (@zakl_nazwa, @zakl_miasto, @zakl_ulica)
Also I've made procedure which deletes the row.
Code Block
CREATE PROCEDURE Zakl_DeleteRow @zakl_id INT AS DELETE Zaklady WHERE Zakl_ID=@zakl_id
So what's the point of my problem?
If I execute the procedure "Zakl_AddNewRow", I will add a new row to ZAKLADY table. Column ZAKL_ID is added automaticly because, as You can see, it is declared as INT IDENTITY(10,10). I have added 5 rows so the ZAKL_ID values will be adequately 10,20,30,40,50. Now I delete the row with ZAKL_ID = 30. After it I add new row once again. The result is that the ZAKL_ID value will be 60.
So the number "30" of the identity sequence became unsigned to any ZAKL_ID. I wanted that new row which was added has ZAKL_ID=30 not 60. The second added row should have ZAKL_ID=60. Is there any possibility to do this.If yes, What should I change in sql code.
Hello Friends, I have created a report using SSRS and in that report I am using group rows in one of the matrix. When I tried to display the subtotal of that group row by using the SSRS in-built feature the subtotal column is coming at the last of the matrix columns but I want it at the front .
so it will be like this,
First the total should be displayed then the group members value.
We just changed over our phone system and the new system uses all of the old extensions except it adds a 1 to the beginning of them. I know that there is a relatively simple way to update my phone extension column to show this, but I can't for the life of me remember what I need to do. Any help?
Hi there, I have a simple data flow from OLE DB to OLE DB that writes data from a view into a table. Is it possible to delete the table or the rows within the table before wirting the new data???
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
Hi , I am using the Integration Services slowly changing dimension to move data from a SQL Server 2000 database table to a SQL Server 2005 table.
The problem is the package is not tracking changes, it is spending a lot of time doing lookups (i'm guessing this cost it's real slow), but ends up creating new records when there has not been a change.
I'm quite sure the business key is set up correctly (I'm using the PK from the source table).
The database I am transferring from has non Unicode data types (ie varchar and char) and the destination database has Unicode data types (ie nvarchar).
Also some of the fields in the dB are NULL - does this have an effect (ie one null doesn't equal another null)? Or shouldn't that matter?
Hi , I am using the Integration Services slowly changing dimension to move data from a SQL Server 2000 database table to a SQL Server 2005 table.
The other problem is the package is not tracking changes it is spending a lot of time doing lookups (it's slow), but ends up creating new records when there has not been a change.
I'm quite sure the business key is set up correctly (I'm using the PK from the source table).
The database I am transferring from has non Unicode data types (ie varchar and char) and the destination database has Unicode data types (ie nvarchar).
Also some of the fields in the dB are NULL - does this have an effect (ie one null doesn't equal another null)? Or shouldn't that matter?
I am trying to use the following Query to create the table Agents and add rows to it.
USE REMAXCLASSIC; IF OBJECT_ID ('dbo.Agents', 'U') IS NOT NULL DROP TABLE Agents; GO CREATE TABLE Agents
[Code] ....
I get the following error messages: Msg 102, Level 15, State 1, Line 34 Incorrect syntax near '0.25' Msg 105, Level 15 mark after the character string ');
I'm a complete novice concerning SQL Server (Express Edition)
I'm trying to Add or Delete rows froma VB 2005 Express Function or Sub. While the program is running everything is ok. Except when restarted added records are gone and deleted records are back.
I have a table which is returning inconsistent results when I queryit!In query analyzer:If I do "SELECT * FROM TABLE_NAME" I get no rows returned.If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned.In Enterprise manager:If I do "return all rows" I get 4 rows returned, and the SQL is listedas being "SELECT * FROM dbo.TABLE_NAME".I've tried adding the "dbo." before my table name in QA, but it seemsto make no difference.I'm using SQL Server 2000, which is apparently 8.00534.Can anyone help me, or give me ideas about what to check?Thanks,Rowland.
IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.syscolumns WHERE id = OBJECT_ID(N'dbo.Employee) and name = 'DoNotCall') BEGIN ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0 IF ( @@ERROR <> 0 ) GOTO QuitWithRollback END
It just takes a LOT of time in SQL Server Management studio. I have to cancel the query and cancelling takes a whole lot time. I am using SQL Server 2008.
I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable. The following fails to parse:
Declare Cursor1 Cursor For 'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name' Open cursor1
That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.
I´m looking to create a select where I sum the daily_return by stock_code, and then I would like to have an additional column containing the most recent "rating" available by date
So if you where to execute the query below, the result would be:
stock1 0.54 3 stock2 0.05 1
Here is what I have so far:
DECLARE @stock_returns TABLE ( stock_code VARCHAR(10) NOT NULL, date1 DATE NOT NULL, daily_return NUMERIC(10, 2) NOT NULL, rating numeric (6,0) not null);
I have an SQL Select statement that I need to add a column to called SalePrice, the SalePrice column needs to be calculated by adding together the values of 12 columns, then multiplying that value by the value in a another column to calculate margin. My issue is that I can only get it to add 7 column values together, if I add any more columns to the equation it just returns and null result. My DB is SQL 2005 Express SP2. My select statement is below: SELECT dbo.MFG_DATA_Machines.ID, dbo.MFG_DATA_Machines.MachineName, dbo.MFG_DATA_Parts_CPU.PartDescription AS CPU, dbo.MFG_DATA_Parts_CPU.PartCost AS CPUCost, dbo.MFG_DATA_Parts_Motherboard.PartDescription AS Motherboard, dbo.MFG_DATA_Parts_Motherboard.PartCost AS MotherboardCost, dbo.MFG_DATA_Parts_RAM.PartDescription AS RAM, dbo.MFG_DATA_Parts_RAM.PartCost AS RAMCost, dbo.MFG_DATA_Parts_HDD.PartDescription AS HDD, dbo.MFG_DATA_Parts_HDD.PartCost AS HDDCost, dbo.MFG_DATA_Parts_OpticalDrive.PartDescription AS OpticalDrive, dbo.MFG_DATA_Parts_OpticalDrive.PartCost AS OpticalDriveCost, dbo.MFG_DATA_Parts_Video.PartDescription AS Video, dbo.MFG_DATA_Parts_Video.PartCost AS VideoCost, dbo.MFG_DATA_Parts_OS.PartDescription AS OS, dbo.MFG_DATA_Parts_OS.PartCost AS OSCost, dbo.MFG_DATA_Parts_Modem.PartDescription AS Modem, dbo.MFG_DATA_Parts_Modem.PartCost AS ModemCost, dbo.MFG_DATA_Parts_FloppyDrive.PartDescription AS FloppyDrive, dbo.MFG_DATA_Parts_FloppyDrive.PartCost AS FloppyDriveCost, dbo.MFG_DATA_Parts_CardReader.PartDescription AS CardReader, dbo.MFG_DATA_Parts_CardReader.PartCost AS CardReaderCost, dbo.MFG_DATA_Parts_PowerSupply.PartDescription AS PowerSupply, dbo.MFG_DATA_Parts_PowerSupply.PartCost AS PowerSupplyCost, dbo.MFG_DATA_Parts_CaseType.PartDescription AS CaseType, dbo.MFG_DATA_Parts_CaseType.PartCost AS CaseTypeCost, dbo.MFG_DATA_Machines.Notes, dbo.MFG_DATA_Machines.MarginPercent, dbo.MFG_DATA_Machines.PriceOverride, (dbo.MFG_DATA_Parts_CPU.PartCost + dbo.MFG_DATA_Parts_Motherboard.PartCost + dbo.MFG_DATA_Parts_RAM.PartCost + dbo.MFG_DATA_Parts_HDD.PartCost + dbo.MFG_DATA_Parts_OpticalDrive.PartCost + dbo.MFG_DATA_Parts_Video.PartCost + dbo.MFG_DATA_Parts_OS.PartCost + dbo.MFG_DATA_Parts_Modem.PartCost + dbo.MFG_DATA_Parts_FloppyDrive.PartCost + dbo.MFG_DATA_Parts_CardReader.PartCost + dbo.MFG_DATA_Parts_PowerSupply.PartCost + dbo.MFG_DATA_Parts_CaseType.PartCost) * ((dbo.MFG_DATA_Machines.MarginPercent + 100) / 100) AS SalePrice FROM dbo.MFG_DATA_Machines LEFT OUTER JOIN dbo.MFG_DATA_Parts_CaseType ON dbo.MFG_DATA_Machines.CaseType = dbo.MFG_DATA_Parts_CaseType.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Motherboard ON dbo.MFG_DATA_Machines.Motherboard = dbo.MFG_DATA_Parts_Motherboard.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Video ON dbo.MFG_DATA_Machines.Video = dbo.MFG_DATA_Parts_Video.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_RAM ON dbo.MFG_DATA_Machines.RAM = dbo.MFG_DATA_Parts_RAM.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_PowerSupply ON dbo.MFG_DATA_Machines.PowerSupply = dbo.MFG_DATA_Parts_PowerSupply.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_OS ON dbo.MFG_DATA_Machines.OS = dbo.MFG_DATA_Parts_OS.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_OpticalDrive ON dbo.MFG_DATA_Machines.OpticalDrive = dbo.MFG_DATA_Parts_OpticalDrive.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Modem ON dbo.MFG_DATA_Machines.Modem = dbo.MFG_DATA_Parts_Modem.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_HDD ON dbo.MFG_DATA_Machines.HardDisk = dbo.MFG_DATA_Parts_HDD.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_FloppyDrive ON dbo.MFG_DATA_Machines.FloppyDrive = dbo.MFG_DATA_Parts_FloppyDrive.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_CPU ON dbo.MFG_DATA_Machines.CPU = dbo.MFG_DATA_Parts_CPU.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_CardReader ON dbo.MFG_DATA_Machines.CardReader = dbo.MFG_DATA_Parts_CardReader.ID
All- Please advise how to configure a gridview so that when introducing non-editable fields to the select statement, the edit function doesn't crash with a "Procedure or function (UpdateCommand) has too many arguments specified." The scenario: Update works find when I have a gridview based on the following SPs:
SELECT: 1 ALTER PROCEDURE dbo.H2SelectCommand 2 AS 3 SET NOCOUNT ON; 4 SELECT headcount_id, person_id, act_session_id, no_answer 5 FROM headcount 6
INSERT:
1 ALTER PROCEDURE dbo.H2UpdateCommand 2 ( 3 @person_id smallint, 4 @act_session_id smallint, 5 @no_answer bit, 6 @Original_headcount_id smallint, 7 @headcount_id smallint 8 ) 9 AS 10 SET NOCOUNT OFF; 11 UPDATE [headcount] SET [person_id] = @person_id, [act_session_id] = @act_session_id, [no_answer] = @no_answer WHERE (([headcount_id] = @headcount_id)); 12
HOWEVER, when I alter the select command so that it includes a column (person.person_name) from a parent directory like this:
1 ALTER PROCEDURE dbo.H2SelectCommand 2 AS 3 SET NOCOUNT ON; 4 SELECT headcount_id, person.person_name, headcount.person_id, act_session_id, no_answer 5 FROM headcount 6 7 INNER JOIN person 8 ON headcount.person_id = person.person_id
...and then proceed to do an Edit/Update operation, I get the pesky "Procedure or function H2UpdateCommand has too many arguments specified" message. The only obvious thing I could think to try was to configure the new column in the gridview to be Read Only, but this didn't help. Do I have to alter the Update SP to somehow account for the new field, even though the warning already says that there's too many arguments in it? Any ideas on how to fix this error would be appreciated! -Kurt
I would like to write 1 proc that can take additional criteria if its sent in. An example is:
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join ( select VendorPackageId from ValidVendorPackages where Vendor = @VENDOR and Sitecode = @SITECODE and PackageType = @PACKAGETYPE )HB on HA.VendorPackageId = HB.VendorPackageId and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_1 and ValidItemType = @VALIDITEMTYPE_1 and ItemValue = @ITEMVALUE_1 )
Multiple @COMPONENTTYPE, @VALIDITEMTYPE,@ITEMVALUE can be sent in. Instead of making multiple procs or copying the proc multiple times with an if statement at the top checking the number of parameters that aren't =''. Is there a way to exectue:
and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_1 and ValidItemType = @VALIDITEMTYPE_1 and ItemValue = @ITEMVALUE_1 ) and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_2 and ValidItemType = @VALIDITEMTYPE_2 and ItemValue = @ITEMVALUE_2 ) and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_3 and ValidItemType = @VALIDITEMTYPE_3 and ItemValue = @ITEMVALUE_3 )
Ignoring the 2nd 2 selects if @COMPONENTTYPE_2, @VALIDITEMTYPE_2,@ITEMVALUE_2 and @COMPONENTTYPE_3, @VALIDITEMTYPE_3,@ITEMVALUE_3 are = ''
as per my client requirement we need subtotal of 'Amount' group by 'OrderNo'. column so am writing a select statement with WHERE condition and I would like to have another column called SUBTOTAL in the result set (select statement result) with subtotals for that order Number as below
Tell me please how to select from a table only rows with different value of one of fields (n_pr field). But I need also to select some other fields where the values can be different for one n_pr field's value and select only first value of them. For getting list of n_pr field's values I can use derived table of query with DISTINCT operator but I don't know how to join with other fields of the table to get only one row for each value of joinning field n_pr. I am using SQL Server 2000 and this DBMS can use TOP operator to select only one row but all the ways I've tryed affect all the query and I get one row at all instead of one for each n_pr field's value. Also I need in this query to connect one more table. So for efficiency it better to do it in one query or in one stored procedure that return a recodset as it needs.
I want to select rows that have been amended or deleted and want to use the amend date as the condition.all amended rows have a flag of 1 and deleted ones have a flag of 2 i.e select * from table where log_changed > '2008-06-23' and log_changed < '2008-06-24' will display all the rows that were amanded on the 2008-6-23 and with a flag of 1 or 2
but i also want the query to return the original row that was amended and has a flag of 0.the original row has a null log_changed field.
the rows that must be returned must be the ones amended or deleted ones on a specific date but with the original ones as well.
I am very new to db and sql. I have a populated table and would like to run a query that brings up the last 20 rows. Here is what I have now...do I need a WHERE statement?
SELECT
d.cache_diff AS $<scachediff>, d.cache_name AS $<scachename>, d.cache_type AS $<scachetype>, d.cache_last_found AS $<cachelastfound>, d.cache_id AS $<cache_view_link>, d.cache_id AS $<log_find_link>
Is it possible to write a stored procedure to select records that start with a passed string without using dynamic sql. E.g. SELECT EmpName FROM Employee WHERE EmpName LIKE 'John%'
How does write the above query as a stored procedure (not dynamic sql). The 'John' should be received as argument to the procedure.
I have a view that I want to find all the rows that have a matching itemid and have more than 3 rows in them and group them by the itemid. I am not quite sure how to do this. Any ideas? ~mike~
I'm having a little bit of trouble trying to figure out how to do this query, right now I have:
SELECT I.AppItemId, P.ProductID, P.PartNum, P.Relist, I.AppUserId FROM ProductsToRelist I join Products P on P.ProductID = I.AppSKU WHERE P.Relist = 1 and I.AppStatus = 5 and Not I.AppItemId is Null
ProductID is the primary key for the Products table, and a product can be in the ProductsToRelist table many times but each row would have a unique AppItemId. I know that I need to use Distinct or a different kind of join, but I'm not sure which. How would you suggest to do this?
