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
 General SQL Server Forums
 New to SQL Server Programming
 Custom reporting?

Author  Topic 

stronius
Starting Member

8 Posts

Posted - 2010-07-20 : 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

22864 Posts

Posted - 2010-07-20 : 10:08:51
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

8 Posts

Posted - 2010-07-20 : 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.
Go to Top of Page

stronius
Starting Member

8 Posts

Posted - 2010-07-20 : 10:53:32
[code]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)
[/code]
The above statement almost gives me what I want.. but not in the format I want it. I get ...
[code]
Batch | RDO | RDP | Total |
16 1024 600 1624
[/code]
I want it displayed like this ...
[code]
Type | OpenClients |
RDO 1024
RDP 600
Total 1624
[/code]

That help any?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-20 : 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
Go to Top of Page

stronius
Starting Member

8 Posts

Posted - 2010-07-20 : 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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-20 : 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
Go to Top of Page

stronius
Starting Member

8 Posts

Posted - 2010-07-20 : 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 ')'.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-21 : 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
Go to Top of Page
   

- Advertisement -