Home Forums SQL Server 2005 SQL Server 2005 Integration Services How can automate report from sqlserver to be sent on email everyday. RE: How can automate report from sqlserver to be sent on email everyday.

  • June 20, 2013 at 11:35 am

    #1626059

    tnnelzo (6/20/2013)

    It is always from the first day of the current month....to the current day

    and b.date_entered >=cast('2013-06-01 00:00:00.000' as datetime)

    and b.date_entered < cast('2013-06-20 00:00:00.000'as datetime)

    and when the month ends it changes to

    and b.date_entered >=cast('2013-06-01 00:00:00.000' as datetime)

    and b.date_entered <=cast('2013-06-30 00:00:00.000'as datetime)

    Thanks and Best Regards.

    so you just need to get used to the DATEADD /DATEDIFF functions,a s they are very powerful;

    this is what i think you are after; note i changed your join to be explicit, and aliased your columns :

    SELECT

    DAY(b.date_entered) AS Day_D,

    SUM(a.despatched_qty) AS DesQty,

    SUM(a.val) AS Val

    FROM scheme.dgtable1 a

    INNER JOIN scheme.dntable3 b

    ON a.order_no = b.order_no

    WHERE a.product LIKE 'Machines%'

    AND b.date_entered >=DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0) --first day of THIS month

    AND b.date_entered < DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 1) --midnight of tomorrow morning

    GROUP BY DAY(b.date_entered)

    ORDER BY DAY(b.date_entered)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!