|
vanikanc wrote: select
'00' + 'TESTIN' + '634' + SPACE(56) + '000001' + CHAR(13) + char(10) +
'1'
In my eyes this is abuse of SQL. Such things should be done with the software not in a SQL-Script.
If you really need to, place to SELECT Statements:
SELECT '00' + 'TESTIN' + '634' + SPACE(56) + '000001';
SELECT 1;
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
Greetings,
I have 3 tables respectively called "Employee_Personal_Info", "Employee_Job_Info" and "Attend_and_Leave_Of_Staff". The Last table contains a log from the fingerprint device and its columns are [Record Number], [Machine ID], [Employee ID], [In/Out Mode] which contains only 0 for attend and 1 for leave, [Verify Mode] which contains only 1 for fingerprint and 15 for face, [Date] and [Time]
Columns concern me here within those tables are
1- Employee_Personal_Info: [Employee ID], [First Name], [Middle Name], [Last Name] and [Family Name]
2- Employee_Job_Info: [Job title], [Shift ID]
3- Attend_And_Leave_Of_Staff: [Date], [Time]
We have 2 work shifts one that starts and ends in the same date begin at 8:00 AM and ends at 4:00 PM and the other begin at 6:30 PM and ends on the next day at 8:00 AM.
I need to create a SQL query that returns me all employees registered on a specific work shift with their attend and leave time. That is it when selecting a specific type of shift the query returns me all the employees registered in such shift and beside each one his attend/leave date/time and returns null if one of the date/time is not found and null for both if there is no attendance data for him. I know it may be a complex query specially when dealing with employees registered in the second shift that starts at a day and ends on another one. I will be grateful for any ideas or any help
|
|
|
|
|
I don't know if I understand what output you wanted, but I tried 
I pretend that the ist the EmployeeID in every table, otherwise you can't join data (and you can't know who belongs to the data). There are other columns missing, too. Look at Attend_And_Leave_Of_Staff - where do you know, to which shiftId the date and time belongs? Just a date and a time column doesn't make sense to me.
SELECT a.FirstName, a.FamilyName, b.JobTitle, c.???
FROM Employee_Personal_Info a INNER JOIN Employee_Job_Info b ON a.EmployeeID = b.EmployeeID
LEFT JOIN Attend_And_Leave_Of_Staff c ON a.EmployeeID = c.EmployeeID AND b.ShiftId = c.ShiftId
WHERE b.ShiftId = @shiftIdToLookUp
I can't go further because of missing data, but that
I changed the second join to LEFT JOIN thanks to Andrei Straut.
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
modified 26-Sep-12 3:18am.
|
|
|
|
|
Maybe you should go with a LEFT JOIN on Attend_And_Leave_Of_Staff?
He said he wanted to see the null values for when records are not found, and your inner join will simply disregard (not display at all) the rows that have no correspondence.
EDIT: I've upvoted your solution, as it seemed to be what the OP wanted.
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
modified 26-Sep-12 3:14am.
|
|
|
|
|
Andrei Straut wrote: Maybe you should go with a LEFT JOIN on Attend_And_Leave_Of_Staff?
Of course! You are absolutely right! Thanks! 
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
You're welcome
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Hello All,
I need to convert this value of datetime
2012-09-16T20:08:30Z to this format "mmddyy".
I tried
select convert(char(10),'2012-09-16T20:08:30Z',101) - but this did not work.
Is there any other method to try before trying to write a whole bunch of casts, replaces!
|
|
|
|
|
vanikanc wrote: I need to convert this value of datetime
2012-09-16T20:08:30Z to this format "mmddyy".
Doesn't seem a valid datetime format to me
As a string-function use:
SELECT LEFT('2012-09-16T20:08:30Z', 10) ...
With values in datetime-format:
SELECT CONVERT(DATE,@datetime)
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
I Would like to ask about sql server commands and materials,could you provide me some topics related to sql server
Thanks,
|
|
|
|
|
MSDN SQL Server Developer Training[^] is a good place to start.
But you didn't Google[^] that, did you?
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Start from here:
http://www.w3schools.com/sql/default.asp[^]
http://www.microsoft.com/en-in/SQLserver/default.aspx[^]
http://learnsqlserver.in/[^]
Start to learn about:
- Various query types - CREATE, UPDATE, DELETE, ALTER, SELECT, INSERT, GROUP BY , HAVING etc
- Various query functions - COUNT, LENGTH, LTRIM, RTRIM, SUBSTRING, etc
- Various query statements - Distinct, Union, Union all, Intersect, Order by, etc
- Various query grouping and execution - Store procedures, Trigger, Cursor, Views, etc
Try!
|
|
|
|
|
Good day,
I have some problem with my SQL Commands in access, really hope that you can help me out.
Category
CatID Category Name Sub- Category Description
1 Beverages 123
2 Condiments 456
3 Juice 1 help
4 Coffee 1 pls
I wanted to display the main category of the Juice which is beverage. No matter how hard i tried, i can't display the Main category in access.
Really hope if any kind soul could help me with this
Regards
Veon
|
|
|
|
|
Could you please show your SQL.
|
|
|
|
|
SELECT Product.[Electronic Product Code], Product.[Item Name], IIf([parent - category] Is Null,[name],[Category].[name]=[category].[Category ID].[name]) AS Category , IIf([Parent - Category] Is Not Null,[name],"") AS SubCategory, Location.Country, Location.Building, Location.[Street Address], [Tracking Record].Timestamp, [RFID Reader].[Reader ID]
FROM Location RIGHT JOIN ((([RFID Reader] RIGHT JOIN (Product LEFT JOIN [Tracking Record] ON Product.[Electronic Product Code] = [Tracking Record].[Electronic Product Code]) ON [RFID Reader].[Reader ID] = [Tracking Record].[RFID Reader ID]) LEFT JOIN Has ON Product.[Electronic Product Code] = Has.[Electronic Product Code]) LEFT JOIN Category ON Has.[Category ID] = Category.[Category ID]) ON (Location.Latitude = [Tracking Record].Latitude) AND (Location.Longtitude = [Tracking Record].Longtitude)
WHERE (((Product.[Electronic Product Code]) Like "*" & [forms]![Search History]![tbxEPC] & "*") AND ((Product.[Item Name]) Like "*" & [forms]![Search History]![tbxProductName] & "*") AND ((Category.[Category ID])=[forms]![Search History]![cmbCat])) OR (((Product.[Electronic Product Code]) Like "*" & [forms]![Search History]![tbxEPC] & "*") AND ((Product.[Item Name]) Like "*" & [forms]![Search History]![tbxProductName] & "*") AND (([forms]![Search History]![cmbCat]) Is Null)) OR (((Product.[Electronic Product Code]) Like "*" & [forms]![Search History]![tbxEPC] & "*") AND ((Product.[Item Name]) Like "*" & [forms]![Search History]![tbxProductName] & "*") AND (([forms]![Search History]![cmbCat]) Is Not Null) AND ((Category.[Parent - Category])=[forms]![Search History]![cmbCat]));</pre>
|
|
|
|
|
veon cheng wrote: I wanted to display the main category of the Juice which is beverage. No matter how hard i tried, i can't display the Main category in access.
SELECT a.CategoryName
FROM Category a INNER JOIN Category b On a.CatId = b.SubId
WHERE b.SubCategory = 'Juice';
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
Hi all,
I am trying to connect db file from a pen drive. it is fine when using on my local machine. But giving following error when trying it on my client machine. i am using vs2008 and mssql express 2008. Mssql express 2008 also installed on my client's machine.
Type of application: Window application.
Error:Failed to generate a user instance of sql server due to a failure in startng the process for the user instance. the connection will be closed.
i have deleted mssqlexpress folder form the location:
C:\Documents and Settings\username\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\
and restarted machine, but still getting the same error.
Connection string: " Data Source='" & (SqlServerName) & "';AttachDbFilename=" & driveName & "dbfolder\dbname2.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
if i remove the "User Instance=True" from the above connection string it gives me following error:
"Create database permission denied in dataase 'master' an attempt to attach an auto named database for file failed. A database with the same name exists, or specified file cannot be opened, or it is located on usc share."
Kindly help me.
Thanks
rmshah
Developer
|
|
|
|
|
r_mohd wrote: if i remove the "User Instance=True" from the above connection string it gives me following error:
Do you need a user-instance, or not?
Microsoft SQL Server 2005 Express Edition (SQL Server Express) supports a new feature, the user instance, which is only available when using the .NET Framework Data Provider for SQL Server (SqlClient). A user instance is a separate instance of the SQL Server Express Database Engine that is generated by a parent instance. User instances allow users who are not administrators on their local computers to attach and connect to SQL Server Express databases. Each instance runs under the security context of the individual user, on a one-instance-per-user basis.
I assume that Sql Express or Sql Server is installed on the machine? Can you post the stacktrace of the exception?
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
r_mohd wrote: I am trying to connect db file from a pen drive.
That needs more explanation.
You have at least the following parts
1. MS SQL Server
2. The location of the MS SQL Server files.
3. The Visual Studio application.
Is the above list a COMPLETE list of everything that you are attempting to run?
From the above list what EXACTLY is on the pen drive?
From the above list what EXACTLY is NOT on the pen drive?
r_mohd wrote: i have deleted mssqlexpress folder form the location:
As stated that is just flat out wrong. You can't just delete that directory.
If SQL Server can't see the database then nothing you do in Visual Studio is going to fix that.
r_mohd wrote: Connection string: " Data Source='" & (SqlServerName) & "';AttachDbFilename=" & driveName & "dbfolder\dbname2.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
Certainly looks like a back slash is missing to me.
|
|
|
|
|
Thanks jschell,
MS SQL Server is installed on client's machine.
The location of the MS SQL Server files. it on client's machine(C drive) and data file and log file lying on pen drive.
The Visual Studio application is lying on client's machine(c drive).
The connection string is fine as it works on my machine.
i am using a pen drive for data file and log file.
Thanks again.
rmshah
Developer
|
|
|
|
|
r_mohd wrote: and data file and log file lying on pen drive.
And you can go into SQL Server Management Studio and create a database using that drive? On your box? Notice that has nothing to do with your application. It is just a test to insure that SQL server accepts that.
|
|
|
|
|
Hi friends. I have a small software that uses an access database. I use it at home but i wonder if i can use it from anywhere by reaching the db over internet. Should i change any code to do so. By the way if Access doesnt support this function, i can replace my db with sql server.
|
|
|
|
|
Bad idea; Microsoft Access is not meant to be used as a server, and it ain't as secure as Sql Server. I'd even recommend against using Sql Server over the internet, would be safer to have IIS on the same server and provide access to the DB using webservices.
I can imagine you don't want to "waste time" with writing a useless layer. If my memory serves me correct, you'd open port 1433 on the firewall. Updating from Access to Sql Server should be easy; if you open the DB in Access, you'll have a button in that lint that says "upgrade to Sql Server" - that launches a wizard that recreates all your tables in Sql Server.
Hope this helps 
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
leone wrote: I use it at home but i wonder if i can use it from anywhere by reaching the db over internet.
As stated no.
MS Access is a file based server. Thus if you have software that uses MS Access then the file must be visible in file system of the client machine.
Thus if you want to run the software on machine A then on A you MUST be able to open explorer and see the database file.
That means that you would need to enable file sharing over the internet. You might be able to do that depending on the machines at either end and how they are connected.
leone wrote: i can replace my db with sql server.
MS SQL Server is accessible via the internet as long as the server machine is accessible. That is not guaranteed (machine is visible) but that is a different discussion.
It is possible to secure MS SQL Server so it can be exposed on the internet however either you must presume that your data is not secure and not important or you are going to need to learn a lot more about internet security and SQL Server before using it. It probably isn't worth learning that since it isn't a skill that businesses want.
|
|
|
|
|
Thank you friends for your answers. I decided to hire a server machine and put everyhing on it.
|
|
|
|
|
Don't understand why this was downvoted, (over)compensating.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
