Professional Web Applications Themes

using modulo function within query - Microsoft SQL / MS SQL Server

Use DATEDIFF to calculate the difference between two dates: SELECT * FROM blah WHERE DATEDIFF(DAY, sql_date, dts_start_day_ref) % days_in_schedule + 1 = day_of_schedule I'm not clear if I've got the parentheses in the correct place because you had one missing from the code you posted. If you still can't get it to work, please post some code to reproduce the problem, including DDL and sample data. Your code worked for me once I replaced the missing bracket. -- David Portas ------------ Please reply only to the newsgroup --...

  1. #1

    Default Re: using modulo function within query

    Use DATEDIFF to calculate the difference between two dates:

    SELECT *
    FROM blah
    WHERE DATEDIFF(DAY, sql_date, dts_start_day_ref) % days_in_schedule + 1 =
    day_of_schedule

    I'm not clear if I've got the parentheses in the correct place because you
    had one missing from the code you posted.

    If you still can't get it to work, please post some code to reproduce the
    problem, including DDL and sample data. Your code worked for me once I
    replaced the missing bracket.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  2. #2

    Default Re: using modulo function within query

    Oops. Date parameters wrong way round.

    SELECT *
    FROM blah
    WHERE DATEDIFF(DAY, dts_start_day_ref, sql_date) % days_in_schedule + 1 =
    day_of_schedule


    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "David Portas" <org> wrote in message
    news:phx.gbl... 
    you 


    David Guest

  3. #3

    Default Re: using modulo function within query

    Will you still get the error with this scenario...

    SELECT *
    FROM blah
    WHERE day_of_schedule = ((cast(sql_date as int) - cast(dts_start_day_ref as
    int)) % days_in_schedule)) + 1

    Jason

    "alex ivascu" <com> wrote in message
    news:016301c34ae7$3dd5abe0$gbl... 


    Jason Guest

  4. #4

    Default using modulo function within query


    Hi
    Use

    select * from blah 
    days_in_schedule)) + 1 = day_of_schedule 
    Should work

    Kn
     
    in 
    kannan Guest

  5. #5

    Default Re: using modulo function within query

    Hi Jason. unfortunately, yes. any other tips? ideas?

    alex ivascu

     
    (dts_start_day_ref as [/ref]
    in [/ref]
    % [/ref]
    that [/ref]
    operator. 
    >
    >
    >.
    >[/ref]
    alex Guest

  6. #6

    Default Re: using modulo function within query

    Please post some code to reproduce the problem.

    Include:
    A CREATE TABLE statement (just the relevant columns)
    Some INSERT statements with sample data.

    The query I posted works for me.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "alex ivascu" <com> wrote in message
    news:021101c34aed$e04f16d0$gbl... 
    > days_in_schedule + 1 = 
    > wrote in message [/ref]
    > dates: [/ref]
    > days_in_schedule + 1 = [/ref]
    > correct place because [/ref]
    > code to reproduce the [/ref]
    > worked for me once I 
    > >
    > >
    > >.
    > >[/ref][/ref]


    David Guest

  7. #7

    Default Re: using modulo function within query

    sorry for the confusion, jason. i should've said no, it doesn't work.

    alex ivascu



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

  8. #8

    Default Re: using modulo function within query

    okay.

    CREATE TABLE NMSCHED_DATETIME_DIM (
    DATETIME_KEY NUMERIC NOT NULL,
    SQL_DATETIME DATETIME NOT NULL,
    CONSTRAINT NMADM_DATETIME_DIM_PK
    PRIMARY KEY ( DATETIME_KEY ) )
    go

    CREATE TABLE NMSCHED_TMPLT_SHFT_XREF (
    TEMPLATE_ID VARCHAR (20) NOT NULL,
    SHIFT_KEY NUMERIC NOT NULL,
    UPDT_USERID VARCHAR (30),
    UPDT_DTS DATETIME,
    SHIFT_ID VARCHAR (30),
    SHIFT_SEQ INTEGER,
    CONSTRAINT PK_NMSCHED_TMPLT_SHFT_XREF
    PRIMARY KEY ( TEMPLATE_ID, SHIFT_KEY ) )
    go

    CREATE TABLE NMSCHED_SCHED_DAY (
    SCHED_ID VARCHAR (20) NOT NULL,
    DAY_OF_SCHEDULE NUMERIC NOT NULL,
    UPDT_USERID VARCHAR (30),
    UPDT_DTS DATETIME,
    TEMPLATE_ID VARCHAR (20),
    CONSTRAINT PK_NMSCHED_SCHED_DAY
    PRIMARY KEY ( SCHED_ID, DAY_OF_SCHEDULE ) )
    go

    CREATE TABLE NMSCHED_SCHED_DEF (
    SCHED_ID VARCHAR (20) NOT NULL,
    UPDT_USERID VARCHAR (30),
    UPDT_DTS DATETIME,
    DAYS_IN_SCHEDULE NUMERIC,
    DTS_START_DAY_REF DATETIME,
    CONSTRAINT PK_NMSCHED_SCHED_DEF
    PRIMARY KEY ( SCHED_ID ) )
    go

    CREATE TABLE NMSCHED_SHIFT_DIM (
    SHIFT_KEY NUMERIC NOT NULL,
    SHIFT_START_TIME NUMERIC NOT NULL,
    SHIFT_END_TIME NUMERIC NOT NULL,
    SHIFT_AVAIL_TIME NUMERIC NOT NULL,
    SHIFT_SEQ NUMERIC NOT NULL,
    SHIFT_ID VARCHAR (30) NOT NULL,
    START_TIME_CHAR VARCHAR (4),
    END_TIME_CHAR VARCHAR (4),
    START_TIME_SSSSS NUMERIC,
    END_TIME_SSSSS NUMERIC,
    CONSTRAINT NMSCHED_SHIFT_DIM_PK
    PRIMARY KEY ( SHIFT_KEY ) )
    go

    and the proc that i'm trying to build is here:

    create PROCEDURE nmetric.NMSCHED_POP_ORG_AVAIL (v_i_org_id
    VARCHAR)
    AS
    declare
    v_org_key numeric,
    v_sched_id VARCHAR(20),
    v_org_date DATETIME,
    v_count int
    --
    BEGIN
    SELECT v_org_key=org_key, v_sched_id=sched_id
    FROM nmetric.NMSCHED_ORG_DIM
    WHERE org_id = v_i_org_id
    --
    BEGIN
    SELECT v_org_date = isnull(MAX(sql_date),'01/01/1970')
    FROM nmetric.NMSCHED_DATE_DIM a
    WHERE EXISTS (SELECT 'x' FROM
    nmetric.NMSCHED_ORG_AVAIL_FACT b
    WHERE b.org_key = v_org_key
    AND a.date_key=b.date_key);
    if rowcount=0
    set v_org_date = '01/01/1970'
    --TO_DATE('01/01/1970','MM/DD/YYYY');
    END
    --
    INSERT INTO nmetric.nmsched_org_avail_fact (date_key, org_key,
    shift_key, sql_date, shift_seq,
    shift_avail_time, shift_avail_time_sssss, start_date_sssss,
    end_date_sssss)
    SELECT distinct a.date_key, v_org_key, b.shift_key, a.sql_date,
    e.shift_seq,
    e.shift_avail_time, e.shift_avail_time_sssss,
    e.start_time_sssss, e.end_time_sssss
    FROM nmetric.NMSCHED_DATE_DIM a,
    nmetric.NMSCHED_TMPLT_SHFT_XREF b,
    nmetric.NMSCHED_SCHED_DAY c,
    nmetric.NMSCHED_SCHED_DEF d,
    nmetric.NMSCHED_SHIFT_DIM e
    WHERE
    ((datediff(day,d.dts_start_day_ref,a.sql_date))%d. days_in_schedule) + 1
    = c.day_of_schedule
    AND c.template_id = b.template_id
    AND c.sched_id = d.sched_id
    AND b.shift_key = e.shift_key
    AND b.shift_seq = e.shift_seq
    AND b.shift_id = e.shift_id
    AND d.sched_id = v_sched_id
    AND a.sql_date > v_org_date
    AND a.sql_date >= d.dts_start_day_ref
    if rowcount=0
    print 'Unable to populate org '+v_i_org_id
    END;


    GO


    ---- THANKS DAVID -----

    alex ivascu




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

  9. #9

    Default Re: using modulo function within query

    Got it.

    days_in_schedule is not an INTEGER. Your DDL defines it as NUMERIC.

    Either change it to INTEGER or change your WHERE clause to:

    WHERE
    ((DATEDIFF(DAY,d.dts_start_day_ref,a.sql_date)) %
    CAST(d.days_in_schedule AS INTEGER)) + 1
    = c.day_of_schedule ....

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  10. #10

    Default Re: using modulo function within query

    For the modulus operand (dividend % divisor), the dividend and the
    divisor must both be of the integer data type but the table DDL has
    days_in_schedule with a numeric data type.

    The solution is to include "cast(days_in_schedule as integer)"

    Here is a repro:

    use tempdb
    go
    CREATE TABLE Testit
    (dts_start_day_ref datetime not null
    ,sql_date datetime not null
    ,days_in_schedule NUMERIC(6,0)
    )
    go
    insert into Testit
    (dts_start_day_ref ,sql_date , days_in_schedule )
    VALUES ( '20010101' , '20010131' ,7)


    select dts_start_day_ref ,sql_date,days_in_schedule
    , datediff(DD,dts_start_day_ref,sql_date) diffDays
    , datediff(DD,dts_start_day_ref,sql_date) % cast( days_in_schedule AS
    INTEGER) + 1 As SchedDayCnt

    ---------------------------------------------------
    -- Without the cast as integer
    -- , datediff(DD,dts_start_day_ref,sql_date) % days_in_schedule + 1
    As SchedDayCnt
    --
    From Testit

    Carl Federl
    Please post DDL (create table) with datatypes, primary and foreign keys.

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

  11. #11

    Default Re: using modulo function within query

    THANK you, David.

    alex ivascu



    "David Portas" <org> wrote in message
    news:phx.gbl... 


    alex Guest

Similar Threads

  1. èDW + PHP + Mysql] modulo inserimento dati con campi calcolati?
    By sweetman in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: November 14th, 02:37 PM
  2. Help with Query Function
    By Daniel Eng in forum Coldfusion - Getting Started
    Replies: 6
    Last Post: September 16th, 11:12 AM
  3. Function to be a query
    By ser_g in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 28th, 01:34 PM
  4. modulo question
    By itsme in forum Macromedia Director Basics
    Replies: 0
    Last Post: January 30th, 02:30 AM
  5. sql-query works alone but not in function...
    By Dennis in forum PHP Development
    Replies: 3
    Last Post: September 27th, 03:35 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