September 19, 2012 at 9:16 am
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 dates. You do yourself no favours by changing the date arithmetic back to the original each time I attempt to correct it. This...
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
will result in very poor performance, because even if there is an index on Transaction_date, SQL Server will not be able to use it. You need to match the datatype of the date constants to the column, not the other way around. In order to do that, you need to know the data type of Transaction_Date, and if it is a datetime, you need to know if the time component has values. Can you answer this?
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:43 am
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 out your dates. You do yourself no favours by changing the date arithmetic back to the original each time I attempt to correct it. This...
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)will result in very poor performance, because even if there is an index on Transaction_date, SQL Server will not be able to use it. You need to match the datatype of the date constants to the column, not the other way around. In order to do that, you need to know the data type of Transaction_Date, and if it is a datetime, you need to know if the time component has values. Can you answer this?
because i m passing the value from web is nvarchar, so i need to convert the nvarchar to date format, otherwise, Transaction_Date is DateTime,i just want to compare date, so i need to convert Datetime to Date only, if not some data didn't show in result.
p/s:i just want to compare date, not Date and Time
and Chris,
how we can do at here
OUTER APPLY ( -- SUM of all the amounts prior to date range
SELECT
Top 1 Before_Amount as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
ORDER BY Transaction_Date
) Option3
if the Open_Balance is null between 16-09-2012 and 30-09-2012,then change the sql query to check the transaction ONLY before 16-09-2012(NOT BETWEEN, IS ONLY BEFORE 16-09-2012) , how to do this??
September 19, 2012 at 9:59 am
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 out your dates. You do yourself no favours by changing the date arithmetic back to the original each time I attempt to correct it. This...
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)will result in very poor performance, because even if there is an index on Transaction_date, SQL Server will not be able to use it. You need to match the datatype of the date constants to the column, not the other way around. In order to do that, you need to know the data type of Transaction_Date, and if it is a datetime, you need to know if the time component has values. Can you answer this?
because i m passing the value from web is nvarchar, so i need to convert the nvarchar to date format, otherwise, Transaction_Date is DateTime,i just want to compare date, so i need to convert Datetime to Date only, if not some data didn't show in result.
p/s:i just want to compare date, not Date and Time
and Chris,
how we can do at here
OUTER APPLY ( -- SUM of all the amounts prior to date rangeSELECT
Top 1 Before_Amount as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
ORDER BY Transaction_Date
) Option3
if the Open_Balance is null between 16-09-2012 and 30-09-2012,then change the sql query to check the transaction ONLY before 16-09-2012(NOT BETWEEN, IS ONLY BEFORE 16-09-2012) , how to do this??
OUTER APPLY ( -- SUM of all the amounts prior to date range
SELECT
Top 1 Before_Amount as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date < convert(Date,'16-09-2012', 105)
ORDER BY Transaction_Date
) Option3
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:50 pm
ChrisM@Work (9/19/2012)
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 out your dates. You do yourself no favours by changing the date arithmetic back to the original each time I attempt to correct it. This...
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)will result in very poor performance, because even if there is an index on Transaction_date, SQL Server will not be able to use it. You need to match the datatype of the date constants to the column, not the other way around. In order to do that, you need to know the data type of Transaction_Date, and if it is a datetime, you need to know if the time component has values. Can you answer this?
because i m passing the value from web is nvarchar, so i need to convert the nvarchar to date format, otherwise, Transaction_Date is DateTime,i just want to compare date, so i need to convert Datetime to Date only, if not some data didn't show in result.
p/s:i just want to compare date, not Date and Time
and Chris,
how we can do at here
OUTER APPLY ( -- SUM of all the amounts prior to date rangeSELECT
Top 1 Before_Amount as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
ORDER BY Transaction_Date
) Option3
if the Open_Balance is null between 16-09-2012 and 30-09-2012,then change the sql query to check the transaction ONLY before 16-09-2012(NOT BETWEEN, IS ONLY BEFORE 16-09-2012) , how to do this??
OUTER APPLY ( -- SUM of all the amounts prior to date rangeSELECT
Top 1 Before_Amount as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date < convert(Date,'16-09-2012', 105)
ORDER BY Transaction_Date
) Option3
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?
September 20, 2012 at 1:42 am
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.
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 2:32 am
ChrisM@Work (9/20/2012)
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.
i have no idea where is how to put in the IsNull
OUTER APPLY ( -- SUM of all the amounts prior to date range
SELECT
IsNull(Top 1 Before_Amount,0.00) as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date < convert(Date,'16-09-2012', 105)
ORDER BY Transaction_Date
) Option3
Chris, Please Help ! Seriously i have no idea at all whereto put
September 20, 2012 at 3:00 am
ChrisM@Work (9/19/2012)
Easy - but first, we need to sort out your dates. You do yourself no favours by changing the date arithmetic back to the original each time I attempt to correct it. This...
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)will result in very poor performance, because even if there is an index on Transaction_date, SQL Server will not be able to use it. You need to match the datatype of the date constants to the column, not the other way around.
Interestingly, this isn't always so - and not in this case. Try running the code with Actual Execution Plan - you get an Index Seek
if object_id('Tbl_Transaction') is not null
drop table Tbl_Transaction;
create table Tbl_Transaction
(
Transaction_Date DateTime
);
create nonclustered index ix_Transaction_Date on Tbl_Transaction ( Transaction_Date );
insert Tbl_Transaction values ( '11 Sep 2012 12:00:00' ) ;
insert Tbl_Transaction values ( '16 Sep 2012 12:00:00' ) ;
insert Tbl_Transaction values ( '19 Sep 2012 12:00:00' ) ;
insert Tbl_Transaction values ( '30 Sep 2012 12:00:00' ) ;
insert Tbl_Transaction values ( '30 Sep 2012 12:00:00' ) ;
insert Tbl_Transaction values ( '01 Oct 2012 12:00:00' ) ;
-- (Edit: Added time element to values.)
select * from Tbl_Transaction;
select *
FROM Tbl_Transaction ti
where convert(Date,ti.Transaction_Date, 105)
BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
See HowardW's post on this thread (at the end, last time I looked).
http://www.sqlservercentral.com/Forums/Topic1361226-392-1.aspx
I agree it's good practice to avoid functions in where clauses, but it's worth bearing in mind that they might be SARGable.
September 20, 2012 at 3:30 am
laurie-789651 (9/20/2012)
ChrisM@Work (9/19/2012)
Easy - but first, we need to sort out your dates. You do yourself no favours by changing the date arithmetic back to the original each time I attempt to correct it. This...
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)will result in very poor performance, because even if there is an index on Transaction_date, SQL Server will not be able to use it. You need to match the datatype of the date constants to the column, not the other way around.
Interestingly, this isn't always so - and not in this case. Try running the code with Actual Execution Plan - you get an Index Seek
if object_id('Tbl_Transaction') is not null
drop table Tbl_Transaction;
create table Tbl_Transaction
(
Transaction_Date DateTime
);
create nonclustered index ix_Transaction_Date on Tbl_Transaction ( Transaction_Date );
insert Tbl_Transaction values ( '11 Sep 2012 12:00:00' ) ;
insert Tbl_Transaction values ( '16 Sep 2012 12:00:00' ) ;
insert Tbl_Transaction values ( '19 Sep 2012 12:00:00' ) ;
insert Tbl_Transaction values ( '30 Sep 2012 12:00:00' ) ;
insert Tbl_Transaction values ( '30 Sep 2012 12:00:00' ) ;
insert Tbl_Transaction values ( '01 Oct 2012 12:00:00' ) ;
-- (Edit: Added time element to values.)
select * from Tbl_Transaction;
select *
FROM Tbl_Transaction ti
where convert(Date,ti.Transaction_Date, 105)
BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
See HowardW's post on this thread (at the end, last time I looked).
http://www.sqlservercentral.com/Forums/Topic1361226-392-1.aspx
I agree it's good practice to avoid functions in where clauses, but it's worth bearing in mind that they might be SARGable.
You do get an index seek, but it's not against a single value as Howard points out. The filter is this:
[tempdb].[dbo].[#test].[DateColumn]>[Expr1006],
[tempdb].[dbo].[#test].[DateColumn]<[Expr1007]
which is a range scan. The compute scalar appears to be calculating a start datetime and end datetime. I'd suggest that this is a highly specific optimisation tailored to a very common task, filtering datetimes using dates. Since it's appropriate here, thanks Laurie for pointing it out. I think it's safe to continue to generalise about functions on columns though.
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 3:34 am
chinye2020 (9/20/2012)
ChrisM@Work (9/20/2012)
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.
i have no idea where is how to put in the IsNull
OUTER APPLY ( -- SUM of all the amounts prior to date rangeSELECT
IsNull(Top 1 Before_Amount,0.00) as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date < convert(Date,'16-09-2012', 105)
ORDER BY Transaction_Date
) Option3
Chris, Please Help ! Seriously i have no idea at all whereto put
Post the full query as you have it now - I've got kinda lost through all the iterations. We'll take it from there.
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 4:22 am
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,
Option3.Open_Balance + t.TransactionsForPeriod AS ClosingBalance
FROM Tbl_Account a
LEFT JOIN (
SELECT
d.Transaction_AccNo,
SUM(d.Amount) AS TransactionsForPeriod,
SUM( case when d.TransType_ID = 0 then d.Amount else 0 end ) as Total_Adjustment,
SUM( case when d.TransType_ID = 1 then d.Amount else 0 end ) as Total_Topup,
SUM( case when d.TransType_ID = 2 then d.Amount else 0 end ) as Total_Comm,
SUM( case when d.TransType_ID = 3 then d.Amount else 0 end ) as Total_Transfer,
SUM( case when d.TransType_ID = 4 then d.Amount else 0 end ) as Total_RecvTransfer,
SUM( case when d.TransType_ID = 1 AND d.Topup_Company = 'M' then d.Amount else 0 end ) as Total_Maxis,
SUM( case when d.TransType_ID = 1 AND d.Topup_Company = 'D' then d.Amount else 0 end ) as Total_Digi,
SUM( case when d.TransType_ID = 1 AND d.Topup_Company = 'C' then d.Amount else 0 end ) as Total_Celcom
FROM (
SELECT
ti.Transaction_AccNo,
ti.TransType_ID,
p.Topup_Company,
Amount = SUM(ti.Amount)
FROM Tbl_Transaction ti
LEFT JOIN Tbl_Topup p ON p.Topup_ID = ti.Topup_ID
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'1-09-2012', 105) AND convert(Date,'30-09-2012', 105)
GROUP BY ti.Transaction_AccNo, ti.TransType_ID, p.Topup_Company
) d
GROUP BY d.Transaction_AccNo
) t
ON t.Transaction_AccNo = a.AccNo
OUTER APPLY ( -- SUM of all the amounts prior to date range
SELECT
Top 1 Before_Amount as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'1-09-2012', 105) AND convert(Date,'30-09-2012', 105)
ORDER BY Transaction_Date
) Option3
ORDER BY a.AccNo
September 20, 2012 at 4:39 am
Note that the date arithmetic is still messed up - but I'm bored with making corrections to it because you keep throwing them away. The date arithmetic I posted was designed to deal with Transaction_Date as datetime.
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,
Open_Balance = ISNULL(Option3.Open_Balance,y.Open_Balance),
ISNULL(Option3.Open_Balance,y.Open_Balance) + ISNULL(t.TransactionsForPeriod,0) AS ClosingBalance
FROM Tbl_Account a
LEFT JOIN (
SELECT
d.Transaction_AccNo,
SUM(d.Amount) AS TransactionsForPeriod,
SUM( case when d.TransType_ID = 0 then d.Amount else 0 end ) as Total_Adjustment,
SUM( case when d.TransType_ID = 1 then d.Amount else 0 end ) as Total_Topup,
SUM( case when d.TransType_ID = 2 then d.Amount else 0 end ) as Total_Comm,
SUM( case when d.TransType_ID = 3 then d.Amount else 0 end ) as Total_Transfer,
SUM( case when d.TransType_ID = 4 then d.Amount else 0 end ) as Total_RecvTransfer,
SUM( case when d.TransType_ID = 1 AND d.Topup_Company = 'M' then d.Amount else 0 end ) as Total_Maxis,
SUM( case when d.TransType_ID = 1 AND d.Topup_Company = 'D' then d.Amount else 0 end ) as Total_Digi,
SUM( case when d.TransType_ID = 1 AND d.Topup_Company = 'C' then d.Amount else 0 end ) as Total_Celcom
FROM (
SELECT
ti.Transaction_AccNo,
ti.TransType_ID,
p.Topup_Company,
Amount = SUM(ti.Amount)
FROM Tbl_Transaction ti
LEFT JOIN Tbl_Topup p ON p.Topup_ID = ti.Topup_ID
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'1-09-2012', 105) AND convert(Date,'30-09-2012', 105)
GROUP BY ti.Transaction_AccNo, ti.TransType_ID, p.Topup_Company
) d
GROUP BY d.Transaction_AccNo
) t
ON t.Transaction_AccNo = a.AccNo
OUTER APPLY (
SELECT
Top 1 Before_Amount as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'1-09-2012', 105) AND convert(Date,'30-09-2012', 105)
ORDER BY Transaction_Date
) Option3
OUTER APPLY ( -- most recent balance prior to selected date range
SELECT
Top 1 Before_Amount as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND convert(Date,ti.Transaction_Date, 105)< convert(Date,'1-09-2012', 105)
ORDER BY Transaction_Date DESC
) y
WHERE a.AccNo <> 1 -- new WHERE clause
ORDER BY a.AccNo
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 4:43 am
Thanks,Chris. understood all.....thanks a lot(but i haven't tried the code yet)
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply