Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Special query on SQL Server 2005

Posted on 2013-12-24
8
Medium Priority
?
577 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 …
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

578 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