Solved

# Date interval

Posted on 2007-10-18
Medium Priority
1,261 Views
MS  SQL 2000
vb.net

Hi experts
I have a question
On sql server I have this table, which contains the next columns:
ID      checkInDate      checkOutDate      sVID
1      10/14/2007      10/19/2007            2
2      10/14/2007      10/19/2007            2
3      10/16/2007      10/19/2007            1
4      10/14/2007      10/20/2007            1
5      10/15/2007      10/21/2007            2

Where is a sVID foreign key from another table.
I shuold write a Query ,Stored Procedure,Functions... This should have two parameters which are from a interval
for example.
@datOD datetime = 10/13/2007
@datDO datetime = 10/20/2007
For each date from the top interval, I have to get the next:
10/13/2007 no rows
10/14/2007
sVID      numOfsVID
2              2
1              1
where is numOfsVID number of  sVID-s
10/15/2007
sVID      numOfsVID
2             3
1             1
etc...
At the end i should have a table which looks like this one:
sVID      10/13/2007      10/14/2007      10/15/2007
2                 0                      2                     3
1                 0                      1                     1

0
Question by:amaimedia

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 20099558
Try this.  Replace foo with your real table name.

CREATE PROCEDURE [dbo].[PivotMatrix]
@Start datetime, @End datetime
AS

DECLARE @Day datetime, @sql varchar(8000)
SET @Day = @Start
SET @sql = 'SELECT DISTINCT t1.sVID'

CREATE TABLE #dates (Dt datetime)

WHILE @Day <= @End
BEGIN
INSERT INTO #dates VALUES (@Day)
SET @sql = @sql + ', (SELECT COUNT(t2.sVID) FROM foo t2 WHERE t2.sVID = t1.sVID AND CONVERT(datetime, ''' +
CONVERT(varchar, @Day, 101) + ''') BETWEEN t2.CheckInDate AND t2.CheckOutDate) AS [' +
CONVERT(varchar, @Day, 101) + ']'
SET @Day = DATEADD(d, 1, @Day)
END

SET @sql = @sql + ' FROM foo t1 ORDER BY t1.sVID'

EXEC(@sql)
DROP TABLE #dates
GO
0

Author Comment

ID: 20099662
Excellent thank you very much.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month6 days, 9 hours left to enroll