Can someone optimize this SQL Query

Question

I have an SQL Logins table "tblLogins" which stores the UserId and the LoginDate for each user that logs in. I need to generate a graph to show how many logins were there daily. So I use this script below [CLASSIC ASP].

Is there any way to get this info without the loop and querying database for each day? As you can see, I am hitting the DB with 30 queries. OUTPUT FORMAT expected is this:

[[1, 26], [2, 16], [3, 16], [4, 26], [5, 0], [6, 0], [7, 25], [8, 21], [9, 90], [10, 12], [11, 11], [12, 21], [13, 0], [14, 18], [15, 17], [16, 21], [17, 23], [18, 19], [19, 0], [20, 0], [21, 12], [22, 17], [23, 12], [24, 7], [25, 11], [26, 21], [27, 0], [28, 18], [29, 20], [30, 0]]

which is [day of month, logins]. I was looking at COALESCE in SQL Server but not sure its possible and don't know the usage well.

THE CODE:

    i=1
    varStartDate =  DateAdd("m",-1, Date) 
    for i = 1 to DayCount
    intUserTotal = 0

    strSQL= "Select IsNull(Count(DISTINCT(userid)),0) as Logins from tblLogins where LoginDate >= '"& varStartDate &"' and LoginDate <= '"& DateAdd("d", 1, varStartDate) &"'



    rsTemp.Open strSQL,objConn,3,2
    if not rsTemp.EOF then
        if not isnull(rsTemp("Logins")) and trim(rsTemp("Logins")) <> "" then
          intUserTotal = trim(rsTemp("Logins"))
        else
          intUserTotal = 0
        end if
    else
        intUserTotal = 0
    end if
    rsTemp.close
    '' append in JSON FORMAT
    strData1= strData1 & "[" & i & ", "  & cInt(intUserTotal) & "], "
    ''' increment the date
    varStartDate =  DateAdd("d",1, varStartDate) 
    '' start with next date
    next

EDIT: There may be NO logins on some days so result should report 0 in the result for that day of the month. @alzaimar is close but there are still some problems there.

I am sorry what do you mean by that? the SQL Statement is there with the table structure as well. — JamesT, Mar 05 '13 at 11:00

score 0 · Accepted Answer · edited May 23 '17 at 12:01

0

How about creating a table with your results à la

declare @FirstOfMonth DateTime

set @FirstOfMonth = DateAdd(d, -DatePart(Day, GetDate())+1, getdate())
set @FirstOfMonth = Cast(floor(Cast(@FirstOfMonth as float)) as DateTime)

select datepart (day,LoginDate) as DayOfMonth, 
       Count(distinct UserID)
  from tblLogins 
 where LoginDate >= @FirstOfMonth
group by datepart (day,LoginDate)
order by 1

And if you want to see dates with no logins as well, you would have to create a table containing all days of that day and left join this table with the above result.

You can then concat the results into a string. An example how this could be done is showed here

Edit: As James made up his mind regarding the date range to be shown ;-) Here's the version showing everything from a month ago (same day) until today, sorted by the day number but excluding days without a login (i.e. they are not showing was 0).

select LoginDay, 
       Logins
  from (
  select  Cast(floor(Cast(LoginDate as float)) as DateTime) as SortDate,
          max(DatePart(d,LoginDate)) as LoginDay,
          Count(distinct UserID) as Logins
     from tblLogins
    where LoginDate >= DateAdd(m,-1, GetDate())
 group by Cast(floor(Cast(LoginDate as float)) as DateTime)
 ) x
 order by [SortDate]

edited May 23 '17 at 12:01

Community

1
1

answered Mar 05 '13 at 10:28

alzaimar

4,572
1
16
30

DateAdd(m,-1,GetDate() returns a start date of one month from today but the result shows DayOfMonth starting from 1. If its for today that should begin from say 5,6,7,etc? – JamesT Mar 05 '13 at 10:56
DayOfMonth | Logins 1 11 2 21 4 18 5 23 6 16 7 16 8 26 etc...---------------- Should be: 5 11 6 21 7 18 8 23 9 16 10 16 11 26 etc... Not able to set that. – JamesT Mar 05 '13 at 11:07
Also, what about the dates when logins are 0? – JamesT Mar 05 '13 at 11:30
I modified my approach. However, you seem to be a bit too much demanding, instead of being thankful. – alzaimar Mar 05 '13 at 12:13
Hi alzaimar, I am not demanding. Sorry if you thought I was. It was just some questions that came up while trying your solution. Thanks for the inputs as I am trying out your solutions and then giving you feedback. Will try the new mod. – JamesT Mar 05 '13 at 12:22
Hi Alzaimar, the previous query you had was giving me the correct results. Can you post that back again please? – JamesT Mar 05 '13 at 13:26
Hi James, no problem. However, the sorting approach will not work like this. I will post another solution – alzaimar Mar 05 '13 at 14:09
Although I like the solution, I think the `Cast(floor(Cast(LoginDate as float)) as DateTime)` part is a bit 'fishy', Assuming you're running a recent version of MSSQL, simply use `Cast(LoginDate as Date)`. – deroby Dec 03 '16 at 20:06
Yep. I used to use this fishy floor-casting when the date data type was not yet part of SQL-Server – alzaimar Dec 03 '16 at 21:05

Can someone optimize this SQL Query

1 Answers1