|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Counting RecordsI have a table (transactionsTable) and I have several records from July 1st
till today that I would like to count I prepared the following SQL statement but it does not work, please advise. Thanks Select count (*) from TransactionsTable where [date] = '2005/07/01' and '2005/07/14' Hi,
Use Between clause Select count (*) from TransactionsTable where [date] between '2005/07/01' and '2005/07/14' Thanks Hari SQL Server MVP Show quote "WhiteJul" <white***@hotmail.com> wrote in message news:%238PLnXDiFHA.1416@TK2MSFTNGP09.phx.gbl... >I have a table (transactionsTable) and I have several records from July 1st >till today that I would like to count > I prepared the following SQL statement but it does not work, please > advise. Thanks > > Select count (*) > from TransactionsTable > where [date] = '2005/07/01' and '2005/07/14' > Hari: it worked, now I have the same table in three separated SQL servers,
how can Iquery the three servers with only one query to see and compare their respective counts. I tried: Select count (*) from sqlServerONE.dbo.TransactionsTable where [date] between '2005/07/01' and '2005/07/14' Select count (*) from sqlServerTWO.dbo.TransactionsTable where [date] between '2005/07/01' and '2005/07/14' Select count (*) from sqlServerTHREE.dbo.TransactionsTable where [date] between '2005/07/01' and '2005/07/14' The three servers have the same password for the SA account. Thanks for any assistance! Show quote "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message news:ewM$LZDiFHA.3316@TK2MSFTNGP14.phx.gbl... > Hi, > > Use Between clause > > Select count (*) from TransactionsTable where [date] between '2005/07/01' > and '2005/07/14' > > Thanks > Hari > SQL Server MVP > > "WhiteJul" <white***@hotmail.com> wrote in message > news:%238PLnXDiFHA.1416@TK2MSFTNGP09.phx.gbl... >>I have a table (transactionsTable) and I have several records from July >>1st till today that I would like to count >> I prepared the following SQL statement but it does not work, please >> advise. Thanks >> >> Select count (*) >> from TransactionsTable >> where [date] = '2005/07/01' and '2005/07/14' >> > > Hi
I assume that you have created linked servers to do this? You will need four part names to do this. if you are running this on server 1 then Select count (*) AS [Number]. 'Server One' AS [Server] from dbo.TransactionsTable where [date] between '2005/07/01' and '2005/07/14' UNION ALL Select count (*), 'Server Two' from sqlServerTWO.db2..TransactionsTable where [date] between '2005/07/01' and '2005/07/14' UNION ALL Select count (*), 'Server Three' from sqlServerTHREE.db3..TransactionsTable where [date] between '2005/07/01' and '2005/07/14' John Show quote > "WhiteJul" wrote: > Hari: it worked, now I have the same table in three separated SQL servers, > how can Iquery the three servers with only one query to see and compare > their respective counts. > > I tried: > > Select count (*) from sqlServerONE.dbo.TransactionsTable > where [date] between '2005/07/01' and '2005/07/14' > > Select count (*) from sqlServerTWO.dbo.TransactionsTable > where [date] between '2005/07/01' and '2005/07/14' > > Select count (*) from sqlServerTHREE.dbo.TransactionsTable > where [date] between '2005/07/01' and '2005/07/14' > > The three servers have the same password for the SA account. > > Thanks for any assistance! > > "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message > news:ewM$LZDiFHA.3316@TK2MSFTNGP14.phx.gbl... > > Hi, > > > > Use Between clause > > > > Select count (*) from TransactionsTable where [date] between '2005/07/01' > > and '2005/07/14' > > > > Thanks > > Hari > > SQL Server MVP > > > > "WhiteJul" <white***@hotmail.com> wrote in message > > news:%238PLnXDiFHA.1416@TK2MSFTNGP09.phx.gbl... > >>I have a table (transactionsTable) and I have several records from July > >>1st till today that I would like to count > >> I prepared the following SQL statement but it does not work, please > >> advise. Thanks > >> > >> Select count (*) > >> from TransactionsTable > >> where [date] = '2005/07/01' and '2005/07/14' > >> > > > > > > > No, I do not have linked servers, how can accomplish this?
I also tried to run this query and got the error message below. Thanks for any help: Select count (*) AS [Number]. 'SqlServerOne' AS [Server] from dbo.TransactionsTable where [date] between '2005/07/10' and '2005/07/14' UNION ALL Select count (*), 'SqlServerTwo' from SqlServerTwo.dbo..TransactionsTable where [date] between '2005/07/10' and '2005/07/14' UNION ALL Select count (*), 'SqlServerThree' from SqlServerThree.dbo..TransactionsTable where [date] between '2005/07/10' and '2005/07/14' Error: ==== Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '.'. Show quote "John Bell" wrote: > Hi > > I assume that you have created linked servers to do this? You will need four > part names to do this. if you are running this on server 1 then > > Select count (*) AS [Number]. 'Server One' AS [Server] > from dbo.TransactionsTable > where [date] between '2005/07/01' and '2005/07/14' > UNION ALL > Select count (*), 'Server Two' > from sqlServerTWO.db2..TransactionsTable > where [date] between '2005/07/01' and '2005/07/14' > UNION ALL > Select count (*), 'Server Three' > from sqlServerTHREE.db3..TransactionsTable > where [date] between '2005/07/01' and '2005/07/14' > > John > > > > "WhiteJul" wrote: > > > Hari: it worked, now I have the same table in three separated SQL servers, > > how can Iquery the three servers with only one query to see and compare > > their respective counts. > > > > I tried: > > > > Select count (*) from sqlServerONE.dbo.TransactionsTable > > where [date] between '2005/07/01' and '2005/07/14' > > > > Select count (*) from sqlServerTWO.dbo.TransactionsTable > > where [date] between '2005/07/01' and '2005/07/14' > > > > Select count (*) from sqlServerTHREE.dbo.TransactionsTable > > where [date] between '2005/07/01' and '2005/07/14' > > > > The three servers have the same password for the SA account. > > > > Thanks for any assistance! > > > > "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message > > news:ewM$LZDiFHA.3316@TK2MSFTNGP14.phx.gbl... > > > Hi, > > > > > > Use Between clause > > > > > > Select count (*) from TransactionsTable where [date] between '2005/07/01' > > > and '2005/07/14' > > > > > > Thanks > > > Hari > > > SQL Server MVP > > > > > > "WhiteJul" <white***@hotmail.com> wrote in message > > > news:%238PLnXDiFHA.1416@TK2MSFTNGP09.phx.gbl... > > >>I have a table (transactionsTable) and I have several records from July > > >>1st till today that I would like to count > > >> I prepared the following SQL statement but it does not work, please > > >> advise. Thanks > > >> > > >> Select count (*) > > >> from TransactionsTable > > >> where [date] = '2005/07/01' and '2005/07/14' > > >> > > > > > > > > > > > > Hi
There is a full-stop instead of a comma after [Number]. To set one up follow the instructions in the topic "How to set up a linked server (Enterprise Manager)" or if you want to use T-SQL and Query Analyser check out the topic "Adding a Linked Server" and "sp_addlinkedserver" also look at "Establishing Security for Linked Servers". All of these topics are in Books Online. John Show quote "WhiteJul" wrote: > No, I do not have linked servers, how can accomplish this? > I also tried to run this query and got the error message below. Thanks for > any help: > > Select count (*) AS [Number]. 'SqlServerOne' AS [Server] > from dbo.TransactionsTable > where [date] between '2005/07/10' and '2005/07/14' > UNION ALL > Select count (*), 'SqlServerTwo' > from SqlServerTwo.dbo..TransactionsTable > where [date] between '2005/07/10' and '2005/07/14' > UNION ALL > Select count (*), 'SqlServerThree' > from SqlServerThree.dbo..TransactionsTable > where [date] between '2005/07/10' and '2005/07/14' > > Error: > ==== > Server: Msg 170, Level 15, State 1, Line 1 > Line 1: Incorrect syntax near '.'. > > "John Bell" wrote: > > > Hi > > > > I assume that you have created linked servers to do this? You will need four > > part names to do this. if you are running this on server 1 then > > > > Select count (*) AS [Number]. 'Server One' AS [Server] > > from dbo.TransactionsTable > > where [date] between '2005/07/01' and '2005/07/14' > > UNION ALL > > Select count (*), 'Server Two' > > from sqlServerTWO.db2..TransactionsTable > > where [date] between '2005/07/01' and '2005/07/14' > > UNION ALL > > Select count (*), 'Server Three' > > from sqlServerTHREE.db3..TransactionsTable > > where [date] between '2005/07/01' and '2005/07/14' > > > > John > > > > > > > "WhiteJul" wrote: > > > > > Hari: it worked, now I have the same table in three separated SQL servers, > > > how can Iquery the three servers with only one query to see and compare > > > their respective counts. > > > > > > I tried: > > > > > > Select count (*) from sqlServerONE.dbo.TransactionsTable > > > where [date] between '2005/07/01' and '2005/07/14' > > > > > > Select count (*) from sqlServerTWO.dbo.TransactionsTable > > > where [date] between '2005/07/01' and '2005/07/14' > > > > > > Select count (*) from sqlServerTHREE.dbo.TransactionsTable > > > where [date] between '2005/07/01' and '2005/07/14' > > > > > > The three servers have the same password for the SA account. > > > > > > Thanks for any assistance! > > > > > > "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message > > > news:ewM$LZDiFHA.3316@TK2MSFTNGP14.phx.gbl... > > > > Hi, > > > > > > > > Use Between clause > > > > > > > > Select count (*) from TransactionsTable where [date] between '2005/07/01' > > > > and '2005/07/14' > > > > > > > > Thanks > > > > Hari > > > > SQL Server MVP > > > > > > > > "WhiteJul" <white***@hotmail.com> wrote in message > > > > news:%238PLnXDiFHA.1416@TK2MSFTNGP09.phx.gbl... > > > >>I have a table (transactionsTable) and I have several records from July > > > >>1st till today that I would like to count > > > >> I prepared the following SQL statement but it does not work, please > > > >> advise. Thanks > > > >> > > > >> Select count (*) > > > >> from TransactionsTable > > > >> where [date] = '2005/07/01' and '2005/07/14' > > > >> > > > > > > > > > > > > > > > > > I did work. I changed the period (.) by a comma and it run fine.
I also managed to created the linked servers. Thanks John. Show quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:740AED82-81DD-4EC2-B74F-B2321FBDF135@microsoft.com... > Hi > > There is a full-stop instead of a comma after [Number]. > > To set one up follow the instructions in the topic "How to set up a linked > server (Enterprise Manager)" or if you want to use T-SQL and Query > Analyser > check out > the topic "Adding a Linked Server" and "sp_addlinkedserver" also look at > "Establishing Security for Linked Servers". All of these topics are in > Books > Online. > > > John > > "WhiteJul" wrote: > >> No, I do not have linked servers, how can accomplish this? >> I also tried to run this query and got the error message below. Thanks >> for >> any help: >> >> Select count (*) AS [Number]. 'SqlServerOne' AS [Server] >> from dbo.TransactionsTable >> where [date] between '2005/07/10' and '2005/07/14' >> UNION ALL >> Select count (*), 'SqlServerTwo' >> from SqlServerTwo.dbo..TransactionsTable >> where [date] between '2005/07/10' and '2005/07/14' >> UNION ALL >> Select count (*), 'SqlServerThree' >> from SqlServerThree.dbo..TransactionsTable >> where [date] between '2005/07/10' and '2005/07/14' >> >> Error: >> ==== >> Server: Msg 170, Level 15, State 1, Line 1 >> Line 1: Incorrect syntax near '.'. >> >> "John Bell" wrote: >> >> > Hi >> > >> > I assume that you have created linked servers to do this? You will need >> > four >> > part names to do this. if you are running this on server 1 then >> > >> > Select count (*) AS [Number]. 'Server One' AS [Server] >> > from dbo.TransactionsTable >> > where [date] between '2005/07/01' and '2005/07/14' >> > UNION ALL >> > Select count (*), 'Server Two' >> > from sqlServerTWO.db2..TransactionsTable >> > where [date] between '2005/07/01' and '2005/07/14' >> > UNION ALL >> > Select count (*), 'Server Three' >> > from sqlServerTHREE.db3..TransactionsTable >> > where [date] between '2005/07/01' and '2005/07/14' >> > >> > John >> > > >> > >> > "WhiteJul" wrote: >> > >> > > Hari: it worked, now I have the same table in three separated SQL >> > > servers, >> > > how can Iquery the three servers with only one query to see and >> > > compare >> > > their respective counts. >> > > >> > > I tried: >> > > >> > > Select count (*) from sqlServerONE.dbo.TransactionsTable >> > > where [date] between '2005/07/01' and '2005/07/14' >> > > >> > > Select count (*) from sqlServerTWO.dbo.TransactionsTable >> > > where [date] between '2005/07/01' and '2005/07/14' >> > > >> > > Select count (*) from sqlServerTHREE.dbo.TransactionsTable >> > > where [date] between '2005/07/01' and '2005/07/14' >> > > >> > > The three servers have the same password for the SA account. >> > > >> > > Thanks for any assistance! >> > > >> > > "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message >> > > news:ewM$LZDiFHA.3316@TK2MSFTNGP14.phx.gbl... >> > > > Hi, >> > > > >> > > > Use Between clause >> > > > >> > > > Select count (*) from TransactionsTable where [date] between >> > > > '2005/07/01' >> > > > and '2005/07/14' >> > > > >> > > > Thanks >> > > > Hari >> > > > SQL Server MVP >> > > > >> > > > "WhiteJul" <white***@hotmail.com> wrote in message >> > > > news:%238PLnXDiFHA.1416@TK2MSFTNGP09.phx.gbl... >> > > >>I have a table (transactionsTable) and I have several records from >> > > >>July >> > > >>1st till today that I would like to count >> > > >> I prepared the following SQL statement but it does not work, >> > > >> please >> > > >> advise. Thanks >> > > >> >> > > >> Select count (*) >> > > >> from TransactionsTable >> > > >> where [date] = '2005/07/01' and '2005/07/14' >> > > >> >> > > > >> > > > >> > > >> > > >> > >  |
|||||||||||||||||||||||
Other interesting topics