CREATE DATABASE failed: following

Showing posts with label following. Show all posts

Thursday, March 29, 2012

create an incremental counter in the stored procedure

Hello, I have a following SP
I want to add an extra field "ranking" that just increments the row number.
Another feature would be: if several users have an equal totalvalue, they
should have an equal ranking number. the rankings following users would have
to be adjusted as well. thanks

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[Rankings]
@.iErrorCode int OUTPUT
AS

SELECT top 30
###COUNTER##,
[user],
[totalvalue], [cash], [stocksvalue]

FROM [dbo].[users]
ORDER BY totalvalue DESC

SELECT @.iErrorCode=@.@.ERROR

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOThere is more than one way to rank a set with tied values.

CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2) NOT
NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)

INSERT INTO Users VALUES (101,1010,100,99)
INSERT INTO Users VALUES (102,2020,100,99)
INSERT INTO Users VALUES (103,3030,100,99)
INSERT INTO Users VALUES (104,3030,100,99)
INSERT INTO Users VALUES (105,1002,100,99)
INSERT INTO Users VALUES (106,1002,100,99)
INSERT INTO Users VALUES (107,1002,100,99)
INSERT INTO Users VALUES (108,1002,100,99)
INSERT INTO Users VALUES (109,1000,100,99)

See if this gives the result you expect:

SELECT COUNT(U2.totalvalue)+1 AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue < U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
3 102 2020.00 100.00 99.00
4 101 1010.00 100.00 99.00
5 105 1002.00 100.00 99.00
5 106 1002.00 100.00 99.00
5 107 1002.00 100.00 99.00
5 108 1002.00 100.00 99.00
9 109 1000.00 100.00 99.00

(9 row(s) affected)

Or maybe this:

SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue <= U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
2 102 2020.00 100.00 99.00
3 101 1010.00 100.00 99.00
4 105 1002.00 100.00 99.00
4 106 1002.00 100.00 99.00
4 107 1002.00 100.00 99.00
4 108 1002.00 100.00 99.00
5 109 1000.00 100.00 99.00

(9 row(s) affected)

--
David Portas
SQL Server MVP
--|||thank you! it works fine.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1pmdnW4hl-GFFt3dRVn-uA@.giganews.com...
> There is more than one way to rank a set with tied values.
> CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2)
NOT
> NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)
> INSERT INTO Users VALUES (101,1010,100,99)
> INSERT INTO Users VALUES (102,2020,100,99)
> INSERT INTO Users VALUES (103,3030,100,99)
> INSERT INTO Users VALUES (104,3030,100,99)
> INSERT INTO Users VALUES (105,1002,100,99)
> INSERT INTO Users VALUES (106,1002,100,99)
> INSERT INTO Users VALUES (107,1002,100,99)
> INSERT INTO Users VALUES (108,1002,100,99)
> INSERT INTO Users VALUES (109,1000,100,99)
> See if this gives the result you expect:
> SELECT COUNT(U2.totalvalue)+1 AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue < U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 3 102 2020.00 100.00 99.00
> 4 101 1010.00 100.00 99.00
> 5 105 1002.00 100.00 99.00
> 5 106 1002.00 100.00 99.00
> 5 107 1002.00 100.00 99.00
> 5 108 1002.00 100.00 99.00
> 9 109 1000.00 100.00 99.00
> (9 row(s) affected)
> Or maybe this:
> SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue <= U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 2 102 2020.00 100.00 99.00
> 3 101 1010.00 100.00 99.00
> 4 105 1002.00 100.00 99.00
> 4 106 1002.00 100.00 99.00
> 4 107 1002.00 100.00 99.00
> 4 108 1002.00 100.00 99.00
> 5 109 1000.00 100.00 99.00
> (9 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --

Tuesday, March 27, 2012

Create a time series

Given the following table information:

HOSTNAME DATETIME
WEBNYC001 2005-06-15 10:30AM
WEBNYC001 2005-06-15 10:31AM
WEBNYC001 2005-06-15 10:31AM
WEBNYC001 2005-06-15 10:34AM
WEBNYC001 2005-06-15 10:35AM
WEBNYC001 2005-06-15 10:35AM
WEBNYC002 2005-06-15 10:30AM
WEBNYC002 2005-06-15 10:30AM
WEBNYC002 2005-06-15 10:33AM
WEBNYC002 2005-06-15 10:35AM
WEBNYC002 2005-06-15 10:35AM
WEBNYC002 2005-06-15 10:35AM

How can I easily return the following results:
HOSTNAME DATETIME COUNT
WEBNYC001 2005-06-15 10:30AM 1
WEBNYC001 2005-06-15 10:31AM 2
WEBNYC001 2005-06-15 10:32AM 0
WEBNYC001 2005-06-15 10:33AM 0
WEBNYC001 2005-06-15 10:34AM 1
WEBNYC001 2005-06-15 10:35AM 2
WEBNYC002 2005-06-15 10:30AM 2
WEBNYC002 2005-06-15 10:31AM 0
WEBNYC002 2005-06-15 10:32AM 0
WEBNYC002 2005-06-15 10:33AM 1
WEBNYC002 2005-06-15 10:34AM 0
WEBNYC002 2005-06-15 10:35AM 3

Thanks!"joshsackett" <joshsackett@.gmail.com> wrote in message
news:1118852141.669360.188390@.g44g2000cwa.googlegr oups.com...
> Given the following table information:
> HOSTNAME DATETIME
> WEBNYC001 2005-06-15 10:30AM
> WEBNYC001 2005-06-15 10:31AM
> WEBNYC001 2005-06-15 10:31AM
> WEBNYC001 2005-06-15 10:34AM
> WEBNYC001 2005-06-15 10:35AM
> WEBNYC001 2005-06-15 10:35AM
> WEBNYC002 2005-06-15 10:30AM
> WEBNYC002 2005-06-15 10:30AM
> WEBNYC002 2005-06-15 10:33AM
> WEBNYC002 2005-06-15 10:35AM
> WEBNYC002 2005-06-15 10:35AM
> WEBNYC002 2005-06-15 10:35AM
> How can I easily return the following results:
> HOSTNAME DATETIME COUNT
> WEBNYC001 2005-06-15 10:30AM 1
> WEBNYC001 2005-06-15 10:31AM 2
> WEBNYC001 2005-06-15 10:32AM 0
> WEBNYC001 2005-06-15 10:33AM 0
> WEBNYC001 2005-06-15 10:34AM 1
> WEBNYC001 2005-06-15 10:35AM 2
> WEBNYC002 2005-06-15 10:30AM 2
> WEBNYC002 2005-06-15 10:31AM 0
> WEBNYC002 2005-06-15 10:32AM 0
> WEBNYC002 2005-06-15 10:33AM 1
> WEBNYC002 2005-06-15 10:34AM 0
> WEBNYC002 2005-06-15 10:35AM 3
> Thanks!

Here's one possible solution. In general, queries involving ranges of times,
dates or numbers are often easier if you have an auxiliary table to join on.
If you don't want to implement such a table, you could create a table-valued
function which returns all required values between two given datetimes
instead - that would avoid having a potentially very large table in the
database.

Simon

create table dbo.Data (
host char(9) not null,
dt datetime not null
/* need a primary key here */
)
go
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:30AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:31AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:31AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:34AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:30AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:30AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:33AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:35AM'
go

create table dbo.Times (
tm datetime not null primary key
)
go

insert into dbo.Times select '2005-06-15 10:30AM'
insert into dbo.Times select '2005-06-15 10:31AM'
insert into dbo.Times select '2005-06-15 10:32AM'
insert into dbo.Times select '2005-06-15 10:33AM'
insert into dbo.Times select '2005-06-15 10:34AM'
insert into dbo.Times select '2005-06-15 10:35AM'
go

select
h.host,
t.tm,
coalesce(a.cnt, 0)
from
dbo.Times t
cross join (select distinct host from dbo.Data) h
left outer join
(
select
host,
dt,
count(*) as 'cnt'
from
dbo.Data
group by
host,
dt
) a
on t.tm = a.dt
and h.host = a.host
order by
h.host,
t.tm
go

drop table dbo.Data
drop table dbo.Times
go|||That kicked ass.

Exactly what I needed... thanks!sql

Create a Testing Database out of existing DB

Hello All,

I was wondering if anybody can help me with the following question:

I'm working on the application where the Database, it's table (2) and several stored procedures are involved. The database is SQL Server 2000. It's also very old and involves a lot of operations, stored proc and so on. I just need to re-write a piece of the app which is using existing stored proc. Most of them are DELETE, INSERT and so on. I don't want to work with real stage DB and need to make a copy of the Database to my Dev box. So I tried:

* Right click, All Tasks, Export Data into the newly created database on my dev box.

That doesn't work, every time I try doing it, it fails somewhere in the middle of the process. I'm thinking it happens because of complexity of the database. I tried several options there already. Still nothing. I need the whole databse to be copied because I'm not sure which stored proc the app is using so I need them all, and tables too. Is there another way of doing this?

Thank you,

Tatyana

If you make a backup of the database (or detach it) you can restore it (or re-attach it) under a different name and therefore create multiple instances of it.

|||

Mark,

Thank you very much! Your suggestion worked!

Tatyana

sql

Create a table using a Query

Hello,
With Oracle, we can create a table as following :
CREATE TABLE tab_name
AS SELECT
col1, col2...
from tab1, tab2
where ...
With MSSQL, can we do the same thing ?The SELECT INTO statement creates a new table and populates it with the result set of the SELECT. The structure of the new table is defined by the attributes of the expressions in the select list.

I suggest you to refer to books online for more information.|||Thanks a lot|||No mention, keep in touch with BOL:)

create a table prior to bcping data

I need to do the following;
1. bcp out data from our reporting server
2. Move bcp file to our archival server
Sounds pretty straight forward.. but of course there is
more...
Since we are using this data for archival purposes, we
just want to store the bcp file. My concern is that there
are many schema changes to the source table and I wanted
to make sure that I had the correct schema needed to bcp
that file back into a table, say three years from now.
Is there a option in bcp or bulk copy that would create
the schema for you? Or can this be done via DTS?
Thanks in advance
Susan
Any help would be greatly appreciated.
Thanks
SusanHi,
You can "Generate a script " of all objects and keep it along with the BCP
OUT data.
How to Generate scripts:
1. In Enterprise manager
2. Connect to the SQL server
3. Select the database you need to generate the script
4. Right click All Tasks - "Select the Generate SQL Script" option
5. Click Show all
6. Go to Options Tab - in the Table options select Script INdexes, Triggers
and Primary keys.../
Click the save button and save the file to your hard disk in safe place.
Once you require create a ne database and Execute this script
and BCP IN the the data back.
Thanks
Hari
MCDBA
"Susan" <anonymous@.discussions.microsoft.com> wrote in message
news:1a40701c41db3$13400250$a501280a@.phx.gbl...
> I need to do the following;
> 1. bcp out data from our reporting server
> 2. Move bcp file to our archival server
> Sounds pretty straight forward.. but of course there is
> more...
> Since we are using this data for archival purposes, we
> just want to store the bcp file. My concern is that there
> are many schema changes to the source table and I wanted
> to make sure that I had the correct schema needed to bcp
> that file back into a table, say three years from now.
> Is there a option in bcp or bulk copy that would create
> the schema for you? Or can this be done via DTS?
> Thanks in advance
> Susan
>
>
> Any help would be greatly appreciated.
> Thanks
> Susan
>|||I've found a solution to my issue and wanted to let you
all know!!
I had also posted my question on the SQL Server Central
site and Mr. Mortensen was nice enough to provide a GENIUS
solution. He has a proc that will
1. Generate schema of the table in question
2. Update stats on table prior to BCPing
3. BCP's out the data into a readable text file
4. Verify row counts from the BCP and the actual table
(VERY NICE)
5. He also has a bat file that will run the schema and bcp
the data into the DB/table of your choice.
It was EXACTLY what I need and I want to thank him for his
help.
BTW - Why did Microsoft get rid of the backup 'table'
option? I used it quite a bit in 6.5 and there are MANY
time you only need to save one table and not the whole
database. I know there is dts and bcp, but nothing
compares to backing up and restoring a table. Other DBMS
provide this.. Just wondering.
Thanks
Susan
Here's the link in case your curious.
http://www.sqlservercentral.com/forums/shwmessage.aspx?
forumid=8&messageid=111276
>--Original Message--
>I need to do the following;
>1. bcp out data from our reporting server
>2. Move bcp file to our archival server
>Sounds pretty straight forward.. but of course there is
>more...
>Since we are using this data for archival purposes, we
>just want to store the bcp file. My concern is that there
>are many schema changes to the source table and I wanted
>to make sure that I had the correct schema needed to bcp
>that file back into a table, say three years from now.
>Is there a option in bcp or bulk copy that would create
>the schema for you? Or can this be done via DTS?
>Thanks in advance
>Susan
>
>
>Any help would be greatly appreciated.
>Thanks
>Susan
>.
>

Create a table and then using it as OLE DB Destination component

I want to do the following in a package:

Create a table at the beginning of a package (using a ExecuteSQLTask component) and then use the created table as a OLE DB destination component, later on the package.

Is this possible in SSIS?

The problem I run into is that I have to point the OLE DB destination component to a table and set up mappings, however as the table does not exist until the package is running, it does not seem to be possible.

I've looked at:

http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

Which is slightly similar to what I want, but the table I create would not be a temp tables, and I need to set up mappings and I don't see how this is possible.

Thanks

To design and build the package you will need to create the table. Once that has been completed, the table can be dropped, and you can rely on the table created in the Exec SQL Task for the actual running of the package. You will however need to change the DelayValidation property to be True, on the Data Flow Task. This will prevent the task (and components it contains) from being validated, and failing due to the missing table. The Data Flow Task will still be validated, immediately prior to execution, but by that stage the Exec SQL Task will have completed, so the table will then exist.|||

Ok, thanks for the answer but thats whats was mentioned on the cochango link. The difference here is that I do not wish to drop the table after it has been created, I want to create it, use it and keep it...

I'm currently creating packages in C#, however the problem occurs when I make the call:

// Establish a connection

componentInstance.AcquireConnections(null);

// Initialize the metadata

componentInstance.ReinitializeMetaData();

The component is question here is a OLE DB Destination component, it throws an exception because the table does not exist in the database whilst the package is being created.

Now, I know I can set DelayValidation on the Task which contains the destination component

It would be nice if I could:

1. set DelayValidation = true on the Task which contains this component

2. Skip the componentInstance.ReinitializeMetaData() method

3. Set up mappings between the input collection and external meta columns (each of which I would have to create)

However, if I skip the method componentInstance.ReinitializeMetaData I receive there is no inputCollection available to map to, so when you call:

// Get the destination's default input and virtual input.

IDTSInput90 input = component.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

there is are no columns available....

Therefore, my question is, can this be done in a C# environment, or does the same rule as creating this in the Designer apply, whereby I would have to have the table available in the database on the creation of the package in order to create the package?

Sunday, March 25, 2012

Create A New UserName

I am using the following ConnectionString in an ASP file so that the ASP
application can communicate with SQL Server 7.0:
CONNECTIONSTRING="Provider=SQLOLEDB;User ID=SoGuest;Password=SoGuest;
Persist Security
Info=True;Initial Catalog=ForestDB;Data Source=MyServer"
In order to ensure that my ASP page interacts with the database ForestDB, I
have to create a new user named SoGuest whose password will be SoGuest. To
do this, I went to 'Users' under the ForestDB tree, right-clicked & then
clicked 'New Database User'. A dialog box opened up. Next when I clicked the
drop-down list for assigning a login name, I find that there's not a single
login name in the drop-down list as a result of which I am not being allowed
to proceed forward to create the new user SoGuest. How do I create this new
user? Please note that when I try to create a new user in some other DB like
Pubs, Northwind etc. I am given 'BUILTIN\Administrators' as an option in the
drop-down list of the login name. The authentication mode I am using is both
SQL Server & Windows NT (I am working on Windows 2000 Professional).
Thanks,
ArpanIt sounds as though you have not yet created a login. In Enterprise Manager
open up the Security container and right-click Logins. Create a new login
called whatever you want. Assign him to those databases you want him to
access. That will create both your login and database user.
A login allows someone to be verified when they connect to SQL. It is an
entry in the sysxlogins table in the Master db.
A User is someone who already has a login in the sysxlogins table, but
requires access to a specific database. They are placed in the sysusers
table in the particular database they are trying to access.
The Enterprise Manager dialog allows you to add a login and user at the same
time.
HTH
Bob
--
Warning: Do not look into the light sabre whilst switching it on
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003|||You need to create the login in the Security folder first (and you can create the user as well from
that dialog).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Arpan" <arpan_de@.hotmail.com> wrote in message news:%23JLbRLchDHA.2420@.TK2MSFTNGP10.phx.gbl...
> I am using the following ConnectionString in an ASP file so that the ASP
> application can communicate with SQL Server 7.0:
> CONNECTIONSTRING="Provider=SQLOLEDB;User ID=SoGuest;Password=SoGuest;
> Persist Security
> Info=True;Initial Catalog=ForestDB;Data Source=MyServer"
> In order to ensure that my ASP page interacts with the database ForestDB, I
> have to create a new user named SoGuest whose password will be SoGuest. To
> do this, I went to 'Users' under the ForestDB tree, right-clicked & then
> clicked 'New Database User'. A dialog box opened up. Next when I clicked the
> drop-down list for assigning a login name, I find that there's not a single
> login name in the drop-down list as a result of which I am not being allowed
> to proceed forward to create the new user SoGuest. How do I create this new
> user? Please note that when I try to create a new user in some other DB like
> Pubs, Northwind etc. I am given 'BUILTIN\Administrators' as an option in the
> drop-down list of the login name. The authentication mode I am using is both
> SQL Server & Windows NT (I am working on Windows 2000 Professional).
> Thanks,
> Arpan
>|||Thanks, Bob, for your advice. You have hit the nail on the head in saying
that I might not have created a login for the database ForestDB. Thanks to
Tibor as well.
Regards,
Arpan
"Bob Simms" <bob_simms@.hotmail.com> wrote in message
news:4XCdb.408$_d.312@.news-binary.blueyonder.co.uk...
> It sounds as though you have not yet created a login. In Enterprise
Manager
> open up the Security container and right-click Logins. Create a new login
> called whatever you want. Assign him to those databases you want him to
> access. That will create both your login and database user.
> A login allows someone to be verified when they connect to SQL. It is an
> entry in the sysxlogins table in the Master db.
> A User is someone who already has a login in the sysxlogins table, but
> requires access to a specific database. They are placed in the sysusers
> table in the particular database they are trying to access.
> The Enterprise Manager dialog allows you to add a login and user at the
same
> time.
> HTH
> Bob
> --
> Warning: Do not look into the light sabre whilst switching it on
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003
>

create a new column

HI,
I have a table ONE have the following two columns
[DATE] [datetime]
[NUM] [float]
I want to create a new column DATE1=DATE+NUM, date type is datetime.
Could anyone give me a help?
Thanks,
Mike
Hi,
I've tried the following code. But it did not work. Please let me help
me out. Thanks!
create table test
(
value float
)
insert into test
values(17188)
exec sp_help test
declare @.startdate datetime
set @.startdate='1/1/1960'
alter table test
add value1 as value+@.startdate
Mike
Michael wrote:
> HI,
> I have a table ONE have the following two columns
> [DATE] [datetime]
> [NUM] [float]
> I want to create a new column DATE1=DATE+NUM, date type is datetime.
> Could anyone give me a help?
> Thanks,
> Mike
|||Thanks a lot!
mladjo wrote:
> Hi.
> What do you want exactly with adding NUM to DATE? Can you get some example?
> Do you want to add day, month, year, or something else?
> I suppose you want something like this:
> create table ONE
> (
> [DATE] datetime,
> [NUM] float
> )
> INSERT INTO ONE values (getdate(), 2)
> INSERT INTO ONE values (getdate(), 3)
> SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
>
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE])
> SELECT * FROM ONE
>
> If you want that your data in DATE1 column must be stored in your table (not
> just computed every time you working something with that column) use
> PERSISTED column as follows:
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
> Mladen.

create a new column

HI,
I have a table ONE have the following two columns
[DATE] [datetime]
[NUM] [float]
I want to create a new column DATE1=DATE+NUM, date type is datetime.
Could anyone give me a help?
Thanks,
MikeHi.
What do you want exactly with adding NUM to DATE? Can you get some example?
Do you want to add day, month, year, or something else?
I suppose you want something like this:
create table ONE
(
[DATE] datetime,
[NUM] float
)
INSERT INTO ONE values (getdate(), 2)
INSERT INTO ONE values (getdate(), 3)
SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE])
SELECT * FROM ONE
If you want that your data in DATE1 column must be stored in your table (not
just computed every time you working something with that column) use
PERSISTED column as follows:
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
Mladen.|||Hi,
I've tried the following code. But it did not work. Please let me help
me out. Thanks!
create table test
(
value float
)
insert into test
values(17188)
exec sp_help test
declare @.startdate datetime
set @.startdate='1/1/1960'
alter table test
add value1 as value+@.startdate
Mike
Michael wrote:
> HI,
> I have a table ONE have the following two columns
> [DATE] [datetime]
> [NUM] [float]
> I want to create a new column DATE1=DATE+NUM, date type is datetime.
> Could anyone give me a help?
> Thanks,
> Mike|||Thanks a lot!
mladjo wrote:
> Hi.
> What do you want exactly with adding NUM to DATE? Can you get some example?
> Do you want to add day, month, year, or something else?
> I suppose you want something like this:
> create table ONE
> (
> [DATE] datetime,
> [NUM] float
> )
> INSERT INTO ONE values (getdate(), 2)
> INSERT INTO ONE values (getdate(), 3)
> SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
>
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE])
> SELECT * FROM ONE
>
> If you want that your data in DATE1 column must be stored in your table (not
> just computed every time you working something with that column) use
> PERSISTED column as follows:
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
> Mladen.

create a new column

HI,
I have a table ONE have the following two columns
[DATE] [datetime]
[NUM] [float]
I want to create a new column DATE1=DATE+NUM, date type is datetime.
Could anyone give me a help?
Thanks,
MikeHi.
What do you want exactly with adding NUM to DATE? Can you get some example?
Do you want to add day, month, year, or something else?
I suppose you want something like this:
create table ONE
(
[DATE] datetime,
[NUM] float
)
INSERT INTO ONE values (getdate(), 2)
INSERT INTO ONE values (getdate(), 3)
SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE])
SELECT * FROM ONE
If you want that your data in DATE1 column must be stored in your table (not
just computed every time you working something with that column) use
PERSISTED column as follows:
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
Mladen.|||Hi,
I've tried the following code. But it did not work. Please let me help
me out. Thanks!
create table test
(
value float
)
insert into test
values(17188)
exec sp_help test
declare @.startdate datetime
set @.startdate='1/1/1960'
alter table test
add value1 as value+@.startdate
Mike
Michael wrote:
> HI,
> I have a table ONE have the following two columns
> [DATE] [datetime]
> [NUM] [float]
> I want to create a new column DATE1=DATE+NUM, date type is datetime.
> Could anyone give me a help?
> Thanks,
> Mike|||Thanks a lot!
mladjo wrote:
> Hi.
> What do you want exactly with adding NUM to DATE? Can you get some example
?
> Do you want to add day, month, year, or something else?
> I suppose you want something like this:
> create table ONE
> (
> [DATE] datetime,
> [NUM] float
> )
> INSERT INTO ONE values (getdate(), 2)
> INSERT INTO ONE values (getdate(), 3)
> SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
>
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE])
> SELECT * FROM ONE
>
> If you want that your data in DATE1 column must be stored in your table (n
ot
> just computed every time you working something with that column) use
> PERSISTED column as follows:
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
> Mladen.

Thursday, March 22, 2012

Create a custom login.

Hi all,

I need an user to do the following tasks. What kind of login I could provide him and how do I give him rights to do them without providing hin the SA (sysadmin role) ?

Create Login
Create Database
Create Tables ( with Pks, Fks & Indexes)
Create Stored procedures
Create Functions ( table valued / scalar valued)
Insert Master data
Create Jobs
Execute Job for first time
Populate sample

Thanks in advance,

DBAnalyst

Refer to books online about database fixed roles for the steps you have asked such as:

1) db_accessadmin

2,3,4,5,6 & 7) db_owner

Tuesday, March 20, 2012

Create a calculated field that gives me the avg 75 percentile

I'm trying to create a calculated field that gives me the avg 75 percentile.

Right now I get this value by doing the following:

Create data set:

Select top 75 percent <field>

from <table>

Then I create the following calculated field

Avg(Fields!<field>.value,"<data_set_name>")

But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.

Is it possible?

Thanks!

Either you can use the existing dataset and have a filter on it (Edit Dataset -> Filters tab) with Top %

Write a custom function in report code (Report -> Report Properties -> Code) that will take an array of values and returns avg of top 75% from that array. To do that just call the function in your textbox expression as:

Code.AvgFunction(Fields!YourField.Value)

When you just sent Fields!YourField.Value, a whole array of values in that fields will be sent and you can receive it in an object array in your function. Then use any of the most optimized logics to calculate top 75% from that array and then calculate the average using any of the vb functions.

Shyam

|||

Shyam,

Thanks for your posting...

I'm having problems displaying correct data using TOP 75 %

I use the following data set:

select top 75 percent <field>

from <table>

order by <field>

My report is grouped by month Jan 2007, Feb 2007, etc...

If I select one month date range, for instance february the TOP 75 % returns 474 rows and that's correct, however if my date range includes other months, for instance range from 01/01/2007 thtough 02/28/2007 the Top 75 % results increases for the month of february and January. It will return 490 rows for february and more rows for January too.

My report only returns correct data if I select one month range.

What am I doing wrong?

Thanks, Susan

|||

Sorry for a delayed reply. The solution for your problem is simple.

Edit your group in the report and in the Filters tab and under Expression type "=Fields!YourField.Value" and under Operator select Top % and under Value type 75.

Shyam

|||

Shyam,

Thanks for your response.

Adding the filter above didn't make a difference. It's returning the same results.

when I display data for the month of February I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007 --498--231.79

when I run for the month of February and January I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007--512--260.46

Jan 2007--565--309.43

and so fourth, here I added december:

@.Month--Count of records--Accept Top 75%

February-2007-- 508-- 252.29

January-2007-- 560-- 300.59

December-2006-- 488-- 258.64

I expected results for February to remain the same when add months to my view.

Thanks, Susan

|||Remove the TOP 75% clause from your SQL query and have them only in group filters.|||

Shyam,

I had tried that before but the Top% in the Filter tab of the group does't seem to work. Whem I remove the top 75 percent from the sql statement and just leave the Top% =75 filter it totally ignores the filter and it displays the total row count and total average instead.

Well, thanks for the help.... :-)

|||

Top 75% of feb month alone is always going to be different from top 75% of both jan and feb combined. So first of all, you have to remove the top 75% from your sql query. Then make sure you are adding the filter on the group on the same field which you had used in your query. Also, try to convert the field to string using CStr function in the filter expression.

Shyam

|||

Shyam,

Thanks for all your help so far.

I noticed that the filter works based on the row count of the group it's in. My Month group only returns on row, that's why the filter doesn't seem to work, because it's trying to return 75% of one row, which is one row. If I put the filter in the detail row of the report it works, but the problem is that I have to group the detail row before the filter can work, if I group the detail row I won't return all the detail data I need. Is there a way to make the month group filter look at the field of the detail row?

Also, in the report I have to use Bottom% =75 which is the equivalent of the SQL - top 75 percent.

|||

You can use any field from the dataset that is bound to the table for filyering in your group. Using the appropriate field for filtering also makes a big difference in the results obtained.

Shyam

Wednesday, March 7, 2012

CPU messages in log

Running x64 AMD procs and receive the following types of messages in our SQL
log
The time stamp counter of CPU on scheduler id 3 is not synchronized with
other CPUs.
I have seen a lot of postings about this, but have not been lucky to stop
it.
Do we know what the latest is on this and what the actual fix is if any ?
ThanksSee http://support.microsoft.com/kb/931279
Linchi
"Hassan" wrote:
> Running x64 AMD procs and receive the following types of messages in our SQL
> log
> The time stamp counter of CPU on scheduler id 3 is not synchronized with
> other CPUs.
> I have seen a lot of postings about this, but have not been lucky to stop
> it.
> Do we know what the latest is on this and what the actual fix is if any ?
> Thanks
>
>|||See the workaround section of this article:
http://support.microsoft.com/kb/931279
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||we tried those and they still pop up.
Have you guys used those workarounds and made them disappear ?
Its not annoying so far, but was curious to know.
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:B633FD5B-849E-45AD-8F4A-6F1BADEC302A@.microsoft.com...
> See the workaround section of this article:
> http://support.microsoft.com/kb/931279
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com|||I have seen this happen only once on a development machine and disabling CPU
frequency variation in BIOS did the trick.
Plamen Ratchev
http://www.SQLStudio.com|||We never tried to make the messages go away since having the messages is a
minor annoyance. I probably won't try it unless the clock drift starts to
cause real problem for the SQL trace metrics.
Linchi
"Hassan" wrote:
> we tried those and they still pop up.
> Have you guys used those workarounds and made them disappear ?
> Its not annoying so far, but was curious to know.
> "Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
> news:B633FD5B-849E-45AD-8F4A-6F1BADEC302A@.microsoft.com...
> > See the workaround section of this article:
> > http://support.microsoft.com/kb/931279
> >
> > HTH,
> >
> > Plamen Ratchev
> > http://www.SQLStudio.com
>
>

Friday, February 24, 2012

Covert Connection from Access to SQL

Edited by haidar_bilal - Please place your code within< code > and < /code > tag. Thank you.

I've got to convert the following functions so it connects to the SQL server that uses this connection string: ("server=(local);database=BCA;Trusted_Connection=yes")

The code below works fine to an Access db. I know I've got to change all the Ole objects below to SQL objects to connect to SQL server. But try as I might, I'm still not getting them right. Can anyone help by showing me how to change the code? Please just take the code and substitute the SQL connection strings for the Access string. That way even I could understand it. Sigh.


Public Class FunctionsPublic Function GetCommandObject(ByVal strCommand As String, ByVal strPath As String, ByVal ExecuteIt As Boolean) As Data.OleDb.OleDbCommand
Dim objConnection As Data.OleDb.OleDbConnection = GetConnectionObject(strPath)
objConnection.Open()
Dim objCommand As New Data.OleDb.OleDbCommand(strCommand, objConnection)
If ExecuteIt = False Then
Return objCommand
Else
objCommand.ExecuteNonQuery()
End If
objConnection.Close()
End Function
Public Function GetConnectionObject(ByVal strPath As String) As Data.OleDb.OleDbConnection
Dim objConnection As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath)
Return objConnection
objConnection.Close()
End Function
Public Function GetReaderObject(ByVal strCommand As String, ByVal strPath As String) As Data.OleDb.OleDbDataReader
Dim objCommand As Data.OleDb.OleDbCommand = GetCommandObject(strCommand, strPath, False)
Dim objReader As Data.OleDb.OleDbDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return objReader
objReader.Close()
End Function
End Class

www.connectionstrings.com show the connection strings for SQLClient:

Standard Security:

"Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
- or -
"Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False"
(booth connection strings produces the same result)

Trusted Connection:

"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
- or -
"Server=Aron1;Database=pubs;Trusted_Connection=True;"
(booth connection strings produces the same result)

(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
Connect via an IP address:

"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
Declare the SqlConnection:

C#:
using System.Data.SqlClient;
SqlConnection oSQLConn = new SqlConnection();
oSQLConn.ConnectionString="my connectionstring";
oSQLConn.Open();

VB.NET:
Imports System.Data.SqlClient
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString="my connectionstring"
oSQLConn.Open()

Which one you use depends upon how you want to login. You can use a username and password, or you can use a Trusted Connection, in which case, the ASPNET user on the Web Server must be a user in SQL Server (this is awkward if you are running SQL Server on a machine other than the Web Server.

Covering Index Question

we have a table with the following structure (not full but should give an
idea how it looks like)
CREATE TABLE [dbo].[JK_Product] (
[Creator] [varchar] (50) NOT NULL ,
[ID] [int] NOT NULL ,
[Product] [int] NOT NULL ,
[PartNum] [char] (32) NOT NULL ,
..
..
..
[CheckShipping] [char] (1) NULL
) ON [PRIMARY]
GO
We have these 3 indexes already on this table
CLUSTERED INDEX1
([Creator], [Product])
PRIMARY KEY NONCLUSTERED INDEX1
([Creator],[ID])
NONCLUSTERED INDEX2
([Creator], [Product], [PartNum])
ITW suggested me these 3 new indexes based on some workload i gave
NONCLUSTERED INDEX3
([ID])
NONCLUSTERED INDEX4
([PartNum])
NONCLUSTERED INDEX5
([Product], [Creator])
is it necessary to create INDEX3 and INDEX4 since we already have those
covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer make
use of the existing indexes effectively? what if we create a covering NCI
consisting of Creator, ID, Product, PartNum columns and drop other NCIs
TIAHi
You need not create Index 3 and 4 as Index 1 and 2 takes care of it.
Creating an Index depends on the query that you frequently use.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"para

a" wrote:

> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer ma
ke
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||First, it is meaningless to talk about a clustered index covering a query. A
clustered indexes
covers all queries against the table.
So lets focus on the other indexes. If you don't have what you search for in
the first column of the
index, SQL Server cannot s

for that condition. It can scan the index, but
not s

. That might be
why it suggested an index in the ID, PartNum and Product, Creator. In short,
without knowing your
workload, selectivity etc, we cannot say whether the recommendations from IT
W are good
recommendations...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"para

a" <para

a@.discussions.microsoft.com> wrote in message
news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer
> make
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||tibor thx for the reply. yeah, i know a clustered index covers all queries
against the table. i was more interested to find out if instead of having 3-
4
small NCIs to cover some X queries, wouldn't it be better to have one long
NCI in general (I haven't fixed the order of columns in it yet...and maybe
the order can be fixed on further analysis of %age of its use in those X
queries)?
"Tibor Karaszi" wrote:

> First, it is meaningless to talk about a clustered index covering a query.
A clustered indexes
> covers all queries against the table.
> So lets focus on the other indexes. If you don't have what you search for
in the first column of the
> index, SQL Server cannot s

for that condition. It can scan the index, b
ut not s

. That might be
> why it suggested an index in the ID, PartNum and Product, Creator. In shor
t, without knowing your
> workload, selectivity etc, we cannot say whether the recommendations from
ITW are good
> recommendations...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "para

a" <para

a@.discussions.microsoft.com> wrote in message
> news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
>|||The ordering of the columns is one big issue. If you have a query with a whe
re clause in which you
only search for colA, then an index on (colB, colA) will not be searchable.
SQL Server can still
scan the index (assuming it covers the query), but a search is much better (
especially if the index
covers the query as no bookmark lookups are needed).
So, you could say that it is better to create a bunch of one-column indexes
and let SQL Server join
them as the query is processed (index intersection) so that the indexes toge
ther will cover the
query. Now you can search for whichever column you have ion the indexes and
you can always have a
s

. OTOH, you will pay the price for the index intersections as the query
is processed (in the
cases then SQL Server joins the indexes in run-time).
So, we cannot say which is better. You can, but either analyzing the queries
etc. Or by running a
load test for your particular load and see which index alternative is pest f
or your load test.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"para

a" <para

a@.discussions.microsoft.com> wrote in message
news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
> tibor thx for the reply. yeah, i know a clustered index covers all queries
> against the table. i was more interested to find out if instead of having
3-4
> small NCIs to cover some X queries, wouldn't it be better to have one long
> NCI in general (I haven't fixed the order of columns in it yet...and maybe
> the order can be fixed on further analysis of %age of its use in those X
> queries)?
> "Tibor Karaszi" wrote:
>|||In addition you may consider the space requirements for the nonclustered
indexes. This can vary but can be high for a table with a large number of
rows and although not recommended - a large clustered index key (will be
dupped in each nc index).
HTH
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u1CSKoZzFHA.2212@.TK2MSFTNGP15.phx.gbl...
> The ordering of the columns is one big issue. If you have a query with a
> where clause in which you only search for colA, then an index on (colB,
> colA) will not be searchable. SQL Server can still scan the index
> (assuming it covers the query), but a search is much better (especially if
> the index covers the query as no bookmark lookups are needed).
> So, you could say that it is better to create a bunch of one-column
> indexes and let SQL Server join them as the query is processed (index
> intersection) so that the indexes together will cover the query. Now you
> can search for whichever column you have ion the indexes and you can
> always have a s

. OTOH, you will pay the price for the index
> intersections as the query is processed (in the cases then SQL Server
> joins the indexes in run-time).
> So, we cannot say which is better. You can, but either analyzing the
> queries etc. Or by running a load test for your particular load and see
> which index alternative is pest for your load test.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "para

a" <para

a@.discussions.microsoft.com> wrote in message
> news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
>

Counts in Date Ranges

Hi,
I have table with the following structure
User Name, Name of Product, Date of Purchase
I want to find out the number of times a particular product was purchased by
anybody in the last 30, 60 and 90 days.
Is there any function in SQL that will give this type of distribution? What
will be the best way of writing the query?
Thanks in anticipation,
Nitin MNitim
SELECT COUNT(ProdunctName) FROM
TableName WHERE [Date of Purchase] >=DATEADD(DAY,-30,GETDATE()) AND
<GETDATE()
AND ProductName='Something'
"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23Zbsk3yPFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have table with the following structure
> User Name, Name of Product, Date of Purchase
> I want to find out the number of times a particular product was purchased
by
> anybody in the last 30, 60 and 90 days.
> Is there any function in SQL that will give this type of distribution?
What
> will be the best way of writing the query?
> Thanks in anticipation,
> Nitin M
>|||Thanks Uri,
This is the solution to get the purchase count for a given time period.
However I was looking for a solution that gives me the purchase counts for
three time periods in one query. I am looking for a result set that looks
like
Product PurchaseInLast30Days PurchaseInLast60Days
PurchaseInLast90Days
----
--
A 10 13
31
B 0 5
5
and so on ...
I have a query that looks like... but it amounts to scanning the table 4
times. Is there a better way out?
select
P.ProductName,
Last30Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 30 group by P1.ProductName),
Last60Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 60 group by P1.ProductName),
Last90Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 90 group by P1.ProductName),
from
Products P
group by
P.ProductName
Thanks,
Nitin M
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eJjWlBzPFHA.2876@.TK2MSFTNGP10.phx.gbl...
> Nitim
> SELECT COUNT(ProdunctName) FROM
> TableName WHERE [Date of Purchase] >=DATEADD(DAY,-30,GETDATE()) AND
> <GETDATE()
> AND ProductName='Something'
>
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:%23Zbsk3yPFHA.2932@.TK2MSFTNGP09.phx.gbl...
> by
> What
>|||I see what you mean
select
ProductName,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 30 THEN 1 END)
Last30Days ,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <=60 THEN 1 END)
Last60Days ,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 90 THEN 1 END)
Last90Days
from
Products
group by
ProductName
"Nitin M" <nitin@.nowhere.com> wrote in message
news:OVtGoHzPFHA.532@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri,
> This is the solution to get the purchase count for a given time period.
> However I was looking for a solution that gives me the purchase counts for
> three time periods in one query. I am looking for a result set that looks
> like
> Product PurchaseInLast30Days PurchaseInLast60Days
> PurchaseInLast90Days
> ----
--
> A 10 13
> 31
> B 0 5
> 5
> and so on ...
> I have a query that looks like... but it amounts to scanning the table 4
> times. Is there a better way out?
> select
> P.ProductName,
> Last30Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 30 group by P1.ProductName),
> Last60Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 60 group by P1.ProductName),
> Last90Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 90 group by P1.ProductName),
> from
> Products P
> group by
> P.ProductName
> Thanks,
> Nitin M
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eJjWlBzPFHA.2876@.TK2MSFTNGP10.phx.gbl...
purchased
>|||Thanks a lot Uri ... I got it ... It helped me.
Thank You
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23dOdsRzPFHA.1096@.TK2MSFTNGP12.phx.gbl...
>I see what you mean
> select
> ProductName,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 30 THEN 1 END)
> Last30Days ,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <=60 THEN 1 END)
> Last60Days ,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 90 THEN 1 END)
> Last90Days
> from
> Products
> group by
> ProductName
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:OVtGoHzPFHA.532@.TK2MSFTNGP09.phx.gbl...
> --
> purchased
>

Sunday, February 19, 2012

counting values in aresult set

Hi,
I have the following query:
SELECT ahd.call_req.ref_num AS Incident
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
which returns a single column called Incidents containing integer values.
4323
4316
4316
4309
4309
4309
What I want is a list of the Integer values and the coresponding number of
times
it appears in the result set.For example;
4323 1
4316 2
4309 3
--
roryI forot to say thank you in advance for any help!
Best wishes
--
rory
"Rory" wrote:
> Hi,
> I have the following query:
> SELECT ahd.call_req.ref_num AS Incident
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> which returns a single column called Incidents containing integer values.
> 4323
> 4316
> 4316
> 4309
> 4309
> 4309
> What I want is a list of the Integer values and the coresponding number of
> times
> it appears in the result set.For example;
> 4323 1
> 4316 2
> 4309 3
> --
> rory|||this is untested but try
SELECT ahd.call_req.ref_num AS Incident, count(*)
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by Incident
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rory" <rory@.discussions.microsoft.com> wrote in message
news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> Hi,
> I have the following query:
> SELECT ahd.call_req.ref_num AS Incident
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> which returns a single column called Incidents containing integer values.
> 4323
> 4316
> 4316
> 4309
> 4309
> 4309
> What I want is a list of the Integer values and the coresponding number
> of
> times
> it appears in the result set.For example;
> 4323 1
> 4316 2
> 4309 3
> --
> rory|||Hi Hilary,
Thanks for that, unortunately it does not work.
Regards,
--
rory
"Hilary Cotter" wrote:
> this is untested but try
> SELECT ahd.call_req.ref_num AS Incident, count(*)
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by Incident
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rory" <rory@.discussions.microsoft.com> wrote in message
> news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > Hi,
> > I have the following query:
> > SELECT ahd.call_req.ref_num AS Incident
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> >
> > which returns a single column called Incidents containing integer values.
> > 4323
> > 4316
> > 4316
> > 4309
> > 4309
> > 4309
> > What I want is a list of the Integer values and the coresponding number
> > of
> > times
> > it appears in the result set.For example;
> >
> > 4323 1
> > 4316 2
> > 4309 3
> > --
> > rory
>
>|||Hi Hilary,
Thanks for your reply, unfortunately it doesn't work. Back to
the "drawing board" for me I'm afraid!
Thanks again
--
rory
"Hilary Cotter" wrote:
> this is untested but try
> SELECT ahd.call_req.ref_num AS Incident, count(*)
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by Incident
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rory" <rory@.discussions.microsoft.com> wrote in message
> news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > Hi,
> > I have the following query:
> > SELECT ahd.call_req.ref_num AS Incident
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> >
> > which returns a single column called Incidents containing integer values.
> > 4323
> > 4316
> > 4316
> > 4309
> > 4309
> > 4309
> > What I want is a list of the Integer values and the coresponding number
> > of
> > times
> > it appears in the result set.For example;
> >
> > 4323 1
> > 4316 2
> > 4309 3
> > --
> > rory
>
>|||Hi Hilary,
I modified the query slightly from your suggestion( just column
names) and hey presto! Success!
Thanks you've made it a very nice Friday for me! Thanks again.
SELECT ahd.call_req.ref_num, count(*)
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
--
rory
"Hilary Cotter" wrote:
> this is untested but try
> SELECT ahd.call_req.ref_num AS Incident, count(*)
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by Incident
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rory" <rory@.discussions.microsoft.com> wrote in message
> news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > Hi,
> > I have the following query:
> > SELECT ahd.call_req.ref_num AS Incident
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> >
> > which returns a single column called Incidents containing integer values.
> > 4323
> > 4316
> > 4316
> > 4309
> > 4309
> > 4309
> > What I want is a list of the Integer values and the coresponding number
> > of
> > times
> > it appears in the result set.For example;
> >
> > 4323 1
> > 4316 2
> > 4309 3
> > --
> > rory
>
>|||Rory,
Change the "group by" clause and use the correct column.
SELECT
ahd.call_req.ref_num AS Incident, count(*)
FROM
ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
go
AMB
"Rory" wrote:
> Hi Hilary,
> Thanks for your reply, unfortunately it doesn't work. Back to
> the "drawing board" for me I'm afraid!
> Thanks again
> --
> rory
>
> "Hilary Cotter" wrote:
> > this is untested but try
> >
> > SELECT ahd.call_req.ref_num AS Incident, count(*)
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> > group by Incident
> >
> >
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Rory" <rory@.discussions.microsoft.com> wrote in message
> > news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > > Hi,
> > > I have the following query:
> > > SELECT ahd.call_req.ref_num AS Incident
> > > FROM ahd.call_req JOIN ahd.act_log
> > > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > > WHERE ahd.act_log.type = 'TR'
> > >
> > > which returns a single column called Incidents containing integer values.
> > > 4323
> > > 4316
> > > 4316
> > > 4309
> > > 4309
> > > 4309
> > > What I want is a list of the Integer values and the coresponding number
> > > of
> > > times
> > > it appears in the result set.For example;
> > >
> > > 4323 1
> > > 4316 2
> > > 4309 3
> > > --
> > > rory
> >
> >
> >|||Hi Alejandro,
I modified the query and it works fine,
SELECT ahd.call_req.ref_num, count(*) AS Hops
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
thanks for your response it is very much appreciated.
--
rory
"Alejandro Mesa" wrote:
> Rory,
> Change the "group by" clause and use the correct column.
> SELECT
> ahd.call_req.ref_num AS Incident, count(*)
> FROM
> ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by ahd.call_req.ref_num
> go
>
> AMB
>
> "Rory" wrote:
> > Hi Hilary,
> > Thanks for your reply, unfortunately it doesn't work. Back to
> > the "drawing board" for me I'm afraid!
> > Thanks again
> > --
> > rory
> >
> >
> > "Hilary Cotter" wrote:
> >
> > > this is untested but try
> > >
> > > SELECT ahd.call_req.ref_num AS Incident, count(*)
> > > FROM ahd.call_req JOIN ahd.act_log
> > > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > > WHERE ahd.act_log.type = 'TR'
> > > group by Incident
> > >
> > >
> > >
> > > --
> > > Hilary Cotter
> > > Director of Text Mining and Database Strategy
> > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > >
> > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > positions, strategies or opinions.
> > >
> > > Looking for a SQL Server replication book?
> > > http://www.nwsu.com/0974973602.html
> > >
> > > Looking for a FAQ on Indexing Services/SQL FTS
> > > http://www.indexserverfaq.com
> > >
> > >
> > >
> > > "Rory" <rory@.discussions.microsoft.com> wrote in message
> > > news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > > > Hi,
> > > > I have the following query:
> > > > SELECT ahd.call_req.ref_num AS Incident
> > > > FROM ahd.call_req JOIN ahd.act_log
> > > > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > > > WHERE ahd.act_log.type = 'TR'
> > > >
> > > > which returns a single column called Incidents containing integer values.
> > > > 4323
> > > > 4316
> > > > 4316
> > > > 4309
> > > > 4309
> > > > 4309
> > > > What I want is a list of the Integer values and the coresponding number
> > > > of
> > > > times
> > > > it appears in the result set.For example;
> > > >
> > > > 4323 1
> > > > 4316 2
> > > > 4309 3
> > > > --
> > > > rory
> > >
> > >
> > >

counting values in aresult set

Friday, February 17, 2012

Counting issue in table

I have a table with the following data (sample)

Type Year Price
-- -- --
A 00 100.00
A 00 200.00
A 01 105.00
A 01 105.00-
B 00 100.00
B 00 200.00
B 01 105.00
B 02 00.00

I need to establish a Type Count. The business rule to do the count is -

For a particular type, take a single year and add up the price. If the price is greater than zero then count = 1 else count = 0. After completing the counts for all the years for a particular type, add up the counts.

Based on the business rule for the above sample data I should have the following count.

Count -
Type A = 1 (For Year 00, price = 300.00 so count = 1 and for year 01, price = .00 (105 + 105.00 - ) and so count = 0. add counts to get a total of 1)

Type B = 2 (For Year 00, price = 300.00 so count = 1 and for year 01, price = 105 and so count = 1 and for year 02 price = 00 so count = 0. add counts to get a total of 2)

How do I implement this count using a query. I am free to add any indicator columns that may be required to perform the counting.

Let me know.

Thanks

VivekUSE Northwind
GO

CREATE TABLE myTable99(Type char(1), [Year] char(2), Price money)
GO

INSERT INTO myTable99(Type, [Year], Price)
SELECT 'A', '00', 100.00 UNION ALL
SELECT 'A', '00', 200.00 UNION ALL
SELECT 'A', '01', 105.00 UNION ALL
SELECT 'A', '01', -105.00 UNION ALL
SELECT 'B', '00', 100.00 UNION ALL
SELECT 'B', '00', 200.00 UNION ALL
SELECT 'B', '01', 105.00 UNION ALL
SELECT 'B', '02', 00.00
GO

SELECT Type
, [Year]
, SUM(Price) AS SUM_Price
, CASE WHEN SUM(Price) > 0 THEN 1 ELSE 0 END AS COUNT_Price
FROM myTable99
GROUP BY Type, [Year]
GO

DROP TABLE myTable99
GO|||Thanks Brett. That helps.|||Can I play too? I added a snippet that I think answers the original questionCREATE TABLE myTable99(Type char(1), [Year] char(2), Price money)
GO

INSERT INTO myTable99(Type, [Year], Price)
SELECT 'A', '00', 100.00 UNION ALL
SELECT 'A', '00', 200.00 UNION ALL
SELECT 'A', '01', 105.00 UNION ALL
SELECT 'A', '01', -105.00 UNION ALL
SELECT 'B', '00', 100.00 UNION ALL
SELECT 'B', '00', 200.00 UNION ALL
SELECT 'B', '01', 105.00 UNION ALL
SELECT 'B', '02', 00.00
GO

SELECT Type
, [Year]
, SUM(Price) AS SUM_Price
, CASE WHEN SUM(Price) > 0 THEN 1 ELSE 0 END AS COUNT_Price
FROM myTable99
GROUP BY Type, [Year]

SELECT type, Sum(price_count)
FROM (SELECT type, [year]
, CASE WHEN 0 < Sum(price) THEN 1 ELSE 0 END AS price_count
FROM myTable99
GROUP BY type, [year]) AS a
GROUP BY type

GO

DROP TABLE myTable99
GO-PatP

Thursday, March 29, 2012

create an incremental counter in the stored procedure

Tuesday, March 27, 2012

Create a time series

Create a Testing Database out of existing DB

Create a table using a Query

create a table prior to bcping data

Create a table and then using it as OLE DB Destination component

Sunday, March 25, 2012

Create A New UserName

create a new column

create a new column

create a new column

Thursday, March 22, 2012

Create a custom login.

Tuesday, March 20, 2012

Create a calculated field that gives me the avg 75 percentile

Wednesday, March 7, 2012

CPU messages in log

Friday, February 24, 2012

Covert Connection from Access to SQL

Covering Index Question

Counts in Date Ranges

Sunday, February 19, 2012

counting values in aresult set

counting values in aresult set

counting values in aresult set

Friday, February 17, 2012

Counting issue in table

CREATE DATABASE failed

Blog Archive

About Me