ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,716 through 5,730 (of 10,143 total)

RE: create YTD in a Table

ScottPletcher (9/5/2012)
Since the table already contains the previous year's YTD totals, would a simple LEFT JOIN suffice to get the prior year's YTD total?
SELECT
t1curr.cod, t1curr.[year], t1curr.[month],...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 6, 2012 at 2:36 am

#1534302
RE: Cannot get Left JOIN to work correctly

Looks fine to me, Tom. Thanks for the feedback.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 6, 2012 at 2:27 am

#1534297
RE: Time as a high precision difference of dates

bmahf (9/5/2012)
Oops, the s_difference+1 is incorrect. That should be been just s_difference. The minus is correct.
Here's a version which corrects for aTime being more recent than bTime, and...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 6, 2012 at 2:18 am

#1534290
RE: Time as a high precision difference of dates

This might be better than the CASE:
With MyCTE (aTime, bTime)
AS
(
SELECT convert(datetime2,'1900-01-01 00:08:00.0000001'), convert(datetime2,'1900-01-01 00:09:18.361')
UNION ALL SELECT '1900-01-01 00:08:00.0000001', '1900-01-01 00:13:50.705'
UNION ALL SELECT '1900-01-01 00:09:18.361', '1900-01-01 03:13:50.7050001'
...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 5, 2012 at 9:55 am

#1533964
RE: it returning null

vinay.varaala (9/5/2012)
select dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0) -- Beginning of previous month
getdate()-- current date
my question I want the records from beginning of previous month to today ..wats...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 5, 2012 at 9:40 am

#1533951
RE: it returning null

vinay.varaala (9/5/2012)
thanks got the error. cheers..
one more question i want to select a record of 2012-05-20 date how to teh code for dateadd()
This is far too vague. What are you...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 5, 2012 at 9:29 am

#1533938
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

SQL Kiwi (9/4/2012)
Blog post is up: http://bit.ly/ComputeScalar
"Deferred execution" - the next best thing to "Let off the hook" 😀
Thanks for an excellent read, Paul.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 5, 2012 at 8:16 am

#1533842
RE: Time as a high precision difference of dates

Tricky but not impossible: this is just a proof of concept.
With MyCTE (TheTime)
AS
(
SELECT convert(datetime2,'1900-01-01 0:08:00.0000001') UNION ALL
SELECT '1900-01-01 0:09:18.361' UNION ALL
SELECT '1900-01-01 0:13:50.705'
)
SELECT
*,
CAST(f.q AS...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 5, 2012 at 7:39 am

#1533814
RE: sql server table valued function parameter issue

nzhang6666 (9/5/2012)
I got following error when compiling --1 (commented --2 and --3).
Incorrect syntax near '('.
Are you testing on SQL server 2005?
It's probably the missing batch separator "GO" after the...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 5, 2012 at 6:23 am

#1533756
RE: SQL help

woody_rd (9/5/2012)
Sorry for the confusion, guys. Let's see if this explains it better. I have come across an issue where a table logs conditions with
a timestamp, eventid...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 5, 2012 at 6:09 am

#1533744
RE: sql server table valued function parameter issue

Lowell (9/5/2012)
except for the missing GO statement between the end of the proc and the DECLARE, when i uncomment #2, all three work perfectly.
Same here. I wonder what the secret...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 5, 2012 at 6:04 am

#1533743
RE: create YTD in a Table

subbareddy542 (9/5/2012)
use below code:
...
After correcting [val].
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 5, 2012 at 5:51 am

#1533735
RE: create YTD in a Table

Koen's nailed all the good reasons for not persisting this information. It's easy enough to calculate on the fly:
-- This running total uses a triangular join.
-- The performance of TJ's...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 5, 2012 at 5:44 am

#1533732
RE: Running Values ( Cummulative)

ChrisM@Work (9/4/2012)
farooq.hbs (9/4/2012)
...
Hoping will get the Solution for this
Thanks
Of course - but you will get a solution much quicker if you can set up DDL and DML for some sample...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 5, 2012 at 3:52 am

#1533703
RE: Running Values ( Cummulative)

Mitesh Oswal (9/4/2012)
Farooq,
Can you please verify the below code. Looks better performance...
Very interesting. Can you post the test script which you used for the comparison?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

September 4, 2012 at 9:32 am

#1533359

Viewing 15 posts - 5,716 through 5,730 (of 10,143 total)