SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Custom reporting?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stronius
Starting Member

Canada
8 Posts

Posted - 07/20/2010 :  09:37:00  Show Profile  Reply with Quote
Alright, basically, I have a table that looks like the example below filled with client data ...


Batch    |    ClientID    |    ClientType    |    LastDelivery
  1            ..              RDO            somedate...
  1            ..              RDO            somedate...
  1            ..              RDP            somedate...
  1            ..              RDP            somedate...
  2            ..              RDO            somedate...
  2            ..              RDP            somedate...
  2            ..              RDO            somedate...
...
..
.

Batch represents the upload batch, each time data is uploaded, it creates a new batch. Anyway, for reporting, I need to modify that data to look like this ... (On a .NET web form)

Type    |    OpenClients         |    ActiveClients
RDO    (# Of Client of type 'RDO)  (# Of ActiveClients of type RDO)
RDP    (# Of Client of type 'RDO)  (# Of ActiveClients of type RDP)


I started working with embedded select's to try and get the results I wanted, but so far, have had no luck and seem to be somewhat stumped. Any ideas? Thanks in advance, much appreciated.

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 07/20/2010 :  10:08:51  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
How do you determine whether the client is a open or active client?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

stronius
Starting Member

Canada
8 Posts

Posted - 07/20/2010 :  10:11:50  Show Profile  Reply with Quote
Open clients are basically all clients within the data table, active clients are clients that have had a delivery in the past 10 months (lastdelivery is within 10 months).
At this point in time, I'm pretty sure I could figure that part out once I figure out how to display the data the way I want.
Go to Top of Page

stronius
Starting Member

Canada
8 Posts

Posted - 07/20/2010 :  10:53:32  Show Profile  Reply with Quote
SELECT DISTINCT [CustomerData].[Batch_ID], 
	(SELECT COUNT(*) FROM [CustomerData] WHERE [Type] = 'RDO' AND [CustomerData].[Batch_ID] = 16) AS 'RDO', 
	(SELECT COUNT(*) FROM [CustomerData] WHERE [Type] = 'RDP' AND [CustomerData].[Batch_ID] = 16) AS 'RDP',
	(SELECT COUNT(*) FROM [CustomerData] WHERE ([Type] = 'RDO' OR [Type] = 'RDP') AND [CustomerData].[Batch_ID] = 16) AS 'Total'	
FROM [dbo].[CustomerData] 
	INNER JOIN [dbo].[CustomerBatch] ON [CustomerData].[Batch_ID] = [CustomerBatch].[Batch_ID]
WHERE ([Type] = 'RDO' OR [TYPE] = 'RDP') AND ([CustomerData].[Batch_ID] = 16)

The above statement almost gives me what I want.. but not in the format I want it. I get ...

Batch    |    RDO    |    RDP    |    Total    |
16          1024         600         1624

I want it displayed like this ...

Type    |    OpenClients    |
RDO           1024
RDP            600
Total         1624


That help any?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 07/20/2010 :  10:56:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
select
clienttype,count(*) as openclients,sum(case when LastDelivery>=dateadd(month,-10,getdate()) as ActiveClients
from table
group by clienttype



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

stronius
Starting Member

Canada
8 Posts

Posted - 07/20/2010 :  11:22:39  Show Profile  Reply with Quote
Wow.. thank you.. much easier than I thought. Although I will still have to add a totals row. I do have another question, but it's a different topic so I will open a new thread. Thanks again, huge help.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 07/20/2010 :  11:28:17  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You should do totalling at the front end application

Here is another method

select
clienttype,count(*) as openclients,sum(case when LastDelivery>=dateadd(month,-10,getdate()) as ActiveClients
from table
group by clienttype
union all
select
'total',count(*)
from table


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

stronius
Starting Member

Canada
8 Posts

Posted - 07/20/2010 :  11:50:47  Show Profile  Reply with Quote
I keep getting the following error with your query ...

Msg 102, Level 15, State 1, Procedure spS, Line 25
Incorrect syntax near ')'.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 07/21/2010 :  05:07:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try this


select
clienttype,count(*) as openclients,sum(case when LastDelivery>=dateadd(month,-10,getdate()) then 1 else 0 end) as ActiveClients
from table
group by clienttype
union all
select
'total',count(*)
from table


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000