Jeff Moden

Forum Replies Created

Viewing 15 posts - 19,366 through 19,380 (of 59,072 total)

RE: Query to Split the String as rows and columns in SQL

Luis Cazares (10/19/2015)
This can be done without temp tables as there's a single row returned.
Unless I'm missing something, here's my suggestion.
--Dynamic Pivot
DECLARE @sql NVARCHAR(MAX);
/*
STUFF is used...
--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)

October 24, 2015 at 7:11 pm

#1835377
RE: How to format data in a grid view (pivot?)

Meatloaf (10/23/2015)
Hello, I am looking for a way to create a stored procedure that will show inventory availability. I would like to show the Inventory Name, The Date,...
--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)

October 24, 2015 at 6:16 pm

#1835374
RE: Using Dynamic SQL To Order Column Names

I'll never understand why anyone considers dynamic SQL to not be an elegant solution when it's so bloody effective (and safe) when done correctly.
--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)

October 24, 2015 at 6:09 pm

#1835373
RE: Positive or Negative

I use the "IsActive" type of thing because 1 is usually considered to be TRUE. I've seen people use negative logic and names before and it's nothing but trouble...
--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)

October 24, 2015 at 6:00 pm

#1835372
RE: need to convert string to datetime datatype in DFT

rajbetl11 (10/23/2015)
team,
I have tried with below code in "Execute SQL Task"
Declare @FileDate datetime
declare @filename varchar
-- filename = test_source_file_20150920
Set @FileDate = substring(@filename,11,8)
select @FileDate
but it's giving default value not from...
--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)

October 24, 2015 at 4:13 pm

#1835369
RE: Just Some Help To Impress My New Boss Pls

asafswis2 (10/23/2015)
i add the file
i need help to build sql query as show in file
tnx all
http://1drv.ms/1R21ene
Links to speadsheets just aren't going to cut it for readily consumable...
--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)

October 24, 2015 at 3:50 pm

#1835368
RE: A Simple Formula to Calculate the ISO Week Number

robert.sterbal 56890 (10/23/2015)
On a related note:
What is the ISO standard for midnight?
2015-03-04 23:59
2015-03-0? 00:00
2015-03-05 00:01
Does ? = 4 or 5
To the best of my knowledge, I know of no temporal...
--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)

October 24, 2015 at 3:36 pm

#1835367
RE: CASE Statement with Date

Tamrak (10/23/2015)
Thank you very much for suggesting the tally table. This prompted me to think of using the 'with cte' statement.
I did not use the tally. But decide to use...
--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)

October 24, 2015 at 2:10 pm

#1835365
RE: A Simple Formula to Calculate the ISO Week Number

marc.corbeel (10/23/2015)
Does this means that Sunday 3 January 2016 is also week 53 in US standards?
No. It means that it's week 53 according to ISO standards.
The US is a...
--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)

October 23, 2015 at 2:17 pm

#1835316
RE: A Simple Formula to Calculate the ISO Week Number

marc.corbeel (10/23/2015)
I don't believe it's a correct calculation for all situations. It doesn't look at the first day of a week. In Europe that is Monday, in other parts of...
--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)

October 23, 2015 at 9:16 am

#1835225
RE: A Simple Formula to Calculate the ISO Week Number

Beugen (10/23/2015)
Hi
Thanks for this simple and effective code.
Is there a similar easy way to get the year of that week we are looking for? Or the "ISO-year"?
Lets say I put...
--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)

October 23, 2015 at 9:14 am

#1835223
RE: A Simple Formula to Calculate the ISO Week Number

robert.sterbal 56890 (10/23/2015)
I like the way your formula skips the leap year problem.
I still remember when the church moved the calendar by a couple of weeks - https://en.wikipedia.org/wiki/Gregorian_calendar
That would be...
--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)

October 23, 2015 at 8:50 am

#1835209
RE: Query on a large database

Jayanth_Kurup (10/23/2015)
Jeff Moden (10/23/2015)
Jayanth_Kurup (10/23/2015)
Hi Sergiy
Here is a screen shot with clustered index on the monolithic table on REF_AGE column as requested. Notice that it took 17 sec ,...
--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)

October 23, 2015 at 8:38 am

#1835206
RE: Query on a large database

Took a quick peek at the code you posted.
EXEC sys.sp_db_vardecimal_storage_format N'partitioned', N'ON'
I could be misreading something but you do know that VarDecimal has been deprecated, yes?
--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)

October 23, 2015 at 8:34 am

#1835202
RE: Export 3 different data records to a flat file

michal.lisinski (10/23/2015)
I developed simlar project a few years ago. It exports invoices to the flat file in specified order:
Invoice 1
Invoice header
Item lines
Subtotal netto
Subtotal by VAT
Total
Footer
...
Invoice n
Invoice header
Item lines
Subtotal...
--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)

October 23, 2015 at 8:28 am

#1835200

Viewing 15 posts - 19,366 through 19,380 (of 59,072 total)