Solved

Special query on SQL Server 2005

Posted on 2013-12-24
8
524 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:ScottPletcher
ScottPletcher 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:ScottPletcher
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

13 Experts available now in Live!

Get 1:1 Help Now