SELECTing different "ranges" from the same column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECTing different "ranges" from the same column

Author

Topic

kennmurrah
Starting Member

13 Posts

Posted - 2006-02-13 : 11:23:07

I'm sure there's an easy solution to this dilemma. I'm equally sure I'm incapable of figuring it out. I hope my explanation is clear.

My query needs to yeild different columns derived from the same field (SUM(amt)) depending on the days since the amt was entered (trx_date). That is, if the date specified is 17 days from the startdate, the amount is given in one column, if it's 37 days, it needs to go into another column ...

Here's what I can do so far: the following SP results in the sum of invoices between 0 and 30 days from the "startdate":
CREATE procedure up_ap_current
(@startdate datetime)
as
SELECT vend_no, trx_dt, SUM(amt) AS 'Current'
FROM APOPNFIL_SQL
WHERE (opn_clos_cd = 'o') AND (vchr_chk_type = '1') AND
(trx_dt <CONVERT(int, CONVERT(varchar(8), GETDATE() - 0, 112))) and (trx_dt > CONVERT(int, CONVERT(varchar(8), GETDATE() - 30, 112)))
GROUP BY vend_no, trx_dt

I can create SP for other other categories I need (31-60 days, 61-90, 90+), but I can't figure out how to get all four results from the same SP, which is obviously what I need to do. If it weren't for the parameter values, I could probably do it with views, but I can't figure out how to get my desired results from one query.

Can any of you far smarter and more experienced SQLers help a guy out here?

Thanks in advance for all help.

Kenn

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-13 : 12:36:03

Something like this?


select 
     vend_no, 
     trx_dt, 
     SUM(case when trx_dt < CONVERT(int, CONVERT(varchar(8), GETDATE() - 0, 112))) and (trx_dt > CONVERT(int, CONVERT(varchar(8), GETDATE() - 30, 112)) then amt else 0 end) AS 'Current',
     SUM(case when trx_dt < CONVERT(int, CONVERT(varchar(8), GETDATE() - 31, 112))) and (trx_dt > CONVERT(int, CONVERT(varchar(8), GETDATE() - 60, 112)) then amt else 0 end) AS '31To60DaysAgo'
FROM APOPNFIL_SQL
WHERE (opn_clos_cd = 'o') AND (vchr_chk_type = '1') AND 
GROUP BY vend_no, trx_dt

Oh, and where are you using @startdate?

kennmurrah
Starting Member

13 Posts

Posted - 2006-02-13 : 15:03:32

I'm not sure .... I'm having syntax errors that I can't resolve ... changed parantheses to read

select
vend_no,
trx_dt,
SUM(case when trx_dt < CONVERT(int, CONVERT(varchar(8), GETDATE() - 0, 112)) and (trx_dt > CONVERT(int, CONVERT(varchar(8), GETDATE() - 30, 112)) then amt else 0 end) AS 'Current',
SUM(case when trx_dt < CONVERT(int, CONVERT(varchar(8), GETDATE() - 31, 112)) and (trx_dt > CONVERT(int, CONVERT(varchar(8), GETDATE() - 60, 112)) then amt else 0 end) AS '31To60DaysAgo'
FROM APOPNFIL_SQL
WHERE (opn_clos_cd = 'o') AND (vchr_chk_type = '1') AND
GROUP BY vend_no, trx_dt

but still get
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'then'.

If I understand the direction you're guiding me, it sounds as though it might work, but I'm having trouble resolving the syntax, so I can't say for sure yet.

Thanks SO MUCH for your help.

Kenn

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-13 : 18:30:31

[code]select
vend_no,
trx_dt,
SUM(case when trx_dt < CONVERT(int, CONVERT(varchar(8), GETDATE() - 0, 112)) and (trx_dt > CONVERT(int, CONVERT(varchar(8), GETDATE() - 30, 112))) then amt else 0 end) AS 'Current',
SUM(case when trx_dt < CONVERT(int, CONVERT(varchar(8), GETDATE() - 31, 112)) and (trx_dt > CONVERT(int, CONVERT(varchar(8), GETDATE() - 60, 112))) then amt else 0 end) AS '31To60DaysAgo'
FROM APOPNFIL_SQL
WHERE (opn_clos_cd = 'o') AND (vchr_chk_type = '1') ~~AND~~
GROUP BY vend_no, trx_dt[/code]

----------------------------------
'KH'
everything that has a beginning has an end

kennmurrah
Starting Member

13 Posts

Posted - 2006-02-15 : 13:19:22

Kevin,

I want to thank you for that answer ... it was EXACTLY what I needed ...

Based on your suggestion, here's what did the trick:

CREATE procedure up_ap_aging

(@startdate datetime)

as
select
APOPNFIL_SQL.vend_no,
SUM(case when APOPNFIL_SQL.trx_dt <=CONVERT(int, CONVERT(varchar(8), @startdate - 0, 112))
and (APOPNFIL_SQL.trx_dt >= CONVERT(int, CONVERT(varchar(8), @startdate - 30, 112))) then amt else 0 end) AS 'Current',

SUM(case when APOPNFIL_SQL.trx_dt <= CONVERT(int, CONVERT(varchar(8), @startdate - 31, 112))
and (APOPNFIL_SQL.trx_dt >= CONVERT(int, CONVERT(varchar(8), @startdate - 60, 112))) then amt else 0 end) AS '31To60DaysAgo',

SUM(case when APOPNFIL_SQL.trx_dt <= CONVERT(int, CONVERT(varchar(8), @startdate - 61, 112))
and (APOPNFIL_SQL.trx_dt >= CONVERT(int, CONVERT(varchar(8), @startdate - 90, 112))) then amt else 0 end) AS '61To90DaysAgo',

SUM(case when (APOPNFIL_SQL.trx_dt <= CONVERT(int, CONVERT(varchar(8), @startdate - 91, 112))) then amt else 0 end) AS '+90'

FROM APOPNFIL_SQL
WHERE (opn_clos_cd = 'o') and vchr_chk_type = 1
GROUP BY vend_no

Once again, I want to thank you for your help (and apologize for not posting this yesterday, as I should have.)

Kenn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources