Pure SQL or SQL + Programming

[ProInfo]

Hello everyone,

I am having trouble making a decision on which direction to go.

I have several users with unique ID's and I am tracking visitor information, time, day, month, year, based on that ID and the month.

Plan A,
12 Select Statements (1 for each month) to get a monthly visitor count. The draw back here is that we could end up with 100 unique id's/accounts and if they are running reports it could amount to 1200 inquiries. Even further, this will be a monthly report, but I'd like weekly and daily reports eventually as well.

Plan B,
1 Select Statement and then use java/jsp to loop through the data getting the months total. The draw back I think might be at the end of the year. There could be around 10,000 records per ID, and to loop through them 12 times might take some time.

The data looks something like this.

ID - Day - Month - Year
1 - 15 - 3 - 2011
1 - 15 - 2 - 2011
2 - 15 - 9 - 2011
3 - 12 - 9 - 2011
2 - 12 - 1 - 2011
2 - 11 - 4 - 2011
1 - 10 - 5 - 2011
1 - 9 - 5 - 2011
3 - 5 - 9 - 2011
3 - 5 - 8 - 2011

Am I being overly paranoid about hitting the database 1200 times and grabbing small amounts of data each time? Tomcat/Struts (THE server framework) and MSSQL are on the same machine. So it doesn't have to go far to get the data.

Thanks for any input!

Tim

 

[Qik3Coder]

Do the aggregating in SQL (It's kinda what it's meant for...)

Have separate Yearly, Monthly, and Detailed (with date range) reports.

If you have trouble with the SQL post here with some some sample data and the code bits you have.

Basic concept of group by month:

Select DatePart(mm, myDate), Count(1)
Group by DatePart(mm, myDate)


Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[gmmastros]

I agree with Qik3Coder. You should be doing your aggregations in the database. Based on the description of your table structure, you may want to try something like this:

Select Id, Month, Year, Count(*) As VisitCount
From   YourTableNameHere
Group By Id, Month, Year
Order By Id, Year, Month

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ProInfo]

Thanks Qik3Coder and Gmmastros. I didn't know the DatePart function, so I have added that to the list of useful sql functions to understand. I ended up tweaking Gmmastros SQL to suit my needs! It works great for the monthly reports, but I have a feeling I will need to use the "DatePart" function when I drill down to weekly/daily stats.

Thanks for the replies!!

 

[gmmastros]

Adding "daily" information would be very similar to the above query, just add in Day, like this:

Select Id, Day, Month, Year, Count(*) As VisitCount
From   YourTableNameHere
Group By Id, Day, Month, Year
Order By Id, Year, Month, Day

Weekly reports may be a bit more challenging because you are not exactly storing that information. To do weekly reports, we would need to reconstruct a date based on the year, month, day columns and then group by DatePart(Week, *Reconstructed Date*). It wouldn't be too difficult though. Let us know if you need help with it.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ProInfo]

George,

Thanks for offering help! I could use some, as it would save me time vs. using the guess and test method!

I finished the monthly report yesterday thanks to your guys help! I'm going to try and tackle the weekly report this afternoon.

I neglected to say last time that I am also collecting a datetime column with this information "4/19/2011 11:04:48 AM". I call it "visit_time", along with visit_year, visit_month. I do not have a column "visit_day", because in the beginning I never thought there would be daily reports.

I would like my report to look like this, but I don't know if it's possible with a single SQL statement. I just don't think the SQL could be aware of the number of weeks in a month.

--- WK1 WK2 WK3 WK4 WK5
Jan 20 10 13 15 0
Feb 15 19 21 12 0
Mar 12 54 51 16 19
Apr 54 21 32 45 0
May 57 55 99 36 17
etc.

If this is not possible, I could use a report similar to what I have below. Then just use Java to visually represent the months with alternating background colors, or something of sorts!

Week 1: 100
Week 2: 145
Week 3: 157
Week 4: 198
Week 5: 102
Week 6: 116
Week 7: 154
etc.

Thanks for your time and help!

-Tim

 

[Qik3Coder]

This is a shades of grey area with SQL.
What are you considering the first week?

Given the following calendar(Thanks July!):
S |M |T |W |Th|F |S
26|27|28|29|30|01|02
03|04|05|06|07|08|09

Is this a business week (i.e. 5 business days)?
Do days with any business days count as a week, so Friday 7/1 is it's own week and 7/4 starts the next?

Is it the first 7 calendar days, Then every 7 after that?

Is it what SQL considers a wk, where part of June and part of July are in the same week?

Your approach is different based on what you need.

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[ProInfo]

Very good point Lodlaiden!

I am going to have to do a bit of soul searching on that one. If the first week of July has only 2 days in it, then the data will be inconsistent when comparing it to other "first weeks" of the month. July has only 2 days in the first week, August has 6. August will have more because it has 3 times as many dates.

Ok, soul has been searched! I need it based on a 52 week period where part of June and part of July are in the same week! This will give me a true weekly comparison where each week has 7 days in it!

Thanks for the logic!

 

[Qik3Coder]

Easy enough then. SQL sorts that out for you.

Select * from(
Select dt, DateAdd(dd,((DatePart(wk, dt)-2)*7)+2, cast(rtrim(DatePart(yy, dt))+'0101' as datetime)) EventWeek, intVal
from @_tbl) as a

Lodlaiden


I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[ProInfo]

Holy smokes!!!

Question: What is "intVal" and "EventWeek", and what's '0101' doing?

Select * from(
Select visit_time, DateAdd(dd,((DatePart(wk, visit_time)-2)*7)+2,
cast(rtrim(DatePart(yy, visit_time))+'0101' as datetime))
EventWeek, intVal from visits) as a

Right now I am getting a "Invalid column name 'intVal'." error.

Regards,
Tim

 

[Qik3Coder]

That;s whatever column actually has your number in it that you need to aggregate. my firewall was freaking out over the table definition (randomly does that) and data load.

EventWeek is what i called the column. This is fabricated from the dates you have in the table.

dt=your date column
intVal = your number to aggregate
@_tbl = your real table name

Lodlaiden



I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[UnsolvedCoding]

What comes from SQL
Date Note
7/21/2011 Testing Notes
6/6/2011 This is a test

What is needed in Excel

7/21/2011 Testing Notes 6/6/2011 This is a test

 

[ProInfo]

Hey, thanks again! I think the code will work. I ran it, and it basically grouped all the dates into a certain day (the beginning of that week), I ordered it by ID, and then by the week. I can use some Java to do the counts.

As for "UnsolvedCoding"...... I am know idea what he is talking about or asking!

 

[Qik3Coder]

Unsolved's post got tacked onto the wrong thread. Don't worry about it.


You can do the counts in SQL.
If you have you code, I can probably massage it without creating the table structures.
It's going to be something like

Select Week, Sum(intVal)
...
Group by Week


I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[ProInfo]

Select * from(
Select DateAdd(dd,((DatePart(wk, lib_time)-2)*7)+2,
cast(rtrim(DatePart(yy, lib_time))+'0101' as datetime))
EventWeek, lib_id from lib_visits) as a WHERE lib_id='PROINF' order BY lib_id,EventWeek

|-EventWeek------------|- ID--|
2011-05-02 00:00:00.000 PROINF
2011-05-02 00:00:00.000 PROINF
2011-05-02 00:00:00.000 PROINF
2011-05-09 00:00:00.000 PROINF
2011-05-09 00:00:00.000 PROINF
2011-05-09 00:00:00.000 PROINF
2011-05-09 00:00:00.000 PROINF
2011-05-09 00:00:00.000 PROINF
2011-05-09 00:00:00.000 PROINF

It's very close to what I need. I tried putting a sum in there, so that it returns

|-EventWeek------------|- ID--|--Total-|
2011-05-02 00:00:00.000 PROINF 3
2011-05-09 00:00:00.000 PROINF 6

But I got a bunch of errors about my column not being an aggregate.

 

[Qik3Coder]

Each of the columns that you want returned need to be in the GROUP BY clause, if it is not in an aggregate (i.e. SUM, COUNT AVG)

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[ProInfo]

Select count(*)as Total,* from(
Select DateAdd(dd,((DatePart(wk, lib_time)-2)*7)+2,
cast(rtrim(DatePart(yy, lib_time))+'0101' as datetime))
EventWeek, lib_id from lib_visits) as a WHERE lib_id='PROINF' GROUP BY lib_id,EventWeek;

|-Total-|------ EventWeek -----|------|
177 2011-05-02 00:00:00.000 PROINF
74 2011-05-09 00:00:00.000 PROINF
44 2011-05-16 00:00:00.000 PROINF
13 2011-05-23 00:00:00.000 PROINF
1 2011-05-30 00:00:00.000 PROINF
2 2011-06-13 00:00:00.000 PROINF
5 2011-06-27 00:00:00.000 PROINF


I think I got the data I needed, but I am unsure if the totals are correct. I am going to do a manual count to ensure it's working correctly.

 

[Qik3Coder]

A count(1) is faster than a count(*), you can use this if you don't need any logic.

If you get to where you need to to a partial count, you can do a
SUM(CASE WHEN X=X THEN 1 ELSE 0 END)


Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[ProInfo]

Thanks!! I changed my code. I didn't notice a speed increase, because I have a minimal amount of records! But I took your advice!

I find myself doing more programming then necessary because of my lack of SQL knowledge!