LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Need Help

Need Help

Options

Previous Topic · Next Topic

apileggi@yahoo.com

Posted: Sunday, May 02, 2010 6:49:06 PM

Rank: Newbie

Joined: 10/28/2009
Posts: 5
Points: 15
Where do you live?: Ohio

Need help on creating a query for a SSRS. Listed below paycodes more then once if consecutive for 2 days or more.
Also need to report combination of UnplanPL and EIllUnpl if occurs in a day or consecutive days. Do not want to list if only one paycode unless it is consecutive day.

Example:

Lname, SUSANA L 4/2/2010 12:00:00 AM 8 UnplanPL --DO NOT Report
Lname, SUSANA L 4/5/2010 12:00:00 AM 8 UnplanPL --Report
Lname, SUSANA L 4/6/2010 12:00:00 AM 2 EIllUnpl --Report

Lname, MARIE-ALICE 4/5/2010 12:00:00 AM 8 UnplanPL --Report
Lname, MARIE-ALICE 4/6/2010 12:00:00 AM 8 UnplanPL --Report

SELECT PERSONFULLNAME, EVENTDATE, HOURS, PAYCODENAME
FROM VP_TABLE1
WHERE (PAYCODENAME IN ('UnplanPL', 'UnplanPL-D', 'EIllUnpl', 'EIllUnpl-D'))
ORDER BY EVENTDATE

Appreciate any help!

-Armand

Scott Whigham

Posted: Monday, May 03, 2010 8:52:17 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Armand - thanks for the sample data and query. To solve a query like this will require one of two things: a correlated subquery in the WHERE clause or a derived table/CTE. You could likely solve it with either solution although my preference would be a correlated subquery. The query would need to correlate on the PERSONFULLNAME and test for the EVENTDATE to have a prior EVENTDATE - something like...

Code:

SELECT PERSONFULLNAME, EVENTDATE, HOURS, PAYCODENAME
FROM VP_TABLE1 main
WHERE (PAYCODENAME IN ('UnplanPL', 'UnplanPL-D', 'EIllUnpl', 'EIllUnpl-D'))
AND EXISTS (
SELECT *
FROM VP_TABLE1 sub
WHERE main.PERSONFULLNAME = sub.PERSONFULLNAME
AND main.EVENTDATE IN (sub.EVENTDATE +1, sub.EVENTDATE - 1)
)
ORDER BY EVENTDATE

That isn't exactly the code but after spending a few minutes thinking about, I think that's close enough to at least get you on the right track.

apileggi@yahoo.com

Posted: Thursday, May 06, 2010 3:14:51 PM

Rank: Newbie

Joined: 10/28/2009
Posts: 5
Points: 15
Where do you live?: Ohio

Hi Scott,

Thank you for the sub query, but it seems to remove Fri, Sat and Sun. Would it be better
to do this in VS or Report Builder 2.0 with an expression? For a simple report, I want to show the employee if they have more than two identical paycodes or a combination of the query listed paycodes in 7 day period.

For a more complex report, can you also let me know if you create querys for an RDL report with much more logic for a quoted price on a consulting basis. It involves 2 tables that has schedule and timecard information.

Thank you,

Armand

Scott Whigham

Posted: Thursday, May 06, 2010 4:30:19 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Well, the query will not remove F, S, S unless your table/set doesn't have data with matching data around it; the query (which still needs work) just says, "Give me any records that have the same name and another date before or after my date." If you don't see F, S, or S then there is no data that matches that pattern.

RE: doing this in an expression in SSRS - that's fine. I think it's easier to just do the SQL and pass parameters from SSRS to SQL though.

RE: Complex queries: sure - you can be as complex as you wish including having multiple datasets, 64 table joins, calling stored procs, etc.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.