Date interval

Posted on 2007-10-18
Last Modified: 2012-08-14
MS  SQL 2000

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
sVID      numOfsVID
2              2
1              1
where is numOfsVID number of  sVID-s
sVID      numOfsVID
2             3
1             1
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

Question by:amaimedia
    LVL 92

    Accepted Solution

    Try this.  Replace foo with your real table name.

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

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

    CREATE TABLE #dates (Dt datetime)

    WHILE @Day <= @End
          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)

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

    DROP TABLE #dates

    Author Comment

    Excellent thank you very much.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now