I am using this below query to sum and select maximum values from table. I have converted the cost column here and how can I possibly sum the cost column?
select ID, MAX(Dates) Dates,'$ ' + replace(convert(varchar(100),
convert(money, Cost), 1), '.00', '') Cost, MAX(Funded) Funded from Application group by ID, Dates, Cost, Funded
Table definition:
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!!
Example:
ID| Code| ParentCode| InternalReference|
1 | M111| NULL | 1|
2 | AAA | M111 | 2|
3 | .... | .... | ....|
4 | AAB | M111 | 2000|
5 | M222 | NULL | 2001|
6 | ZZZ | M222 | 2002|
7 | .... | .... | .... |
8 | ZZA | M222 | 4000|
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?
How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?
View 3 Replies View RelatedHallo Cracks,
what I try is a little bit heavy, maybe, but I only miss the minimum/maximum fuction - or I didn´t found it; not here in the Forum and also not in the onlinehelp of the SQL Server.
What I try to do:
I have 2 columns in my table; a start- and an end-date. For this period of time between end and start i have to calculate the days for the years. Here my thoughts (for the current year):
Is the startdate <= 31.12.2004 and the enddate >= 1.1.2004 i have to calculate die datediff between max(1.1.2004/startdate) and min(31.12.2004/enddate)
like this sqlstatement:
SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS
varchar) + '-31-12' AS smalldatetime) AND dbo.Phases.phasenabschlussist >=
CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime)
THEN 365 ELSE 0 END AS Expr2,
FROM dbo.Phases
WHERE (phasenstart IS NOT NULL) AND (phasenabschlussist IS NOT NULL)
instead of 365 there must be the above calculation. Is start=3.1.2003 and end=30.1.2004 I expect as result only the 30 days in 2004.
thanks in advance and kind regards :-)
Cappu
hello all,
i have table - compmail in that i have compmailid, compmaildetail and compmaildate - columns
compmailid compmaildetail compmaildate
-------------------------------------------
94341 comp mailing list 2008-1-23 16:41:17.037
94445 comp mailing vital 2008-1-23 23:05:18.152
94592 comp mailing items 2008-1-24 10:48:08.077
108229 comp mailing sales 2008-1-27 15:13:37.207
108740 comp mailing usages 2008-1-27 16:44:17.517
108261 comp mailing stats 2008-1-27 14:42:55.780
so i want maxcompmailid for each date
so output will be like:
compmailid compmaildetail compmaildate
-------------------------------------------
94445 comp mailing vital 2008-1-23 23:05:18.152
94592 comp mailing items 2008-1-24 10:48:08.077
108740 comp mailing usages 2008-1-27 16:44:17.517
if i m doing:
select max(compmailid),compmaildetail,max(compmaildate) from compmail
group by compmaildetail
not giving me correct results which i want
can anyone have idea?
I am trying to get the minimum and maximum values from a field in SQL Server 2008 Express, but I cannot even get started because I keep getting this error that I cannot figure out.
View 6 Replies View Related
Hi,
I have a table with 3 colums and col1 and col2 are datetime columns as follows
ID Col1 Col2
----------------------------------------
1 D1 NULL
2 D2 D3
3 NULL D4
I want to retrive the ID along with the latest date from either of remaining columns.
Can any one let me know the query please??
~Mohan
Hi, I have something similar to the following: CREATE PROCEDURE dbo.MySproc @columnVal int = nullASBEGIN SELECT * FROM MyTable WHERE MyTable.column = @columnVal END If columnVal is not passed into the stored proc i want it to just select everything from 'MyTable' (without the WHERE clause)....how can I do this in as few lines of code possible?thanks
View 3 Replies View RelatedSo I have this table called "listings"... there are 100 unique listings with an integer ID for each.
I have another table called "ratings"... in there are multiple entries that have a listing_id field and a rating field. The rating field is a value from 0-10.
I want to select ALL "listings" from the listings table... and then sort based on the average number that the multiple rating fields in the ratings table has for that listing.
I CAN NOT figure it out!! Any help would be greatly appreciated. Please respond if I have not explained this properly. Thanks in advance.
I'm trying to understand why I can enter a query such as:
select 5,"random"
from customers;
and get two columns with 5 and "random" in every respective column field.Why don't I receive a syntax error ?
as i stated in the subject i want to gather the biggest five records from the table with sql, how can i do that?
View 5 Replies View RelatedCREATE TABLE product
( product_id integer
, translated_name NVARCHAR2(50) );
insert into product values(1,N'kenenisa')
insert into product values(2,N'Ethiopia')
Note that i have used N becuase it is unicode data
then
select * from product; this works fine.
select * from product where product_id=1; this also works fine,
but
select * from product where translated_name=N'kenenisa'; this doesnot work correctly, so how can i fix this problem ?
I need to access a table and return 3 values from it in the samerecordset - ie one iteration of the recordset will have 3 values fromthe same database, I have looked at sub queries but they dont seem tobe able to do what i want.I would be grateful for any guidanceS
View 1 Replies View RelatedHi,
I've got a table with trialsubscriptions. When someone orders a trialsubscription he has to fill in a form. With the values in the form I like to fill a database with some extra fields from the trialsubscription table. I get those extra fields with a datareader. But when I insert I can't use the same datareader (its an insert), I can't make two datareaders because I have to close the first one etc.
Does someone had the same problem and has some example code (or make some :-)) Some keywords are also possible!
Thanks!
Roel
DECLARE @Query varchar(8000)
Create Table
#tempCountRichard (Status varchar(50), Number Varchar(1000)
Set @Query = 'Insert Into #tempCountRichard
Select Count(Status),
[code]...
With the following SQL. When I select from the temp table I return the right count but my second column doesn't return anything.
Count Status
1010 2000
1111 2222
When I run the query that is being inserted into the the temp table I return the correct results
Count Status
1010 Pass
2000 Pass with Obs
1111 Fail
2222 None
how to select the data from the temp table exactly the same way it was inserted. As I need to select the exact same data from the insert.
I have a table 'wRelated' with the following columns
[related_id] [int]
[channel_id] [int]
[mui] [varchar]
[price_group_id]
[type_id] [int]
[related_mui] [varchar] (100)
[date_started] [smalldatetime]
[date_ended] [smalldatetime]
[date_entered] [datetime]
[deleted] [tinyint],
[rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.
[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'
I have tried Unions and temporary table inserts.
This has me stumped.
I have a table UnitRateItems with the following columns
ItemID, Description
and table UnitRates with the following columns
ItemID,Year,UnitRate
UnitRateItems and UnitRates have a primary-foreign key relationship on ItemID.
In UnitRates, there may be several rows with the same value for ItemID but a different value for Year. What I want to do is pick the row with the largest value of Year for a given ItemID. I want to do this for ALL the rows in the table (not just for a particular ItemID).
In other words, I want to return one row in UnitRates for each row in UnitRateItems. I want that one row to be the one with the highest Year for that ItemID.
It sounds simple, but I can't seem to figure out how to do it. The MAX function only works on a single column. I can get the MAX Year, but then I can't tell it "pick up the other values that go with the row with the MAX Year".
Any help would be greatly appreciated!
Hi All,
Here is my story, how to change a column called Flag_Status based on the maximum Updated date. i.e. i want to make Flag_Status be 1 for the records which have maximum Updated_date (current record) and the rest to make it 0. for example accountID 1 has three records updated, but only one is current the rest are historical, thus i want the history record to be Falg_status 0 and the current record be 1. Note that Inserted_Date and Updated_Date are created using SSIS Derived column During loading the source table, it helps me when each record is inserted into the Data warehouse.
Here is My source table Name: Source_table,
CREATE TABLE dbo.Source_table
(
AccountID INT PRIMARY KEY,
Price int,
Address varchar(30),
added DATETIME DEFAULT GETDATE(),
edited DATETIME DEFAULT GETDATE(),
Flag_Status Int Default 1
)
GO
Here is the Fact_table
CREATE TABLE dbo.Fact_table
(
Fact_table_Key Int Identity (1, 1) NOT NULL,
AccountID INT,
Price INT,
Address varchar(30),
added DATETIME DEFAULT GETDATE(),
edited DATETIME DEFAULT GETDATE(),
editor VARCHAR(64),
Flag_Status Int Default 1,
Inserted_Date DATETIME DEFAULT GETDATE(),
Updated_Date DATETIME DEFAULT GETDATE()
)
GO
Source_table:
AccountID Price Address added edited Flag_Status
---------------- --------- ------------- ----------- ------------- ------------------
1 10 xyz 01-2006 01-2006 1
2 14 abc 02-2006 02-2006 1
3 13 mno 03-2006 03-2006 1
Here is the fact table, table name= Fact_table
Fact_table_Key AccountID Price Address added edited Flag_Status Inserted_Date Updated_Date
-------------------- ------------- --------- ------------ ------- --------- ---------------- ------------------ -------------------
1 1 10 xyz 01-2006 01-2006 1 05-2006 NULL
2 2 14 abc 02-2006 02-2006 1 05-2006 NULL
3 3 13 mno 03-2006 03-2006 1 05-2006 NULL
4 1 17 ght 01-2006 06-2006 1 NULL 08-2006
5 2 18 dmc 02-2006 07-2006 1 NULL 08-2006
6 3 20 kmc 03-2006 09-2006 1 NULL 10-2006
7 1 19 xyz 01-2006 11-2006 1 NULL 12-2006
8 2 19 klm 02-2006 01-2007 1 NULL 02-2007
9 3 21 pqr 03-2006 03-2007 1 NULL 04-2007
Here is what i am thinking: But it gives me Wrong Flag_Status.
UPDATE Fact_table
SET Flag_Status =
CASE
WHEN (SELECT Max(Updated_Date) From Fact_table
WHERE AccountID IN (SELECT AccountID FROM Fact_table
Group By AccountID Having Count(AccountID)>1)) >
(SELECT Max(edited) From Source_table
WHERE Flag_Status = 1)
THEN 1
WHEN (SELECT AccountID From Source_table
Group By AccountID
Having Count(AccountID) =1) =
(SELECT AccountID From Fact_table
WHERE Updated_Date IS NULL)
THEN 1
ELSE 0
END
I have two tables, D and C, whose INTERSECT / EXCEPT values I'd like to insert in the following table I've created
CREATE TABLE DinCMatch (
servername varchar(50),
account_name varchar (50),
date_checked datetime DEFAULT getdate()
)
The following stmt won't work...
INSERT INTO DinCMatch
select servername, account_name from D
intersect
select servername, account_name from C
... as the number of supplied values does not match table definition.
I have Database Library, which has a lot of tables and we need 3 tables for quary:
Table Librarians: field ID, field Surname;
Table StudentCard: field ID, foreign key on table Librarians and other fields, which we don't use
Table TeacherCard: field ID, foreign key on table Librarians and other fields, which we don't use
Query: Select the librarian's surname, which gave the most count of books.
I know, how to resolve, when i took datas only from one table, e. g. TeacherCard
SELECT TOP 1 WITH TIES Librarians.LastName, MAX(Librarians.CountOfBooks) AS Books FROM
(SELECT L.LastName, COUNT(*) AS CountOfBooks FROM Libs L, T_Cards T
WHERE T.Id_Lib IN (SELECT L.Id)
GROUP BY L.LastName) AS Librarians
GROUP BY Librarians.LastName
ORDER BY MAX(Librarians.CountOfBooks) DESC
GO
I dont know, how to use datas from TeacherCard and from StudetnCard at the same time.
Hi,
How to select max of year-month in the Reporting Services. For example I have 3 fields :
1. Year-Month (format : yyyy-mm)
2. City
3. Amount Sales
In the bar chart graph, I have put the City as X-Categories, Amount Sales as Data
And I want to filter only the lastest year month in the database.
Can we do this without going to database and set the filter there ?
Thanks
best regards,
Tanipar
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.
“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.
My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.
I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.
DECLARE
@FileName varchar(50),
@Path varchar(50),
@SqlCmd varchar(1000)
= '',
@ASXCode varchar(5),
@Offset decimal(18,0),
[code]....
I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -
Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
how can i resolve this?
I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:
check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from
hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where
h.freight_wagon_serial_number = fw.freight_wagon_serial_number and
fw.freight_wagon_type = fwt.freight_wagon_type and
h.train_number = t.train_number) <
(select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where
lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))
The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.
I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.
I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies View RelatedDear 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.
How do I set the maximum vertical size of a table? I would like to set it based on total vertical size in inches or a maximum number of rows returned.
Thanks for any help.
Brad
i have a table with productID and OrderID. For ech product there are orders.
So for each productID there are lot Of OrderID's are present.
My data like
ProductID OrderID
1 12012
1 12447
1 12478
2 24154
2 21457 etc.......
so, i need to get the maximum count for a product.
i can do by using Temporary tables. but i need without using temporary tables.
please help me out.
thanks
-Praveen.
--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.
I inherited a table with this structure:
Value a Value b
x date a
x date b
x date c
y date d
z date e
z date e
z date f
Value a fields are one to many. The objective is to obtain the maximum date value for each unique a value.
Can any one help me, i'm building a dynamic database driven site using dreamweaver and MS SQL2000 andi'm haveing problem storing over 8000 characters in a table filed (IE: it wont let me!!) is there a special table field value that i need to set to get more characters in a table field or is this a limitation of SQL.
Any help or suggestions would be appreciated
Regards
B
I have a problem (who not?) with a function which i'm using in a view.This function is a function which calculates a integer value of adate. For example: '12/31/2004 00:00:00" becomes 20041231. This isvery handy in a datawarehouse and performes superfast. But here is myproblem.My calendar table is limited by a couple of years. What happens isthat sometimes a value is loaded which is not in the range of theCalendardate. What we want to do is when a date is loaded is that thisfunction insert a minimum date when date < minimum date and a maximumdate when date > maximum date.Yes i know you're thinking : This is datamanipulation and yes this istrue. But now we loose information in our cubes and reports by innerjoining. So if we can use a minimum and a maximum than a user wouldsay: "This is strange, a lot of values on 1980/1/1!" instead of "Ithink that i have not all the data!"GreetzHennie
View 2 Replies View Related