HELP on SQL - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

HELP on SQL

Author

Topic

dreamland
Starting Member

1 Post

Posted - 2006-09-19 : 12:58:55

Hello all,
01: I want to retrive records from two different databases. Is that possible? if yes could you let me know how I can do it?

02: I want to have a query that returns like this

Documnet type Total in last week Total
xxxxx xx xx

so I wrote

select dt.DocuType, count(d.DocId) AS 'Total in last week' FROM document d left JOIN docutypeid dt on d.DocTypeId = dt.DocTypeId
GROUP BY dt.DocuType,uploadDate HAVING uploadDate>=(DATEDIFF(dd,d.UploadDate,getDate()));

This displays only "Documnet type","Total in last week"
but even if I modifed this query to display the total it show but it is the same as "Total in last week" because of the "Having" clause.
Please help me on getting the right query for this.
Thanks

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-19 : 15:30:34

1: You can query across databases by prefixing the table with the database name (and owner/schema) like:

SELECT a.field1, b.field2
FROM database1.dbo.table1 a
INNER JOIN database2.dbo.table2 b
ON a.ID = b.ID

2: what are you trying to achive by DATEDIFF(dd,d.UploadDate,getDate())) ?

-- The Heisenberg uncertainty principle also applies when debugging

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-19 : 17:17:04

I think you're looking for something like this. Of course you have to define when last week began for you (this assumes a week is Sunday - Saturday).

DECLARE @BeginLastWeek datetime
SET @BeginLastWeek = DateAdd(dd, -7, DateDiff(dd, 0, DateAdd(dd, 1 - DatePart(dw, GetDate()), GetDate())))

SELECT
DT.DocuType
, COUNT(D.tid) AS 'Total In Last Week'
FROM
#doc D
LEFT OUTER JOIN
#doctype DT ON D.DocuTypeID = DT.DocuTypeID
WHERE
UploadDate >= @BeginLastWeek
AND
UploadDate < DATEADD(dd, 7, @BeginLastWeek)
GROUP BY
DT.DocuType

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources