How to display all dates in stored procedure from table.?

Question

0.00/5 (No votes)

See more:

hello friends , Can somebody guide me how to write the store procedure query

my sql table :

dates | contacted
20/10/15| 3
21/10/15| 1
22/10/15| 5
24/10/15| 10
27/10/15| 3

this is table which i will get while inserting

here i want write the query for stored procedure

if u see table 23rd and 25th dates are missing in that dates i want 0 value

i want to get the stored procedure result like

VB

 dates | contacted
20/10/15| 3
21/10/15| 1
23/10/15| 0
22/10/15| 5
24/10/15| 10
25/10/15| 0
26/10/15| 0
27/10/15| 3

can somebody guide me how to write the query

Posted 29-Oct-15 0:25am

12045649

Add a Solution

2 solutions

Solution 1

Try to use CTE[^] then join data returned by recursive query with your data:

SQL

;WITH AllDates AS
(
    --initial date
    SELECT CONVERT(DATE, '2015-10-20') AS Dates
    --recursive part
    UNION ALL
    SELECT DATEADD(DD, 1, Dates) AS Dates
    FROM AllDates
    WHERE DATEADD(DD, 1, Dates)< '2015-10-28'
)
SELECT t1.Dates, COALESCE(t2.Contacted, 0) AS Contacted
FROM AllDates As t1 LEFT JOIN YourTableName AS t2 ON t1.Dates = t2.Dates

For further information, please see:
WITH common_table_expression (Transact-SQL)[^]
Recursive Queries Using Common Table Expressions[^]
DATEADD[^]
Visual Representation of SQL Joins[^]

Complete sample:

SQL

SET DATEFORMAT ymd;

DECLARE @YourTableName TABLE(dates DATE, contacted INT)

INSERT INTO @YourTableName (dates, contacted)
VALUES('2015-10-20',  3),
('2015-10-21',  1),
('2015-10-22',  5),
('2015-10-24',  10),
('2015-10-27',  3)


DECLARE @startDate DATE = '2015-10-20'
DECLARE @endDate DATE = '2015-10-28'

;WITH AllDates AS
(
    --initial date
    SELECT  @startDate AS Dates
    --recursive part
    UNION ALL
    SELECT DATEADD(DD, 1, Dates) AS Dates
    FROM AllDates
    WHERE DATEADD(DD, 1, Dates)<= @endDate
)
SELECT t1.Dates, COALESCE(t2.Contacted, 0) AS Contacted
FROM AllDates As t1 LEFT JOIN @YourTableName AS t2 ON t1.Dates = t2.Dates

Returns:

Dates	Contacted
2015-10-20	3
2015-10-21	1
2015-10-22	5
2015-10-23	0
2015-10-24	10
2015-10-25	0
2015-10-26	0
2015-10-27	3
2015-10-28	0

Posted 29-Oct-15 0:42am

Maciej Los

Updated 29-Oct-15 1:35am

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PANKAJMAURYA · Accepted Answer · 2015-10-29T00:50:00

Solution 2

You can try this

SQL

DECLARE @MinDate DATE
DECLARE @MaxDate DATE
DECLARE @MissingDates TABLE ( [dates] DATE,[contacted] int )

SELECT @MinDate = min(dates) FROM [get_date]
SELECT @MaxDate = max(dates) FROM [get_date]

WHILE @MinDate <= @MaxDate
BEGIN
    IF NOT EXISTS (SELECT dates FROM [get_date]
                   WHERE dates = @MinDate)
        INSERT INTO @MissingDates ( [dates], [contacted])
        VALUES ( @MinDate,0 )

    SET @MinDate = DATEADD(day,1,@MinDate)
END

SELECT [dates], [contacted]  FROM [get_date]
UNION
SELECT [dates], [contacted]  FROM @MissingDates

1. In Above Query first you have to identify min date and max date .
2. Create a table variable which will hold the missing dates with contacted as 0
3. Loop through all the dates from min to max if it is not in your original table insert it in table variable.
4. Now you have two table First one is your original table having dates and contacted second one is generated table variable which have only missing dates with contacted 0
5. Now you have to take tha data from both tables which will have all the dates.

Posted 29-Oct-15 0:50am

PANKAJMAURYA

Updated 29-Oct-15 1:39am

v4

Comments

12045649 29-Oct-15 7:04am

hello Pankajmaurya ..thanks for reply

should i write date in place of @minDate and table name in place of @missingcartypeids

Can u please rewrite the query neatly..with comments understandable.plz

[no name] 29-Oct-15 7:06am

Only you have to change your tablename where i have used Table1.

[no name] 29-Oct-15 7:11am

Please post your full query for select so that We can updates our solution with your table which you need to execute only in your database.

12045649 29-Oct-15 7:24am

Actually my table name is: get_date

My table :

dates | contacted
20/10/15| 3
21/10/15| 1
22/10/15| 5
24/10/15| 10
27/10/15| 3

I want to get like

dates | contacted
20/10/15| 3
21/10/15| 1
23/10/15| 0
22/10/15| 5
24/10/15| 10
25/10/15| 0
26/10/15| 0
27/10/15| 3

select * from get_date

[no name] 29-Oct-15 7:27am

Try to execute query in your database. I have updated the query according to you.

12045649 29-Oct-15 7:30am

Msg 1087, Level 15, State 2, Line 13
Must declare the table variable "@MissingCarTypeIDs".

12045649 29-Oct-15 7:33am

I am getting error like this:

Msg 1087, Level 15, State 2, Line 13
Must declare the table variable "@MissingCarTypeIDs".

Please guide me

[no name] 29-Oct-15 7:40am

Sorry For that. Try Now.

12045649 29-Oct-15 8:09am

PANKAJMAURYA can u tell me the query..

[no name] 30-Oct-15 0:35am

Query is as above which i updated.

or you can use

DECLARE @MinDate DATE
DECLARE @MaxDate DATE
DECLARE @MissingDates TABLE ( [dates] DATE,[contacted] int )

SELECT @MinDate = min(dates) FROM [get_date]
SELECT @MaxDate = max(dates) FROM [get_date]

WHILE @MinDate <= @MaxDate
BEGIN
IF NOT EXISTS (SELECT dates FROM [get_date]
WHERE dates = @MinDate)
INSERT INTO @MissingDates ( [dates], [contacted])
VALUES ( @MinDate,0 )

SET @MinDate = DATEADD(day,1,@MinDate)
END

SELECT [dates], [contacted] FROM [get_date]
UNION
SELECT [dates], [contacted] FROM @MissingDates