Plus 1 for Shauns suggestion.

Any interfacing with the database should be done through stored procedures. Not only does this prevent injection attacks, it will also improve develoment because the...

Thanks for the explanation guys, I learned something today.

Actually 2 things. 1) scope and 2) yet another reason not to use subqueries

@kingston, It is a result but...

interesting (and worrying!)

SELECT * FROM customer WHERE customer.customerid in (SELECT customer.customerid FROM salesorder)

If you just run the subquery, then you get an error.

The answer you get (two records) is wrong...

basically when employee get confirmed company give him a leaves but some employee are not confirmed so they didnt get leave what leave do when confirm employee absent user enter...

The short answer is with difficulty

As kingston said PIVOT is available from SQL2005 onwards so there is no point if you are on 2000 :w00t:

The first challenge is that PIVOT...

you will get an error message

Select Customer.CustomerID from salesorder

table Customer has not been defined in the subquery.

The subquery should read

Select salesorder.CustomerID from salesorder

or

Select CustomerID from salesorder

There is no need to...

What any means is that the FROM clause is just listing the tables and not the relationships between them and relies on the WHERE clause for its joining criteria.

Old Style

Select

...

immaduddinahmed (6/19/2013)

---

i use this syntax in sql server

when t.timein is null and l.date is not null then u.description

and make join like this

FROM attend_log) t

left join leaveinformation l on t.eid...

FOR XML is one of the best tools in MSSQL but I would regard it as something that only a power user should attempt as it's syntax is confusing.

Basically it...

Hi FishRman.

Not sure why it should suddenly start failing. What version(s) of MSSQL are you seeing this on.

What applications and versions is the VBA running in and is the...

The answer to the OP question is to use LEFT JOIN

SELECT Calendar.date, shiftQuery.* From Calendar LEFT JOIN shiftQuery on Calendar.date = shiftQuery.date

This will give you the calendar record EVERY TIME...

My 2 Cents worth.

If in doubt, create your tables to reflect the entity that it models and use the proper identity, primary and foreign keys to keep the data in...

does the number of leading zero change?

My gut feel would be to find the position of the first non zero character using regex and then trim from there to the...

My 2 Cents worth,

Seeing as Count(tickets) and Sum(duation) are both aggregates on the same dataset with the same grouping, why not do them in the one CTE and then UNPIVOT()...

@chrism-2

I managed to run that against my entire dataset (some 800K records) and it took less than 30 seconds so a big thanks for that code.

Obiron