February 10, 2011 at 5:33 pm
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
February 15, 2011 at 6:53 am
Here is one possible approach to keep everything DYNAMIC.
Adding a new SubSystem will dynamically keep counts tracked properly.
------------------------------------------------------------------------------------------------------
Step 1 - Since LoggedTransactions is where a new TransactionCount is introduced,
first step is to make sure we have one TransactionCount record for every
LoggedTransaction [Appid & Logdate] To accomplish this, we add a trigger
to the LoggedTransactions table such that it will assure a TransactionCount
record does exist.
---------------------------------------------------------
CREATE TRIGGER [dbo].[TransactionCount_AssureExistence]
ON [dbo].[LoggedTransactions]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @QuantRecs as int
select @QuantRecs=count(*) from INSERTED
if (@QuantRecs=1)
BEGIN
declare @AppId int
declare @LogDate datetime
select @AppId=Appid, @LogDate=LogDate from INSERTED
if (not (exists (select id from dbo.TransactionCount where Appid=@AppId and LogDate=@LogDate)))
BEGIN
INSERT dbo.TransactionCount(Appid, LogDate)
VALUES (@AppId, @LogDate)
END
END
END
------------------------------------------------------------------------------------------------------
Step 2 - We create a scalar function to do the sum work
---------------------------------------------------------
CREATE FUNCTION [dbo].[LoggedTransactions_CountFor_TransactionCount]
(
@Appid int,
@LogDate datetime
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SET @Result = null
select @Result=count(*) from dbo.LoggedTransactions where AppId=@Appid and LogDate=@LogDate
if @Result is null SET @Result=0
RETURN @Result
END
------------------------------------------------------------------------------------------------------
Step 3 - We change the DailyCount field in TransactionCount to be a calculated field that uses
the above Scalar function call
---------------------------------------------------------
CREATE TABLE [dbo].[TransactionCount](
[id] [int] IDENTITY(1,1) NOT NULL,
[Appid] [int] NOT NULL,
[LogDate] [datetime] NULL,
[DailyCount] AS ([dbo].[LoggedTransactions_CountFor_TransactionCount]([Appid],[LogDate])),
CONSTRAINT [PK_TransactionCount] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
------------------------------------------------------------------------------------------------------
Hope this helps
Frederick Volking
P.S. The new Trigger, can add a record with an identity column, this can cause problems
if your other code is simply using @@IDENTITY to determine the last record inserted. Because
the Trigger can insert a record too, @@IDENTITY can return the WRONG ID (it returns the ID
of the Triggers inserted record instead of the one you want) Meaning, do not use @@Identity
in other code. Instead use SCOPE_IDENTITY()
March 8, 2011 at 8:15 pm
hi thanks for taking the time to reply to my query. I really appreciate it. I will give it a try. i tried doing something like the following 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
March 9, 2011 at 12:25 pm
jojupi01,
You can't use a variable in an IN () construct. It's one of those things we've all wished for. You will have to do the whole query in Dynamic SQL and concatenate the variable you've already populated for the PIVOT IN into the main string used for the query.
Todd Fifield
March 9, 2011 at 2:12 pm
tfifield (3/9/2011)
jojupi01,You can't use a variable in an IN () construct. It's one of those things we've all wished for. You will have to do the whole query in Dynamic SQL and concatenate the variable you've already populated for the PIVOT IN into the main string used for the query.
Todd Fifield
Sounds just like another recent thread huh? 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 9, 2011 at 2:19 pm
-- 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
I don't think this is going to get you what you think it might. I think you are trying to get a comma separated list of subsystem into both @cols and @pivotIn. The way this is being assigned it will only get one value from the table, and with no order by clause you can't even know for sure which one it will get.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply