Case When use with Dates - SQL Server Forums

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)

Case When use with Dates

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2009-03-05 : 15:50:42

I need help getting some information from a table. Here is my existing table:

DETAIL TABLE
PatsNumber|PatsName|Status|TxDate|CntrDate|CntrAmt
3837744873|Mel Abny|Approve|01/05/2009|01/04/2009|4209
8736485202|Trey White|Pending|(blank)|01/04/2009|(blank)

The differenct Statuses are: Approved, Denied, Pending, Recall, N/A

I need to return a SUMMARY TABLE that will sum up the above table based on the Statuses and then if the other columns have pertinent data. If there is a valid date then count it otherwise do not.

Here is what i have but it doesn't work.

select
sum(case when TxDate > ' ' then 1 else 0 end) as 'TxStarted',
sum(case when CntrDate > ' ' then 1 else 0 end) as 'Contracts',
sum(case when CntrAmt > 0 then 1 else 0 end) as 'Contract Value',
sum(case when StartTxDate = ' ' then 1 else 0 end) as 'No TxStarted',
sum(case when CntrDate <> ' ' then 1 else 0 end) as 'Contracts',
sum(case when CntrAmt > 0 then 1 else 0 end) as 'Contract Value'
from #tmpDetails
where Status = 'Approved'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 17:39:40

Looks kinda ok....Maybe you want to group by status and add it in your select..

I have just created some sample data and ran this query below...and got your desired output...

You can try this..

declare @t table (Stat char(30),TxDate char(30),CntrDate char(30),CntrAmt char(30))

insert @t
select 'Approved','01/05/2009','01/04/2009','4209' union all
select 'Approved','01/05/2009','01/04/2009','' union all
select 'Pending','','01/04/2009','' union all
select 'Pending','01/05/2009','01/04/2009',''

select Stat,
sum(case when TxDate > '' then 1 else 0 end) as 'TxStarted',
sum(case when CntrDate > '' then 1 else 0 end) as 'Contracts',
sum(case when CntrAmt > 0 then 1 else 0 end) as 'Contract Value',
sum(case when TxDate = '' then 1 else 0 end) as 'No TxStarted'
from @t
group by Stat

Result

Stat TxStarted Contracts Contract Value No TxStarted
------------------------------ ----------- ----------- -------------- ------------
Approved 2 2 1 0
Pending 1 2 0 1

andros30
Yak Posting Veteran

80 Posts

Posted - 2009-03-06 : 09:48:46

I may have more than 600 records returned. Am I correct to understand that I have to insert each record the way you suggest above?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 10:06:48

No..the result set of the SELECT statement will be directly inserted into your table

for ex:

INSERT INTO urtable (Status,TxStarted,Contracts,Contract Value,No TxStarted)
SELECT Stat,
sum(case when TxDate > '' then 1 else 0 end) as 'TxStarted',
sum(case when CntrDate > '' then 1 else 0 end) as 'Contracts',
sum(case when CntrAmt > 0 then 1 else 0 end) as 'Contract Value',
sum(case when TxDate = '' then 1 else 0 end) as 'No TxStarted'
from @t
group by Stat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources