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 | ActiveClientsRDO (# 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?MadhivananFailing to plan is Planning to fail |
|
|
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. |
|
|
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 1024RDP 600Total 1624[/code]That help any? |
|
|
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 clienttypeMadhivananFailing to plan is Planning to fail |
|
|
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. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-07-20 : 11:28:17
|
You should do totalling at the front end applicationHere is another methodselect clienttype,count(*) as openclients,sum(case when LastDelivery>=dateadd(month,-10,getdate()) as ActiveClients from table group by clienttypeunion allselect 'total',count(*) from table MadhivananFailing to plan is Planning to fail |
|
|
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 25Incorrect syntax near ')'. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-07-21 : 05:07:45
|
Try thisselect clienttype,count(*) as openclients,sum(case when LastDelivery>=dateadd(month,-10,getdate()) then 1 else 0 end) as ActiveClients from table group by clienttypeunion allselect 'total',count(*) from table MadhivananFailing to plan is Planning to fail |
|
|
|