Solved

Special query on SQL Server 2005

Posted on 2013-12-24
8
557 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 42

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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

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 42

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

837 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