I have a tricky sql statment I have to write (tricky for me) and I am
stuck. I'm having trouble with the following problem.
Table1 (Column a, Column b, Column c)
Table2 (Column a, Column b, Column c)
Table3 (Column a, Column b, Column c)
Table1 contains a row of value (1, 2, 3)
Table2 contains a row of value (4, 5, 6)
Table3 contains a row of value (7, 8, 9)
What I need to do is write a single SELECT statement (embedded selects
and so forth are allowed) to produce the table:
TableNew (Column a, Column b, Column c)
TableNew contains a row of value (1, 2, 3)
and a row of value (4, 5, 6)
and a row of value (7, 8, 9)
I've been playing with the FULL OUTER JOIN and I get
TableNew (Table1.Column a, Table1.Column b, Table1.Column c,
Table1.Column a, Table1.Column b, Table1.Column c,
Table1.Column a, Table1.Column b, Table1.Column c)
TableNew contains a row of value (1, 2, 3, null, null, null, null,
null, null)
and a row of value (null, null, null, 4, 5, 6, null,
null, null)
and a row of value (null, null, null, null, null, null,
7, 8, 9)
Or I get only 1 row of data that looks like (1,2,3,4,5,6,7,8,9)
If someone has the solution to this problem, please let me know.
Thanks.
Tables license TABLE --------------- license_id int PK vendor_id int FK po_id int FK Nulls license_type_id int FK lic_user_id int FK Nulls location_id int FK Nulls lic_start_date smalldatetime Nulls days_allowed int Nulls serial_num varchar(50) Nulls activation_key varchar(50) Nulls max_users int Nulls comments varchar(1000) Nulls
software TABLE ------------------ software_id int PK os_id int software_name varchar(150) comments varchar(1000) Nulls
software_license TABLE -------------------------- license_id int PK , FK software_id int PK , FK comments varchar(1000) Nulls
dept_license TABLE --------------------- dept_id int PK , FK license_id int PK , FK comments varchar(1000) Nulls
dept_purch_order TABLE ---------------------------- po_id int PK , FK dept_id int PK , FK comments varchar(1000) Nulls
I am using the following stored proceedure to try to create a useful report from those tables:
Code Block
ALTER PROCEDURE [dbo].[ListDepartmentLicenses] @Department int, @Software int AS BEGIN SET NOCOUNT ON; SELECT dept_license.license_id, license.lic_start_date, department.dept_name, purch_order.po_number, software.software_name FROM dept_license JOIN license ON dept_license.license_id = license.license_id JOIN department ON dept_license.dept_id = department.dept_id JOIN dept_purch_order ON dept_purch_order.dept_id = dept_license.dept_id JOIN purch_order ON dept_purch_order.po_id = purch_order.po_id JOIN software_license ON dept_license.license_id = software_license.license_id JOIN software ON software_license.software_id = software.software_id WHERE (department.dept_id = @Department or @Department Is Null) and (software.software_id = @Software or @Software Is Null) ORDER BY license.lic_start_date END
The sproc compiles fine but I get no rows returned.
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033 4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators. The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
I have a DTS package that migrates data from Excel to Access to SQL Server. Before that occurs, at the very beginning, I have a delete script that deletes the existing data in the Access database. When I hit that first step, it gives me the error:
Microsoft JET Database Engine (-2147467259) - The Microsoft Jet database engine cannot open the file '<path to access .mdb>'. It is already opened exclusively by another user, or you need permission to view its data.
I checked the permissions and the account that the web site is running under (impersonated) has full permissions to the file. Also, I am running the web site on my machine, I'm triggering the DTS package through .NET code, the access database is on a file/print server, and the SQL Server is a separate machine.
I'm thinking that maybe the issue is that the access database is on a separate machine, but I'm not quite sure.
Each record represent information for each ProductID, per PriceZone, per Date. There's always one record per product; per zone; per Date. Here is the sample of the records (Just for example I am just using one product in one zone)
Here; Date is the maximum date available in the entire [History] table. Meaning the "Date" value will be same for all the records in the output. Price is the Price for that product in that zone on Date Cost is the cost for that product in that zone on Date
About "Last4Dates" & "Last12Dates": "Last4Dates" is the last 4 dates in the entire table from 'Date" value , including "Date" value and prior 3 dats before the "Date" value. In the above data set example it would be from "2008-04-25" to "2008-04-30" "Last12Dates" is the last 12 dates in the entire table from 'Date" value, including "Date" value and prior 11 Dates before the "Date" value. In the above data set example it would be from "2008-04-09" to "2008-04-30"
UnitsSum_ForLast4Dates and UnitsSum_ForLast12Dates is the sum of Units for 4 and 12 weeks respectively; per product, per zone
DollarSalesSum_ForLast4Dates and DollarSalesSum_ForLast12Dates is the sum of DollarSales for 4 and 12 weeks respectively; per product, per zone
AvgMargin_ForLast4Dates and AvgMargin_ForLast12Dates is the Average of DollarSales for 4 and 12 weeks respectively; per product, per zone
ProfitSum_ForLast4Dates and ProfitSum_ForLast12Dates is the sum of Profit for 4 and 12 weeks respectively; per product, per zone
We're experiencing a large number of deadlocks since we began runningSQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyperthreading intel processors. We don't have the same problem on Dell6650's w/o the hyper threading. If I turn off the parallel queryprocessing option the deadlocks stop. I've tried all of the suggestionsfrom the Microsoft Knowledge Base under the following link -http://support.microsoft.com/?kbid=837983The only suggestion that actually yielded results was turning offparallel query processing but I don't want to give up what should be aperformance advantage if it wasn't for the deadlocks. Query tuning andindex tuning hasn't helped. Any suggestions? I haven't applied SP4yet. I'm wondering if anyone has seen the same problem resolved withSP4.*** Sent via Developersdex http://www.developersdex.com ***
Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:
create table [User_Tbl]( [ID] int IDENTITY(1,1) PRIMARY KEY, [Name] varchar(100), [Password] varchar(100), ) go
create table [Printer_Tbl( [ID] int IDENTITY(1,1) PRIMARY KEY, [Name] varchar(100), [PaperCost] int ) go
I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @MinDate - @MaxDate)
So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.
One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!
1. RubricReport 2. RubricReportDetail and 3. RubricReportTemplate
RubricReportDetail has columns ReportID and Age RubricReportTemplate has columns IndicatorID and Salary
I want to insert a row in RubricReport Table with ReportID = @ReportID (RubricReportDetail) and IndicatorID = @IndicatorID (of RubricReportTemplate). How can I do this?
I am one very frustrated beginner. If it were not for wonderful information in this forum I would have taken early retirement by now!
Table contains information about new and departing computer and phone users in several departments which we support. This is an existing table which I'm trying to clean up. The essential part:
CREATE TABLE [dbo].[HelpDesk_NewUser]( [AutoNumber] [int] IDENTITY(1,1) NOT NULL, [SubmittedDate] [datetime] NULL, [FirstName] [varchar](100) NOT NULL, [LastName] [varchar](100) NOT NULL, [Department] [varchar](100) NOT NULL, [StartDate] [datetime] NULL, [DepartDate] [datetime] NULL [RequestedBy] [varchar](100) NOT NULL, [UpdatedBy] [varchar](16) NOT NULL, [CurrentStatus] [varchar](10) NOT NULL , [DateCurrentStatus] [datetime] NULL, [FormType] [varchar](10) NOT NULL, ) ON [PRIMARY]
There can be more than one record per FirstName, LastName. FormType can be N for new or D for departing. I want to do this for each record: Read the FormType and Department from the record with the most recent activity (SubmittedDate) for each user Convert Department to a 4 character department number Update CurrentStatus with (FormType concatenated with the 4 character dept number) in all records for that user.
I have created another table called UserMostRecent which contains the most recent record for each user.
I have written a query to do this by brute force (read a record, set local variables, update a record), but I would like learn a simpler way to do it. I don't understand the syntax of CASE because it seems to change depending on where it is used.
Here is what I have tried that does not work. Error is "Incorrect syntax near word CASE"
UPDATE HelpDesk_NewUser SET DateCurrentStatus = b.DateMostRecent, CurrentStatus = (b.FormType + a.Department CASE WHEN 'Roads Department' THEN '3000' WHEN 'Engineering and Survey Services' THEN '1900' WHEN 'Waste Management' THEN '8999' WHEN 'Kern Air Pollution Control District' THEN '9149' WHEN 'Environmental Health' THEN '4113' WHEN 'Building Inspection' THEN '2625' WHEN 'Animal Control' THEN '2760' WHEN 'Planning Department' THEN '2750' WHEN 'Community and Economic Development' THEN '5940' WHEN 'Resource Management Agency' THEN '2730' WHEN 'Code Compliance' THEN '2620' WHEN 'Roads Kern Regional Transit' THEN '8998' END) FROM HelpDesk_NewUser a JOIN UserMostRecent b ON (a.LastName = b.LastName and a.FirstName = b.FirstName)
I seem to have a little problem with my SQL. I'm attempting to use a an .NET grid-view to page a list of search results using an ObjectDataSource (ODS). It's a very simple search setup. The ODS retrieves the Search keyword from the query-string sending it to the BLL's GetResult method which then utilises the DAL's version and retrieves the result. Not all of them of course because I'm paging so it only retrieves the first page (Lets say the first 10 items). This part of the SQL works fine as the same SQL method is used else where on the site and it's well tested. But to to use the Paging function I'm using the SelectCountMethod attribute of the ODS. Again this gets the Search term from the QueryString and sends it too the BLL which retrieves the information from the DAL. Now this is where it starts playing up. The Sql count method is as follows and is run as a Stored Procedure: 1 ALTER PROCEDURE dbo.StoreName_Store_GetProductSearchCount 2 ( 3 @SearchQuery nvarchar 4 ) 5 AS 6 SET NOCOUNT ON 7 8 SELECT COUNT(*) 9 FROM StoreName_Products 10 WHERE Title LIKE '%' + @SearchQuery + '%' Nice and Easy. Not a Problem... Isn't it? When I run this query by selecting the database in the Server Explorer in VS2005 and running a 'New Query', it retrieves the correct results. (I only run this query from the Start of the SELECT Query in this mode, don't add the stuff above it)When I run this query through the website as intended and retrieves the int from the Stored Procedure, it always returns the entire count of the number of products in the table.At first I thought It might be a simple error with my caching system. Nope, it's not as after I purged it, it still returns the same value. I thought, well maybe just maybe the BLL or the DAL are some point converting it to this number but I'm quite sure it wasn't... and nope it wasn't that either because the ExecuteScalar method was found to be returning this number. So I ran the Stored Procedure by opening it up and simply right clicking the SQL Query and Executing it adding a value for @SearchQuery when it requested. Bingo. It's returning the full number of the products.So my question is... What's the deal? How can the same query return two completely different results? and most of all... How do I overcome this? Is this because it's in a stored procedure and I should be executing it as a SqlCommand from my DAL? I'm a bit confused. Is there a problem running this sort of query in a stored procedure that I don't know about and probably really should?Thankful for any answers,Regards,Luke
- Since the parameters are optional user can enter either one or can leave both blank. - If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL - If user enters SD, query should use @StartDate as the SD and GetDate() as the ED - If user enters ED, query should use @EndDate as the ED and MIN() of the Date field as SD
I was able to write query which did almost everything as is stated above expect for incorporating NULLs The query is as below
AS IF ( @StartDate IS NULL) Select @StartDate = MIN(DateInputted) from Document
Select FName as 'First Name', LName as 'Last Name', ID as 'Student ID', Orphan as 'Orphan', DocumentType as 'Document Type', DocDesc as 'Description of the Document', DateInputted as 'Date Entered', InputtedBy as 'Entered by'
From Document,DocumentTypeCodes Where FName LIKE ISNULL(@FName,'%') AND LName LIKE ISNULL(@LName,'%' + NULL) AND ID LIKE ISNULL(@ID,'%' + NULL) AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL) AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
Any help would be appreciated Thanks in advance :)
I want to create an UPDATE statement that updates a value in a table indatabase A based on the results of an inner join between tables eachresiding in deifferent databases.What is the correct syntax for doing this?The following should give you an idea of what I'm trying to do:UPDATE A.dbo.tblCarsSET A.dbo.tblCars.Car = 'Ferrari'FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ONA.dbo.tblCars.RecID = B.dbo.tblHouses.RecIDWHERE (B.dbo.tblHouses = 'Mansion')
where, statString is a string variable containing Hebrew characters.
Till here, my code works fine. i.e, Hebrew characters are properly inserted to the database. The problem is when I try to retrieve the String_Id based upon the statString I inserted to the table static_string1.
Code Snippet String sql = "select String_Id from Static_String1 where String like ('" + statString +"')"; Statement statement=connection.createStatement(); ResultSet rs=statement.executeQuery(sql); rs.next(); int stringId=rs.getInt("String_Id");
I tried hardcoding the string in the query and to execute it from the SQL Server Management Studio as below
Code Snippetselect String_Id from Static_String1 where String like( ' ×”×–×—' );
But even this is returning null rows, even though the entry is present in the table Please help me out asap.
Please pardon me if this is not the right section to post my doubt. I didnt find any other relevant section here.
ID PID From To Code 1 1 14/02/07 17/02/07 X 2 1 17/02/07 19/02/07 X 3 1. 19/02/07 23/02/07 E 4 1 26/02/07 28/02/07 X 5 1 1/4/07 1/5/07 E 6 2 01/03/07 03/03/07 X 7 2 04/03/07 10/03/07 X 8 2 10/03/07 14/03/07 E
Result
ID PID Date 4 1 26/02/07 7 2 04/03/07
I want to be able to create a select query on the above table. The table will show ID, PersonID (PID), From and to date, and code. If the code is X then the next €˜from record€™ should be the same date as the €˜to date€™. If the code is E then the next €˜to€™ date can be anytime after the previous €˜to€™ date. I want to be able to report on all record where there is a day difference between the previous €˜to€™ date. I.e. ID 4 and 7 €“ the previous records both have an X and there is at least a days difference between the dates.
I am quite surprise by getting wrong resultset from a simple query like:
select Order_No from Delivery_Order where cust_id = 5 and do_date >= '6/15/2008' and do_date <= '6/31/2008'
In the database, there are data since the last two years. There is no data beyond today's date, in fact. But when I tried to query for 'Order_No' with specified cust_id within above date range (which data is not in the DB), the result will be the 'Order_No' from '6/15/2007' to '6/31/2007'. Isn't it supposed to return null? simply because there is no such date as 2008 yet in the DB. Help from anyone is needed. Thanks in advance.
Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email FROM #temp_pregovb, [Survey Return] INNER JOIN SelectedInvited ON [Survey Return].SelectedID = SelectedInvited.SelectedID --*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact] ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID WHERE [panelist contact].email=#temp_pregovb.email AND SelectedInvited.CellRef IN ( SELECT surveycell FROm [Survey Cells] WHERe SurveyRef='5')
I'm attempting to create a complex query and i'm not sure exactly how i need to tackle I have a series of tables:
[tblEmployee]
empID
empName
deptID
jobtID
[tblDept]
deptID
deptNum
deptName
[tblJobTitle]
jobtID
jobtNam
[tblTrainng]
trnID
trnName
[tblTrnRev]
trnrevID
trnID
trnrevRev
trnrevDate
[tblEduJob]
ejID
jobtID
trnID
[tblEducation]
eduD
empID
trnrvID
eduDate
The jist of this database is for storage of training. The Training table is used for storing a list of training classes. The TrnRev links and shows each time the training was updated. The EduJob table links each Job title (position) in the company to each trainng class that position should be trained on. The Education table links each employee to which revision of a class they have attended.
What i need to do is create a query that for each employee, based on their job title, wil show what classes they are required to be trained on. I want the query to return the employee, the training, the latest revision of that class, and then show if a) the person's trainig is current for that revision, b) the person has been trained on that topic but not the latest revision, or c) they've had no training at all on that topic.
NOTE: I apologize to anyone (especially moderators) who may notice that I am basically repeating a question that was already posted by me in another recent thread. The reason why I am reposting is because I want to filter my question down to its crux because the other question may not have been asked in the most clear way.
The Question:
The sceanrio is this. (1) I have a sales person dimension with a hierarchy. (2) In this hierarchy, Bill and Ted roll up to John. (3) Bill sells 10 units, Ted sells 8 units, and John sells 5 units.
When you process this hierarchy, what would you expect the total to be for John? (A) 23, which is the sum for Bill, Ted, and John or (B) 18, which is the sum of Bill and Ted only and overwritting John's number
I say (A) and I think most will choose the same, and all the examples I've been reflects (A).
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
I'm a newbe in the realm of database reporting. At my current position, I'm reporting off of CRM databases using Crystal V-11. Previously I'd experience working with HR databases using the same reporting tool. I am interested in progressing to work with database design and scripting. Any suggestion from anyone on which certifications to pursue?
I am running a vba procedure ( adp file ) that executes successively 5 stored procedures . however it happens that the execution breaks at the middle of the code thus giving a situation where only 2 tables among 5 are updated.
Is it any solution to rollback transactions update already done before the code breaks due to error ?
I was thinking about combining all stored proc on a big one and use Begin transaction - commit transaction and rollback transaction ... however i am not sure wheter updates involving several tables can be handled on one transaction.
Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?
The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)
However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.
I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,
Table A Contains a Main Image, this image is displayed in the results Table B Contains an Icon, this image is displayed in the results Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.
Select title, description, image from tableA UNION ALL Select title, description, icon as image from tableB UNION ALL title, description, ( inner Join SELECT top(1) from imageTableC where imagetableC.FK = tableC.PK) as image from tableC
Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.
Hello, I have four diffrent tabels: bo_ Class, bo_Competition, bo_Result, bo_Licence And list all Licence where bo_Class.classRankingNbr >0
ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale ------------ --------- --------------- ------------- ----------- --------------- ---------- M70891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H M050887PON01 Pontus Svensson U23 SM 2007 - Herrar 2 1 H M181188MAR01 Marcus Edlund U23 SM 2007 - Herrar 3 1 H M190291JOH01 Johan Helldén U23 SM 2007 - Herrar 4 1 H M180360GER01 Gert Lindholm Herrar klass A 1 2 H M041062MIC01 Micael Hamberg Herrar klass A 2 2 H K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D K050785CAR01 Carin Johansson U23 SM 2007- Damer 3 1 D
If bo_Class.ClassRowNbr for an Class is 1 get out place 1 from that competition, and if an ClassRowNbr is 2 get out top2 from that competition. And so on.
From that list i want an SubQuery list where all licens order by where bo_Result.ResultPlace = bo_Class.ClassRowNbr.
In text form:
ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale ------------ --------- --------------- ------------- ----------- --------------- ---------------------- M170891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H M180360GER01 Gert Lindholm Herrar klass A 1 2 H M041062MIC01 Micael Hamberg Herrar klass A 2 2 H K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D K180793LIN01 Linnéa Hamberg Damer Klass A 1 1 D
SELECT m.Namn + ' ' + m.ENamn AS Spelare, SUM(r.Serier) AS Ser, SUM(r.Poang) AS Po, ROUND(SUM(r.Resultat) / SUM(r.Serier * 1.0), 2, 1)AS Snitt, ROUND(SUM(r.Poang * 1.0) / SUM(r.Serier), 2) AS [P Snitt], ROUND(SUM(r.Miss * 1.0 / r.Serier), 2, 1) AS Miss, SUM(r.Miss) AS [Sa Miss], MAX(r.Resultat) AS Bästa, MIN(r.Resultat) AS Sämsta FROM Resultat r INNER JOIN Medlemmar m ON r.Spelare = m.ID WHERE (r.Omgang IN (SELECT DISTINCT TOP 3 Omgang FROM Resultat ORDER BY Omgang DESC)) GROUP BY m.Namn + ' ' + m.ENamn ORDER BY 4 DESC
What i want to do is sort out Min(r.Resultat) where serier is mor than 3, Not WHERE (r.Resultat) >3. More like in Access "Min(IIf(r.serier=4,r.resultat,Null)) AS [Sämsta]" But that i cant do in SQL
Have also tryed with "WHERE (SELECT MIN(r.Serier) FROM Resultat Resultat r INNER JOIN medlemmar m ON r.Spelare = m.ID" Get lowest result same on all players.
Hello allI've got this tricky situation that I would like to solve in SQL, butdon't know how to do. This is the table:Id = 3, VId = 2, Time1 = 10:00, Time2 = 14:00Id = 4, VId = 2, Time1 = 16:00, Time2 = 17:00Id = 5, VId = 2, Time1 = 18:00, Time2 = 19:00Id = 6, VId = 2, Time1 = 20:00, Time2 = 21:00Id = 7, VId = 3, Time1 = 11:00, Time2 = 13:00Id = 8, VId = 3, Time1 = 15:00, Time2 = 16:00Id = 9, VId = 3, Time1 = 18:00, Time2 = 20:00GetRows @Time='15:30' will return row with Id=4GetRows @Time='16:30' will return row with Id=4 and row=9Logic behind this:Return row n where Time2 of Id=(n-1) < @Time < Time 1 of Id=(n) and sameVId.Ie. if @Time = '15:30' then Time2 of Id = 3 is lower than @Time, andTime1 of Id = 4 is higher than @Time => return row with Id = 4.This got a bit messy but if someone could decipher this and possiblygive an answer I'd be very glad.regardsJohnny
I have an integer in the database that was saved in reverse byte order (BigEndian). Due to some backward compatibility issues (long story) I can't just convert the number to the normal format and save it that way in the database. Instead, when I read the number in my program, I just reverse its bytes and display the proper value, and translate the number back when it has to be saved back to the database.
Now, the problem is that there are some views that pull this numbers directly from the database and display it.
My question is: can this number be converted from BigEndian to LittleEndian similarly to how I do in my program using T-SQL?
I have a LastName field which holds this dataLastNameJohnson|VasquesAdams|Fox|JohnsonVasques|Smith Now let’s say I have a SELECT Stored Procedure which takes 1 parameter @LastName.The @LastName can be something like this: “Fox|Smith�.I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith . Thank you.
This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it. I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).
