November 3, 2010 at 9:10 am
mbender (11/3/2010)
Also what is the difference between the IsNull and Coalesce?
both provide the ability to substitute a null for a known value, but COALESCE is overloaded with a param array, so you can use multiple columns:
SELECT COALESCE(Child.Address,Parent.Address,GranParent.Address,'I Giveup its blank')
FROM ...
Lowell
November 3, 2010 at 9:17 am
mbender (11/3/2010)
What does indexing do? None of our tables are indexed. By doing the Create Index, what will that do?
NONE of them??? wow...
The purpose of indexes is to allow for faster retrieval of data from the tables. It can make a HUGE difference in performance.
Without indexes, every query will have to scan the entire table to determine which records meet the specified criteria. With an index, SQL can go directly to just the records that meet the criteria.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 3, 2010 at 9:19 am
Will it cause any issues if we go back and index the tables?
November 3, 2010 at 9:20 am
Stefan Krzywicki (11/3/2010)
mbender (11/3/2010)
this is really cool code, would never have thought to do this. The one thing i'm missing is what happens if there is values in the previous month but not in the current month. I still need to show the value from the previous month. This doesn't show it.Who are you responding to?
Which code are you trying that you're getting this?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 3, 2010 at 9:28 am
WayneS (10/29/2010)
First of all, I'd recommend these two indexes (your code benefits from these also!):
CREATE INDEX [Cover1] ON [dbo].[aa_BillBillingAgreement](BaiOffice) INCLUDE (BaiPKey, AgrPKey);CREATE INDEX [Cover1] ON [dbo].[aa_BillBillingAdjustments](BiaBillingDate, BiaInvoiceApproved, BiaStartDate) INCLUDE (BaiPKey, BiaEndDate, BiaAmount);
This code produces the same results, with no temp tables (or table variables). Doing this saves a bit of IO - in fact, when running both your query and this one together, this one is 1/3 of the total cost.
Oh - one other thing. The @office variable - I've changed it from varchar(100) to nvarchar(50) to match the datatype of the table (this will avoid an implicit conversion to nvarchar - and allow the use of an index on that column).
SET STATISTICS IO,TIME ON;Declare @MonthDate date
Declare @office nvarchar(50)
Declare @PreviousMonth date
Set @MonthDate='10/01/2010'
Set @PreviousMonth=DATEADD(m,-1,@MonthDate)
Set @office=N'Chicago'
WITH CurrentMonth (AgrPKey, CurrentMonthAmount) AS
(
SELECT AgrPKey, SUM(Total)
FROM (
SELECT BillBillingAgreement.AgrPKey,
SUM(BillBillingAdjustments.BiaAmount) AS Total
FROM aa_BillBillingAgreement as BillBillingAgreement
JOIN aa_BillBillingAdjustments as BillBillingAdjustments
ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAgreement.BaiOffice = @office)
AND (BillBillingAdjustments.BiaInvoiceApproved = 1)
AND (BillBillingAdjustments.BiaStartDate <=@MonthDate)
AND (BillBillingAdjustments.BiaEndDate >= @MonthDate)
AND (BillBillingAdjustments.BiaBillingDate <=@MonthDate)
GROUP BY BillBillingAgreement.AgrPKey
UNION ALL
SELECT BillBillingAgreement.AgrPKey,
SUM(Case When @MonthDate<=BiaEndDate Then (DateDiff(m,BiaStartDate,@MonthDate))*BillBillingAdjustments.BiaAmount
Else (DateDiff(m,BiaEndDate,BiaStartDate)+1)*BillBillingAdjustments.BiaAmount
end) AS TotaPP
FROM aa_BillBillingAgreement as BillBillingAgreement
JOIN aa_BillBillingAdjustments as BillBillingAdjustments
ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAdjustments.BiaBillingDate = @MonthDate)
AND (BillBillingAdjustments.BiaInvoiceApproved = 1)
AND (BillBillingAdjustments.BiaStartDate < BillBillingAdjustments.BiaBillingDate)
AND (BillBillingAgreement.BaiOffice = @office)
GROUP BY BillBillingAgreement.AgrPKey) ds
GROUP BY AgrPKey
), PreviousMonth (AgrPKey, CurrentMonthAmount) AS
(
SELECT AgrPKey, SUM(Total)
FROM (
SELECT BillBillingAgreement.AgrPKey,
SUM(BillBillingAdjustments.BiaAmount) AS Total
FROM aa_BillBillingAgreement as BillBillingAgreement
JOIN aa_BillBillingAdjustments as BillBillingAdjustments
ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAgreement.BaiOffice = @office)
AND (BillBillingAdjustments.BiaInvoiceApproved = 1)
AND (BillBillingAdjustments.BiaStartDate <=@PreviousMonth)
AND (BillBillingAdjustments.BiaEndDate >= @PreviousMonth)
AND (BillBillingAdjustments.BiaBillingDate <=@PreviousMonth)
GROUP BY BillBillingAgreement.AgrPKey
UNION ALL
SELECT BillBillingAgreement.AgrPKey,
SUM(Case When @PreviousMonth<=BiaEndDate Then (DateDiff(m,BiaStartDate,@PreviousMonth))*BillBillingAdjustments.BiaAmount
Else (DateDiff(m,BiaEndDate,BiaStartDate)+1)*BillBillingAdjustments.BiaAmount
end) AS TotaPP
FROM aa_BillBillingAgreement as BillBillingAgreement
JOIN aa_BillBillingAdjustments as BillBillingAdjustments
ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAdjustments.BiaBillingDate = @PreviousMonth)
AND (BillBillingAdjustments.BiaInvoiceApproved = 1)
AND (BillBillingAdjustments.BiaStartDate < BillBillingAdjustments.BiaBillingDate)
AND (BillBillingAgreement.BaiOffice = @office)
GROUP BY BillBillingAgreement.AgrPKey) ds
GROUP BY AgrPKey
), Merged AS
(
SELECT pm.AgrPKey,
PreviousMonthAmount = pm.CurrentMonthAmount,
CurrentMonthAmount = IsNull(cm.CurrentMonthAmount,0)
FROM PreviousMonth pm
LEFT JOIN CurrentMonth cm
ON cm.AgrPKey = pm.AgrPKey
)
SELECT AgrPKey,
PreviousMonthAmount,
CurrentMonthAmount,
Variance = (CurrentMonthAmount - PreviousMonthAmount)
FROM Merged
ORDER BY Variance;
SET STATISTICS IO,TIME OFF;
It won't surprise me if someone can tweak this a bit further - but it's already running quite a bit better.
Sorry this is the code I was looking at. I may have missed this in the sample data that i sent, where it exists in one month but not the other.
November 3, 2010 at 9:32 am
mbender (11/3/2010)
Also what is the difference between the IsNull and Coalesce?
2 main differences
1. Data-type precedence and conversion
2. COALESCE can accept any number of input column, the first column to have non-null value will be output; while, ISNULL takes only 2 parameters and finds out the non-null value between them.
Refer Books Online to get more grip on these two.. Just Google Difference Between ISNULL and COALESCE in google, you will gazillion pages on them..
~Edit : Wrong post was Quoted; corrected it
November 3, 2010 at 12:52 pm
Awesome I got the code to work by adding in some master tables. Thanks for all your assistance and extra information. I have some studying to do.
November 8, 2010 at 11:51 pm
CELKO (10/28/2010)
I tried to read what you posted got 45 minutes. You have re-discovered the same algorithms I used on mag tape files decades ago. Your temp tables are scratch tapes. You do things one step at time,rewind the tape and make another pass over the Master file.You use BIT flags as if we were still in Assembly language.
You use MONEY in spite of the math problems it has. But it looks like a formatted PIC field declaration in COBOL.
You put the meta data term "_key" in column names.
You write:
AND BBD.bia_start_date <= @in_something_month
AND BBD.bia_end_date >= @in_something_month
instead of:
@in_something_month BETWEEN BBD.bia_start_date
AND BBD.bia_end_date
You call a function to get the first day of a month, and it name violates ISO-11179 naming rules-- drop the silly "ufn-" prefix.
SET @in_something_month = dbo.ufn_GetFirstDayOfMonth(@in_something_month);
instead of writing an expression the optimizer could use.
This is not SQL. It is COBOL and tape files written in SQL. This is a bigger problem than a Forum can handle. Sorry.
Great post! It's very nice. Thank you so much for your post.
__________________
Viewing 8 posts - 46 through 52 (of 52 total)
You must be logged in to reply to this topic. Login to reply