Need syntax help please

Question

Post reply

Need syntax help please

Ron-279530

Ten Centuries

Points: 1381
More actions
February 8, 2006 at 4:05 pm

#89415

I need to modify the query below:
SELECT DISTINCT PhoneNum
FROM History
WHERE ProjectID IN (945, 1172)
AND CallDateTime >(Select getdate() -1 Interval) AND
AttResult = 5
so that the results are for the last 7 days where a PhoneNum appears in the History table twice in the last 15 days. The last seven days part is easy, I just change the 1 before the interval to 7. But how would the syntax look that would capture any records that had the same phone number from the History table for just those projects over the last 15 days?
Thanks in advance

Viewing 11 posts - 1 through 10 (of 10 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

SELECT H1.PhoneNum

FROM History H1

INNER JOIN History H2 on H1.ProjectID = H2.ProjectID and H1.PhoneNum = H2.PhoneNum

WHERE H1.ProjectID IN (945, 1172)

AND CallDateTime > getdate() -7 AND AttResult = 5

GROUP BY H1.PhoneNum

HAVING COUNT(H2.PhoneNum) > 1

_____________
Code for TallyGenerator

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 2

Sergiy, I have changed your query a little

SELECT H1.PhoneNum

FROM History H1

INNER JOIN History H2 on H1.ProjectID = H2.ProjectID and H1.PhoneNum = H2.PhoneNum

WHERE H1.ProjectID IN (945, 1172)

AND H1.CallDateTime > getdate() -7 AND H1.AttResult = 5

AND H2.CallDateTime > getdate() -15 AND H2.AttResult = 5

GROUP BY H1.PhoneNum

HAVING COUNT(H2.PhoneNum) > 1

Not sure if you need "H1.ProjectID = H2.ProjectID"... Maybe "H2.ProjectID IN (945, 1172)" instead...

Ron-279530 Ten Centuries Points: 1381 More actions · Answer 3

Ron-279530

Ten Centuries

Points: 1381

February 9, 2006 at 7:20 am

#619764

Thanks for the help, I'll give this a try.

Ron-279530 Ten Centuries Points: 1381 More actions · Answer 4

One more question if I may? If I needed other data, say a date/time stamp and a some resolution codes, from the same table returned with the query, what would be the best way to structure that? I tried putting those fields on the end of the select statement but got a error msg about bad syntax near my FROM statement. Thanks again!!

SQLBill SSC Guru Points: 51440 More actions · Answer 5

Post the new script you created and we may be able to help you.

-SQLBill

Veronica Brown Old Hand Points: 318 More actions · Answer 6

Hello,

If you use the select statement that Jesper posted, you'd have to put the additional items in the select portion (as you mentioned) but I think you have to have them in the "Group by" clause as well.

Hope that helps.

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 7

Exactly - something like

SELECT H1.PhoneNum, H1.NewCol

FROM History H1

INNER JOIN History H2 on H1.ProjectID = H2.ProjectID and H1.PhoneNum = H2.PhoneNum

WHERE H1.ProjectID IN (945, 1172)

AND H1.CallDateTime > getdate() -7 AND H1.AttResult = 5

AND H2.CallDateTime > getdate() -15 AND H2.AttResult = 5

GROUP BY H1.PhoneNum, H1.NewCol

HAVING COUNT(H2.PhoneNum) > 1

Ron-279530 Ten Centuries Points: 1381 More actions · Answer 8

Ron-279530

Ten Centuries

Points: 1381

February 9, 2006 at 8:40 am

#619814

thanks again

SQLBill SSC Guru Points: 51440 More actions · Answer 9

But that shouldn't give the error you got...if that is indeed the problem, you should have gotten an error stating something similar to "items in the select must be in the group by"

-SQLBill

Ron-279530 Ten Centuries Points: 1381 More actions · Answer 10

I think I was having two problems at the same time, first was the lack of having the extra columns in both the select line and the group by line (thanks Jesper) but I also had an stray comma at the end of my select statement which generated the error regarding the FROM statement.

Thanks all, I am always amazed at the talent and speed of the responses on this site with my trivial T-SQL problems.

Now I'm off to get a DTS package to automate this. Does anyone have experience in putting this into a spreadsheet that has formatting setup already? The user wants the rows to alternate white and grey for readability and I'm reading J. Sack's article but haven't quite figured out how to dump results into a spreadsheet that has formatting. I know this is the wrong forum for this question but thought I would ask and roll the dice.