Calculating columns volues based on different tbl

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)

Calculating columns volues based on different tbl

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-13 : 09:16:03

I have a current SQL stored procedure that displays Monthly report based on information from "ImplTable"
Recently I added [StatusCompletedDate], [StatusCancelledDate] to schema of KPITbl. I would like to write a SQL
code that will automatically generate for me [ProjectsEffortsCancelled],[ProjectseffortsCompleted] columns in
"ImplTable" based on [StatusCompletedDate] and [StatusCancelledDate]. (Right now I have a GridView that I take to edit mode and I insert these #s manually, displaying later
in the report).

ALTER procedure [dbo].[sp_MonthlyReport]

as

declare @dtDate as datetime
declare @MonthStartDate as datetime
declare @MonthEndDate as datetime
set @dtDate= getDate()
select @MonthStartDate = convert(char(10),dateadd(month,-1,dateadd(day,-1*datepart(day,@dtDate)+1,@dtDate)),101)
select @MonthEndDate = convert(char(10),dateadd(day,-1*datepart(day,@dtDate),@dtDate),101)

select * from dbo.ImplTable where date between @MonthStartDate and @MonthEndDate ORDER BY [Date] DESC

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
the final result of this query should be :

Date ProjectsEffortsCancelled projectseffortsCompleted DataMovedGB TotalFTE TotalFTC
------------------- --------------------------- -------------- ------------- -------- ----------
2007-12-31 00:00:00 0 171 0 10 6
2007-12-24 00:00:00 4 0 0 11 5
2007-12-17 00:00:00 4 0 22 11 5
2007-12-10 00:00:00 4 2 136 11 5
2007-12-03 00:00:00 4 0 16 11 5

This is a broken piece of code I was trying to write for this:

select Date,COUNT([Status]) as ProjectsCompleted,COUNT([dbo.KPITBL.Status]) as ProjectsCancelled from dbo.KPITBL,dbo.ImplTable
where
StatusCancelledDate
between ( @MonthStartDate and @MonthEndDate )
and
StatusCompletedDate
between (@MonthStartDate and @MonthEndDate )
GROUP by DATE

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE TABLE [dbo].[ImplTable](
[Date] [smalldatetime] NULL,
[InfrastructureDeliveryprojects] [int] NULL,
[InfraprojectsInInitiation] [int] NULL,
[ActiveInfraInImplementation] [int] NULL,
[ProjectsOnHOLD] [int] NULL,
[ProjectsEffortsCancelled] [int] NULL,
[projectseffortsCompleted] [int] NULL,
[NonDPIorrefreshServerAdditions] [int] NULL,
[ServerMoves] [int] NULL,
[DataMovedGB] [int] NULL,
[DataDeletedGB] [int] NULL,
[AllActiveTspans] [int] NULL,
[SystemMaintenanceUpdates] [int] NULL,
[TotalNumberOfChangeControls] [int] NULL,
[TotalFTE] [int] NULL,
[TotalFTC] [int] NULL,
[RecID] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_ImplTable] PRIMARY KEY CLUSTERED
(
[RecID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE TABLE [dbo].[KPITbl](
[TaskID] [int] IDENTITY(1,1) NOT NULL,
[Lead] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WRM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PTDB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAR] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RequestingLOB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOB] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinishLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Responsible] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RAG] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDates] [smalldatetime] NULL,
[EndDates] [smalldatetime] NULL,
[TreeorDomainImpacted] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfSites] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfUsers] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GBdatamoved] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GBdatadeleted] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfSrvrsAdded] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfSrvrsDecommed] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfAppsDeployed] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EUTEngineeringConsult] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TimeSpend] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Complexity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastUpdated] [smalldatetime] NULL,
[DCOorSTANDALONE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ECM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatusCompletedDate] [smalldatetime] NULL,
[StatusCancelledDate] [smalldatetime] NULL,
CONSTRAINT [PK_Sheet1a] PRIMARY KEY CLUSTERED
(
[TaskID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-13 : 10:56:26

You can simplify the date variables, or just calculate them in the where clause also..


declare @dtDate as datetime
declare @MonthStartDate as datetime
declare @MonthEndDate as datetime

select @MonthEndDate = getdate() - day(getdate()) 
select @MonthStartDate = @monthEnddate - day(@MonthEndDate) + 1


Print: @monthenddate
Print: @monthstartdate
Print: (getdate() - day(Getdate())) - day((getdate() - day(Getdate()))) + 1
Print: (getdate() - day(Getdate()))

Select * 
FROM dbo.ImplTable 
WHERE [Date] >= @MonthStartDate AND  -- month start date
      [Date] <= @MonthEndDate --month end date

Select * 
FROM dbo.ImplTable 
WHERE [Date] >= (getdate() - day(Getdate())) - day((getdate() - day(Getdate()))) + 1 AND  -- month start date
      [Date] <= (getdate() - day(Getdate())) --month end date

Maybe have a look here:
http://weblogs.sqlteam.com/jeffs/jeffs/jeffs/jeffs/archive/2007/08/29/SQL-Dates-and-Times.aspx

and here:
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

Also...

Poor planning on your part does not constitute an emergency on my part.

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-13 : 11:40:11

Ok, but I already have this.... The question was how to calculate
[ProjectsEffortsCancelled],[ProjectseffortsCompleted] automatically, in "ImplTable" and based on [StatusCompletedDate] and [StatusCancelledDate] from "KPITBL", so there is an entry for each Weekly date per month.

Any help is truly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-13 : 11:56:03

quote:
Originally posted by jszulc

Ok, but I already have this.... The question was how to calculate
[ProjectsEffortsCancelled],[ProjectseffortsCompleted] automatically, in "ImplTable" and based on [StatusCompletedDate] and [StatusCancelledDate] from "KPITBL", so there is an entry for each Weekly date per month.

Any help is truly appreciated

so you will have only one record per week in ImplTable? What's the feild in KPITbl that corresponds to date field in implTable?

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-13 : 12:14:24

Yes I will always have one record per week in ImplTable.
These 2 tables are not linked by Foreign key. ImplTable was used so far to enter the data manually, I was asked to make 2 columns automatic: ProjectsEffortsCancelled and projectseffortsCompleted.

The values from these 2 columns should come from KPITbl perhaps a COUNT function on [StatusCompletedDate] and [StatusCancelledDate] within specific date range based on criteria for dates in KPIImpl table. The problem is that I don't know how to link those tables because some of the data is enetered manually to IMPLTable. Perhaps VIEW would do this? or maybe introducing some other column?
Thank you for looking into this, I know you helped me previously with this db.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-13 : 12:27:26

Try this:-

UPDATE i
SET i.[projectseffortsCompleted]=t1.CompleteCount,
i.[ProjectsEffortsCancelled]=t2.CancelledCount
FROM ImplTbl i
CROSS APPLY(SELECT Count(*) AS CompleteCount FROM KPITbl WHERE [StatusCompletedDate] BETWEEN i.Date AND DATEADD(d,7,t.Date)) t1
CROSS APPLY(SELECT Count(*) AS CancelledCount FROM KPITbl WHERE [StatusCancelledDate] BETWEEN i.Date AND DATEADD(d,7,t.Date)) t2

I'm assuming that entry is made on ImplTbl at start of each week. If its at end of week replace BETWEEN Date AND DATEADD(d,7,Date) by BETWEEN DATEADD(d,-7,Date) AND Date

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-13 : 12:40:28

I will be testing your code now. By the way I have a button to click on Insert Week in my app. Behind th ebutton I have this:
ALTER procedure [dbo].[sp_InsertWeeklyDate]
as
declare @lastKPIdate as smalldatetime
declare @dateToinsert as smalldatetime
select top 1 @lastKPIdate = date from dbo.ImplTable ORDER BY [Date] DESC
--IF DATEDIFF(dd, @lastKPIdate, GETDATE())> 7
BEGIN
select @datetoinsert= dateadd(dd, 7, @lastKPIdate)
select Date= dateadd(dd, 7, @lastKPIdate)
SET CONTEXT_INFO 0x80
--SET IDENTITY_INSERT ImplTable ON
insert into dbo.ImplTable
(Date,InfrastructureDeliveryprojects,InfraprojectsInInitiation,ActiveInfraInImplementation,ProjectsOnHOLD,ProjectsEffortsCancelled,projectseffortsCompleted,NonDPIorrefreshServerAdditions,ServerMoves,DataMovedGB,DataDeletedGB,AllActiveTspans,SystemMaintenanceUpdates,TotalNumberOfChangeControls,TotalFTE,TotalFTC)
values (@datetoinsert,'0','0','0','0','0','0','0','0','0','0','0','0','0','0','0')

end

I could not figure out how to do it automatically. Thx agian will let you know shortly

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-13 : 13:18:38

Getting this error:
The multi-part identifier "t.Date" could not be bound

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-13 : 18:51:18

visakh16:
Thank you! Works now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources