Who's Counting? ...Apparently Not Me

Question

Who's Counting? ...Apparently Not Me

Christine M

Old Hand

Points: 393
More actions
January 2, 2008 at 2:07 pm

#177823

Hello All. Happy New Year.
I a table which lists changes that have taken place over the past week. I would like to list records for the past two business days, but only if there is a record for each business day, the 31st of December, 2007 and the 2nd of January, 2008. I do not want to see the records from the 31st of December, 2007 if they do not have a corresponding January record.
I originally wrote this, without the #Retrieval table:
--Get results from the most recent two days and place in staging table
SELECT DateReported, OWN, Name, EmployeeStatus, FullorPartTime,
GradeLevel, Title, JobEntryDate, OriginalHireDate, CurrentHireDate,
TermDate1, LOAStartDate1, LOAReturnDate1, DepartmentDesc, DepartmentEntryDate,
MgrOWN, ManagersName, isnull(OTC,0) as OTC, TypeCode, isnull(JobCode, 'na') as JobCode
into #Retrieval
from smcsarchive..CEM_PersonnelChanges2008
where datepart(dd,DateReported) = 2 --**Set most recent two days
or datepart(dd,DateReported) = 31
group by DateReported, OWN, Name, EmployeeStatus, FullorPartTime,
GradeLevel, Title, JobEntryDate, OriginalHireDate, CurrentHireDate,
TermDate1, LOAStartDate1, LOAReturnDate1, DepartmentDesc, DepartmentEntryDate,
MgrOWN, ManagersName, OTC, TypeCode, JobCode
having count(OWN) >1
ORDER BY Name, OWN, DateReported
I received all of the 12/31/2007 records and all of the 01/02/2008 records. I had hoped the clause " having count(OWN) >1" would eliminate some if not all of the unnecessary records. But it did not.
I then tried removing the count clause, and inserting into #Retrieval and doing this:
--Get data from #Retrieval table for past two days only (not previous day compared to the day before it)
SELECT DateReported, OWN, Name, EmployeeStatus, FullorPartTime,
GradeLevel, Title, JobEntryDate, OriginalHireDate, CurrentHireDate,
TermDate1, LOAStartDate1, LOAReturnDate1, DepartmentDesc, DepartmentEntryDate,
MgrOWN, ManagersName, OTC, TypeCode, JobCode
from #Retrieval
group by DateReported, OWN, Name, EmployeeStatus, FullorPartTime,
GradeLevel, Title, JobEntryDate, OriginalHireDate, CurrentHireDate,
TermDate1, LOAStartDate1, LOAReturnDate1, DepartmentDesc, DepartmentEntryDate,
MgrOWN, ManagersName, OTC, TypeCode, JobCode
having count(OWN) > 1
ORDER BY Name, OWN, DateReported
At which time I receive no data. (Corrected to say no data.)
Am I going about this all wrong? Suggestions? Advice?
Thanks for reading.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

It would help if you could provide some test data, the DDL for the table(s), and the expected results. Just looking at the code, its hare to tell what the problem could be.

😎

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)