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)
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
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 am trying to do a nested count and cannot seem to get it to work. I do realize I cannot do this within the expression so I have been trying to use the code block (report > report properties > code tab) to do this and I have zero experience with VB.
This is for a PO delivery report. The first expression is just counting how many lines on an individual PO are late divided by total number of lines on the PO. In second expression I would like to count how many POs that have a late line on it.
Here is what I have so far.
Expression 1 with count function (this work properly): =iif(Fields!DS_Desired_Recv_Date.Value is nothing, ((Count(IIF(Fields!LAST_RECEIVED_DATE.Value > Fields!DESIRED_RECV_DATE.Value,1,Nothing))/Count(Fields!LINE_NO.Value))), ((Count(IIF(Fields!ACTUAL_RECV_DATE.Value > Fields!DS_Desired_Recv_Date.Value,1,Nothing))/Count(Fields!LINE_NO.Value))))
Code block: Private Shared count as Integer count=0 Public Function IncrementCount() As String count = count +1 IncrementCount = CStr(count) End Function
Hiya, I have a need for a complex SQL statement to provide reporting information, but the SQL is way over my head and although I have some of the elements, I can't seem to pull them together to create a working SQL statement. My database structure is as outlined below:
and then for A2, etc (although missing out A3 as it is a text item, but I can deal with that via ASP code beforehand). I would expect to have to hit the DB for each of the IRVitems (so once for getting the data for A1, againfor A2, etc).
I was provided a great bit of code to use SUBSTRING and GROUP BY to get this data (thanks zuomin), but I didn't consider the possible text values or numbers >9 when defining what I was trying to do (to be fair, they're new requirements):
SELECT SUBSTRING(IRVvalues, 1, 1) AS Value, COUNT(ID) AS Count FROM tblIRV GROUP BY SUBSTRING(IRVvalues, 1, 1) ORDER BY value
which returns like this:
Value Count 1 187 2 163 3 2
Can I do a similar query to get all the info I need in one go? I saw the articale on a user-defined 'split' function (http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput&referringTitle=Home) that can be used to split up the IRVvalues string to access the position I need in the array, but I'm a little clueless on how to then embed that into a SQl statement. Can anyone point me in the right direction please?
I have the following table Region Table ID ParentID RegionName
RestaurantTable ID RestaurantName RegionID
What i tried to do is count the number of restaurants by specific regionname. My current query is Select RegionID, RegionName, count(*) as RestaurantNo From Region Inner Join Restaurant On Region.ID = Restaurant.RegionID
However I only get the results below RegionID RegionName RestaurantNO 1 A1 0 2 A1.1 2 3 A1.2 1 4 A1.3 0
Where A1.1 , A1.2, and A1.3 are children of A1 in Region table The result is not correct due to A1 should have 3 in RestaurantNo due to it contains A1.1 , A1.2 and A1.3 Could anyone help me to solve this problem. Thank you
I have added some SQL to an Access form which updates the dbo_BM_Map table when the user hits the Apply button. There is a temp table with various fields, two being "Chapter_No" and "Initial_Mapping_Complete" which the update is based on.
I want this update to only apply to chapters that only have one name in the "Initial_Mapping_Complete" column. If a chapter has more than one then the update should ignore it. The attached screengrab shows you. The update should ignore chapter 19 as there are two people (Jim and James) in the Initial_Mapping_Complete field. Here is my code.
pdate dbo_BM_Map inner Join Temp_Progression_Populate on dbo_BM_Map.Product_ID = Temp_Progression_Populate.Product_ID Set dbo_BM_Map.Initial_Mapping_Complete = Temp_Progression_Populate.Initial_Mapping_Complete Where dbo_BM_Map.Chapter_No = Temp_Progression_Populate.Chapter_No And Temp_Progression_Populate.Initial_Mapping_Complete in (Select count(Initial_Mapping_Complete), Chapter_No from Temp_Progression_Populate Group by Chapter_No Having Count(Initial_Mapping_Complete) = 1)
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
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?
Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed' From Project WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees, ( SELECT COUNT(gifts.donor_id) AS Count2 FROM gifts WHERE gifts.donor_id = ed.id_number ) subquery2
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
Hello, I would like to count the number of items in a table. I used the following code:1 Dim Comments As Integer 2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString()) 3 Dim SqlCommand As New SqlCommand("SELECT COUNT(CommentID) FROM Comments WHERE ThemeID = '3', cnn") 4 5 cnn.Open() 6 Comments = SqlCommand.ExecuteScalar() 7 cnn.Close() But this only gives me the error message "ExecuteScalar: Connection property has not been initialized." Can anyone help me with this? Thanks
I need to select total number of rows from my data base table....here is what ive been trying....I know it wrong...but maybe someone can fix it. Thank you very much. Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As BooleanDim MyConn As New Data.SqlClient.SqlConnection(ConnectionString) Dim cmd As New Data.SqlClient.SqlCommand("Select count * from ClassifiedAds", MyConn)Dim dr As Data.SqlClient.SqlDataReader 'cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@UserName", textbox_username.Text)) ' cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@Password", textbox_password.Text)) cmd.Connection.Open() dr = cmd.ExecuteReader() dr.Read() If dr.HasRows Then Label_totalclassifieds.Text = dr.Read Return True Else dr.Close() cmd.Connection.Close() Return False End If End Function
I'm new to SQL. For a statistic application, I wish know the subtotal of lines pro region (Mitte, ost, west, ost, etc).
How can I do that?
A lot of thx for your help and time, Regards, Dominique
Code:
SELECT distinct case when ANZSUCHEN.KANTON = '' then 'nicht_zugeteilt' when ANZSUCHEN.KANTON = '----------------------------------' then 'nicht_zugeteilt' when ANZSUCHEN.KANTON = 'AG' then 'mitte' when ANZSUCHEN.KANTON = 'AI' then 'ost' when ANZSUCHEN.KANTON = 'AR' then 'ost' when ANZSUCHEN.KANTON = 'BE' then 'bern' when ANZSUCHEN.KANTON = 'BL' then 'mitte' when ANZSUCHEN.KANTON = 'BS' then 'mitte' when ANZSUCHEN.KANTON = 'FR' then 'west' when ANZSUCHEN.KANTON = 'GE' then 'west' when ANZSUCHEN.KANTON = 'GL' then 'ost' when ANZSUCHEN.KANTON = 'GR' then 'ost' when ANZSUCHEN.KANTON = 'JU' then 'west' when ANZSUCHEN.KANTON = 'LU' then 'mitte' when ANZSUCHEN.KANTON = 'NE' then 'west' when ANZSUCHEN.KANTON = 'NW' then 'mitte' when ANZSUCHEN.KANTON = 'OW' then 'mitte' when ANZSUCHEN.KANTON = 'SG' then 'ost' when ANZSUCHEN.KANTON = 'SH' then 'ost' when ANZSUCHEN.KANTON = 'SO' then 'mitte' when ANZSUCHEN.KANTON = 'SZ' then 'mitte' when ANZSUCHEN.KANTON = 'TG' then 'ost' when ANZSUCHEN.KANTON = 'TI' then 'west' when ANZSUCHEN.KANTON = 'UR' then 'mitte' when ANZSUCHEN.KANTON = 'VD' then 'west' when ANZSUCHEN.KANTON = 'VS' then 'west' when ANZSUCHEN.KANTON = 'ZG' then 'mitte' when ANZSUCHEN.KANTON = 'ZH' then 'ost' end as region, (SELECT count(*) FROM ANZSUCHEN WHERE ANZSUCHEN.KANTON = 'FR') FROM ANZSUCHEN GROUP BY ANZSUCHEN.KANTON ORDER BY region
Results:
Code:
region (No colomn name) bern34 mitte34 nicht_zugeteilt34 ost34 west34