Showing posts with label appreciate. Show all posts
Showing posts with label appreciate. Show all posts

Thursday, March 29, 2012

cursor to insert into a table variable

I would appreciate some help in writing a cursor to populate a table variable based on a result set
All the cursor does is find all rows with same area ID and areaName and insert into the table variable the one with the latest modified date, if all modified dates are equal then insert the one with the latest created date.
This is the result set(id int, areaID int, areaName varchar(30), created datetime, modifieddatetime):
3 10 MA 2005-03-04 2005-03-04
6 10 MA 2005-05-04 2005-03-04
9 10 MA 2005-03-04 2005-03-04
1 52 PUERTO RICO 2005-03-04 2005-03-04
2 52 PUERTO RICO 2005-03-04 2005-03-04
4 52 PUERTO RICO 2005-03-04 2005-05-04
8 52 PUERTO RICO 2005-03-04 2005-03-04
5 5 NY 2005-04-04 2005-03-04
7 5 NY 2005-03-04 2005-03-04
table variable @.tempTable(id int, areaID int, areaName varchar(30), created datetime, modified datetime)
After running this cursor my temp table would contain:
6 10 MA 2005-05-04 2005-03-04
4 52 PUERTO RICO 2005-03-04 2005-05-04
7 5 NY 2005-03-04 2005-03-04
Any help would be appreciated

you don't need a cursor for this at all
this should do it, change YourTable to your table name

insert into @.tempTable(id ,areaID ,areaName , created , modified )
select t1.* from
YourTable t1 join(
select areaID ,areaName , max(created)as MaxCreated,max( modified) as MaxModified
from YourTable
group by areaID ,areaName) t2 on
t1.areaID = t2.areaID
and t1.areaName = t2.areaName
and t1.created = t2.MaxCreated
and t1.modified = t2.MaxModified

if it is possible that you can have created and modified that are the same for an area then you have to group on t1 and grab the min or max id


Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

The following query will do..

Select TwoCol.* From
TwoCol
Join
(
Select
IdWise.ColOne,
IdMaxModdate,
IdMaxCrdDate
From


(Select ColOne,ModifieddateTime,Count(*) ModDateCount from TwoCol
group By ColOne,ModifieddateTime) as ModDateWise

Join (Select ColOne,Count(*) IdCount,max(ModifieddateTime) IdMaxModdate, max(createddatetime) IdMaxCrdDate
From TwoCol Group By ColOne) as IdWise On ModDateWise.ColOne = IdWise.ColOne And IdMaxModdate = ModifieddateTime
) as T On T.ColOne = TwoCol.ColOne And ModifieddateTime = IdMaxModdate And createddatetime = IdMaxCrdDate

Saturday, February 25, 2012

cube design/MDX for Product Owns vs does not Own

I would really appreciate if you could give us your input for following design, I am having hard time to visualize the cube design.

I have a fact table and dimension tables as follows with rows.

CREATE TABLE [dbo].[Fact_ProductOwnership](
[OrgID] [int] NULL,
[SegmentID] [int] NULL,
[SubSegmentID] [int] NULL,
[ProductID] [int] NOT NULL
) ON [PRIMARY]

OrgID SegmentID SubSegmentID ProductID 1 1 1 2 1 1 1 3 1 1 1 6 2 2 2 2 2 2 2 3 2 2 2 6 3 1 1 1 3 1 1 3 3 1 1 5

Segment table rows

CREATE TABLE [dbo].[Segment](
[ID] [int] NULL,
[SegmentName] [varchar](20) NULL
) ON [PRIMARY]

ID SegmentName

1 Enterprize

2 Small Business


SubgSegment table and Rows

CREATE TABLE [dbo].[SubSegment](
[ID] [int] NULL,
[SubSegmentName] [varchar](20) NULL
) ON [PRIMARY]

ID SubSegmentName

1 Enterprize

2 Small Business

Product table and Rows

CREATE TABLE [dbo].[Products](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductDesc] [varchar](20) NULL
) ON [PRIMARY]

ID ProductDesc

1 Access_Yes

2 Access_No

3 Excel_Yes

4 Excel_No

5 SqlServer_Yes

6 SqlServer_No

What I would like achieve from above design is to get the list orgs for the following scenarios

1) Show the companies who owns Access_Yes & Excel_No

Above should display 2 orgs (1 and 2 qualify for this scenario)

2) Show the companies who owns SqlServer_yes & SqlServer_Ye

Above query should display 0 orgs(no org qualifies)

Could you please suggest me thtat is my above design wrong or is it too hard to ahieve from cube? Is there any alogorithm this can solve above problem ?

I would really appreciate if you could answer to above problem ?

You can design your cube with dimensions for Org and Product (and optionally Segment and SubSegment) and then create a row count measure. Then you would query for Orgs and use NONEMPTYCROSSJOIN to filter by products.

Something like:

SELECT NONEMPTYCROSSJOIN(NONEMPTYCROSSJOIN([Org].Members, {[Product].[Access_Yes]}, 1) {[Product].[Excel_No], 1) ON COLUMNS,

{} ON ROWS

FROM [MyCube]

Friday, February 17, 2012

CSV import into MS SQL.

Hello,
I am new to the world of databases and would appreciate a
critique/brain storm of my idea to load CSV data files into a MS SQL
database.
My problem is that I have multiple types of CSV files, ones with first
rows equaling the column names and some with accompanying .ini files
with more specific attribute information.
So, my idea is to convert these csv files into xml and then apply an
xsl transform to a generic xml format (with applicable tag names for
the db) - which that I can then insert into a MS SQL database using a
c# data layer.
Depending on the data I have to undertake different actions (i.e. new
data create new records or mark records as amended).
Does this sound like a reasonable approach, this must be a very common
task in the world of databases?
Thank you so much for your time,
Dan.
You can import csv files with DTS or BCP
DTS is the easiest, BCP is the fastest (if you have really big files)
Lookup DTS and BCP in BOL
A great DTS site is http://www.sqldts.com/
http://sqlservercode.blogspot.com/
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>
|||Thank-You .... Can't understand why that was so hard for me...
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>

CSV import into MS SQL.

Hello,
I am new to the world of databases and would appreciate a
critique/brain storm of my idea to load CSV data files into a MS SQL
database.
My problem is that I have multiple types of CSV files, ones with first
rows equaling the column names and some with accompanying .ini files
with more specific attribute information.
So, my idea is to convert these csv files into xml and then apply an
xsl transform to a generic xml format (with applicable tag names for
the db) - which that I can then insert into a MS SQL database using a
c# data layer.
Depending on the data I have to undertake different actions (i.e. new
data create new records or mark records as amended).
Does this sound like a reasonable approach, this must be a very common
task in the world of databases?
Thank you so much for your time,
Dan.You can import csv files with DTS or BCP
DTS is the easiest, BCP is the fastest (if you have really big files)
Lookup DTS and BCP in BOL
A great DTS site is http://www.sqldts.com/
http://sqlservercode.blogspot.com/
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>|||Thank-You .... Can't understand why that was so hard for me...
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>

CSV import into MS SQL.

Hello,
I am new to the world of databases and would appreciate a
critique/brain storm of my idea to load CSV data files into a MS SQL
database.
My problem is that I have multiple types of CSV files, ones with first
rows equaling the column names and some with accompanying .ini files
with more specific attribute information.
So, my idea is to convert these csv files into xml and then apply an
xsl transform to a generic xml format (with applicable tag names for
the db) - which that I can then insert into a MS SQL database using a
c# data layer.
Depending on the data I have to undertake different actions (i.e. new
data create new records or mark records as amended).
Does this sound like a reasonable approach, this must be a very common
task in the world of databases?
Thank you so much for your time,
Dan.You can import csv files with DTS or BCP
DTS is the easiest, BCP is the fastest (if you have really big files)
Lookup DTS and BCP in BOL
A great DTS site is http://www.sqldts.com/
http://sqlservercode.blogspot.com/
"Dan E" wrote:
> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>|||Thank-You .... Can't understand why that was so hard for me...
"Dan E" wrote:
> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>