Professional Web Applications Themes

How to get a 0 for value field when no records exist for that datetime.. - Microsoft SQL / MS SQL Server

Hi all In my tableA I have tagkey,datetime and value fields. Tagkey is not the PK. Records look like 12,'2003-07-01 00:00:00',23.4 12,'2003-07-02 00:00:00',24.5 12,'2003-07-03 00:00:00',25.43 12,'2003-07-07 00:00:00', 26.12 12,'2003,07-09 00:00:00',26.39 .... .... I need a select query that selects records from tableA between a given datetime range (e.g. between '2003-07-01 00:00:00' and '2003-07-10 00:00:00' but gives me a zero value for a datetime for which a record does not exist. I should get output as 12,'2003-07-01 00:00:00',23.4 12,'2003-07-02 00:00:00',24.5 12,'2003-07-03 00:00:00',25.43 12,'2003-07-04 00:00:00',0 12,'2003-07-05 00:00:00',0 12,'2003-07-06 00:00:00',0 12,'2003-07-07 00:00:00', 26.12 12,'2003-07-08 00:00:00',0 12,'2003,07-09 00:00:00',26.39 .... .... Thanks...

  1. #1

    Default How to get a 0 for value field when no records exist for that datetime..

    Hi all

    In my tableA I have tagkey,datetime and value fields.
    Tagkey is not the PK. Records look like

    12,'2003-07-01 00:00:00',23.4
    12,'2003-07-02 00:00:00',24.5
    12,'2003-07-03 00:00:00',25.43
    12,'2003-07-07 00:00:00', 26.12
    12,'2003,07-09 00:00:00',26.39
    ....
    ....

    I need a select query that selects records from tableA
    between a given datetime range (e.g. between '2003-07-01
    00:00:00' and '2003-07-10 00:00:00' but gives me a zero
    value for a datetime for which a record does not exist.

    I should get output as

    12,'2003-07-01 00:00:00',23.4
    12,'2003-07-02 00:00:00',24.5
    12,'2003-07-03 00:00:00',25.43
    12,'2003-07-04 00:00:00',0
    12,'2003-07-05 00:00:00',0
    12,'2003-07-06 00:00:00',0
    12,'2003-07-07 00:00:00', 26.12
    12,'2003-07-08 00:00:00',0
    12,'2003,07-09 00:00:00',26.39
    ....
    ....

    Thanks
    Ricky Guest

  2. #2

    Default Re: How to get a 0 for value field when no records exist for that datetime..

    Create a calendar table (search the archives for tips) & you can do:

    SELECT c1.dt, COALESCE(val, 0)
    FROM Calendar c1
    LEFT OUTER JOIN tbl
    ON c1.dt = tbl.dt
    AND tbl.dt BETWEEN '20030701' AND '20030710';

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: How to get a 0 for value field when no records exist for that datetime..

    SELECT *,
    newfield =
    CASE WHEN datetime >firstdate AND datetime <=seconddate THEN datetime
    ELSE 0
    END
    FROM tableA

    but all of your dates will show as 1/1/1900 since the data type is now a
    datatime for this column.

    HTH,

    Robert

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Robert Guest

  4. #4

    Default Re: How to get a 0 for value field when no records exist for that datetime..

    >> I really don't want to maintain a calendar table for every datetime
    value. <<

    You can create one on the fly like:

    SELECT c1.dt, COALESCE(val, 0)
    FROM (
    SELECT DATEADD(d, Dates.Digit, '20030101')
    FROM (
    SELECT 1 * Unit.Digit + 10 * Ten.Digit + 100 * Hundred.Digit
    FROM (
    SELECT 0 UNION SELECT 1 UNION ... SELECT 9
    ) AS Unit ( Digit )
    CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION ... SELECT 9
    ) AS Ten ( Digit )
    CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION ... SELECT 9
    ) AS Hundred ( Digit )
    ) AS Dates ( Digit )
    WHERE YEAR(DATEADD(d, Dates.Digit, '20030101')) = 2003
    ) c1 (dt)
    LEFT OUTER JOIN tbl
    ON c1.dt = tbl.dt
    AND tbl.dt BETWEEN '20030701' AND '20030710';

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  5. #5

    Default Re: How to get a 0 for value field when no records exist for that datetime..

    Thank You Steeve and Anith. I will create a dummy table
    with all dates or just insert NULL values for my data for
    all the dates and use isnull(value,0). Thanks Anith for
    taking time to write that dynamic calender code snippet.

    Thanks
     
    01 
    07- 
    >you can do: 
    >.
    >[/ref]
    Ricky Guest

Similar Threads

  1. Replies: 3
    Last Post: October 1st, 09:24 PM
  2. NULL To DateTime field through sp
    By cp_anil@rediff in forum Macromedia ColdFusion
    Replies: 1
    Last Post: April 15th, 09:10 AM
  3. Set a datetime field to Null
    By Baiju K U in forum ASP Database
    Replies: 3
    Last Post: October 15th, 12:36 PM
  4. A2k: data entry subform for records that don't exist
    By lfaherty in forum Microsoft Access
    Replies: 1
    Last Post: July 22nd, 12:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139