How can I get all in query result into one view

Question

Post reply

How can I get all in query result into one view

peter2501

SSChasing Mays

Points: 601
More actions
August 27, 2012 at 8:27 am

#280792

I have a query to only one table.
I want to have all unique usernames per day as a total per day out of the table.
Now I have written this script which works fine, but the answer is getting into multiple views.
How can I get all info in one overview so I can work on that data.
I ave tried UNION and UNION ALL, but that does not work, because there is a loop in between.
Below the query, it only uses the date (in the table defined as Start) and the usernames
----------
Declare @Maand int
Declare @dag int
Set @Maand = DATEPART (Month, Getdate())
Set @dag = 1
While @dag<32
Begin
Select @dag AS August, COUNT (DISTINCT Username) AS uniquelogon from MiviewAugust
Where Activity = 'Logon'
and Datepart (Month, Start)=@Maand
and DATEPART (day,start) >(@dag-1)
and DATEPART (day,start) <=@dag
Set @dag=@dag+1
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

The first rule would be getting rid of that horrible while loop and use the dates intervals.

DECLARE @Date1datetime,

@Date2 datetime

SET @Date1 = DATEADD( mm, DATEDIFF(mm, 0, Getdate()), 0)

SET @Date2 = DATEADD( mm, 1, @Date1)

SelectDAY( Start) AS August,

COUNT (DISTINCT Username) AS uniquelogon

from MiviewAugust

Where Activity = 'Logon'

and Start >= @Date1

and start < @Date2

GROUP BY DAY( Start)

Before you use this code, try to explain what it's doing to be sure you understand it.

EDIT: I had an error on my query and returned only one row.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Table definitions please?

As a guess, I'd say join to a calender table and just do a group by on the date. No looping required. http://www.sqlservercentral.com/articles/T-SQL/70482/

You need a calender table if there's the chance that some dates will be missing from your data table and you want those days to show 0 not be completely missing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass