| Author |
 Topic  |
|
|
stronius
Starting Member
Canada
8 Posts |
 Posted - 07/20/2010 : 09:37:00
|
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
22461 Posts |
Posted - 07/20/2010 : 10:08:51
|
How do you determine whether the client is a open or active client?
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
stronius
Starting Member
Canada
8 Posts |
Posted - 07/20/2010 : 10:11:50
|
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. |
|
|
|
stronius
Starting Member
Canada
8 Posts |
Posted - 07/20/2010 : 10:53:32
|
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?
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 07/20/2010 : 10:56:53
|
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 |
|
|
|
stronius
Starting Member
Canada
8 Posts |
Posted - 07/20/2010 : 11:22:39
|
| 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. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 07/20/2010 : 11:28:17
|
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 |
|
|
|
stronius
Starting Member
Canada
8 Posts |
Posted - 07/20/2010 : 11:50:47
|
I keep getting the following error with your query ...
Msg 102, Level 15, State 1, Procedure spS, Line 25
Incorrect syntax near ')'.
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 07/21/2010 : 05:07:45
|
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 |
|
|
| |
Topic |
|
Custom reporting?
