InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 08:45:12 GMT20http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspxThanks,Chris. understood all.....thanks a lot(but i haven't tried the code yet)Thu, 20 Sep 2012 04:43:51 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspxNote 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.[code="sql"]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 ClosingBalanceFROM Tbl_Account aLEFT 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) Option3OUTER 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) yWHERE a.AccNo <> 1 -- new WHERE clauseORDER BY a.AccNo[/code]Thu, 20 Sep 2012 04:39:44 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspxSELECTa.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 ClosingBalanceFROM Tbl_Account aLEFT JOIN (SELECTd.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_CelcomFROM (SELECTti.Transaction_AccNo,ti.TransType_ID,p.Topup_Company,Amount = SUM(ti.Amount)FROM Tbl_Transaction tiLEFT JOIN Tbl_Topup p ON p.Topup_ID = ti.Topup_IDwhere 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 [b]OUTER APPLY ( -- SUM of all the amounts prior to date rangeSELECTTop 1 Before_Amount as Open_BalanceFROM Tbl_Transaction tiWHERE ti.Transaction_AccNo = a.AccNo -- outer refAND 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[/b]ORDER BY a.AccNoThu, 20 Sep 2012 04:22:19 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]chinye2020 (9/20/2012)[/b][hr][quote][b]ChrisM@Work (9/20/2012)[/b][hr][quote][b]chinye2020 (9/19/2012)[/b][hr]...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?[/quote]Use ISNULL or COALESCE in the output set.[/quote]i have no idea where is how to put in the IsNull[code="sql"]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[/code]Chris, Please Help ! Seriously i have no idea at all whereto put[/quote]Post the full query as you have it now - I've got kinda lost through all the iterations. We'll take it from there.Thu, 20 Sep 2012 03:34:19 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]laurie-789651 (9/20/2012)[/b][hr][quote][b]ChrisM@Work (9/19/2012)[/b][hr]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...[code="sql"] where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)[/code]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. [/quote]Interestingly, this isn't always so - and not in this case. Try running the code with Actual Execution Plan - you get an Index Seek[code="sql"]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 tiwhere convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)[/code]See HowardW's post on this thread (at the end, last time I looked).[url]http://www.sqlservercentral.com/Forums/Topic1361226-392-1.aspx[/url]I agree it's good practice to avoid functions in where clauses, but it's worth bearing in mind that they might be SARGable.[/quote]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.Thu, 20 Sep 2012 03:30:36 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]ChrisM@Work (9/19/2012)[/b][hr]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...[code="sql"] where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)[/code]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. [/quote]Interestingly, this isn't always so - and not in this case. Try running the code with Actual Execution Plan - you get an Index Seek[code="sql"]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 tiwhere convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)[/code]See HowardW's post on this thread (at the end, last time I looked).[url]http://www.sqlservercentral.com/Forums/Topic1361226-392-1.aspx[/url]I agree it's good practice to avoid functions in where clauses, but it's worth bearing in mind that they might be SARGable.Thu, 20 Sep 2012 03:00:42 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]ChrisM@Work (9/20/2012)[/b][hr][quote][b]chinye2020 (9/19/2012)[/b][hr]...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?[/quote]Use ISNULL or COALESCE in the output set.[/quote]i have no idea where is how to put in the IsNull[code="sql"]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[/code]Chris, Please Help ! Seriously i have no idea at all whereto putThu, 20 Sep 2012 02:32:22 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]chinye2020 (9/19/2012)[/b][hr]...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?[/quote]Use ISNULL or COALESCE in the output set.Thu, 20 Sep 2012 01:42:31 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]ChrisM@Work (9/19/2012)[/b][hr][quote][b]chinye2020 (9/19/2012)[/b][hr][quote][b]ChrisM@Work (9/19/2012)[/b][hr][quote][b]chinye2020 (9/19/2012)[/b][hr][quote]...This work perfectly like the CTE, well done, but, how to edit sql query only show AccNo <> 1 ??...[/quote]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...[code="sql"] where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)[/code]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?[/quote]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 Timeand Chris,how we can do at here[code="sql"]OUTER APPLY ( -- SUM of all the amounts prior to date rangeSELECTTop 1 Before_Amount as Open_BalanceFROM Tbl_Transaction tiWHERE ti.Transaction_AccNo = a.AccNo -- outer refAND 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[/code]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??[/quote][code="sql"]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[/code][/quote]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?Wed, 19 Sep 2012 15:50:24 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]chinye2020 (9/19/2012)[/b][hr][quote][b]ChrisM@Work (9/19/2012)[/b][hr][quote][b]chinye2020 (9/19/2012)[/b][hr][quote]...This work perfectly like the CTE, well done, but, how to edit sql query only show AccNo <> 1 ??...[/quote]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...[code="sql"] where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)[/code]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?[/quote]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 Timeand Chris,how we can do at here[code="sql"]OUTER APPLY ( -- SUM of all the amounts prior to date rangeSELECTTop 1 Before_Amount as Open_BalanceFROM Tbl_Transaction tiWHERE ti.Transaction_AccNo = a.AccNo -- outer refAND 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[/code]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??[/quote][code="sql"]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[/code]Wed, 19 Sep 2012 09:59:51 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]ChrisM@Work (9/19/2012)[/b][hr][quote][b]chinye2020 (9/19/2012)[/b][hr][quote]...This work perfectly like the CTE, well done, but, how to edit sql query only show AccNo <> 1 ??...[/quote]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...[code="sql"] where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)[/code]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?[/quote]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 Timeand Chris,how we can do at here[code="sql"]OUTER APPLY ( -- SUM of all the amounts prior to date rangeSELECTTop 1 Before_Amount as Open_BalanceFROM Tbl_Transaction tiWHERE ti.Transaction_AccNo = a.AccNo -- outer refAND 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[/code]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??Wed, 19 Sep 2012 09:43:12 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]chinye2020 (9/19/2012)[/b][hr][quote]...This work perfectly like the CTE, well done, but, how to edit sql query only show AccNo <> 1 ??...[/quote]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...[code="sql"] where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)[/code]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?Wed, 19 Sep 2012 09:16:23 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]ChrisM@Work (9/19/2012)[/b][hr][quote][b]chinye2020 (9/19/2012)[/b][hr][code="sql"]...DONE![/quote]That's [i]not [/i]opening and closing balance - it's simply the amounts of the first and last transactions in the date range.[code="sql"]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 aLEFT 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 = 2 AND d.Topup_Company = 'M' then d.Amount else 0 end ) as Total_Maxis, SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'D' then d.Amount else 0 end ) as Total_Digi, SUM( case when d.TransType_ID = 2 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 = t.Topup_ID WHERE ti.Transaction_Date >= convert(Date,'2012-09-1', 105) AND ti.Transaction_Date < convert(Date,'2012-10-01', 105) GROUP BY ti.Transaction_AccNo, ti.TransType_ID, p.Topup_Company ) d GROUP BY d.Transaction_AccNo) tON t.Transaction_AccNo = a.AccNo OUTER APPLY ( -- SUM of all the amounts prior to date range SELECT Open_Balance = SUM(ti.Amount) FROM Tbl_Transaction ti WHERE ti.Transaction_AccNo = a.AccNo -- outer ref AND ti.Transaction_Date < convert(Date,'2012-09-1', 105) ) Option3ORDER BY a.AccNo[/code][/quote]This work perfectly like the CTE, well done, but, how to edit sql query only show AccNo <> 1 ??[code="sql"]SELECTa.AccNo,isnull(t.Total_Adjustment,0.00),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 ClosingBalanceFROM Tbl_Account a [b]WHERE A.AccNo <> 1 <---- add at here??? ERROR ...[/b]LEFT JOIN (SELECTd.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 = 2 AND d.Topup_Company = 'M' then d.Amount else 0 end ) as Total_Maxis,SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'D' then d.Amount else 0 end ) as Total_Digi,SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'C' then d.Amount else 0 end ) as Total_CelcomFROM (SELECTti.Transaction_AccNo,ti.TransType_ID,p.Topup_Company,Amount = SUM(ti.Amount)FROM Tbl_Transaction tiLEFT JOIN Tbl_Topup p ON p.Topup_ID = ti.Topup_IDwhere convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-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.AccNoOUTER APPLY ( -- SUM of all the amounts prior to date rangeSELECTTop 1 Before_Amount as Open_BalanceFROM Tbl_Transaction tiWHERE ti.Transaction_AccNo = a.AccNo -- outer refAND convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)ORDER BY Transaction_Date) Option3ORDER BY a.AccNo[/code]Wed, 19 Sep 2012 08:55:32 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]chinye2020 (9/19/2012)[/b][hr][code="sql"]...DONE![/quote]That's [i]not [/i]opening and closing balance - it's simply the amounts of the first and last transactions in the date range.[code="sql"]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 aLEFT 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 = 2 AND d.Topup_Company = 'M' then d.Amount else 0 end ) as Total_Maxis, SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'D' then d.Amount else 0 end ) as Total_Digi, SUM( case when d.TransType_ID = 2 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 = t.Topup_ID WHERE ti.Transaction_Date >= convert(Date,'2012-09-1', 105) AND ti.Transaction_Date < convert(Date,'2012-10-01', 105) GROUP BY ti.Transaction_AccNo, ti.TransType_ID, p.Topup_Company ) d GROUP BY d.Transaction_AccNo) tON t.Transaction_AccNo = a.AccNo OUTER APPLY ( -- SUM of all the amounts prior to date range SELECT Open_Balance = SUM(ti.Amount) FROM Tbl_Transaction ti WHERE ti.Transaction_AccNo = a.AccNo -- outer ref AND ti.Transaction_Date < convert(Date,'2012-09-1', 105) ) Option3ORDER BY a.AccNo[/code]Wed, 19 Sep 2012 07:58:46 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[code="sql"]with CTE as( SELECT Transaction_AccNo, Amount,Before_Amount,After_Amount, TransType_ID, Topup_ID, ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo ORDER BY Transaction_Date) as RowNum, ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo ORDER BY Transaction_Date DESC) as LastRowNum from Tbl_Transaction where convert(Date,Transaction_Date, 105) BETWEEN '2012-09-16' AND '2012-09-30')select Tbl_Account.AccNo, SUM( case when TransType_ID = 0 then Amount else 0 end ) as Total_Adjustment, SUM( case when TransType_ID = 1 then Amount else 0 end ) as Total_Topup, SUM( case when TransType_ID = 2 then Amount else 0 end ) as Total_Comm, SUM( case when TransType_ID = 3 then Amount else 0 end ) as Total_Transfer, SUM( case when TransType_ID = 4 then Amount else 0 end ) as Total_RecvTransfer, SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='M' then Amount else 0 end ) as Total_Maxis, SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='D' then Amount else 0 end ) as Total_Digi, SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='C' then Amount else 0 end ) as Total_Celcom, SUM( case when RowNum = 1 then Before_Amount else 0 end) as Open_Balance, SUM( case when LastRowNum = 1 then After_Amount else 0 end) as Close_BalanceFROM Tbl_AccountLEFT JOIN CTE ON Tbl_Account.AccNo = CTE.Transaction_AccNoLEFT JOIN Tbl_Topup ON CTE.Topup_ID = Tbl_Topup.Topup_IDGroup By Tbl_Account.AccNoORDER BY Tbl_Account.AccNo[/code]DONE!Wed, 19 Sep 2012 07:38:55 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]chinye2020 (9/19/2012)[/b][hr][code="sql"]...[/code]DONE !, this is select Top 1 Amount,But how to select the LAST Amount as Closing_Balance?[/quote]Performance will be very poor. What datatype is Tbl_Transaction.Transaction_Date?Wed, 19 Sep 2012 07:24:17 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[code="sql"]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 t.TransType_ID = 2 then t.Amount else 0 end ) as Total_Comm, SUM( case when t.TransType_ID = 3 then t.Amount else 0 end ) as Total_Transfer, SUM( case when t.TransType_ID = 4 then t.Amount else 0 end ) as Total_RecvTransfer, SUM( case when t.TransType_ID = 2 AND p.Topup_Company='M' then t.Amount else 0 end ) as Total_Maxis, SUM( case when t.TransType_ID = 2 AND p.Topup_Company='D' then t.Amount else 0 end ) as Total_Digi, SUM( case when t.TransType_ID = 2 AND p.Topup_Company='C' then t.Amount else 0 end ) as Total_Celcom, Option1.Open_Balance, Option2.Open_Balance, Option3.Open_Balance FROM Tbl_Account aLEFT JOIN Tbl_Transaction t ON a.AccNo = t.Transaction_AccNo -- this date arithmetic should provide the same filter as the original -- but is SARGable (use Google) AND t.Transaction_Date > convert(Date,'16-9-2012', 105) AND t.Transaction_Date < convert(Date,'01-10-2012', 105)LEFT JOIN Tbl_Topup p ON t.Topup_ID = p.Topup_IDOUTER APPLY (-- first transaction of the date range, if there is one SELECT TOP 1 Open_Balance = ti.Amount FROM Tbl_Transaction ti WHERE ti.Transaction_AccNo = a.AccNo -- outer ref AND ti.Transaction_Date > convert(Date,'16-9-2012', 105) AND ti.Transaction_Date < convert(Date,'01-10-2012', 105) ORDER BY ti.Transaction_Date ) Option1OUTER APPLY (-- largest value of a transaction, if there is one SELECT TOP 1 Open_Balance = ti.Amount FROM Tbl_Transaction ti WHERE ti.Transaction_AccNo = a.AccNo -- outer ref AND ti.Transaction_Date > convert(Date,'16-9-2012', 105) AND ti.Transaction_Date < convert(Date,'01-10-2012', 105) ORDER BY ti.Amount DESC ) Option2CROSS APPLY ( -- SUM of all the amounts prior to date range SELECT Open_Balance = SUM(ti.Amount) FROM Tbl_Transaction ti WHERE ti.Transaction_AccNo = a.AccNo -- outer ref AND ti.Transaction_Date <= convert(Date,'16-9-2012', 105) ) Option3Group By a.AccNoORDER BY a.AccNo[/code]Wed, 19 Sep 2012 07:21:43 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[code="sql"]with CTE as( SELECT Transaction_AccNo, Amount, TransType_ID, Topup_ID, ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo ORDER BY convert(Date,Transaction_Date, 105)) as RowNum from Tbl_Transaction where convert(Date,Transaction_Date, 105) BETWEEN '2012-09-1' AND '2012-09-30')select Tbl_Account.AccNo, SUM( case when TransType_ID = 0 then Amount else 0 end ) as Total_Adjustment, SUM( case when TransType_ID = 1 then Amount else 0 end ) as Total_Topup, SUM( case when TransType_ID = 2 then Amount else 0 end ) as Total_Comm, SUM( case when TransType_ID = 3 then Amount else 0 end ) as Total_Transfer, SUM( case when TransType_ID = 4 then Amount else 0 end ) as Total_RecvTransfer, SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='M' then Amount else 0 end ) as Total_Maxis, SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='D' then Amount else 0 end ) as Total_Digi, SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='C' then Amount else 0 end ) as Total_Celcom, SUM( case when RowNum = 1 then Amount else 0 end) as Open_BalanceFROM Tbl_AccountLEFT JOIN CTE ON Tbl_Account.AccNo = CTE.Transaction_AccNoLEFT JOIN Tbl_Topup ON CTE.Topup_ID = Tbl_Topup.Topup_IDGroup By Tbl_Account.AccNoORDER BY Tbl_Account.AccNo[/code]DONE !, this is select Top 1 Amount,But how to select the LAST Amount as Closing_Balance?Wed, 19 Sep 2012 07:10:25 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]ChrisM@Work (9/19/2012)[/b][hr][quote][b]chinye2020 (9/19/2012)[/b][hr]i mean select the top 1 Amount in the sql query[/quote]Ordered by what? Amount? Transaction date?[/quote]all Sum will according the TrasnType_ID and Acc No, so Result will show the all Acc No and their TransType Total Amount,and i just want in the Tbl_Transaction where is Top 1 Amount and show the Top 1 Amount as Opening_Balance, that's allWed, 19 Sep 2012 06:54:57 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]chinye2020 (9/19/2012)[/b][hr]i mean select the top 1 Amount in the sql query[/quote]Ordered by what? Amount? Transaction date?Wed, 19 Sep 2012 06:47:16 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspxi mean select the top 1 Amount in the sql queryWed, 19 Sep 2012 06:23:38 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspx[quote][b]chinye2020 (9/18/2012)[/b][hr]...[b](SELECT Top 1 Amount as Open_Balance)<<---how to do this at here?i want to get the first Amount as Opening Balance[u][/u][/b]...[/quote]Write a separate query for this, then integrate it into the main query. We'll help with that part. The problem is, "the first Amount" isn't easily understood with the information we have so far. Do you mean the first transaction against an account within the specified date range?In the meantime, here's your original query with two key improvements:Table aliases used throughoutDate filter is now SARGable - if there's an index on transaction date, it can now be used.[code="sql"]SELECT a.AccNo, SUM( case when t.TransType_ID = 0 then a.Amount else 0 end ) as Total_Adjustment, SUM( case when t.TransType_ID = 1 then a.Amount else 0 end ) as Total_Topup, SUM( case when t.TransType_ID = 2 then a.Amount else 0 end ) as Total_Comm, SUM( case when t.TransType_ID = 3 then a.Amount else 0 end ) as Total_Transfer, SUM( case when t.TransType_ID = 4 then a.Amount else 0 end ) as Total_RecvTransfer, SUM( case when t.TransType_ID = 2 AND p.Topup_Company='M' then a.Amount else 0 end ) as Total_Maxis, SUM( case when t.TransType_ID = 2 AND p.Topup_Company='D' then a.Amount else 0 end ) as Total_Digi, SUM( case when t.TransType_ID = 2 AND p.Topup_Company='C' then a.Amount else 0 end ) as Total_Celcom--, --(SELECT Top 1 Amount as Open_Balance)<<---how to do this at here?i want to get the first Amount as Opening BalanceFROM Tbl_Account aLEFT JOIN Tbl_Transaction t ON a.AccNo = t.Transaction_AccNo -- this date arithmetic should provide the same filter as the original -- but is SARGable (use Google) AND t.Transaction_Date > convert(Date,'16-9-2012', 105) AND t.Transaction_Date < convert(Date,'01-10-2012', 105)LEFT JOIN Tbl_Topup p ON t.Topup_ID = p.Topup_ID Group By a.AccNoORDER BY a.AccNo[/code]Edit: spelingWed, 19 Sep 2012 02:18:59 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspxi totally no idea with this,pls help~!Tue, 18 Sep 2012 22:54:57 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspxSELECT Tbl_Account.AccNo,SUM( case when TransType_ID = 0 then Amount else 0 end ) as Total_Adjustment,SUM( case when TransType_ID = 1 then Amount else 0 end ) as Total_Topup,SUM( case when TransType_ID = 2 then Amount else 0 end ) as Total_Comm,SUM( case when TransType_ID = 3 then Amount else 0 end ) as Total_Transfer,SUM( case when TransType_ID = 4 then Amount else 0 end ) as Total_RecvTransfer,SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='M' then Amount else 0 end ) as Total_Maxis,SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='D' then Amount else 0 end ) as Total_Digi,SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='C' then Amount else 0 end ) as Total_Celcom,[b](SELECT Top 1 Amount as Open_Balance)<<---how to do this at here?i want to get the first Amount as Opening Balance[u][/u][/b]FROM Tbl_AccountLEFT JOIN Tbl_Transaction ON Tbl_Account.AccNo = Tbl_Transaction.Transaction_AccNo AND (SELECT convert(Date,Tbl_Transaction.Transaction_Date, 105)) BETWEEN (SELECT convert(Date,'16-9-2012', 105)) AND (SELECT convert(Date,'30-9-2012', 105))LEFT JOIN Tbl_Topup ON Tbl_Transaction.Topup_ID=Tbl_Topup.Topup_ID Group By Tbl_Account.AccNoORDER BY Tbl_Account.AccNoTue, 18 Sep 2012 14:30:50 GMTchinye2020http://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspxFor a dynamic solution, you should read this article:[url=http://www.sqlservercentral.com/articles/Crosstab/65048/]Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]And maybe the first part as well.Tue, 18 Sep 2012 14:12:52 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspxHiYou could try the following. Of course it will only work for the three TransType_IDs[code="sql"]SELECT SUM(CASE WHEN TransType_ID = 0 THEN AMOUNT ELSE null END) SumAmount_TransType_ID_0 , SUM(CASE WHEN TransType_ID = 2 THEN AMOUNT ELSE null END) SumAmount_TransType_ID_2 , SUM(CASE WHEN TransType_ID = 3 THEN AMOUNT ELSE null END) SumAmount_TransType_ID_3 , Transaction_AccNoFROM Tbl_TransactionGROUP BY Transaction_AccNo[/code]Tue, 18 Sep 2012 14:08:13 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1360975-391-1.aspxSELECT SUM(AMOUNT) as SumAmount,TransType_ID,Transaction_AccNo FROM Tbl_TransactionGroup By TransType_ID,Transaction_AccNobelow is the sql query resultSumAmount TransType_ID Transaction_AccNo65000.000 0 1-35157.000 3 1-1872.235 2 1[b]Question = i want to show only the Sum Amount With different TransType ID column and group by Acc No? ,example[/b]SumAmount_TransType_ID_0 SumAmount_TransType_ID_3 SumAmount_TransType_ID_2 Transaction_AccNo65000.000 -35157.000 -1872.235 1how is the sql query??Tue, 18 Sep 2012 13:11:09 GMTchinye2020