Include all dates when there is no value ... 3

Chance1234 · Apr 3, 2008

I have a table which has data like the following

client, start, title , BCount
==============================
Bob, 1/2/2004, Send Reports ,1
Bob, 12/2/2004, Chase Reports ,1
Sid, 3/2/2004, Send Reports,1
Sid, 12/2/2004, Chase Reports,1

etc etc

I then have a view from this table , something like

SELECT Client, Title, DATEPART(Year, Start) AS Year, DATENAME(month, Start) AS Month, DATEPART(Day, Start) AS Day, BCount, Start
FROM dbo.vw_be_main
ORDER BY Start

All good, but i want to return days where there is no records as well

Only way i can think of is to create a tempoary table with dates in and do a join, is there a better way to acheive this ?

Chance,

F, G + 1MSTG

bborissov · Apr 3, 2008

Could you give an example what you want?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

Chance1234 · Apr 3, 2008

The end result would be

client, start, title , BCount
==============================
Bob, 1/2/2004, Send Reports ,1
null, 2/2/2004,null,null
Sid, 3/2/2004,Send Reports,1
null, 4/2/2004,null,null
null, 5/2/2004,null,null
null, 6/2/2004,null,null
null, 7/2/2004,null,null
null, 8/2/2004,null,null
null, 9/2/2004,null,null
null, 10/2/2004,null,null
null, 11/2/2004,null,null
Bob, 12/2/2004, Chase Reports ,1
Sid, 12/2/2004, Chase Reports,1
null,13/2/2004,null,null
null,14/2/2004,null,null
null,15/2/2004,null,null

etc, etc

Chance,

F, G + 1MSTG

SQLSister · Apr 3, 2008

Create a permanent dates table. Create a job to add all the dates for the next year that you will schedule to run in December every year. You will probably find you need this often and will not want to go to the trouble (And performance hit) to recreate with all possible dates in a temp table every time. Then join to the table as you said.

"NOTHING is more important in a database than integrity." ESquared

bborissov · Apr 3, 2008

Code:

[COLOR=blue]DECLARE[/color] @dBegin [COLOR=#FF00FF]datetime[/color], @dEnd [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]SET[/color] @dBegin = [COLOR=red]'20040201'[/color]
[COLOR=blue]SET[/color] @dEnd   = [COLOR=red]'20040229'[/color]
[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (client [COLOR=blue]varchar[/color](200), [COLOR=#FF00FF]start[/color] [COLOR=#FF00FF]datetime[/color], title [COLOR=blue]varchar[/color](200), BCount [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'Bob'[/color],[COLOR=red]'20040201'[/color],[COLOR=red]'Send Reports'[/color] ,1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'Bob'[/color],[COLOR=red]'20040212'[/color],[COLOR=red]'Chase Reports'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'Sid'[/color],[COLOR=red]'20040203'[/color],[COLOR=red]'Send Reports'[/color] ,1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'Sid'[/color],[COLOR=red]'20040212'[/color],[COLOR=red]'Chase Reports'[/color],1)



[COLOR=green]-- Single select statement here
[/color][COLOR=blue]select[/color] Tst.Client,
       r.range_date,
       Tst.Title,
       Tst.BCount  
[COLOR=blue]from[/color]  @Test Tst
[COLOR=#FF00FF]right[/color] [COLOR=blue]outer[/color] [COLOR=blue]join[/color] (
[COLOR=blue]select[/color] @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
[COLOR=blue]from[/color]       ([COLOR=blue]select[/color] 0 b1  union [COLOR=blue]select[/color] 1    b1)  t1
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b2  union [COLOR=blue]select[/color] 2    b2)  t2
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b3  union [COLOR=blue]select[/color] 4    b3)  t3
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b4  union [COLOR=blue]select[/color] 8    b4)  t4
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b5  union [COLOR=blue]select[/color] 16   b5)  t5
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b6  union [COLOR=blue]select[/color] 32   b6)  t6
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b7  union [COLOR=blue]select[/color] 64   b7)  t7
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b8  union [COLOR=blue]select[/color] 128  b8)  t8
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b9  union [COLOR=blue]select[/color] 256  b9)  t9
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b10 union [COLOR=blue]select[/color] 512  b10) t10
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b11 union [COLOR=blue]select[/color] 1024 b11) t11
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b12 union [COLOR=blue]select[/color] 2048 b12) t12
[COLOR=blue]where[/color] @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r
[COLOR=blue]on[/color] Tst.Start = r.range_date
[COLOR=blue]order[/color] [COLOR=blue]by[/color] r.range_date

But I am with SQLSister here. Create a permanent table with all dates.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

Chance1234 · Apr 3, 2008

OK Ive created my calendar table

Code:

USE [ReferenceCalendar]
GO
/****** Object:  Table [dbo].[Calendar]    Script Date: 04/03/2008 16:29:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Calendar](
	[dt] [datetime] NOT NULL,
	[isWeekday] [bit] NULL,
	[isHoliday] [bit] NULL,
	[Y] [smallint] NULL,
	[FY] [smallint] NULL,
	[Q] [tinyint] NULL,
	[M] [tinyint] NULL,
	[D] [tinyint] NULL,
	[DW] [tinyint] NULL,
	[monthname] [varchar](9) NULL,
	[dayname] [varchar](9) NULL,
	[W] [tinyint] NULL,
 CONSTRAINT [PK__Calendar__7C8480AE] PRIMARY KEY CLUSTERED 
(
	[dt] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

And I now have my master view

Code:

SELECT     TOP (100) PERCENT ReferenceCalendar.dbo.Calendar.FY, ReferenceCalendar.dbo.Calendar.M, ReferenceCalendar.dbo.Calendar.DW, 
                      ReferenceCalendar.dbo.Calendar.monthname, ReferenceCalendar.dbo.Calendar.dayname, dbo.vw_be_main.Start, dbo.vw_be_main.Department, 
                      dbo.vw_be_main.Client, dbo.vw_be_main.Title, ReferenceCalendar.dbo.Calendar.D, ReferenceCalendar.dbo.Calendar.dt, dbo.vw_be_main.JoinDate, 
                      CASE WHEN Title IS NULL THEN 0 ELSE 1 END AS Bcount
FROM         ReferenceCalendar.dbo.Calendar LEFT OUTER JOIN
                      dbo.vw_be_main ON ReferenceCalendar.dbo.Calendar.dt = dbo.vw_be_main.JoinDate
ORDER BY ReferenceCalendar.dbo.Calendar.dt

However, Im running into another problem , just noticed bborisov reply when typing this.

if you run bborisov example, that is how my date is coming out in my view

I then was plannign to run a second query off of this , where i am filtering by department, so in bborisoc example

I then only want to get records where the client is bob. However doing this, I loose the record for 2002-02-03 as sid has one but bob doesnt.

Chance,

F, G + 1MSTG

bborissov · Apr 3, 2008

Code:

[COLOR=blue]DECLARE[/color] @dBegin [COLOR=#FF00FF]datetime[/color], @dEnd [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]SET[/color] @dBegin = [COLOR=red]'20040201'[/color]
[COLOR=blue]SET[/color] @dEnd   = [COLOR=red]'20040229'[/color]
[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (client [COLOR=blue]varchar[/color](200), [COLOR=#FF00FF]start[/color] [COLOR=#FF00FF]datetime[/color], title [COLOR=blue]varchar[/color](200), BCount [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'Bob'[/color],[COLOR=red]'20040201'[/color],[COLOR=red]'Send Reports'[/color] ,1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'Bob'[/color],[COLOR=red]'20040212'[/color],[COLOR=red]'Chase Reports'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'Sid'[/color],[COLOR=red]'20040203'[/color],[COLOR=red]'Send Reports'[/color] ,1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'Sid'[/color],[COLOR=red]'20040212'[/color],[COLOR=red]'Chase Reports'[/color],1)



[COLOR=green]-- Single select statement here
[/color][COLOR=blue]select[/color] Tst.Client,
       r.range_date,
       Tst.Title,
       Tst.BCount  
[COLOR=blue]from[/color]  ([COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] @Test [COLOR=blue]WHERE[/color] Client = [COLOR=red]'Bob'[/color]) Tst
[COLOR=#FF00FF]RIGHT[/color] [COLOR=blue]JOIN[/color] (
[COLOR=blue]select[/color] @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
[COLOR=blue]from[/color]       ([COLOR=blue]select[/color] 0 b1  union [COLOR=blue]select[/color] 1    b1)  t1
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b2  union [COLOR=blue]select[/color] 2    b2)  t2
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b3  union [COLOR=blue]select[/color] 4    b3)  t3
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b4  union [COLOR=blue]select[/color] 8    b4)  t4
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b5  union [COLOR=blue]select[/color] 16   b5)  t5
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b6  union [COLOR=blue]select[/color] 32   b6)  t6
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b7  union [COLOR=blue]select[/color] 64   b7)  t7
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b8  union [COLOR=blue]select[/color] 128  b8)  t8
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b9  union [COLOR=blue]select[/color] 256  b9)  t9
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b10 union [COLOR=blue]select[/color] 512  b10) t10
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b11 union [COLOR=blue]select[/color] 1024 b11) t11
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b12 union [COLOR=blue]select[/color] 2048 b12) t12
[COLOR=blue]where[/color] @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r
[COLOR=blue]on[/color] Tst.Start = r.range_date
[COLOR=blue]order[/color] [COLOR=blue]by[/color] r.range_date

But I don't think that is possible for View. I totally missed the View part from your original question.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

Chance1234 · Apr 3, 2008

What I am trying to do ultimatly is come up with a query/s
that can I then select againt in reporting services for each department without having to mantain to much in the backend.

with this one, its for a report which mimics a calendar and i want to produce a calendar for each department. hence why I m trying to get all the dates alogn.

Chance,

F, G + 1MSTG

SQLSister · Apr 3, 2008

If you are doing two views why not make the first one sect all the records by person without joining to the calendar table and then do the join to the calendar table in the second one that has the departmental filter

"NOTHING is more important in a database than integrity." ESquared

Chance1234 · Apr 3, 2008

Seems then i wuold have to do a seperate view for each departement, which might be a valid option,

Chance,

F, G + 1MSTG

SQLSister · Apr 3, 2008

actually I would do a stored proc that uses the view by person and then joins to the date table. That way you can pass in the departmental parameter.

"NOTHING is more important in a database than integrity." ESquared

Chance1234 · Apr 4, 2008

trouble with the SP is Im querying this then from reporting services. Im currently looking at table functions at the moment.

Chance,

F, G + 1MSTG

ESquared · Apr 4, 2008

You can't use SPs from reporting services? Are you sure? That doesn't sound right to me.

Code:

SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO
CREATE DATABASE Calendar
go
USE Calendar
go
CREATE TABLE Numbers (Num int identity(1, 1) CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED)
SET NOCOUNT ON
INSERT Numbers DEFAULT VALUES
WHILE Scope_Identity() < 10000 INSERT Numbers DEFAULT VALUES 

CREATE TABLE ClientCal (
   Client varchar(100),
   Start datetime,
   Title varchar(100),
   BCount int,
   CONSTRAINT PK_ClientCal PRIMARY KEY CLUSTERED (Client, Start, Title)
)
INSERT ClientCal VALUES ('Bob','20040201','Send Reports' ,1)
INSERT ClientCal VALUES ('Bob','20040212','Chase Reports',1)
INSERT ClientCal VALUES ('Sid','20040203','Send Reports' ,1)
INSERT ClientCal VALUES ('Sid','20040212','Chase Reports',1)
INSERT ClientCal VALUES ('Joe','20040303','Send Reports' ,1)
INSERT ClientCal VALUES ('Joe','20040312','Chase Reports',1)
INSERT ClientCal VALUES ('Sally','20040302','Send Reports' ,1)
INSERT ClientCal VALUES ('Sally','20040313','Chase Reports',1)
INSERT ClientCal VALUES ('Jack','20040405','Send Reports' ,1)
INSERT ClientCal VALUES ('Jack','20040416','Chase Reports',1)
INSERT ClientCal VALUES ('Victoria','20040404','Send Reports' ,1)
INSERT ClientCal VALUES ('Victoria','20040415','Chase Reports',1)
INSERT ClientCal SELECT Client, Start + 1, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 2, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 4, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 8, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 16, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 32, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 64, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 128, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 256, Title, BCount FROM ClientCal

CREATE TABLE Dept (
   DeptName varchar(100) CONSTRAINT PK_Dept PRIMARY KEY CLUSTERED
)
INSERT Dept VALUES ('Finance')
INSERT Dept VALUES ('Marketing')

CREATE TABLE ClientDept (
   Client varchar(200),
   DeptName varchar(100),
   CONSTRAINT PK_ClientDept PRIMARY KEY CLUSTERED (Client, DeptName)
)
INSERT ClientDept VALUES ('Bob', 'Finance')
INSERT ClientDept VALUES ('Sid', 'Marketing')

SET NOCOUNT OFF
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuildCalendarProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BuildCalendarProc]
GO
CREATE PROCEDURE BuildCalendarProc
   @StartMonth datetime,
   @EndMonth datetime,
   @Dept varchar(100) = NULL
AS
SET @StartMonth = DateAdd(mm, DateDiff(mm, 0, @StartMonth), 0)
SET @EndMonth = DateAdd(mm, DateDiff(mm, 0, @EndMonth) + 1, 0)

SELECT
   CalendarDate = @StartMonth + Num - 1,
   C.Client,
   C.Title,
   C.BCount
FROM
   Numbers N
   LEFT JOIN (
      ClientCal C
      INNER JOIN ClientDept D ON C.Client = D.Client AND (D.DeptName = @Dept OR @Dept IS NULL)
   ) ON N.Num = C.Start - @StartMonth + 1
WHERE
   N.Num <= @EndMonth - @StartMonth

RETURN @@Error
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuildCalendarFunc]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[BuildCalendarFunc]
GO
CREATE FUNCTION BuildCalendarFunc (
   @StartMonth datetime,
   @EndMonth datetime,
   @Dept varchar(100)
)
RETURNS @Calendar TABLE (
   CalendarDate datetime,
   Client varchar(100),
   Title varchar(100),
   BCount int
)
AS
BEGIN
   SET @StartMonth = DateAdd(mm, DateDiff(mm, 0, @StartMonth), 0)
   SET @EndMonth = DateAdd(mm, DateDiff(mm, 0, @EndMonth) + 1, 0)

   INSERT @Calendar   
   SELECT
      @StartMonth + Num - 1,
      C.Client,
      C.Title,
      C.BCount
   FROM
      Numbers N
      LEFT JOIN (
         ClientCal C
         INNER JOIN ClientDept D ON C.Client = D.Client AND (D.DeptName = @Dept OR @Dept IS NULL)
      ) ON N.Num = C.Start - @StartMonth + 1
   WHERE
      N.Num <= @EndMonth - @StartMonth

   RETURN
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuildCalendarView]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[BuildCalendarView]
GO
CREATE VIEW BuildCalendarView
AS
SELECT
   CalendarDate = MinDate + Num - 1,
   C.Client,
   C.Title,
   C.BCount,
   D.DeptName
FROM
   Numbers N
   INNER JOIN (
      SELECT
         MinDate = DateAdd(yy, Year((Min(Start))) - 1900, 0),
         MaxDate = DateAdd(yy, Year((Max(Start))) - 1899, 0) 
      FROM ClientCal
   ) X ON N.Num <= MaxDate - MinDate
   CROSS JOIN Dept D
   LEFT JOIN (
      ClientCal C
      INNER JOIN ClientDept CD ON C.Client = CD.Client
   ) ON N.Num = C.Start - MinDate AND D.DeptName = CD.DeptName
WHERE
   N.Num <= MaxDate - MinDate
GO
EXEC BuildCalendarProc '20040115', '20040602'
EXEC BuildCalendarProc '20040115', '20041227', 'Finance'
SELECT * FROM BuildCalendarFunc ('20040115', '20040602', NULL)
SELECT * FROM BuildCalendarFunc ('20040115', '20041227', 'Finance')
SELECT DISTINCT CalendarDate, Client, Title FROM BuildCalendarView WHERE CalendarDate BETWEEN '20040101' AND '20040630'
SELECT CalendarDate, Client, Title FROM BuildCalendarView WHERE CalendarDate BETWEEN '20040101' AND '20041231' AND DeptName = 'Finance'
GO
/*
USE Master
*/
GO
/*
DROP DATABASE Calendar
*/
GO

I honestly am not getting the performance results I expected out of this...

[tt] CPU Reads Duration
Proc1: 265 4123 297
Proc2: 235 3265 297
Func1: 281 4890 297
Func2: 250 4112 297
View1: 156 468 172
View2: 344 2955 390[/tt]

For CPU and Duration, anything within 15 milliseconds or so is not significant.

The really weird part is that the view was hugely slower for a small amount of data, but now for a large amount of data it's superior for the first invocation by a TON. Perhaps some index tuning or query index hints could help out.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

SQLSister · Apr 4, 2008

http://blogs.msdn.com/glengordon/archive/2004/05/10/129356.aspx

"NOTHING is more important in a database than integrity." ESquared

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Include all dates when there is no value ... 3

Chance1234

IS-IT--Management

bborissov

Programmer

Chance1234

IS-IT--Management

SQLSister

Programmer

bborissov

Programmer

Chance1234

IS-IT--Management

bborissov

Programmer

Chance1234

IS-IT--Management

SQLSister

Programmer

Chance1234

IS-IT--Management

SQLSister

Programmer

Chance1234

IS-IT--Management

ESquared

Programmer

SQLSister

Programmer

Similar threads

Part and Inventory Search

Sponsor