|
|
Posted by Dan Guzman on 08/05/06 18:22
> Does SQL Server have problems recognizing m/d/yyyy? I hadn't heard.
When you pass a date string so SQL Server, the interpretation depends on
your DATAFORMAT setting. I suggest you use 'yyyymmdd' so that the value is
understood correctly regardless of the DATAFORMAT setting.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DFS" <nospam@dfs_.com> wrote in message
news:G72Bg.42676$Bd.9890@bignews6.bellsouth.net...
> --CELKO-- wrote:
>
>>DFS wrote
>> I want to get a list of visits made to the same building on the same
>> day, but by different employees, and for different visit codes (e.g.
>> records [sic] 5-6 or 9-11)
>>
>> Why are you posting Oracle dialect in a SQL Server newsgroup?
>
> Too lazy to change it from the similar post I made to an Oracle newsgroup.
>
> NUMBER to NUMERIC
> DATE to DATETIME
> VARCHAR2 to VARCHAR
>
> and that DDL runs fine in SQL Server.
>
>
>
>
>> Why are you using an improper date format?
>
> Does SQL Server have problems recognizing m/d/yyyy? I hadn't heard.
>
>
>
>> Why are you avoiding the natural
>> key with a fake "record number"
>
> I have comments and other info attached to those visits. It makes life
> much easier to use a unique ID like that. It wasn't necessary for this
> exercise, true.
>
>
>
>> you even use the word record, apparently not understanding
>> what a row is and how it is not like a record at all.
>
> The difference is academic (and I'm sure you'll tell me why it's not).
>
>
>
>> First, translate the dialect into Standard SQL and remove the
>> redundant non-key:
>>
>> CREATE TABLE Visits
>> (bldg_code VARCHAR(10) NOT NULL,
>> visit_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
>> emp_id NUMERIC(5,0) NOT NULL,
>> visit_code VARCHAR(5) NOT NULL,
>> PRIMARY KEY (bldg_code, visit_date, emp_id, visit_code));
>>
>> Now, thinking in sets instead of VB or other procedural languages that
>> have records, fields and files, think in sets. We want groups
>> (subsets) of rows based on (bldg_code, visit_date) with elements that
>> are unlike on the other two attributes. Once you say it that way the
>> query writes itself.
>>
>> SELECT bldg_code, visit_date, COUNT(*)
>> FROM Visits AS V
>> GROUP BY bldg_code, visit_date
>> HAVING MIN(emp_id) <> MAX(emp_id)
>> AND MIN(visit_code) <> MAX(visit_code);
>
>
> Very nice! Thanks for the good response. I like the 'thinking in sets'
> approach. That's my mindset, too. (well, recordsets anyway :)
>
> I didn't quite correctly specify the results I wanted. Besides the
> "visits by different employees, for different visit codes" I wanted to see
> all other rows for buildings in that subset. So a quick join of the table
> to your query, ala
>
> SELECT V.*
> FROM VISITS V INNER JOIN
> (
> SELECT BLDGCODE, VISITDATE
> FROM VISITS V
> GROUP BY BLDGCODE, VISITDATE
> HAVING MIN(EMPID) <> MAX(EMPID)
> AND MIN(VISITCODE) <> MAX(VISITCODE)
> ) V2
> ON (V.BLDGCODE = V2.BLDGCODE)
> AND (V.VISITDATE = V2.VISITDATE);
>
> and I got exactly what I needed (takes it to 177 rows versus 74. 177 is
> also the row count I got from my kludgey VB approach, so it's a nice
> confirmation I'm only partially dense).
>
> ps I bought SQL for Smarties 2nd Ed. Good stuff.
>
> Thanks
>
>
>
Navigation:
[Reply to this message]
|