SQL Challenge -

[cfcProgrammer]

Hi Everyone,

I'm not sure if this is even possible to do within a sql statment but I thought I would throw it out to all of you SQL experts to see what you might come up with.

What I need is report that will display how many times each employee signs on per month. Simple right??? wrong... LOL

I need the report to have a column for the "Employee Name", "UserName" and one for each month.

Here is the code that will give the total for the year but I can't seem to figure out how to separate by month...

select distinct p.name, p.username, count(p.profile_id)as "ALL MONTHS"
from profiles p join login_history l on p.profile_id = l.profile_id
where logdate between '01-APR-2007' and '30-MAR-2008' and company_id = 15 group by p.name, p.username 

[U]EmployeeName|UserName|All Months|[/U]...

I need..

[U]EmployeeName|UserName|April|May|June|July[/U]...

You get the idea... :)

any suggestions out there??

Thanks so much.

I've posted quite a few questions on here in the last little while and I have to say.. I've learned quite a bit and I appreciate everyone's help in getting me up to speed in this new environment.

cfcProgrammer

 

[r937]

SELECT p.name
     , p.username
     , COUNT(CASE WHEN logdate BETWEEN '01-APR-2007' 
                                   and '30-APR-2007'
                  THEN 'yes' ELSE NULL END) AS April
     , COUNT(CASE WHEN logdate BETWEEN '01-MAY-2007' 
                                   and '31-MAY-2007'
                  THEN 'yes' ELSE NULL END) AS May
     , ...
     , COUNT(CASE WHEN logdate BETWEEN '01-MAR-2008' 
                                   and '31-MAR-2008'
                  THEN 'yes' ELSE NULL END) AS March
  FROM profiles AS p 
INNER
  JOIN login_history AS l 
    ON l.profile_id = p.profile_id
 WHERE company_id = 15 
GROUP 
    BY p.name
     , p.username

note that DISTINCT is hardly ever appropriate when you use GROUP BY -- there are instances when it is needed, but they are rare and this isn't one of them

r937.com | rudy.ca

 

[bborissov]

SELECT profiles.name,
       profiles.username,
       SUM(CASE WHEN MONTH(logdate) = 1
                     THEN 1
                ELSE 0 END) AS January,
       SUM(CASE WHEN MONTH(logdate) = 2
                     THEN 1
                ELSE 0 END) AS February
...
       SUM(CASE WHEN MONTH(logdate) = 12
                     THEN 1
                ELSE 0 END) AS December
FROM profiles
INNER JOIN login_history ON 
           login_history.profile_id = profiles.profile_id AND
           YEAR(login_history.logdate) = 2008 -- or 2007 or whatever
WHERE Company_id = 15
GROUP BY profiles.name, profiles.username

not tested





Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

boris, it's a fiscal year -- april to march

r937.com | rudy.ca

 

[bborissov]

From where you get this?
I re-read again the question, but no fiscal year mentioned there.

SELECT profiles.name,
       profiles.username,
       SUM(CASE WHEN MONTH(logdate) = 1
                     THEN 1
                ELSE 0 END) AS January,
       SUM(CASE WHEN MONTH(logdate) = 2
                     THEN 1
                ELSE 0 END) AS February
...
       SUM(CASE WHEN MONTH(logdate) = 12
                     THEN 1
                ELSE 0 END) AS December
FROM profiles
INNER JOIN login_history ON
           login_history.profile_id = profiles.profile_id AND
           login_history.logdate >= '20070401' AND 
           login_history.logdate <  '20080401'
WHERE Company_id = 15
GROUP BY profiles.name, profiles.username

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

i got it from here --

where logdate between [red]'01-APR-2007' and '30-MAR-2008'[/red]

and from here --

EmployeeName|UserName|[red]April|May|June|July...[/red]





r937.com | rudy.ca

 

[cfcProgrammer]

Hi,

The dates will be passed by the user entering a date range which I guess brings us to another issue to try to resolve. sorry for the confusion.

so the user will be entering a from date and a to date and these dates will be passed into a stored procedure as parameters.

From there I am trying to figure out how to take the code you both suggested and break it down using the parameters.

I should have mentioned the entire scenario at first, I didn't want to throw too much out there at once and I thought I could figure that out easily but again.. it is not coming too easily to me.

So sorry for any confusion or frustration I may have caused.

cfcProgrammer

 

[cfcProgrammer]

But you are right... I was entering a fiscal year... this is what they will be running this on 9 times out of 10.

cfcProgrammer

 

[r937]

my advice is to deconstruct the complex query, make it truly simple, and then do the cosmetic reformatting (multiple months on the same line of output) using your front-end application language (coldfusion?)

SELECT p.name
     , p.username
     , CONVERT(CHAR(7),logdate,120) AS yyyy_mm
     , COUNT(*) AS logins 
  FROM profiles AS p 
INNER
  JOIN login_history AS l 
    ON l.profile_id = p.profile_id
 WHERE company_id = 15 
   AND logdate >= #startdate#
   AND logdate  < #enddate#
GROUP 
    BY p.name
     , p.username 
     , CONVERT(CHAR(7),logdate,120)

r937.com | rudy.ca

 

[bborissov]

But what if you get dates that have the same month but different years?
01/01/2007 to 31/12/2008?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

in that case, there should be 24 monthly counts

as there will be with my yyyy_mm suggestion



r937.com | rudy.ca

 

[cfcProgrammer]

I'm using ASP/HTML to call this procedure and design the report...

This isn't going to be easy to accomplish is it??




cfcProgrammer

 

[bborissov]

CREATE PROCEDURE my_SpGetReport(
       @StartDate datetime,
       @EndDate   datetime)
AS
BEGIN
   SET @EndDate = @EndDate+1
-- Get a flat result set
   CRATE TABLE #Test (profile_id int,
                      name varchar(200),
                      username varchar(200),
                      YrMnt char(6))
   
   INSERT INTO #Test
   SELECT profiles.profile_id,
          profiles.name,
          profiles.username,
          CONVERT(varchar(6),logdate,112) AS YrMnt
   FROM profiles
   INNER JOIN login_history ON
              login_history.profile_id = profiles.profile_id AND
              login_history.logdate >= @StartDate AND
              login_history.logdate <  @EndDate
   WHERE Company_id = 15

-- Let do some pivoting
   DECLARE @SQL varchar(8000)
   SET @SQL = 'SELECT *
                      FROM #Test
               PIVOT (COUNT(profile_id) 
                      FOR YrMnt IN ('
  SELECT DISTINCT @SQL = @SQL + '['+YrMnt+'],'
         FROM #Test
         ORDER BY YrMnt
  SET @SQL = LEFT(@SQL,LEN(@SQL)-1) + ')) AS Pvt'
  EXEC (@sql)
END

NOT TESTED AT ALL!!!!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Oops, I forgot

DROP TABLE #Test

at the end of the SP

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[cfcProgrammer]

I am still trying to figure this out....

The keyword PIVOT is not accepted by SQL Server 2000. Is there a way to do this in SQL Server 2000.



cfcProgrammer

 

[r937]

have you tried my suggestion?

r937.com | rudy.ca

 

[SQLSister]

There are a coupl of FAQs on crosstab queries, I suggest you look at them. This is significantly nmore complicated in SQl server 2000 than 2005 which has the pivot keyword.

"NOTHING is more important in a database than integrity." ESquared

 

[bborissov]

That's why you always have to mention what version of the software you use , No matter in what forum you ask.
Agree with SQLSister you should search in FAQ section to see any references to crosstab queries.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.