Stock Age Query To Accept Multiple Rows As A Result
Jan 22, 2014
I worked with someone else to create a query that gives us the age of a stock. How long it has been in the warehouse since the Purchase order date (without completely selling out). It does exactly what I want, the problem is that it only accepts 1 row as a result.
The error message I get is:
quote:Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So my question is; can this code be modified to pass it multiple SKUS and run a report on every item currently in stock?
SELECT TOP 1
@skuVar, CAST(GETDATE()-ReceivedOn AS INT) AS 'Age'
FROM
(SELECT pir.id AS id,aggregateQty AS aggregateQty,-qtyreceived as qtyreceived, (aggregateQty - qtyreceived) AS Diff, ReceivedOn AS ReceivedOn
,(
SELECT SUM (PurchaseItemReceive.qtyreceived)
FROM bvc_product pp
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
TABLE [dbo].[Tbl_Products]( [Product_ID] [int] IDENTITY(1,1) NOT NULL, [Product_Name] [nvarchar](50) NOT NULL, [Catagory_ID] [int] NOT NULL, [Entry_Date] [date] NOT NULL,
[Code] ....
I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )
But am getting a multiple result if the order purchase has more than 1 item :
SELECT DISTINCT B.Product_Name,A.AllPieceBoxes, A.BuyPrice,A.TotalPrice,A.BuyPrice FROM Tbl_Products B INNER JOIN Tbl_PurchaseHeader C ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A ON A.PurchaseOrder=C.purchaseOrder WHERE A.PurchaseOrder=3
My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:
"In the forums there is X results for the word X In the articles there is X results...."
And when the user click one of those lines, the list shows the specific results in that section.
My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows…
Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)
Working in Sql Server 2005,got 3 different table.Need to fetch the list of persons.A person can belong to different categories.When i am using inner join on tables i am getting the duplicate rows because a perosn can belong to different categories.I want that there should be only onoe row for the person and the different categories he belongs to can come up in single field as comma sepatated string Now the results are like this: firstname lastname adress category abc xyz aaa a abc xyz aaa b abc xyz aaa c I want like below: abc xyz aaa a,b,c I am thinking of using cursors in the stored procedure.Can you provide me the solution of this including the stored procedure..
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate ORDER BY t.topicid ASC,th.lastpostdate DESC Whose result set is as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 On modifying the query to:- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid) ORDER BY t.topicid ASC,th.lastpostdate DESC I get the result set as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
Well probably not that complex for some of you out there!
I need to work out the amount of stock which was sold between @datefrom and @dateto and how much we currently have on hand (to work out if we are over ordering etc). That's the pretty easy part but I also need to include a column which works out how many items have been sold 3 months prior to @datefrom (from invoiceline). The proc I have so far works out the items sold between 2 dates so basically what I need is another column which is the amount sold (QtySold) in the 3 months prior to datefrom
This is the basic part I have so far:
ALTER PROCEDURE [dbo].[rptstockholdinglevel] -- Add the parameters for the stored procedure here @datefrom datetime, @dateto datetime, @periodname varchar(50), @percentage int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here SELECT products.productname, @periodname AS periodname, isnull(SUM(invoiceline.qty),0) AS QtySold, products.qtyonhand AS OnHand, nominals.nominalname, productcategories.categoryname, productmanufacturers.manufacturername FROM productmanufacturers RIGHT OUTER JOIN invoices INNER JOIN invoiceline ON invoices.invoiceid = invoiceline.invoiceid RIGHT OUTER JOIN products ON invoiceline.productid = products.productid ON productmanufacturers.manufacturerid = products.manufacturerid LEFT OUTER JOIN nominals INNER JOIN productcategories ON nominals.nominalid = productcategories.salesnominal ON products.categoryid = productcategories.productcategoryid WHERE (invoices.invoicedate BETWEEN @datefrom AND @dateto) OR (invoices.invoicedate is null) GROUP BY products.productname, products.qtyonhand, productcategories.categoryname, nominals.nominalname, productmanufacturers.manufacturername HAVING (SUM(isnull(invoiceline.qty,0)) < products.qtyonhand) ORDER BY nominals.nominalname, productcategories.categoryname, productmanufacturers.manufacturername END
I'd be really grateful for any pointers as I'm just going round in circles on this one.
helo all...,i want to make procedure like:examplei have table: item (itemid,itemname,stock)orderdetail(no_order,itemid,quantity)itemmoment(itemid,itemname,stock)item table itemid itemname stock c1 coconut 2 p1 peanut 2orderdetail tableno_order itemid quantity 1 c1 5itemmoment tableitemid itemname stock c1 coconut 0 p1 peanut 0 when customer paid, his quantity in orderdetail decrease stock in item table..so stock in item table became:itemid itemname stock c1 coconut -3 p1 peanut 2it's not good, because stock may not minus...so i want to move -3 to itemmoment table..so stock in item table became:itemid itemname stock c1 coconut 0 p1 peanut 2and in itemmoment table became:itemid itemname stock c1 coconut 3 p1 peanut 0my store procedure like:ALTER PROCEDURE [dbo].[orders]( @no_order as integer, @itemid AS varchar(50), @quantity AS INT)ASBEGIN BEGIN TRANSACTION DECLARE @currentStock AS INT SET @currentStock = (SELECT [Stok] FROM [item] WHERE [itemid] = @itemid) UPDATE [item] SET [Stock] = @currentStock - @quantity WHERE [itemid] = @itemid COMMIT TRANSACTIONENDit's only decrease stock with quantity. i want move stock minus from item to itemmoment..can anyone add code to my store procedure?plss.. helpp.thxx....
Okay, here's an algorithm question for you TSQL gurus out there...
Due to circumstances beyond our control, our group has been tasked with a massive project and a very short timeline. And of course, timely completion is needed because our STOCK OPTIONS grants depend on this! And of course board meetings are always scheduled sooner than you expect.
Here's one of the killer questions we're trying to solve...
Given a table of employee ID's, associated supervisor ID's, and the amount of stock options given, how would you write a stored procedure to return, for any branch of the organizational tree, the sum of all the stock options in a particular branch?
What we need is something like : "sp_StockOptionsPerDepartment @SUPERVISORID=30" with a result : "1750".
Basically we're building an organizational chart of our company from this table, on the fly, and also counting up for certain branches of the org chart, the total stock options.
If a manager has two managers under him, and each sub-manager has three employees, then we want to know the total stock options that all 3+3+2+1 = 9 people possess. Basically it's the total pool of stock options for a department, or work group, or division, etc.
Got this to work for a small set of employees, but when we begin to scale up to entire departments, the query times out because it takes tooooo long...
Any ideas? ANY ideas at ALL would be helpful...
Comments (1) This is basically a tree-traversal algorithm, but conversion into SQL is not always so straightforward. Starting from an arbitrary root node, we must visit every child node underneath, walking all the way down to the leaves.
(2) We tried a brute force algorithm which is fast for smaller sets, but impossibly long for sets where we're dealing with hundreds of employees. Any cheats? Caching results as we go? Any ideas out there?
I have 3 tables. Stores, Dates and Transactions. I want to combine all Stores with all Dates in one table and then calculate Last Stock Quantity.
Stores London Paris Prague
Dates 1.1.2014 2.1.2014 3.1.2014
Transactions 1.1.2014 London 1000 1.1.2014 Paris 1300 1.1.2014 Prague 1500 2.1.2014 London 800 3.1.2014 Prague 1200
And result should look like this Last_Quantity should be Quantity for last date in Transactions table.
1.1.2014 London 1000 1.1.2014 Paris 1300 1.1.2014 Prague 1500 2.1.2014 London 800 2.1.2014 Paris 1300 2.1.2014 Prague 1500 3.1.2014 London 800 3.1.2014 Paris 1300 3.1.2014 Prague 1200
I am trying to have a query with the option for items to be null. (So users don't need to fill in the other fields if they choose not too) SELECT Tickets.TicketID, Tickets.UserID, Tickets.SystemID, Tickets.Title, Tickets.Description, Tickets.Software, Tickets.Date, Systems.OS, OS.OS AS OstitleFROM Tickets INNER JOIN Systems ON Tickets.SystemID = Systems.SystemID INNER JOIN OS ON Systems.OS = OS.osIDWHERE (Tickets.Title LIKE '%' + @title + '%') AND (Tickets.Software LIKE '%' + @software + '%') AND (Tickets.Description LIKE '%' + @descrip + '%') AND (Systems.OS = @osid) OR (@osid IS NULL)This works when i give the LIKE values % as a parameter. So they can choose to search by title + software but not description or description and nothing else etc etc etc. The problem is, the osid. If I give it a value it works but if i try to do null, *. or % it always displays every item in the databse ignoring any of the previous like statements. Anyone have an idea?
When I run the Query I get... Message="Parameterized Query '(@ID int,@EmpID int,@Photo nvarchar(260),@DoB smalldatetime,@Med' expects parameter @EmpID, which was not supplied."
I also get this when the parameter can be a string. I have set the parameter properties so that SourceColumnNullMaping to true (it was set as false - so hoped this might fix it!)
I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:
ITEM MASTER -------------- NCODE ITEMNAME 1 A 2 B 3 C 4 D 5 E
I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1
-------------------------------------------- FOR OFFICE - 1 -------------------------------------------- ITEMCODE ITEMNAME OFFICEID -------------------------------------------- 1 A 1 2 B 1 3 C 1 4 D NULL 5 E NULL
i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.
I'm trying to run a sql query using the core dotnetnuke database. I am tying to pull from two separate tables in the same database and return the results below: ------------------------------------
LastName | FirstName | City | PhoneNumber
Smith Joe New York (555)555-5555
------------------------------------
Last Name and FirstName are two separate columns in the DB_Users table, while City and PhoneNumber are both values of the same column DB_UserProfile.PropertyValue located in separate rows defined by an ID in the DB_UserProfile.PropertyDefinitionID column.
The UserProfile table looks something like this...
ID | PropertyDefinitionID | PropertyValue
1 27 New York 2 31 (555)555-5555
------------------------------------
This is what I have so far but it is not working out to well...
SELECT DB_Users.FirstName, DB_Users.LastName, DB_UserProfile.PropertyValue AS City, DB_UserProfile.PropertyValue AS PhoneNumber FROM DB_Users, DB_UserProfile WHERE DB_Users.UserID = DB_UserProfile.UserID AND DB_UserProfile.PropertyDefinitionID = 27 AND DB_UserProfile.PropertyDefinitionID = 31 ORDER BY DB_Users.LastName
Can someone please help with a query I have? Basically I want to return all rows in a table that have multiple date entries that are different. For example:
This security 1636 has two entries in the DB with different dates. They are lots of securities with multiple entries with the same date but I need a list of the ones with different dates. Any ideas please?
I am trying to do the following: Insert n rows into A Table called EAItems. For each row that is inserted into EAItems I need to take that ItemID(PK) and insert a row into EAPackageItems. I'm inserting rows from a Table called EATemplateItems. So far I have something like this: (I have the PackageID already at the start of the query). INSERT INTO EAItems(Description, Recommendation, HeadingID)SELECT Description, Recommendation, HeadingIDFROM EATemplateItems WHERE EATemplateItems.TemplateID = @TemplateID INSERT INTO EAPackageItems(ItemID, PackageID) ....
I have no idea how to grab each ITemID as it's created, and then put it into the EAPackageItems right away.
How can I run a single SP by asking multiple sales question eitherby using the logical operator AND for all the questions; or usingthe logical operator OR for all the questions. So it's alwayseither AND or OR but never mixed together.We can use Northwind database for my question, it is very similarto the structure of the problem on the database I am working on.IF(SELECT OBJECT_ID('REPORT')) IS NOT NULLDROP TABLE REPORT_SELECTIONGOCREATE TABLE REPORT_SELECTION(AUTOID INT IDENTITY(1, 1) NOT NULL,REPSELNO INT NOT NULL, -- Idenitifies which report query this-- "sales question" is part ofSupplierID INT NOT NULL, -- from the Suppliers tableProductID INT NOT NULL, -- from the Products table, if you choose--a ProductID, SupplierID is selected also by inheritenceCategoryID INT NOT NULL, -- from the Categories tableSOLDDFROM DATETIME NULL, -- Sold from which dateSOLDTO DATETIME NULL, -- Sold to which dateMINSALES INT NOT NULL, -- The minimum amount of salesMAXSALES INT NOT NULL, -- The maximum amount of salesOPERATOR TINYINT NOT NULL -- 1 is logical operator AND, 2 is OR)GOINSERT INTO REPORT_SELECTIONSELECT 1, 1, 2, 1, '1/1/1996', '1/1/2000', 10, 10000, 1 UNION ALLSELECT 1, -1, -1, 1, '1/1/1996', '1/1/2000', 10, 1000, 1You can ask all kinds of sales questions like:1-I want all employees that sold products from supplierID 1(Exotic Liquids), specifically the ProductID 2 (Chang) from theCategoryID 1 (Beverages) between Jan 1 1996 to Jan 1 2000 and soldbetween $10 and $10000 - AND for my 2nd sales question2-I want all employees that sold CategoryID 1 (beverages) betweenJan 1 1996 to Jan 1 2000 and sold between $10 and $1000I want to get the common result of both questions and find outwhich employee(s) are in this list.Here are some of the points:1-I want my query to return the list of employees fitting theresult of my sales question(s).2-If I ask three questions with the logical operator AND, I wantthe list of employees that are common to all three questions.3-If I ask 2-3-4. questions with the logical operator OR, I wantthe list of employees that are in the list of the 1st "successful"sales question (the first question that returns any employee isgood enough)4-You can ask all kind of sales question you want even if theycontradict each other. The SP should still run and returnnothing if that is the case.5-Let's assume you can have the same product name from the samesupplier but under different categories. So entering a ProductIDshould not automatically enter the CategoryID also; whereasentering the ProductID should automatically enter its SupplierID.6-SOLDFROM, SOLDTO, MINSALES, MAXSALES, OPERATOR are mandatoryfields, you can't leave them NULL7-SupplierID, ProductID and CategoryID are the dynamic inputparameters, there can be 5 different combinations to choose from:a-SupplierID onlyb-SupplierID and a ProductID,c-SupplierID and a CategoryIDd-SupplierID, ProductID and a CategoryIDe-CategoryID onlyf-Any time you choose a ProductID, the SupplierID valuewill be filled automatically based on the ProductID'srelationshipg-Any of the three values here that is not chosen by theuser will take a default value of -1 (meaning return ALLfor this Column, in other words don't filter by this column)The major problem I have is I can't use dynamic SQL for choosingthe three dynamic columns as the 2nd row of records would have adifferent selection of dynamic columns (at least I don't know howif the solution is dynamic SQL). The only solution I can think oflooks pretty bad to me. I would use a cursor, run each row at atime, store a TRUE, FALSE value to stop processing or not andstore the result in another detail table. Then if all ANDquestions have ended with TRUE do a union of all the result andreturn the common list of employees. It sounds pretty awful as anapproach. I am hoping there's a simpler method for achieving this.Does anyone know if any SQL book has a topic on this type ofquery? If so I'll definitely buy the book.I appreciate any help you can provide.Thank you
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer): user1 answer1 answer2 answer3user2 answer1 answer2 answer3 How can this be done? How can all answers of a user appear on a single row Thanx,Danny.
What is the easiest way to return rows 200 through to 300 of a 500 row result set using SQL? Is there a simple way of doing this or do I need to write some Transact SQL? Any ideas would be appreciated.
I have two tables that I am pulling data from to insert into a third table.
Table 1: Organization Address Date Name 404 St. 12/31/1999
Table 2: Organization Software Type Quantity Name SW1 5 Name SW2 6 Name SW3 7 Name SW4 8
My query looks something like this:
INSERT INTO Organization SW Reqs (Name, Address, Date, SW1 Quantity, SW2 Quantity, SW3 Quantity) SELECT Table 1.Name, Table 1.Address, Table 1.Date, (Select Table 2.Quantity Where Table 2.Software Type = 'SW1'), (Select Table 2.Quantity Where Table 2.Software Type = 'SW2'), (Select Table 2.Quantity Where Table 2.Software Type = 'SW3') FROM Table 1, Table 2 WHERE Table 1.Organization = Table 2. Organization
Sadly, this query gives me four rows, one for each Software Type. I have tried putting DISTINCT after my SELECT, but that narrows it down to two rows only. I would really like to have just a one row result per organization.
In the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1071904&SiteID=1 mike.groh stated that you would define the result set as a data set variable through an execute sql task. He then pushed the variable out as a dataset type. (ds = CType(Dts.Variables("Email_CurrentDataset").Value, DataSet)
How would this type of object be declared as a user variable? Is it of type object? What is the logic behind converting a result set to a data set?
Specifically I am having problems with converting / casting the result set to a data set in that I am getting an error stating that it cannot convert the com object to a data.dataset class...
Imports ADODB
Imports System
Imports System.Xml
Imports System.Collections
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts
Imports Microsoft.SqlServer.Dts.DtsClient
Public Class ScriptMain
Public Sub Main()
Dim ds As DataSet
Dim dr As DataRow
Dim str As String
Dim dtm As String
Dim s As String
Try
ds = CType(Dts.Variables("resultSet").Value, DataSet) ' <-- Error on converting
