SQL Query to generate reports for Sharepoint Lists in MOSS 2007

Question

0.00/5 (No votes)

See more:

MOSS2007

Hi,

I have a Sharepoint List called Candidates.

Each Candidate has a Status which varies between 9 different values.

For each Candidate there may be multiple Status fields.

For Example

Candidate Name      Status         Date when Status was created/modified    ID
John Doe            New                   20/07/2011                         1
John Doe            Shortlisted           21/07/2011                         1
John Doe            Shortlisted           22/07/2011                         1
John Doe            Reject                23/07/2011                         1 
Jane Doe            New                   20/07/2011                         2
Jane Doe            Shortlisted           22/07/2011                         2
Jane Doe            Shortlisted           23/07/2011                         2
Jane Doe            Shortlisted           23/07/2011                         2
Jane Doe            Joined                23/07/2011                         2

So now I want a query(to generate a report) which gets the Candidate Name, and his latest Status for each status value... I mean that the query should generate the table below.

Candidate Name      Status         Date when Status was created/modified
John Doe            New                   20/07/2011
John Doe            Shortlisted           22/07/2011
John Doe            Reject                23/07/2011
Jane Doe            New                   20/07/2011
Jane Doe            Shortlisted           23/07/2011 
Jane Doe            Joined                23/07/2011

So as you can see I need to get only the latest Shortlisted value or the latest Joined value (if there are any multiple values with status Joined)

What I am able to get so far is for each name I am able to get the latest Status

ie

Candidate Name      Status         Date when Status was created/modified
John Doe            Reject                23/07/2011
Jane Doe            Joined                23/07/2011

thru the following query:

SQL

SELECT  [tp_ID],
        [nvarchar14],
        [tp_Modified]
FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY [tp_ID] ORDER BY [tp_Modified] DESC) AS rn,
                    [tp_ID],
                    [nvarchar14],
                    [tp_Modified]
          FROM      [WSS_Intvw666Mgmt].[dbo].[AllUserData]
          WHERE     [tp_ListId] = 'b126efb6-1239-4b8a-978b-897960078552'
        ) a
WHERE   rn = 1

In this query
tp_ID is the Candidate ID... which is unique
nvarchar14 is the Status
and tp_Modified is the date when the Status was created/modified

Can anyone please help me out with this?

Posted 26-Jul-11 1:05am

getbacktosrinu

Updated 26-Jul-11 14:36pm

Chris Maunder773K

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Not Active · Answer 1 · 2011-07-26T14:11:00

Solution 1

You are trying to query the SharePoint database directly with SQL? This is not supported and should not be attempted. Use a CAML query with the SPQuery or SPSiteDataQuery object

Posted 26-Jul-11 14:11pm

Not Active

Comments

getbacktosrinu 27-Jul-11 7:23am

Can u please suggest different methods to generate reports from Sharepoint Lists?

[no name] 27-Jul-11 8:04am

It depends what you mean by generate "report"

getbacktosrinu 28-Jul-11 7:56am

There are two requirements actually:
1. The number of days it has taken for a candidate to move from 'New' to 'Shortlisted' and from 'New' to 'Joined'
2. To know the number of candidates in different stages and the how the candidate profiles came in like internal resources etc

Any solution which can provide all this data at a glance in a bar chart or its equivalent is sufficient. Also this chart should be accessible in the Sharepoint site itself. So the only solution which I could think of was reports using SSRS.

[no name] 28-Jul-11 9:19am

Dundas provides some nice data visualization tools for SharePoint. You can form a CAML query to return the dates and status for each candidate then use the chart to configure the display. If you don't want/can't use Dundas then create your own WebPart.

SSRS is overkill for this feature as you have described it.

getbacktosrinu 29-Jul-11 5:25am

Thanks for the info Mark.

And yes I can't use Dundas. So I will try to create my own webpart. Let us see how it turns out...