Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] Check rol permisiion

Expand Messages
  • Paul Livengood
    SELECT USER_NAME(p.uid),o .nameFROM sysprotectspINNER JOINsys.objectso ONp.id =o.object_id ________________________________ From: Surya A. Saputra
    Message 1 of 3 , Feb 29 8:22 AM
    • 0 Attachment
      SELECT
      USER_NAME(p.uid),o
      .nameFROM
      sysprotectspINNER
      JOINsys.objectso
      ONp.id =o.object_id



      ________________________________
      From: Surya A. Saputra <surya@...>
      To: SQL Int <SQLQueriesNoCode@yahoogroups.com>
      Sent: Wednesday, February 29, 2012 1:39 AM
      Subject: [SQLQueriesNoCode] Check rol permisiion



       

      Dear all,
      Is there anyone know how to check list of sp has been granted for spesific role or detail permision for spesific role e.g public ?

      Best Regards,
      Surya A. Saputra

      [Non-text portions of this message have been removed]




      [Non-text portions of this message have been removed]
    • Noman Aftab
      Hi, I have table which records reading of devices. The table structure is like: Device Table: DeviceId int DeviceName varchar Data: 1 LS30 2 LS31 3 LS32
      Message 2 of 3 , Mar 17, 2012
      • 0 Attachment
        Hi,
        I have table which records reading of devices. The table structure is like:

        Device Table:
        DeviceId int
        DeviceName varchar

        Data:
        1 LS30
        2 LS31
        3 LS32


        DeviceReading Table:
        DeviceId int
        ReceivedOn datetime
        Reading decimal

        Data:
        1 '2012-03-17 11:00 AM' 22.5

        2 '2012-03-17 11:01 AM' 21.5

        3 '2012-03-17 11:00 AM' 18.0

        1 '2012-03-17 11:17 AM' 34.5


        2 '2012-03-17 11:16 AM' 10.4


        2 '2012-03-17 11:34 AM' 45.6


        3 '2012-03-17 11:32 AM' 25.5



        I am looking for a query which gives result, such that if client wants to query data of every 15 minutes interval.

        The other developer wrote this query, which I do not understand much :)
        The result of this query is used in a cross tab, crystal report.

         (select 
        DEVICE_READING.Reading,
        DEVICE_READING.Received_on,
        DEVICE_READING.Device_ID
        from DEVICE_READING 
        inner join 
        (SELECT          Device_ID,
                        MIN(Received_on) AS Received_on
        FROM            (
        SELECT Device_ID, Min(Received_on) AS Received_on, Grp
        FROM
        (
        SELECT  Device_ID, Received_on,
                                 isNull(NullIF (DATEDIFF(MINUTE, MIN(Received_on) OVER (PARTITION BY Device_ID), Received_on) / 15 , 0),0) + 1  AS Grp
                         FROM    DEVICE_READING 
        WHERE [DEVICE_ID] IN (74,75)
        AND [RECEIVED_ON] >= ISNULL('2012-3-14', [RECEIVED_ON])
        AND [RECEIVED_ON] <= ISNULL('2012-3-17 23:59:59', [RECEIVED_ON])

        ) A
        group by Device_ID, Grp) cteLog
        GROUP BY        Device_ID,
                        Grp) dr2 on
        DEVICE_READING.Device_ID = dr2.Device_ID and 
        DEVICE_READING.Received_on = dr2.Received_on)


        The problem with this query is that, if datetime of two records (of two different devices) differs, then two different groups are shown.

        Is it possible to round the datetime field to a nearest datetime value. For example, the desired returns result as:

        1 '2012-03-17 11:00 AM' 22.5

        2 '2012-03-17 11:00 AM' 21.5

        3 '2012-03-17 11:00 AM' 18.0

        1 '2012-03-17 11:15 AM' 34.5


        2 '2012-03-17 11:15 AM' 10.4


        2 '2012-03-17 11:30 AM' 45.6


        3 '2012-03-17 11:30 AM' 25.5



         

        Best Wishes,
        Noman Aftab

         
        http://corpus.quran.com/wordbyword.jsp


        [Non-text portions of this message have been removed]
      Your message has been successfully submitted and would be delivered to recipients shortly.