Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Special query on SQL Server 2005

Posted on 2013-12-24
8
Medium Priority
?
575 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 43

Accepted Solution

by:
pcelba earned 1600 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

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
 
LVL 43

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 70

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 43

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

596 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