Solved

Special query on SQL Server 2005

Posted on 2013-12-24
8
535 Views
Last Modified: 2013-12-25
I have a table T like this

FromDate char(10)
ToDate char(10)
Text varchar(30)

In fact, this table contains closing dates of my customer's company.
For example, this table currently contains

2013-12-24  2013-12-24  From 12 AM
2013-12-25  2014-01-02  Christmas bridge

I need to write a stored procedure that will receive as parameter a date (in fact a month), like '2013-12' and that will be able to return all closing dates and corresponding text, example for 2013-12 is :

2013-12-24  From 12 AM
2013-12-25  Christmas bridge
2013-12-26  Christmas bridge
2013-12-27  Christmas bridge
2013-12-28  Christmas bridge
2013-12-29  Christmas bridge
2013-12-30  Christmas bridge
2013-12-31  Christmas bridge

Is there a way to return this kind of dataset without creating a temporary table ?
Is a kind of view needed ?

Thanks
0
Comment
Question by:LeTay
  • 3
  • 3
  • 2
8 Comments
 
LVL 41

Accepted Solution

by:
pcelba earned 400 total points
ID: 39737780
Yes, you could use some recursion BUT it is always easier to create calendar table and use it as the starting point for your task. The calendar table should contain one record for each day in given range and you may create it as a temporary table or keep it as permanent table in your database.

Alternate (but not so good) solution is to use some system table which contains a line of cardinal numbers.

And some code:
CREATE TABLE Calendar (daydate char(10))
INSERT INTO Calendar VALUES ('2013-12-24')
INSERT INTO Calendar VALUES ('2013-12-25')
INSERT INTO Calendar VALUES ('2013-12-26')
INSERT INTO Calendar VALUES ('2013-12-27')
INSERT INTO Calendar VALUES ('2013-12-28')
INSERT INTO Calendar VALUES ('2013-12-29')
INSERT INTO Calendar VALUES ('2013-12-30')
INSERT INTO Calendar VALUES ('2013-12-31')
-- etc. ... Of course, you may use a loop and DATEADD() function to populate this table

SELECT c.daydate, T.text
  FROM Calendar c, T
 WHERE c.daydate BETWEEN T.FromDate AND T.ToDate
   AND LEFT(c.daydate, 7) = '2013-12'
 ORDER BY c.daydate

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39738305
You don't need actual dates in the table; a tally "table", really just in-line CTEs, work fine.

For example, see code below.  I included sample dates for Nov, Dec and Jan, and did a run for Nov after Dec just as an added test:

-- this temp table is just to re-create your "T" table containing non-work dates
IF OBJECT_ID('tempdb..#T') IS NOT NULL
    DROP TABLE #T
CREATE TABLE #T (
    FromDate char(10),
    ToDate char(10),
    Text varchar(30)
    )
INSERT INTO #T
SELECT '2013-11-28' AS FromDate, '2013-11-29' AS ToDate, 'Thanksgiving and Friday after' AS Text
UNION ALL
SELECT '2013-12-24' AS FromDate, '2013-12-24' AS ToDate, 'From 12 AM' AS Text
UNION ALL
SELECT '2013-12-25', '2014-01-02', 'Christmas bridge'
UNION ALL
SELECT '2014-01-01', '2014-01-01', 'New Year''s Day'

------------------------------------------------------------------------------------------------------------------------

DECLARE @input_date char(7)

------------------------------------------------------------------------------------------------------------------------

SET @input_date = '2013-12'

-- create a list of day numbers "on-the-fly" using CTEs and CROSS JOIN
;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    INNER JOIN cteDigits [10s] ON [10s].digit BETWEEN 0 AND 3
)
SELECT
    DATEADD(DAY, tally.tally, input.start_date) AS Close_Date,
    T.Text
FROM (
    SELECT @input_date + '-01' AS start_date
) AS input
CROSS JOIN #T AS T
INNER JOIN cteTally tally ON
    tally.tally BETWEEN 0 AND 30
WHERE
    DATEADD(DAY, tally.tally, input.start_date) >= FromDate AND
    DATEADD(DAY, tally.tally, input.start_date) <= ToDate
ORDER BY
    Close_Date

------------------------------------------------------------------------------------------------------------------------

SET @input_date = '2013-11'

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    INNER JOIN cteDigits [10s] ON [10s].digit BETWEEN 0 AND 3
)
SELECT
    DATEADD(DAY, tally.tally, input.start_date) AS Close_Date,
    T.Text
FROM (
    SELECT @input_date + '-01' AS start_date
) AS input
CROSS JOIN #T AS T
INNER JOIN cteTally tally ON
    tally.tally BETWEEN 0 AND 30
WHERE
    DATEADD(DAY, tally.tally, input.start_date) >= FromDate AND
    DATEADD(DAY, tally.tally, input.start_date) <= ToDate
ORDER BY
    Close_Date
0
 

Author Closing Comment

by:LeTay
ID: 39738314
I will create a Calendar table
Looks the most performant solution
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39738330
LOL.  No, it's actually slower.  Any read from a physical table will always be more overhead than just in-line calcs.  Don't mistake the length of code for the performance of code.  (Besides, you originally stated you did not want to create another table.)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 41

Expert Comment

by:pcelba
ID: 39738482
I did not say the Calendar table is faster. It is just easier to code which is the measure making money today... Thanks for the points  :-)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39738497
You didn't, LeTay did: "a Calendar table Looks like the most performant solution".

Especially with this code:
     AND LEFT(c.daydate, 7) = '2013-12'
it will perform much less well against a large calendar table, since it will always have to scan the entire table (or covering index, if one exists).
0
 
LVL 41

Expert Comment

by:pcelba
ID: 39738941
"performant" from LeTay point of view could mean his own performance not SQL code performance :-) ...

Beside the fact above part of query is easy to optimize by appropriate index creation you don't need large calendar table. Even 10 years old data means about 3600 records which is nothing for SQL Server...

The code should work at the first place. Then you may optimize it if necessary. (There is no reason to optimize the query which takes 5 seconds once in a month.)

Remember the fact too complex code is not so easy to maintain. Your code does not reflect number of days in a month. To make it month length independent will take some additional time.

But your code is very good material to study. CTEs are still not so widely used.
0
 

Author Comment

by:LeTay
ID: 39738994
Hello friends !
No panic.
I understand that the inline code is nice but I prefer the table solution with then a very very simple query.
The fact is that it is accessed by a internet server.
My customer site (www.galeriemoderne.be) has a menu to show the calendar.
Currently, the data there is based on scheduled auctions.
It is "created" for the surfer month by month (code ASP from a third party) so to incorporate the closing dates of the customer, one query per month is needed, and per surfer.
I guess that the data of the calendar table, very very small size (something like 45 lines a year) is not a big issue
Anyway, I will read the code of scottpletcher
Thanks again for the work
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 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

22 Experts available now in Live!

Get 1:1 Help Now