ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,611 through 5,625 (of 10,143 total)

RE: Sum with Multi Category and Group By Acc No ?

chinye2020 (9/19/2012)
...
No,Chris, When the Acc No's Open_balance is null Between the date only change the sql query to before 16-09-12...
how to do that?
Use ISNULL or COALESCE in the output set.
^{“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 20, 2012 at 1:42 am

#1539920
RE: Fuzzy-String Search: Find misspelled information with T-SQL

Thomas Keller (9/19/2012)
@chrism-2@Work: Interesting approaches, but I'm wondering why you use DATALENGTH instead of LEN, are you trying to include trailing spaces? (In order to work with NVARCHAR instead of...
^{“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 19, 2012 at 10:00 am

#1539664
RE: Sum with Multi Category and Group By Acc No ?

chinye2020 (9/19/2012)
ChrisM@Work (9/19/2012)
chinye2020 (9/19/2012)
...
This work perfectly like the CTE, well done, but, how to edit sql query only show AccNo <> 1 ??
...
Easy - but first, we need to sort...
^{“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 19, 2012 at 9:59 am

#1539663
RE: Sum with Multi Category and Group By Acc No ?

chinye2020 (9/19/2012)
...
This work perfectly like the CTE, well done, but, how to edit sql query only show AccNo <> 1 ??
...
Easy - but first, we need to sort out your...
^{“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 19, 2012 at 9:16 am

#1539625
RE: Datatype truncation

Sean Lange (9/19/2012)
ChrisM@Work (9/19/2012)
Sean Lange (9/7/2012)
ChrisM@Work (9/7/2012)
sqlnaive (9/7/2012)
For such kind of reasons we should avoid float and real datatypes ? And rather use decimal and numeric ?
Have a look in...
^{“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 19, 2012 at 8:13 am

#1539589
RE: Sum with Multi Category and Group By Acc No ?

chinye2020 (9/19/2012)
...
DONE!
That's not opening and closing balance - it's simply the amounts of the first and last transactions in the date range.
SELECT
a.AccNo,
t.Total_Adjustment,
t.Total_Topup,
t.Total_Comm,
t.Total_Transfer,
t.Total_RecvTransfer,
t.Total_Maxis,
t.Total_Digi,
t.Total_Celcom,
Option3.Open_Balance + t.TransactionsForPeriod AS ClosingBalance
FROM Tbl_Account a
LEFT JOIN...
^{“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 19, 2012 at 7:58 am

#1539576
RE: Sum with Multi Category and Group By Acc No ?

chinye2020 (9/19/2012)
...
DONE !, this is select Top 1 Amount,But how to select the LAST Amount as Closing_Balance?
Performance will be very poor. What datatype is Tbl_Transaction.Transaction_Date?
^{“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 19, 2012 at 7:24 am

#1539544
RE: Sum with Multi Category and Group By Acc No ?

SELECT
a.AccNo,
SUM( case when t.TransType_ID = 0 then t.Amount else 0 end ) as Total_Adjustment,
SUM( case when t.TransType_ID = 1 then t.Amount else 0 end ) as Total_Topup,
SUM( case when...
^{“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 19, 2012 at 7:21 am

#1539542
RE: Sum with Multi Category and Group By Acc No ?

chinye2020 (9/19/2012)
i mean select the top 1 Amount in the sql query
Ordered by what? Amount? Transaction date?
^{“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 19, 2012 at 6:47 am

#1539523
RE: Fuzzy-String Search: Find misspelled information with T-SQL

Interesting article and well worth a second read, and a play with the code.
I've used two TSQL fuzzy-matching algorithms in the past, both work "well enough" - subjective and also...
^{“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 19, 2012 at 4:39 am

#1539481
RE: Today's Random Word!

L' Eomot Inversé (9/15/2012)
capnhector (9/14/2012)
supercalifornication?
Or supercalifellation?
Sounds like a turkey.
^{“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 19, 2012 at 3:06 am

#1539445
RE: Sum with Multi Category and Group By Acc No ?

chinye2020 (9/18/2012)
...(SELECT Top 1 Amount as Open_Balance)<<---how to do this at here?i want to get the first Amount as Opening Balance
...
Write a separate query for this, then integrate it into...
^{“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 19, 2012 at 2:19 am

#1539426
RE: Variable String + Variable

samtwilliams (9/19/2012)
Can anyoen help with this.
Thanks
DECLARE @variable varchar(100), @command VARCHAR(500)
SET @variable = 'E:\MyData\Target\CSV'
SET @command = N'Exec dbo.pReport_Update_Unit ''' + @variable + ''''
SELECT @command
^{“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 19, 2012 at 2:08 am

#1539423
RE: Datatype truncation

Sean Lange (9/7/2012)
ChrisM@Work (9/7/2012)
sqlnaive (9/7/2012)
For such kind of reasons we should avoid float and real datatypes ? And rather use decimal and numeric ?
Have a look in BOL under CONVERT...
^{“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 19, 2012 at 2:01 am

#1539420
RE: Need Help In Query .

abhas (9/18/2012)
Hi Chris,
I tried using CTE but not able to do.
No problem - post what you have, we'll help you make it work.
^{“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 18, 2012 at 2:13 am

#1538942

Viewing 15 posts - 5,611 through 5,625 (of 10,143 total)