Stored Proc for Reports

I have to write stored proc to get the daily transaction for a specified date. Right now what i do is everytime i log a transaction in table3 I update table 2 with the count. Then i use table 2 to write the stored proc and to get the desired result. I have pasted my stored proc below with the output. My problem is when another subsystem is added in the AppInfo table i need to update my stored proc because i have hardcoded the subsystem name. Can you please show me a better way to do this.



Table1:AppInfo

AppId SubSystem

1 Dev
2 Test
3 Prod

Table2:TransactionCount
Id Appid LogDate DailyCount
----------------------------------------------------------
1 1 11/9/2010 12:00:00 AM 2
2 2 11/9/2010 12:00:00 AM 1
3 3 11/9/2010 12:00:00 AM 1
4 1 11/19/2010 12:00:00 AM 2
5 2 11/19/2010 12:00:00 AM 1
6 3 11/19/2010 12:00:00 AM 1


Table3: LoggedTransactions

Id AppId LogDate
------------------------------------------
1 1 11/9/2010 12:00:00 AM
2 2 11/9/2010 12:00:00 AM
3 1 11/9/2010 1:00:00 PM
4 3 11/9/2010 2:00:00 PM
5 1 11/19/2010 12:00:00 AM
6 2 11/19/2010 12:00:00 AM
7 1 11/19/2010 1:00:00 PM
8 3 11/19/2010 2:00:00 PM

Stored Proc DailyTransCnt

@FromDate datetime,
@ToDate datetime

SELECT LogDate, ISNULL([Dev],0) AS Dev, ISNULL([Test],0) AS Test,
ISNULL([Prod],0) AS Prod
FROM
(SELECT LogDate, SubSystem, DailyCount from appInfo A INNER JOIN TransactionCount B ON A.AppId=B.AppId
where (logdate >= @FromDate and logdate < DateAdd(dd,1,@ToDate)) )ps
PIVOT
(
SUM (DailyCount)
FOR [SubSystem] IN
( [Dev], [Test], [Prod])
) AS pv

Result after executing stored proc
----------------------------------

Logdate Dev Test Prod
-----------------------------------
2010-11-9 2 1 1
2010-11-19 2 1 1

Hii....
The solution to your problem can be acheived by dynamic SQL.The below script show s how it can be acheived.This solution may not be the best and efficient solution as I am not the expert.There are risk s of SQL injection when using dynamic SQL and hence should be used cautiously.I got the sample test table from the msdn Code gallery.
---Create A sample Table----
create TABLE OrderDetail
(orderid
int,productname
varchar(30),productqty
int
)
-- Load Sample DataINSERT
INTO OrderDetail VALUES (1, 'Gift Card', 2)INSERT
INTO OrderDetail VALUES (1, 'Shipping', 1)INSERT
INTO OrderDetail VALUES (2, 'Gift Card', 2)INSERT
INTO OrderDetail VALUES (2, 'T-Shirt', 2)INSERT
INTO OrderDetail VALUES (2, 'Shipping', 2)insert
into OrderDetail values (3,'sales',4)declare
@part1 nvarchar(4000)declare
@part2 nvarchar(4000)declare
@part3 nvarchar(4000)declare
@i int,@max int,@productname varchar(10)
create table #test
(sr_no
int identity(1,1),productname
varchar(100)) insert
into #test(productname) select distinct productname from orderdetailselect @i=1,@max=MAX(sr_no) from #test set
@part1=' SELECT orderid, 'set @part2=' from (select orderid,productname,productqty from orderdetail) as source pivot ( sum(productqty) for productname in( 'while
(@i<=@max)begin
select @productname=productname from #test where sr_no=@i
set @part1=@part1+'['+@productname+'],'
set @part2=@part2+'['+@productname+'],'
set @i=@i+1endset
@part3=left(@part1,LEN(@part1)-1)+left(@part2,LEN(@part2)-1)+')) as Pvt ' exec sp_executesql @part3 drop
table #testdrop table OrderDetail

Hi hrishikesh_nk i tried using the pivot table.i tried doing something like thefollowing but i keep getting an error message Incorrect syntax near'@pivotIn'. Can you please tell me what i am doing wrong. Thanks.
ALTER PROCEDURE [dbo].[getDyn]
-- Add the parameters for the stored procedure here
@FromDate datetime,
@ToDate datetime

AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
declare @cols nvarchar(150)
declare @pivotIn nvarchar(150)
declare @sql nvarchar(1000)
declare @sqlParam nvarchar(100)

select @cols =
isnull(
@cols+', ISNULL(['+subsystem+'],0) AS ['+subsystem+']',
'ISNULL(['+subsystem+'],0) AS ['+subsystem+']'),
@pivotIn =
isnull(@pivotIn+', ['+subsystem+']', '['+subsystem+']')
from appinfo

SELECT LogDate, @cols
FROM (
SELECT LogDate, SubSystem, DailyCount
from appInfo A INNER JOIN TransactionCount B
ON A.AppId=B.AppId
where (logdate >= @FromDate and logdate < DateAdd(dd,1,@ToDate))
) ps
PIVOT(
SUM (DailyCount)
FOR [SubSystem] IN
(@pivotIn)
) AS pv

END

Hii can you put square brackets near the @pivotln in the pivot query and then run the query.
SELECT LogDate, @cols
FROM (
SELECT LogDate, SubSystem, DailyCount
from appInfo A INNER JOIN TransactionCount B
ON A.AppId=B.AppId
where (logdate >= @FromDate and logdate < DateAdd(dd,1,@ToDate))
) ps
PIVOT(
SUM (DailyCount)
FOR [SubSystem] IN
----brackets near @pivotln------
( [@pivotIn] )
) AS pv

Hii Madhivanan,
Its a Great Article