CROSS APPLY, NULL RECORDS - 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)

CROSS APPLY, NULL RECORDS

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-15 : 17:14:43

visakh16 (Aged Yak Warrior) provided excellent answers before for me on this forum.
I need to write a SQL query that would find all records without populated dates in KPITBL table. The columns are :[StatusCancelledDate] and [StatusCompletedDate] and I am looking for NULL records. ( These records are also set
to: STATUS='ACTIVE', STATUS='HOLD' and STATUS='DISCOVERY' in KPITBL) then take the # of these and update [InfrastructureDeliveryprojects] column based on weekly generated dates in
[ImplTable] in the DATE field. Any idea how to do this? Help is greatly appreciated.

This is what I have so far getting ERROR on 'BETWEEN':
ALTER PROCEDURE [dbo].[sp_InfraProjects_NO_CompletedorCancelled]
AS
BEGIN
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)

UPDATE i
SET i.[InfrastructureDeliveryprojects]=t1.AllProjects
FROM ImplTable i
CROSS APPLY(select count(*) as [AllProjects] from KPITBL WHERE
[StatusCompletedDate] is NULL
AND [StatusCancelledDate] is NULL
BETWEEN DATEADD(d,-7,Date) AND i.Date) t1

````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
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] [int] NULL,
[NumOfUsers] [int] NULL,
[GBdatamoved] [int] NULL,
[GBdatadeleted] [int] NULL,
[NumOfSrvrsAdded] [int] NULL,
[NumOfSrvrsDecommed] [int] NULL,
[NumOfAppsDeployed] [int] 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]
``````````````````````````````````````````````````````````````````````````````````````````````````````````````````
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]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-15 : 22:11:03

[code]UPDATE i
SET i.[InfrastructureDeliveryprojects]=t1.AllProjects
FROM ImplTable i
CROSS APPLY
(
SELECT COUNT(*) AS [AllProjects]
FROM KPITBL
WHERE [StatusCompletedDate] IS NULL
AND [StatusCancelledDate] IS NULL
AND <some date column> BETWEEN DATEADD(d,-7,Date) AND i.Date
) t1[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 00:39:28

jszulc, i think you might have to use the [LastUpdated] field in place of <some date column> to get desired result.

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-16 : 11:16:13

Thank you for your responses. [LastUpdated] column in place of <some date column> in SQL query above would imply that only updated records that fit the DATE range would get cross applied to specific week date rows in ImplTable? What I need is the actual # of so called "active records" for specific week that are not called "completed" or 'cancelled'. I take this number per week and I do some calculations in my C# app. Should I perhaps introduce other columns to track it. Please guide me through it. Thx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 11:27:27

but u would require some date field column to get count based on week.Do u have any other fields to identify week wise count?

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-16 : 11:40:48

No I don't have other columns to identify week count. But shoudl I perhaps introduce a column(s) to get this data in format I need? I know this is more of a design question that comes in late...

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-16 : 11:42:28

Maybe [StartDates], [EndDates] of the project?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 11:43:13

You should have had a datecreated column for achieving this which records date & time when record is entered

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-16 : 11:58:56

OK I am adding one now in INSERT statement, [CreatedDate] smalldatetime in KPITbl, how would you use it in SQL statement. Thx again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 12:06:32

quote:
Originally posted by jszulc

OK I am adding one now in INSERT statement, [CreatedDate] smalldatetime in KPITbl, how would you use it in SQL statement. Thx again

UPDATE i
SET i.[InfrastructureDeliveryprojects]=t1.AllProjects
FROM ImplTable i
CROSS APPLY
(
SELECT COUNT(*) AS [AllProjects]
FROM KPITBL
WHERE [StatusCompletedDate] IS NULL
AND [StatusCancelledDate] IS NULL
AND [CreatedDate] BETWEEN DATEADD(d,-7,i.Date) AND i.Date
) t1

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-16 : 13:45:59

That gives me zeros for each week ... I updated "CreatedDate" for each record with October 1st 2007, so each record has cretaed date. The point is all active, on hold and Discovery projects should be visible for each date below.

Date Infrastructure Delivery Projects Infrastructure Projects In Initiation Active Inrastructure Projects Projects On HOLD Cancelled Projects Completed Projetcs Non DPI Or Refresh Server Additions Server Moves Data Moved GB Data Deleted GB Active Tspans System Maintenance Updates Total Number Of Change Controls Total FTE Total FTC Project To Staff Ratio
12/31/2007 0 4 779 11 0 0 0 0 0 0 177 0 0 11 0
12/24/2007 0 4 819 11 0 0 0 0 0 0 177 0 0 11 0
12/17/2007 0 4 788 11 0 0 0 5 146 0 177 0 1 11 1
12/10/2007 0 4 773 11 0 0 0 0 270 2 177 0 1 11 1
12/3/2007 0 4 819 12 0 0 0 0 72 0 177 0 1 11 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 23:34:53

You need to set created date to original date when record was created. Its based on this date that it aggregates and takes the count and matches with corresponding date in ImplTable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources