Jeff Moden

Forum Replies Created

Viewing 15 posts - 32,656 through 32,670 (of 59,099 total)

RE: Same Date and Various Time Select in Single Row in sql server

tamil.selvanmca (9/2/2011)
EmpCode ShiftDateTime
2058 01/02/2011 13:43
2058 01/02/2011 20:12
2058 01/03/2011 14:04
2058 01/03/2011 20:13
2058 01/04/2011 14:09
2058 01/04/2011 20:12
I Want Show Like This
EmpCode ShiftDateTime TimeDetail
2058 01/02/2011 13:43 13:43,20:12
2058 01/03/2011 14:04 14:04,20:13
2058 01/04/2011 14:09 14:19,20:12
Please...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 8:11 pm

#1376732
RE: Select where records dont exist

sqlzealot-81 does the trick although you could get rid of the outer select.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 7:55 pm

#1376731
RE: Looking for help taking the RBAR from this problem

@clubbavich,
What is the average number of Device_ID's you have per server? I'm asking because I think I see some major issues with the indexes on this table but don't...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 7:16 pm

#1376729
RE: combine string from multiple records

Sean Lange (9/2/2011)
Jeff Moden (9/2/2011)
The XML method won't work on NTEXT, period. Also be advised than NTEXT has been deprecated in favor of NVARCHAR(MAX). Since you're posting in...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 6:38 pm

#1376728
RE: how to convert sqlserver UDF into CLR based function

There's one major problem that I see in all of this... [font="Arial Black"]why are we converting things that aren't dates into NULLs when the original data is SUPPOSED TO ONLY...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 6:32 pm

#1376727
RE: how to convert sqlserver UDF into CLR based function

nellisjp (9/2/2011)
Right, just a simple case statement like this (I'm sure this probably has plenty of holes, but it's not a half-bad place to start)
Select Case When Len(InputDate) <> 8...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 6:29 pm

#1376726
RE: how to convert sqlserver UDF into CLR based function

Noooooooooooooo!!!! 😉
You don't need to even have a UDF to do this. Just cast the 8 character input field as a DATETIME datatype and you're done!
As a side bar,...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 2:33 pm

#1376690
RE: converting into standard date format

You bet... thank you for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 2:25 pm

#1376689
RE: Bulk Insert with bad data

Darryl Wilson (9/2/2011)
I have a bulk insert statement for a csv, but this particular clients' data is making it difficult to import.
Its in the following format:
GREEN,0016861779528,"Dembo, Ron C","""Roulette"" or the...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 2:25 pm

#1376688
RE: Storing old and new values table structure

falcon00 (9/2/2011)
I have always been hesitant when such requests for a new table structure would come in.
The purpose is simply to use it for a request, where user would physically...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 2:16 pm

#1376686
RE: combine string from multiple records

The XML method won't work on NTEXT, period. Also be advised than NTEXT has been deprecated in favor of NVARCHAR(MAX). Since you're posting in a 2k8 forum, I...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 1:54 pm

#1376670
RE: What is an efficient way to retrieve product counts at multiple levels of a hierarchy?

First, welcome aboard! 🙂
There are 3 basic types of hierarchies... how are your hierarchies being stored? Adjacency List, Hierarchical Path, Nested Sets?
Since you're brand-spanking new to this forum, please...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 8:01 am

#1376447
RE: converting into standard date format

If your 6 digit (2 year ISO date) is actually an Integer, then the following will do...
SELECT CAST(CAST(110815 AS CHAR(6)) AS DATETIME);
If it's already character based, then the following...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 7:44 am

#1376435
RE: The maximum recursion 100 has been exhausted before statement completion

I see lot's of folks using Recursive CTE's (rCTE's) for this task. [font="Arial Black"]PLEASE [/font]don't use or recommend rCTE's for this or any simple counting task. Use one...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 7:28 am

#1376424
RE: The maximum recursion 100 has been exhausted before statement completion

alexander.semenov (2/23/2010)
Hi Grasshopper,
Just stumbled on your topic and thought about why one should limit oneself with maximum recursion limit? We can use even less memory if we want to... The...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 2, 2011 at 7:25 am

#1376423

Viewing 15 posts - 32,656 through 32,670 (of 59,099 total)