locked
Need help on SQL query+CRM+SQL Server RRS feed

  • Question

  • Hi All,

    Currently the below query is displaying two columns data that is createdbyname & siteidname for particular date. 

    We want below additional columns information as well in the output. Can you please advise how to get below additional columns information using below query/or tables.

    required output:

    craetedbyname    siteidname   DateTime  totaluserscount

    Query:

    select distinct Filteredetel_bi_header.CreatedByName, FilteredSystemUser.siteidname
    from Filteredetel_bi_header, FilteredSystemUser
    where Filteredetel_bi_header.createdby = FilteredSystemUser.systemuserid
    and convert(varchar(12), Filteredetel_bi_header.CreatedOn, 103) = '15/05/2016'

    Monday, May 16, 2016 12:18 PM

All replies

  • Hi,

    I think you need to provide a bit more information regarding this. Which tables is the data coming from, what is it you want to filter?

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Monday, May 16, 2016 1:08 PM
  • Hello Rickard,

    I have already mentioned table names in from clause. we have to create report to check number of user (s) connected to CRM application per hour Ex from 10 AM to 11 AM--the total number of users count is 40 and users names are like A, B,C,etc & siteIDName like USA, UK,etc.)

    Required Output columns:

    CreatedByName       SiteIDName          DateTime               TotalUsersCountPerHour


    Monday, May 16, 2016 1:15 PM
  • Hi,

    Well, from that query you call siteidname from systemuser and createdbyname from etel_bi_header, more than that I don't see. I really can't say where the DateTime should come from and the TotalUserCountPerHour which probably is some sort of aggregated data.

    Sorry, I can't help you, best of luck!

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Monday, May 16, 2016 1:59 PM