Nicely explained Jeff.

I may have to "borrow" your method of generating random DATETIME data, my method is more difficult to understand when people glance at it. 😀

ChrisM@Work (4/26/2012)

---

Cadavre (4/26/2012)

---

...

The issue is that you're using formatted data in the database. Instead, you should store this as TIME or DATETIME then convert it to the correct format at...

Here's a very very not pretty solution.

DECLARE @StandardHours VARCHAR(20) = '133:50:15';

DECLARE @ActualHours VARCHAR(20) = '98:16:57';

SELECT

CAST(diffSeconds/3600 AS VARCHAR(6)) + ':'+ RIGHT('00'+CAST(diffSeconds%(3600)/60 AS VARCHAR(2)),2)+':'+RIGHT('00'+CAST(diffSeconds%60 AS VARCHAR(2)),2)

FROM (SELECT

...

kotharibij (4/26/2012)

---

Thank you Eugene.

Both my SQL and yours (very interesting SQL), gives the same result.

Day RegTotal

116

4121

532

636

737

836

933

1046

1145

I thought of a calendar table but...

Nope, the points are pretty meaningless here. You get 1...

brally123 (4/26/2012)

---

WOW!!! i'm just amazed on how you guys do this! Thank you so much!

That was fantastic!

Buuuut, I'm sorry I forgot to add this:

foreach distinct COND in tbl_2, i would...

dwain.c (4/26/2012)

---

Cadavre,

I didn't realize that if you DECLAREd the local variables within the scope of the dynamic SQL, they would still be in scope upon return to the calling session.

That...

--First, let's create your sample data

--First table

CREATE TABLE #yourTable1 (ID INT, TAG VARCHAR(5) CHECK (TAG IN ('true','false')), COLOR VARCHAR(10));

INSERT INTO #yourTable1

SELECT ID, TAG, COLOR

FROM (VALUES(1, 'true', 'WHITE'),

...

No. The point I was trying to illustrate with the dynamic SQL I showed you, is that...

vinu512 (4/25/2012)

---

Cadavre, i can accomplish that result by this query:

Select * From Ex

😀

But I want the results as follows:

Declare

@temp1 varchar(30) = 'Jack',

@temp2 varchar(30) = 'Vinu',

@temp3 varchar(30) = 'Jim',

@temp4 varchar(30) =...

vinu512 (4/25/2012)

---

Suppose, I have this DDL and Sample Data:

--DDL

Create Table Ex(Name varchar(30) )

--Sample Data

Insert Into Ex

Select 'Jack'

Union all

Select 'Vinu'

Union all

Select 'Jim'

Union all

Select 'Stan'

Union all

Select 'Ash'

I want to loop through the...

dwain.c (4/23/2012)

---

DECLARE @Sample TABLE ( Product varchar(10), City varchar(50), Sales decimal(12,2))

INSERT INTO @Sample VALUES('12345','NewYork','20.00')

INSERT INTO @Sample VALUES('54321','NewYork','15.00')

INSERT INTO @Sample VALUES('54321','NewYork','9.00')

INSERT INTO @Sample VALUES('12345','NewHaven','8.00')

SELECT * FROM @Sample

;WITH cte AS (

SELECT Product,...

Try something more like this: -

CREATE myproc (@operator VARCHAR(7),...

salam 52473 (4/20/2012)

---

Hey,

I am making attendance management system in VS2010 with RFID technology, doing all my backend data manipulation in SQL server 2005, I know basic SQL, but I'm going...

ColdCoffee (4/20/2012)

---

Cadavre (4/20/2012)

---

Vedran Kesegic (4/19/2012)

---

Yes, but CTE bring limitations: it wont work in a view, and it works on less SQL versions that without CTE. So, my choice is to...