date comparison results in large select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

date comparison results in large select

Author

Topic

cranky franky

12 Posts

Posted - 2009-09-02 : 20:33:48

Newbie to more complex queries. My desires have outpaced my skill set. ;-) I have a "check-in" sheet for a Daycare that I'm working on within Reporting Services. The query/report works great, however the staff wishes to add something. Dang users

Now I need to "test" whether "today" is the first day a child has EVER been to the daycare. The final result involves data from multiple tables. My thought was to "create" a field populated with the "test" results so that I could then use that field in an expression in the Reporting Services app. After days of playing around and searching, I'm lost. The examples of aggregate use I've found all have very simple selects.

There are two queries below. First one is the query using the MIN function to test my code for finding the earliest date a guest has been to daycare and comparing it to today and it works. Can't get that "idea" into the full blown final version - which is query 2.

My pseudo code for my problem is: get all kids (last_name, etc.) that are signed up for daycare today; if the earliest start_time in their history (join g.guest_num with s.guest_num then find min(s.start_time)) equals todays date, then FirstDay = 'y', else 'n'; sort it by POD (resource1) and last_name ASC;

Data notes: There are parameters from the report in here as well - @resource1, @start_day, @end_day. I use the parameter "builder" in the report designer so I did not include the declarations of those in the example. Similarly, there are other fields in the real select that I left out as I believe simply more fields beyond those listed doesn't affect the solution - could be wrong there. Lastly, this DB was not built by me, hopefully what I've listed below for the data structure is adequate. I can add more table details if needed. Both of the queries use the same data.

Data:
tables: guests as g
first_name, last_name, birth_date(yyyy-mm-dd), memo_1, text_1, guest_num

schedule as s
resource1, start_time (date/time of event, yyyy-mm-dd hh:mm:ss.ms), guest_num

address as a
area_code, phone, guest_num

Query 1: test for comparing earliest date to daycare to today using an alias FirstDay:
select s.resource1, g.last_name,
CONVERT(varchar(12), min(s.start_time), 107)AS FirstDay,
case when (CONVERT(varchar(12), min(s.start_time), 107) = CONVERT (varchar(12),GETDATE(), 107))
then 'y'
else 'n'
end AS test
from schedule as s
left outer join guests AS g ON s.guest_num = g.guest_num
where s.resource1 LIKE '%inf%'AND g.last_name = 'williams'
group by s.resource1, g.last_name

result which works:
resource1 last_name FirstDay test
----------- --------- ---------- ---
DAYCARE INF williams 2009-03-07 n
DAYCARE INF williams 2009-09-02 y

so now, I wish to get that idea (if not the code) into the following.

query 2:
SELECT CASE WHEN s.resource1 LIKE '%inf%' THEN 'Inf'
WHEN s.resource1 LIKE '%2-3%' THEN 'Tod'
WHEN s.resource1 LIKE '%pre%' THEN 'Pre'
WHEN s.resource1 LIKE '%7-12%' THEN 'Sum'
ELSE 'unknown'
END AS POD,
g.last_name AS name, g.memo_1 AS Parent, a.area_code + ' ' + a.phone AS Phone,
DATEPART(yy, GETDATE()) - DATEPART(yy, g.birth_date) -
CASE
WHEN SUBSTRING(CONVERT(CHAR(8), GETDATE(), 112), 5, 4)
< SUBSTRING(CONVERT(CHAR(8), birth_date, 112), 5, 4)
THEN 1
ELSE 0
END AS Age,
CONVERT(varchar(12), @start_day, 107) AS Today
FROM schedule AS s
LEFT OUTER JOIN guests AS g ON s.guest_num = g.guest_num
LEFT OUTER JOIN address AS a ON g.guest_num = a.guest_um
WHERE (s.resource1 LIKE @resource1)
AND (s.start_time BETWEEN CONVERT(varchar(12), @start_day, 101)
AND DATEADD(d, 1, CONVERT(varchar(12), @end_day, 101)))
ORDER BY s.resource1, last_name

desired results:

POD name test Age Phone Parent
--- --------- ---- --- ------------ ---------
Inf Smith n 1 111-111-1111 Sally
Inf Jones y 1 222-222-2222 Tom
Pre Wilks n 5 333-333-3333 Frank
Pre Tully n 6 444-444-4444 Laura
Tod Green y 3 555-555-5555 Hank

Frankly I'm more interested in the theory as to HOW to do this more than simply the correct answer for my data. First, is my method (query 1) comparing today to the earliest found date in the guest record ok or is there a more efficient method?
Secondly, I still struggle with aggregate function use when needing large result sets. With other reports I'm working on I frequently seem to need/desire a result set that includes a column with a result from an aggregate function along with other data. I've discovered some of my other/previous problems can be done in the report design app (totals, grouping, etc) but this issue does not seem to fit that. My procedural programming background is messing me up I'm afraid.

I'm hoping the answer(s) will be so eloquent that I won't feel so stupid for not figuring this out.

Thanks for any help and sorry for the lengthy post and multiple questions.

Franklin

"Let a complex system run long enough and something interesting is bound to happen."

ScottWhigham
Starting Member

49 Posts

Posted - 2009-09-03 : 05:52:26

Kudos for providing table schema, queries, and data but look - you've way overcomplicated this whole thing: is including columns like Age, Phone, Parent, POD germane to solving your problem? No - by including them you have made it more complex (i.e. more code thus a longer post) which scares people off. Your query has CASE statements, SUBSTRING, DATEADD, CONVERT, etc - all completely unrelated to solving your stated problem (at least to anyone wanting to help you). Due to how the forum formats code, I can't make heads or tails of the second query as a result. I can tell that the query is not the same one to produce the "desired results" though (and it appears as though it is supposed to?).

After spending a few minutes looking at it, it would seem a subquery would likely be what you need for this - have you tried that?

For complex situations, it's also often helpful to include a DDL script complete with the DML to load up some sample data. No one is going to take the time to go write their own CREATE TABLE and INSERT statements to test this out.

========================================================

I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-03 : 10:42:47

First link in my signature tells you how to give us the DML and DDL .

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

cranky franky

12 Posts

Posted - 2009-09-03 : 11:43:23

I was worried about the length and had read one post on how to present the data. found the link you provided above on how to provide DML and DDL AFTER I had posted. dolp. will try to do that and repost a shorter version. A bit overwhelmed learning all this, so thanks for reply.

"Let a complex system run long enough and something interesting is bound to happen."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources