SELECT SUM (dbo.HOLDING.Shares_Par_Value_Qty * dbo.ASSET.Current_Prc) AS MarketValue FROM dbo.HOLDING INNER JOIN dbo.ASSET ON dbo.HOLDING.Property_Num = dbo.ASSET.Property_Num Group by dbo.HOLDING.Account_ID
Account_ID is the same in both the queries ie in both the tables Holding and Account.
I need the output like this
Select Account_Id, Branch_Cd, MarketValue from -------
But MarketValue should be calculated exactly in the above method. How do I combine these two queries. I need it asap. Help me out.
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
What's worng, please help? SELECT TTarea,personel,Date FROM person_table WHERE TTarea = (SELECT TTarea FROM TTarea_table WHERE Center='CENTER_office') I have many TTarea and I want to send back from inner SELECT statement but give an error that inner select statement don't return many result.I want to return many result and I display many TTarea in the CENTER_office
SELECT Tbl_Region.REGION, [NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS], [NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS], [TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS], [TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS] FROM (((Tbl_Region LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg, Count(qry_New_Members_HMO_All_Regions_1.CONTRACT_N UM) AS [# of New Members] FROM (SELECT tbl_hmo.Reg, tbl_hmo.CONTRACT_NUM FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null)) GROUP BY tbl_hmo.reg, tbl_hmo.CONTRACT_NUM
) AS qry_New_Members_HMO_All_Regions_1
GROUP BY qry_New_Members_HMO_All_Regions_1.reg ) AS NEW_HMO_CONTRACTS ON Tbl_Region.REGION = [NEW_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_New_Members_HMO_All_Regions_1.reg, Count(qry_New_Members_HMO_All_Regions_1.MEMBER_NUM ) AS [# of New Members] FROM (SELECT tbl_hmo.reg, tbl_hmo.MEMBER_NUM FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM WHERE (((tbl_hmo_History.MEMBER_NUM) Is Null)) GROUP BY tbl_hmo.Aff_Area, tbl_hmo.MEMBER_NUM
) AS qry_New_Members_HMO_All_Regions_1 GROUP BY qry_New_Members_HMO_All_Regions_1.reg) AS 4_NEW_HMO_MEMBERS ON Tbl_Region.REGION = [4_NEW_HMO_MEMBERS].reg) LEFT JOIN (SELECT qry_Termed_Contracts_HMO_All_Regions_1.reg, Count(qry_Termed_Contracts_HMO_All_Regions_1.CONTR ACT_NUM) AS [# of Termed Contracts] FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM WHERE (((tbl_hmo.CONTRACT_NUM) Is Null)) GROUP BY tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM ) AS qry_Termed_Contracts_HMO_All_Regions_1 GROUP BY qry_Termed_Contracts_HMO_All_Regions_1.reg) AS TERMED_HMO_CONTRACTS ON Tbl_Region.REGION = [TERMED_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_Termed_Members_HMO_All_Regions_1.reg, Count(qry_Termed_Members_HMO_All_Regions_1.MEMBER_ NUM) AS [# of Termed Members] FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM WHERE (((tbl_hmo.MEMBER_NUM) Is Null)) GROUP BY tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM ) AS qry_Termed_Members_HMO_All_Regions_1 GROUP BY qry_Termed_Members_HMO_All_Regions_1.reg) AS TERMED_HMO_MEMBERS ON Tbl_Region.REGION = [TERMED_HMO_MEMBERS].reg;
error: Server: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'FROM'. Server: Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'AS'. Server: Msg 156, Level 15, State 1, Line 18 Incorrect syntax near the keyword 'AS'. Server: Msg 156, Level 15, State 1, Line 24 Incorrect syntax near the keyword 'AS'. Server: Msg 156, Level 15, State 1, Line 31 Incorrect syntax near the keyword 'AS'.
I am trying to get some functionality from nested IF's witin a SQL Select Statement. I do not want to create a stored procedure as I have another program that must use select statements that I will be using once I have this query written. Below is my code.
SELECT Sales.OrderID, Buyers.Userid, Buyers.Email, Sales.ShippingCo, IF (Addresses.Company IS NULL OR Addresses.Company = '') BEGIN IF ( RTRIM(LTRIM(Addresses.FirstName)) IS NULL RTRIM(LTRIM(Adresses.LastName)) AS CompanyOrName, ELSE RTRIM(LTRIM(Addresses.FirstName)) & ' ' & RTRIM(LTRIM(Adresses.LastName)) AS CompanyOrName, END ELSE Addresses.Company AS CompanyOrName, END
All I am trying to do is join the first and last names as the company in my table if the company doesn't exist, and then only display the last name if the first name is null.
I keep getting the error "Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'IF'."
I know I am close to geting this to work, but for some reason googling for tutorials on writing IF statements isn't helping out.
Thanks ahead of time for any help. It will be greatly appreciated.
Hi all,I have the following databasedesign :www.marinescu.dk/databasedesign.pdfwhich i have a nested SELECT on but i need some more information which idon't know how to retrieve. I have the following SELECT :SELECT DISTINCT Resource.ResourceID, Localized.ResourceValue,Localized.Font, Resource.ResourceName, Resource.Comment, Type.TypeName FROMLocalized, Resource,Type WHERE Localized.ResourceID = Resource.ResourceIDAND Resource.TypeID = Type.TypeID ORDER BY Resource.ResourceIDFor some Resources there are Rules. I will like to have a new column namedRulesText in my query where there will be shown the RuleText if there is anyfor that particular Resource.Could anybody help me here ????Best RegardsMihai Marinescu
HI All, I have what is most likely a simple MS SQL query problem (2005).I need to add two computed columns to a result set, both of those columns are required to provide the count of a query that contains a parmamter. (The id of a row in the result set) 3 Tables (Showing only the keys)t_Sessions-> SessionID (Unique) t_SessionActivity-> SessionID (*)-> ErrorID (Unique) t_SessionErrors-> ErrorID (1) I need to return something like (Be warned the following is garbage, hopefully you can decifer my ramblings and suggest how this can actualy be done) SELECT SessionID, (SELECT COUNT(1) AS "Activities" FROM t_SessionActivity WHERE t_SessionActivity.SessionID = t_Sessions.SessionID), (SELECT COUNT(1) AS "Errors" FROM dbo.t_Sessions INNER JOIN dbo.t_SessionActivity ON dbo.t_Sessions.SessionID = dbo.t_SessionActivity.SessionID INNER JOIN dbo.t_SessionErrors ON dbo.t_SessionActivity.ErrorID = dbo.t_SessionErrors.ErrorID WHERE t_SessionActivity.SessionID = t_Sessions.SessionID)FROM t_Sessions Any help greatfully received. Thanks
I need help nesting select statements. Here's what I'm trying to do:
select (select e.emp_name_lf as employee, e.emp_id from employee e, install_payroll_detail ipd where e.emp_id = ipd.emplno) e.emp_name_lf as username from employee e, install_payroll_master ipm where e.emp_id = ipm.entered_by
I just want one row with both the employee and username, however I cannot get the syntax. Any help is greatly appreciated.
I have been struggling over the following problem for a few days... i was wondering if anyone could shed some light...!
I have the following query:
SELECT Field1, Field2 FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE Table2.Field1 = ( SELECT TOP 1 Field1 FROM Table2 WHERE Field3='X' ORDER BY Date1) AND Table2.Field2 = ( SELECT TOP 1 Field2 FROM Table2 WHERE Field3='X' ORDER BY Date1)
Is there a better way to do this. I was thinking of something very similar to the below query (Which doesnt work):
SELECT * FROM Table1 A INNER JOIN Table2 B ON (A.ID=B.ID) INNER JOIN ( SELECT TOP 1 * FROM Table2 WHERE Field3='X' ORDER BY Date1 ) C ON (A.ID=C.ID) WHERE B.Field1 = C.Field1 B.Field2 = C.Field1
1)table of customers: CustomerName, CustomerId, CustomerAddress 2)table of orders: OrderId, CustomerId, OrderAmount
I would like to have a query that returns everything from the customer table and add one column that has the amount of orders the customer has made, this is what I have so far:
CREATE PROCEDURE dbo.GetAllCutomerInfo AS
DECLARE @OrderCount int
SELECT CustomerName, CustomerId, CustomerAddress, (SELECT COUNT(OrderId) FROM Cust_Orders WHERE CustomerId= CustomerId) FROM Customers ORDER BY CustomerName
Can you add a variable:
CREATE PROCEDURE dbo.GetAllCutomerInfo AS
DECLARE @CustID int
DECLARE @OrderCount int
SELECT CustomerName, @CustID=CustomerId, CustomerAddress, (SELECT COUNT(OrderId) FROM Cust_Orders WHERE CustomerId= @CustID) FROM Customers ORDER BY CustomerName
Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:
OPBal| Receipt| IssTrns| Transfer| ClBal
SELECT dbo.inventory.location, dbo.inventory.itemnum, (select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt, ( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer, ( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal, ( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal, ( sum(matusetrans.linecost) ) As IssTrnf FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )
WHERE dbo.inventory.location = dbo.matusetrans.storeloc AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid
OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc
OR dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost), dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost
hi all, i have speed issue on displaying 4k line of records using temp table.. before this it works fine and fast.. but maybe when i starts joining group by it loads slower.
SELECT DISTINCT customlotno, itemid, ItemName, Ownership, TotalCTNInPlt, TotalCarton, sum(CartonPcs) AS CartonPcs, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate INTO #ByItemID FROM ( SELECT * FROM tblItemdetail )AS L1 GROUP BY customlotno, itemid, ItemName, ownership, TotalCTNInPlt, TotalCarton, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate
SELECT * FROM #ByItemID ORDER BY CustomLotNo
DROP TABLE #ByItemID
---------------------------- or maybe just use something like nested SELECT like this, but cannot work:-
select customlotno, itemid, locid(
select * from tblitemdetail where customlotno='IN28606000'
In a SQL db we have we get the following error when just doing a simple select query against the view. Msg 217, Level 16, State 1...Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
No changes have been made to triggers or stored procedures recently and all was good prior to that.I understand that if my triggers loop this error will occur. But the select query does not fire any triggers functions or any other items. and the select worked with no issues last week.
I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.
hey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN
So I have another query I can't seem to function the way I was hoping. I've learned a lot in this past month or so but I've hit another challenge. Anyways what I'm trying to do is when a user/student wants to add a new major I want to show a list of majors that are NOT already in his/her profile. So basically if I had a full list of majors: Accounting Computer Science Mathematics
and the user already had Mathematics in his/her profile I'd like it to display only:
Accounting Mathematics
Below is the layout of the tables, my attempt at the query, and then below that some example data.
Code Snippet
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[Majors] Script Date: 04/17/2008 22:38:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Majors]( [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Majors] PRIMARY KEY CLUSTERED ( [MajorID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MajorDisciplines]( [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID] GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID]) REFERENCES [dbo].[Majors] ([MajorID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MajorDisciplines]( [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID] GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID]) REFERENCES [dbo].[Majors] ([MajorID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
SELECT MajorID FROM Majors majs WHERE majs.MajorID NOT IN (SELECT majDis.MajorID FROM MajorDisciplines majDis WHERE majDis.MajorDisciplineID NOT IN (SELECT sMajors.MajorDisciplineID FROM Student_Majors sMajors WHERE sMajors.StudentID = 0)) dbo.Majors MajorID Accounting Computer Science Mathematics
Oh also for the MajorIDs I don't want it to return duplicates such as the Accounting in MajorDisciplines...which I was hoping my query wouldn't do but it returns absolutely random data...
I need to perform an update where there are multiple scenarios which determine the value that is entered. Below is a sort've psuedo code of how it needs to be.
Update MyTable SET MyColumn = CASE WHEN MyCol1 = 'Value1' Then NewValue Else WHEN MyCol1 <> 'Value1' And MyCol2 = 'Active' Then 'Value1'
In the scenario where MyCol1 <> Value1 and MyCol2 <> 'Active' then no update would occur and the original value would remain intact.
I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:
Oracle Version:
Code Snippetselect avg(days) as days from ( select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data where dm_type = 'prime' and dm_closedate <= '31-dec-2007' and dm_closedate >= '1-dec-2007' and program = 'aads' group by dm_number)
SQL Version:
select round(abs(avg(days)), 0) as days from (select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days from dm_data dm, ProgramXref px where px.Program_Name = 'aads' and dm.Program_Id = px.Program_Id and dm.dm_type = 'prime' and dm.dm_closedate <= '31-dec-2007' and dm.dm_closedate >= '1-dec-2007' group by dm.dm_number)
In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know. What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in. This is the relationship of the data. I have at the highest group, an industry code, then a customer, then the part and then the fiscal year. What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC 001 - Signposts M12345 Part 1 2006 5 6 2 2007 0 3 1
Hi, i wanted to take all the orderID that fall between a certain date. I was thinking of using this syntax... Dim sql as string = "SELECT orderID " & _ "FROM orders " & _ "WHERE orderDate BETWEEN 11/11/2007 AND 15/11/2007" in my database i set the date to nvarchar, not date... so will this syntax works?? thx
Hi, I have a query as follows:select transactionDate, (select count(transID) from ......)I am having a problem which is that dates are not shown uniquely.For example,oct 1, 2005 00:12:00 - 12oct 1, 2005 01:30:00 - 12oct 1, 2005 01:13:00 - 12I want only to have:oct 1, 2005 01:13:00 - 12The select is working on time and not on a date, can you help me please?thank you.
I need a hand in this script. SELECT a.id ,value = CASE when (x1-x0) = 0 then ((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0)) ELSE ((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0)) END ,value2 = CASE when (x1-x0) = 0 then ((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0)) ELSE ((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0)) END FROM table_name a where value > 0 ORDER BY VALUE
The problem I'm having is in the where clause. Because the value is not a column name sql server is giving me an error. I need to evaluate the value returned. How can I get around this problem? Any sort of advise will help! Thank you in advance!
hey friends! pleaze help, i am using sql server 2000.i have tried to fix my problem for more than five weeks just to solve for one problem and just still now it is unsolved, opps my due date is almost approaching, i don't know what to do more than what i did, i have search through the net, but still i did not get the correct answer to my problem, friends please just forward your answer to me, it may be best answer to my question . create table mytable( english varchar(120), tigrigna Nvarchar(120)) insert into mytable values('peace',N'sälam') insert into mytable values('kiss',N'samä ') insert into mytable values('to kiss each other',N'täsasamä ') then select * from mytable where english='peace'; this works fine but select * from mytable where tigrigna=N'sälam'; this doesnot work; select * from mytable where tigrigna='sälam'; this also doesnot work, so what should i do to select this unicode select statement?
tigrigna is one of the spoken language in east africa(ethiopia). Hopefully, i have joined this forum today, and just looking for your reply
proj_name res_name yr SumOfHours x Mike 2007 1 x Mike 2008 2 x Mike 2008 3
Here is table dbo._LastWeekTotalHours
proj_name res_name yr td_hours Week x Mike 2007 1 1 x Mike 2008 2 8 x Mike 2008 3 9
now here is what the output should look like
Lets assume the user well enter 8 for week number
proj_name res_name totalHours LastWeekHours x Mike 6 2 Y AJ .. €¦.
I am trying to write a select statement for that:
Here is what I have:
SELECT a.proj_name, a.res_name,a.TotalHours,g.TotalHours as LastWeekhours FROM
Line 1( SELECT r.proj_name,r.res_name, SUM(r.td_hours) AS TotalHours FROM line 2 dbo._LastWeekTotalHours as r, _vw_Employee as e Line 3 WHERE e.RES_NAME = r.res_name and r.Week = 5 and r.yr = 2008 Line 4 GROUP BY r.proj_name, r.res_name ) as g
Line 5(SELECT r.proj_name, r.res_name, r.TotalHours Line 6 FROM Line 7 (SELECT proj_name, res_name, SUM(td_hours) AS TotalHours FROM line 8 dbo._LastWeekTotalHours line 9 GROUP BY proj_name, res_name ) r , line 10 (SELECT proj_name, res_name, SUM(SumOfHours) AS TotalHours FROM dbo.vw_TimebyProjectAll line13 GROUP BY proj_name, res_name )w )a
if i excute line 1 - 4 i will get the last week hour correctly if i excute line 5 - 13 i will get the totalhours correctly
my problem i can't put these two pieces work togather. feel free to re-write the select statment thanks
Code Snippet use master select top 25 percent id from ( select top 112 * from syscolumns ) d order by id desc
Script below returns 57 rows, must return 56!
Code Snippet use master select top 50 percent id from ( select top 112 * from syscolumns ) d order by id desc
Reproducable on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
and
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Could anyone from MS SQL server team clarify what's happened and WHAT TO DO?
Hi there friend...I'm in a midle of a program(vb.net). and i've got this problem, I've got do compare two tables from two diferent sql servers. Is this possible??? How can I do this???
I have a stored procedure in an Informix-database that returns a string. Its used in a SELECT statement like this. SELECT t1.id, t2.id, sp_name(t1.id, t2.id) FROM table1 t1, table2 t2
I want to write it in SQLserver. I have tried this syntax but get error. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 't'.
i run a sql command like this select * from table_name what i want is that if no. of records found are greater then 500 than it should stop the query and only show those 500 records dont go further and stop the process waiting for reply ashish bhatnagar
