Year To Date Function in T SQL

[dleewms]

I am attempting to subtotal all amounts with dates in the current year (year to date) and dates last year by this time. Crystal reports has a yeartodate function in which I can simply say invoicedate in yeartodate. Need to accomplish same goal in the view. Is there a similar function in TSQL?

Below is the code with the part sum part missing. I want a sum of all invoice amounts for each customer.

Thanks!

select -- invoice
ivc.batch_no,
ivc.invoice_no,
--Added by DLW 06/29/2011
coalesce(ivc.orig_invoice_no, ivc.invoice_no) as orig_ivc_no,
ivc.invoice_date,
ivc.branch_code,
ivc.customer_id,
current_sub_amt = (select sum(ivc2.sub_amt)
from invoice ivc2
where ivc.invoice_date in year
ivc.cust_name,
ivc.sub_amt,
-- invoice group
ig.doc_no,
doc_type = case
when ig.doc_type IN ('CBL', 'CO', 'COL') then 'CO'
when ig.doc_type IN ('WO', 'WOA', 'WOS', 'WOT') then 'WO'
when ig.doc_type IN ('SO', 'SOL', 'SOS', 'SSL') then 'SO'
when ig.doc_type IN ('IA', 'IAL') then 'IA'
when ig.doc_type IN ('CLH', 'CLA', 'CLT') then 'CLH'
else ig.doc_type
end,
ig.customer_po
from invoice ivc
join invoice_group ig on ig.invoice_no = ivc.invoice_no
join currency cu on cu.curr_code = ivc.curr

 

[Qik3Coder]

Do you need
1/1/2010-12/31/2010
1/1/2011-7/8/2011

or

1/1/2010-7/8/2010
1/1/2011-7/8/2011

or ??

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[dleewms]

I need 1/1/2010-7/8/20101 and 1/1/2011-7/8/2011

I've been able to get the current year to date using this and it works perfectly:

>= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

However, Crystal is hanging when I attempt to get last year's YTD values. I've tried using BETWEEN with DateAdd(yyyy, DateDiff(yyyy, 365, GetDate()), 0) and DateAdd(dd, DateDiff(dd, 365, GetDate()) +1, 0 as well as >= and < with the same expressions as used with BETWEEN but both are taking too long to process. Do you have any suggestions to improve performance?

Thanks!

 

[Qik3Coder]

Wherever possible you should compare against variables instead of doing gnarly calculations, but before we get to that, do you need to just do this year last year or is this a Group by like value?

Are you planning on static fields:
ClientID, ClientName, ThisYearInvToDate, LastYearInvToDate

or is this a row type return with :

Year InvTot
2011 29.95
2010 34.57
2009 12.45

You approach these drastically differently. You can do subqueries to obtain single values and then join to them specifically, or else you have to do a query and group on some interesting values.

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[dleewms]

I am using static fields customerID, CustomerName, ThisYTD, LastYTD. Actually, I have two groupings in Crystal. The first is by Branch and the second is by Customer.

Thanks!

 

[Qik3Coder]

I'm assuming this is a procedure, giving you the ability to create local variables.

Create table invoice(
branch_code varchar(5),
customer_id int,
invoice_date datetime,
sub_amt money)

Insert into invoice
SELECT 'A', 1, '20110102', 50.00 UNION ALL
SELECT 'A', 1, '20110202', 40.00 UNION ALL
SELECT 'A', 1, '20100102', 10.00 UNION ALL
SELECT 'A', 1, '20100802', 20.00 UNION ALL
SELECT 'A', 1, '20100302', 30.00

Declare @ThisYear int, @MMDD varchar(4)
select @MMDD = right(convert(varchar(10), GetDate(),112),4), @ThisYear = Year(GetDate())

Select i.*, ThisYearInv.InvTot, LastYearInv.InvTot
FROM 
	(Select distinct branch_code, customer_id
	FROM invoice (NOLOCK)) as i
LEFT JOIN 
	(SELECT branch_code, customer_id, sum(sub_amt) InvTot
	from invoice ivc_i1 (NOLOCK)
	where ivc_i1.invoice_date BETWEEN rtrim(@ThisYear)+'0101' and rtrim(@ThisYear)+@MMDD
	GROUP BY ivc_i1.branch_code, ivc_i1.customer_id
	) as ThisYearInv ON
	i.branch_code = ThisYearInv.branch_code
	and i.customer_id = ThisYearInv.customer_ID
LEFT JOIN 
	(SELECT ivc_i2.branch_code, ivc_i2.customer_id, sum(sub_amt) InvTot
	from invoice ivc_i2 (NOLOCK)
	where ivc_i2.invoice_date BETWEEN rtrim(@ThisYear-1)+'0101' and rtrim(@ThisYear-1)+@MMDD
	GROUP BY ivc_i2.branch_code, ivc_i2.customer_id
	) as LastYearInv ON
	i.branch_code = LastYearInv.branch_code

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[dleewms]

Thanks so much for helping me out here. Actually, I wasn't creating a stored procedure, but was attempting to modify an already existing view to add the subtotal fields. Also, I'm not sure I follow what's going on in your resolution. I will see if I can get it to work and let you know if I need more help.

Thanks!

 

[Qik3Coder]

Here's with no variables, for the view:

Select i.*, ThisYearInv.InvTot, LastYearInv.InvTot
FROM 
	(Select distinct branch_code, customer_id
	FROM invoice (NOLOCK)) as i
LEFT JOIN 
	(SELECT branch_code, customer_id, sum(sub_amt) InvTot
	from invoice ivc_i1 (NOLOCK)
	where ivc_i1.invoice_date BETWEEN rtrim(Year(GetDate()))+'0101' and rtrim(Year(GetDate()))+right(convert(varchar(10), GetDate(),112),4)
	GROUP BY ivc_i1.branch_code, ivc_i1.customer_id
	) as ThisYearInv ON
	i.branch_code = ThisYearInv.branch_code
	and i.customer_id = ThisYearInv.customer_ID
LEFT JOIN 
	(SELECT ivc_i2.branch_code, ivc_i2.customer_id, sum(sub_amt) InvTot
	from invoice ivc_i2 (NOLOCK)
	where ivc_i2.invoice_date BETWEEN rtrim(Year(GetDate())-1)+'0101' and rtrim(Year(GetDate())-1)+right(convert(varchar(10), GetDate(),112),4)
	GROUP BY ivc_i2.branch_code, ivc_i2.customer_id
	) as LastYearInv ON
	i.branch_code = LastYearInv.branch_code
	and i.customer_id = LastYearInv.customer_ID

pseudo code:

Select i.*, ThisYearInv.InvTot, LastYearInv.InvTot
FROM 
	[b](Get a clean list of people we have invoiced)[/b]
LEFT JOIN 
	[b](All the invoices from 1/1/2011 to today)[/b] ON
	i.branch_code = ThisYearInv.branch_code
	and i.customer_id = ThisYearInv.customer_ID
LEFT JOIN 
	[b](All The invoices from 1/1/2010 to today last year)[/b] as LastYearInv ON
	i.branch_code = LastYearInv.branch_code
	and i.customer_id = LastYearInv.customer_ID

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[dleewms]

Thanks for all of the help. I later found out that I had to get all records up to the last full month. My view is similar to yours. Here it is. It works but is slow. Probably needs to be a stored procedure like you first suggested, but I'm not as familiar creating complex SP.

select -- invoice
YTD_Sum_Amt.cust_subtotal,
Last_YTD_Sum_Amt.last_cust_subtotal,
ivc.batch_no,
ivc.invoice_no,
--Added by DLW 06/29/2011
coalesce(ivc.orig_invoice_no, ivc.invoice_no) as orig_ivc_no,
ivc.invoice_date,
ivc.sub_amt,
ivc.branch_code,
ivc.customer_id,
ivc.cust_name,
-- invoice group
ig.doc_no,
doc_type = case
when ig.doc_type IN ('CBL', 'CO', 'COL') then 'CO'
when ig.doc_type IN ('WO', 'WOA', 'WOS', 'WOT') then 'WO'
when ig.doc_type IN ('SO', 'SOL', 'SOS', 'SSL') then 'SO'
when ig.doc_type IN ('IA', 'IAL') then 'IA'
when ig.doc_type IN ('CLH', 'CLA', 'CLT') then 'CLH'
else ig.doc_type
end,
ig.customer_po
from invoice ivc
join invoice_group ig on ig.invoice_no = ivc.invoice_no
join currency cu on cu.curr_code = ivc.curr
Left Outer Join
(Select Sum(ivc2.sub_amt) cust_subtotal, ivc2.Branch_code, ivc2.cust_name
From dbo.invoice_register_all ivc2
inner join business_location bl
on ivc2.sell_to_business_id = bl.business_id
and ivc2.sell_to_location_no = bl.location_no
Inner Join dbo.ZipCodeTerritoryForCustomers Zip
On bl.postal_code = Zip.ZipCode
WHERE ivc2.cust_name =
(SELECT c2.cust_name
FROM dbo.invoice C2
WHERE C2.invoice_no = ivc2.invoice_no
and c2.Branch_code in ('DSC', 'DSI'))
AND ivc2.invoice_date BETWEEN DateAdd(yyyy, DateDiff(yyyy, 0, GetDate()) -0, 0)
AND DateAdd(mm, DateDiff(mm, 0, GetDate())-0, 0)
Group BY ivc2.Branch_code, ivc2.cust_name)YTD_Sum_Amt
ON YTD_Sum_Amt.cust_name = ivc.cust_name
AND YTD_Sum_Amt.Branch_code = ivc.Branch_code
Left Outer Join
(Select Sum(ivc2.sub_amt) last_cust_subtotal, ivc2.Branch_code, ivc2.cust_name
From dbo.invoice_register_all ivc2
inner join business_location bl
on ivc2.sell_to_business_id = bl.business_id
and ivc2.sell_to_location_no = bl.location_no
Inner Join dbo.ZipCodeTerritoryForCustomers Zip
On bl.postal_code = Zip.ZipCode
WHERE ivc2.cust_name =
(SELECT c2.cust_name
FROM dbo.invoice C2
WHERE C2.invoice_no = ivc2.invoice_no
and c2.Branch_code in ('DSC', 'DSI'))
AND ivc2.invoice_date BETWEEN DateAdd(yyyy, DateDiff(yyyy, 0, GetDate()) -1, 0)
AND DateAdd(mm, DateDiff(mm, 0, GetDate())-12, 0)
Group BY ivc2.Branch_code, ivc2.cust_name)Last_YTD_Sum_Amt
ON Last_YTD_Sum_Amt.cust_name = ivc.cust_name
AND Last_YTD_Sum_Amt.Branch_code = ivc.Branch_code

Thanks!

 

[Qik3Coder]

How are you consuming the data provided by this select?

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[dleewms]

It is the data source for a Crystal report.

Just found out t has to be changed to group the values by "Other" when the cust_subtotal+last_cust_subtotal is greater than 2500. I thought it would be easier to simply do a select on this view as opposed to modifying it again with the latest request, hence my other post (1654586)

 

[Qik3Coder]

they are separate enough issues to desire/require a new thread.
I commented there.

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]