Database Discussion Boards

sindhuan wrote:
:setvar DatabaseName "Test2"

Well you are not in SQL-Commandmode, I bet. Change it in the MS Sql Server Management Studio. Go to Query->SQLCMD-Mode

Better way to change (in my opinion):

SQL

SET @DatabaseName VARCHAR(32) = 'Test'
...
IF (DB_ID(N'$(@DatabaseName)') IS NOT NULL

------------------------------
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 need to run this script in SQLCMD mode.

Goto the menu, click Query > SQLCMD Mode

Now you will be able to run your script.

What am I supposed to do...go to Query->SQLCMD MODE then paste the script and click on execute??

sindhuan wrote:
What am I supposed to do...go to Query->SQLCMD MODE then paste the script and click on execute??

Well, give it a try! $Shucks | :-\$

------------------------------
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.

sindhuan wrote:
What am I supposed to do...go to Query->SQLCMD MODE then paste the script and click on execute??

You could ask Google what this "CMD MODE" is, what it does, and where it lives. Try to get to know the beast, as opposed to "try and see if it works".

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

You don't need to copy/paste.

Just open the script in SSMS, then click Query>CMD Mode, then click run.

hi,

I have the following sql.
select
'00' + 'TESTIN' + '634' + SPACE(56) + '000001' + CHAR(13) + char(10) +
'1'

I need the 1 on the next line, as I have specified CHAR(13) + char(10). But it does show that way, it still appears on the same line.

Any help is appreciated.

So where exactly are you running this?

In other words where exactly do you expect this "next line" to show up?

Thank you.

Sql server 2005

vanikanc wrote:
Sql server 2005

SQL Server is a database server.

The end of line character means nothing in the database.
There is no way to 'display' it.

So that is not how you are using it.

Best I can suppose is that you are running the Microsoft SQL Server Management Studio and it show 'rows' and columns and your value is thus in a row and in a column and it will not display it any other way.

Hi
char(10) and char(13) will work only when results to text..

it won't work if the results are displayed in grid

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:

SQL

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 Smile | :)

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! Roll eyes | :rolleyes:

------------------------------
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 Smile | :)

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:

SQL

SELECT LEFT('2012-09-16T20:08:30Z', 10) ...

With values in datetime-format:

SQL

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!

Sandeep Mewara
Microsoft ASP.NET MVP

[My latest Article]: Server side Delimiters in ASP.NET[^]

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.

Sorry its kinda messy, its from access.. the bold part is the 1 have issue. once againg sorry

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],&quot;&quot;) 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 &quot;*&quot; &amp; [forms]![Search History]![tbxEPC] &amp; &quot;*&quot;) AND ((Product.[Item Name]) Like &quot;*&quot; &amp; [forms]![Search History]![tbxProductName] &amp; &quot;*&quot;) AND ((Category.[Category ID])=[forms]![Search History]![cmbCat])) OR (((Product.[Electronic Product Code]) Like &quot;*&quot; &amp; [forms]![Search History]![tbxEPC] &amp; &quot;*&quot;) AND ((Product.[Item Name]) Like &quot;*&quot; &amp; [forms]![Search History]![tbxProductName] &amp; &quot;*&quot;) AND (([forms]![Search History]![cmbCat]) Is Null)) OR (((Product.[Electronic Product Code]) Like &quot;*&quot; &amp; [forms]![Search History]![tbxEPC] &amp; &quot;*&quot;) AND ((Product.[Item Name]) Like &quot;*&quot; &amp; [forms]![Search History]![tbxProductName] &amp; &quot;*&quot;) AND (([forms]![Search History]![cmbCat]) Is Not Null) AND ((Category.[Parent - Category])=[forms]![Search History]![cmbCat]));</pre>