September 4, 2014 at 9:24 am
Please forgive my newbieness! 🙂 I have a simple script where I want to pull GLAcct, GLDesc and Amounts by Period. I want my results to look like attached snip.
I tried playing around with the dates; however, I'm receiving errors. Just to note that when I ran for 07/01/14 - 07/31/14 with the transaction date in where clause I was able to retrieve the correct results. Now I want to expand to get a view set up for the whole year....automation!
select
gl_account.id as GLAcct,
gl_account.descr as GLDesc,
sum(gl_ledger.amount_n) as Net
from gl_account
left join gl_ledger on gl_account.id = gl_ledger.glid
where gl_account.id between '4000' and '9999' and
gl_ledger.transaction_date between '2014-07-01 00:00:00.000' and '2014-07-31 23:59:59.000'
group by gl_account.id, gl_account.descr
order by gl_account.id
How do I get this in my script and still have the ability to group by GLAcct with amounts in each period?
gl_ledger.transaction_date between '2014-01-01 00:00:00.000' and '2014-01-31 23:59:59.000' as P1,
gl_ledger.transaction_date between '2014-02-01 00:00:00.000' and '2014-02-28 23:59:59.000' as P2,
gl_ledger.transaction_date between '2014-03-01 00:00:00.000' and '2014-03-31 23:59:59.000' as P3,
gl_ledger.transaction_date between '2014-04-01 00:00:00.000' and '2014-04-30 23:59:59.000' as P4,
gl_ledger.transaction_date between '2014-05-01 00:00:00.000' and '2014-05-31 23:59:59.000' as P5,
gl_ledger.transaction_date between '2014-06-01 00:00:00.000' and '2014-06-30 23:59:59.000' as P6,
gl_ledger.transaction_date between '2014-07-01 00:00:00.000' and '2014-07-31 23:59:59.000' as P7,
gl_ledger.transaction_date between '2014-08-01 00:00:00.000' and '2014-08-31 23:59:59.000' as P8,
gl_ledger.transaction_date between '2014-09-01 00:00:00.000' and '2014-09-30 23:59:59.000' as P9,
gl_ledger.transaction_date between '2014-10-01 00:00:00.000' and '2014-10-31 23:59:59.000' as P10,
gl_ledger.transaction_date between '2014-11-01 00:00:00.000' and '2014-11-30 23:59:59.000' as P11,
gl_ledger.transaction_date between '2014-12-01 00:00:00.000' and '2014-12-31 23:59:59.000' as P12,
September 4, 2014 at 9:35 am
If i understood the requirment correctly you can use case statement
select
gl_account.id as GLAcct,
gl_account.descr as GLDesc,
case when gl_ledger.transaction_date between '2014-01-01 00:00:00.000' and '2014-01-31 23:59:59.000' then sum(gl_ledger.amount_n) end as P1,
case when gl_ledger.transaction_date between '2014-02-01 00:00:00.000' and '2014-02-28 23:59:59.000' then sum(gl_ledger.amount_n) end as P2,
case when gl_ledger.transaction_date between '2014-03-01 00:00:00.000' and '2014-03-31 23:59:59.000' then sum(gl_ledger.amount_n) endas P3,
case when gl_ledger.transaction_date between '2014-04-01 00:00:00.000' and '2014-04-30 23:59:59.000' then sum(gl_ledger.amount_n) end as P4
..
..
..
..
from gl_account
left join gl_ledger on gl_account.id = gl_ledger.glid
where gl_account.id between '4000' and '9999' and
group by gl_account.id, gl_account.descr,transaction_date
order by gl_account.id
September 4, 2014 at 10:08 am
@rxm119528 - This worked great with a little modification. Thank you so much! Have a great day!
September 4, 2014 at 10:09 am
select
g_a.id as GLAcct,
g_a.descr as GLDesc,
sum(case when g_l.transaction_date >= '20140101' AND g_l.transaction_date < '20140201' THEN g_l.amount_n ELSE 0 END) AS Jan,
--...
sum(case when g_l.transaction_date >= '20140701' AND g_l.transaction_date < '20140801' THEN g_l.amount_n ELSE 0 END) AS Jul,
--...
sum(case when g_l.transaction_date >= '20141201' AND g_l.transaction_date < '20150101' THEN g_l.amount_n ELSE 0 END) AS Dec
from gl_account g_a
left join gl_ledger g_l on g_a.id = g_l.glid
where g_a.id between '4000' and '9999' and
g_l.transaction_date >= '20140101' and g_l.transaction_date < '20150101'
group by g_a.id, g_a.descr
order by g_a.id
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 4, 2014 at 1:00 pm
Here is what I used and it worked great:
select
gl_account.id as GLAcct,
gl_account.descr as GLDesc,
case when gl_ledger.transaction_date between '2014-01-01 00:00:00.000' and '2014-01-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Jan,
case when gl_ledger.transaction_date between '2014-02-01 00:00:00.000' and '2014-02-28 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Feb,
case when gl_ledger.transaction_date between '2014-03-01 00:00:00.000' and '2014-03-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Mar,
case when gl_ledger.transaction_date between '2014-04-01 00:00:00.000' and '2014-04-30 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Apr,
case when gl_ledger.transaction_date between '2014-05-01 00:00:00.000' and '2014-05-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as May,
case when gl_ledger.transaction_date between '2014-06-01 00:00:00.000' and '2014-06-30 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Jun,
case when gl_ledger.transaction_date between '2014-07-01 00:00:00.000' and '2014-07-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Jul,
case when gl_ledger.transaction_date between '2014-08-01 00:00:00.000' and '2014-08-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Aug,
case when gl_ledger.transaction_date between '2014-09-01 00:00:00.000' and '2014-09-30 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Sep,
case when gl_ledger.transaction_date between '2014-10-01 00:00:00.000' and '2014-10-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Oct,
case when gl_ledger.transaction_date between '2014-11-01 00:00:00.000' and '2014-11-30 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Nov,
case when gl_ledger.transaction_date between '2014-12-01 00:00:00.000' and '2014-12-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Dec
from gl_account
Left join gl_ledger
on gl_account.id = gl_ledger.glid
where gl_account.id between '4000' and '9999' and
gl_account.id not in ('61010099', '62350099', '62370099', '62380099', '90010099') and
gl_ledger.transaction_date between '2014-01-01 00:00:00.000' and '2014-12-31 23:59:59.000'
group by gl_account.id, gl_account.descr, gl_ledger.transaction_date
Thank you so much for your expertise!
September 4, 2014 at 5:05 pm
KathyJ (9/4/2014)
Here is what I used and it worked great:
selectgl_account.id as GLAcct,
gl_account.descr as GLDesc,
case when gl_ledger.transaction_date between '2014-01-01 00:00:00.000' and '2014-01-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Jan,
case when gl_ledger.transaction_date between '2014-02-01 00:00:00.000' and '2014-02-28 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Feb,
case when gl_ledger.transaction_date between '2014-03-01 00:00:00.000' and '2014-03-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Mar,
case when gl_ledger.transaction_date between '2014-04-01 00:00:00.000' and '2014-04-30 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Apr,
case when gl_ledger.transaction_date between '2014-05-01 00:00:00.000' and '2014-05-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as May,
case when gl_ledger.transaction_date between '2014-06-01 00:00:00.000' and '2014-06-30 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Jun,
case when gl_ledger.transaction_date between '2014-07-01 00:00:00.000' and '2014-07-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Jul,
case when gl_ledger.transaction_date between '2014-08-01 00:00:00.000' and '2014-08-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Aug,
case when gl_ledger.transaction_date between '2014-09-01 00:00:00.000' and '2014-09-30 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Sep,
case when gl_ledger.transaction_date between '2014-10-01 00:00:00.000' and '2014-10-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Oct,
case when gl_ledger.transaction_date between '2014-11-01 00:00:00.000' and '2014-11-30 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Nov,
case when gl_ledger.transaction_date between '2014-12-01 00:00:00.000' and '2014-12-31 23:59:59.000' then sum(gl_ledger.amount_n) else 0 end as Dec
from gl_account
Left join gl_ledger
on gl_account.id = gl_ledger.glid
where gl_account.id between '4000' and '9999' and
gl_account.id not in ('61010099', '62350099', '62370099', '62380099', '90010099') and
gl_ledger.transaction_date between '2014-01-01 00:00:00.000' and '2014-12-31 23:59:59.000'
group by gl_account.id, gl_account.descr, gl_ledger.transaction_date
Thank you so much for your expertise!
I could certainly be wrong because I've not tested the code above but it looks like you'll get more than one row for a given month if more than one row with different dates in the same month appear for a given gl_account.id, gl_account.descr pair. In order for this to work accurately in any given case, you need to include the CASE statement in the SUM and NOT group by the gl_ledger.transaction_date column.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2014 at 7:10 pm
Here is my take on this solution:
select
gla.id as GLAcct,
gla.descr as GLDesc,
sum(case when cast(gl.transaction_date as DATE) between dateadd(year,datediff(year,0,getdate()),0) and dateadd(month,1,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Jan,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,1,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,2,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Feb,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,2,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,3,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Mar,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,3,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,4,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Apr,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,4,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,5,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as May,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,5,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,6,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Jun,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,6,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,7,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Jul,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,7,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,8,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Aug,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,8,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,9,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Sep,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,9,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,10,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Oct,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,10,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,11,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Nov,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,11,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,12,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Dec
from
dbo.gl_account gla
left outer join dbo.gl_ledger gl
on gla.id = gl.glid
where
gla.id between '4000' and '9999' and
gla.id not in ('61010099', '62350099', '62370099', '62380099', '90010099') and
-- gl.transaction_date between '2014-01-01 00:00:00.000' and '2014-12-31 23:59:59.000'
gl.transaction_date >= dateadd(year,datediff(year,0,getdate()),0) and gl.transaction_date < dateadd(year,datediff(year,0,getdate()) + 1,0)
group by
gla.id,
gla.descr,
gl.transaction_date;
September 4, 2014 at 11:48 pm
I believe I'd still remove the Transaction_Date from the Group By. It's not needed.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2014 at 12:06 am
I see some real danger in the WHERE clause of all of these queries. What is the data-type for the gl_account.id column, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2014 at 7:41 am
Jeff Moden (9/4/2014)
I believe I'd still remove the Transaction_Date from the Group By. It's not needed.
You are most likely correct. I missed that as I was reworking my code.
Curious what you fear in the where clause.
September 5, 2014 at 7:43 am
Minor change based on Jeff's comment and taking a closer look at the query I agree:
select
gla.id as GLAcct,
gla.descr as GLDesc,
sum(case when cast(gl.transaction_date as DATE) between dateadd(year,datediff(year,0,getdate()),0) and dateadd(month,1,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Jan,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,1,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,2,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Feb,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,2,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,3,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Mar,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,3,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,4,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Apr,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,4,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,5,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as May,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,5,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,6,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Jun,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,6,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,7,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Jul,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,7,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,8,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Aug,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,8,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,9,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Sep,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,9,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,10,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Oct,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,10,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,11,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Nov,
sum(case when cast(gl.transaction_date as DATE) between dateadd(month,11,dateadd(year,datediff(year,0,getdate()),0)) and dateadd(month,12,dateadd(year,datediff(year,0,getdate()),-1)) then gl.amount_n else 0 end) as Dec
from
dbo.gl_account gla
left outer join dbo.gl_ledger gl
on gla.id = gl.glid
where
gla.id between '4000' and '9999' and
gla.id not in ('61010099', '62350099', '62370099', '62380099', '90010099') and
-- gl.transaction_date between '2014-01-01 00:00:00.000' and '2014-12-31 23:59:59.000'
gl.transaction_date >= dateadd(year,datediff(year,0,getdate()),0) and gl.transaction_date < dateadd(year,datediff(year,0,getdate()) + 1,0)
group by
gla.id,
gla.descr;
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply