Stored Proc for Reports - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Proc for Reports

Author

Topic

jojupi01
Starting Member

1 Post

Posted - 2011-02-10 : 19:11:47

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources