?
Solved

Date interval

Posted on 2007-10-18
2
Medium Priority
?
1,261 Views
Last Modified: 2012-08-14
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
Comment
Question by:amaimedia
2 Comments
 
LVL 93

Accepted Solution

by:
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

by:amaimedia
ID: 20099662
Excellent thank you very much.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

594 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