Create a flat file for each record in a table

I'm rather new to ssis and I've been reading and testing but didn't find a solution for this problem.Supose I've got a table Customer with some fields. One of the fields is CustID.I want to create as many flat files as there are Customers in the table with flat file name set to the CustID.If you could point me in a good direction, It would be nice.Greetings from Belgium

Do you want any data in these files or just the file to be created? If you just want the file to be created you could aggregate the data and then use the FileExtractor to create the file (You would need to add 2 columns one for the file name and one for the file data (empty)). If you actually want the data to flow into these files then you would most likely want to use a script component.

HTH,

Matt

|||

It sounds like you want to run a exectue SQL task to get an ADO object which holds the records you want. Then for each of these you what to push thenm to a for loop and have a dataflow inside the for loop. In the Dataflow the source would be the ADO object and you can push it to the text file.

These pages should help a bit

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/2458.aspx

http://sqljunkies.com/WebLog/knight_reign/archive/category/458.aspx

|||Thanks!

creat a solution in SQLServer 2005 and intall that solution in SQLServer 2005 Express

Hi,

can i develop a solution in SQLServer 2005 with the analysis Services (OLAP cubes) and then put that solution in a client that only have the SQLServer 2005 Express?

Thaks

Larokas

Analysis Services are not part of SQL Server 2005 Express installation.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Crash of Enterprise Manager, strange solution

Hi,
Every time I try to use enterprise manager it suddenlly crashes.
The server is on, I can access it in other ways but without using
enterprise manager.
I removed sqlserver from the system, and installed it again.
I also installed the service pack 4.
I made many attempt to solve the problem.
At last I renamed the file "C:\Program files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
et voila EM runs well again.
Does anyone know why?
Thanks
MassimoHi
I presume copying the MMC back to the original file name MMC still fails? In
which case MMC there must be some configuration information linked to the
file name.
John
"mappopo" wrote:

> Hi,
> Every time I try to use enterprise manager it suddenlly crashes.
> The server is on, I can access it in other ways but without using
> enterprise manager.
> I removed sqlserver from the system, and installed it again.
> I also installed the service pack 4.
> I made many attempt to solve the problem.
> At last I renamed the file "C:\Program files\Microsoft SQL
> Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
> et voila EM runs well again.
> Does anyone know why?
> Thanks
> Massimo
>|||I think so. Where are those informations?|||Hi
I would have expected it to be in the registry, but searching it didn't seem
to turn up anything!
John
"mappopo" wrote:

> I think so. Where are those informations?
>

Crash of Enterprise Manager, strange solution

Hi,
Every time I try to use enterprise manager it suddenlly crashes.
The server is on, I can access it in other ways but without using
enterprise manager.
I removed sqlserver from the system, and installed it again.
I also installed the service pack 4.
I made many attempt to solve the problem.
At last I renamed the file "C:\Program files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
et voila EM runs well again.
Does anyone know why?
Thanks
Massimo
Hi
I presume copying the MMC back to the original file name MMC still fails? In
which case MMC there must be some configuration information linked to the
file name.
John
"mappopo" wrote:

> Hi,
> Every time I try to use enterprise manager it suddenlly crashes.
> The server is on, I can access it in other ways but without using
> enterprise manager.
> I removed sqlserver from the system, and installed it again.
> I also installed the service pack 4.
> I made many attempt to solve the problem.
> At last I renamed the file "C:\Program files\Microsoft SQL
> Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
> et voila EM runs well again.
> Does anyone know why?
> Thanks
> Massimo
>
|||I think so. Where are those informations?
|||Hi
I would have expected it to be in the registry, but searching it didn't seem
to turn up anything!
John
"mappopo" wrote:

> I think so. Where are those informations?
>

Crash of Enterprise Manager, strange solution

Hi,
Every time I try to use enterprise manager it suddenlly crashes.
The server is on, I can access it in other ways but without using
enterprise manager.
I removed sqlserver from the system, and installed it again.
I also installed the service pack 4.
I made many attempt to solve the problem.
At last I renamed the file "C:\Program files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
et voila EM runs well again.
Does anyone know why?
Thanks
Massimo
Hi
I presume copying the MMC back to the original file name MMC still fails? In
which case MMC there must be some configuration information linked to the
file name.
John
"mappopo" wrote:

> Hi,
> Every time I try to use enterprise manager it suddenlly crashes.
> The server is on, I can access it in other ways but without using
> enterprise manager.
> I removed sqlserver from the system, and installed it again.
> I also installed the service pack 4.
> I made many attempt to solve the problem.
> At last I renamed the file "C:\Program files\Microsoft SQL
> Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
> et voila EM runs well again.
> Does anyone know why?
> Thanks
> Massimo
>
|||I think so. Where are those informations?
|||Hi
I would have expected it to be in the registry, but searching it didn't seem
to turn up anything!
John
"mappopo" wrote:

> I think so. Where are those informations?
>

Crash of Enterprise Manager, strange solution

Hi,
Every time I try to use enterprise manager it suddenlly crashes.
The server is on, I can access it in other ways but without using
enterprise manager.
I removed sqlserver from the system, and installed it again.
I also installed the service pack 4.
I made many attempt to solve the problem.
At last I renamed the file "C:\Program files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
et voila EM runs well again.
Does anyone know why?
Thanks
MassimoHi
I presume copying the MMC back to the original file name MMC still fails? In
which case MMC there must be some configuration information linked to the
file name.
John
"mappopo" wrote:
> Hi,
> Every time I try to use enterprise manager it suddenlly crashes.
> The server is on, I can access it in other ways but without using
> enterprise manager.
> I removed sqlserver from the system, and installed it again.
> I also installed the service pack 4.
> I made many attempt to solve the problem.
> At last I renamed the file "C:\Program files\Microsoft SQL
> Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
> et voila EM runs well again.
> Does anyone know why?
> Thanks
> Massimo
>|||I think so. Where are those informations?|||Hi
I would have expected it to be in the registry, but searching it didn't seem
to turn up anything!
John
"mappopo" wrote:
> I think so. Where are those informations?
>

Couple of newbie questions

My company is moving to SQL Server soon, and I'm certainly excited about it. Currently we are using a FoxPro based solution which is slow and unreliable.

I'm working on the configuration and have a few questions. We are planning on going with a SQL Server 2000 Standard Edition running on Windows 2000 Server. Some of what Microsoft has said on the web site is very vague concerning the licensing and other maximums.

What is the max memory that you can use with SQL Standard on a Windows 2000 Server (not Advanced)?
If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2 GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.
Do you need seperate CALs on Windows 2000, or does it work like other apps like Exchange, where you only need the CAL's for the application (or in this case, the Processor licences).
For a 3 processor server, does anyone think that for about 60-70 users that I should go with a CAL or Processor license model?
Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.

Thanks in advance.According to Books Online:

1. What is the max memory that you can use with SQL Standard on a Windows 2000 Server (not Advanced)?

2GB is the maximum that SQL Std can use.

2. If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2 GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.

If the OS can see the additional RAM, this should be ok. I cannot think how much memory that Win2k std can use.

3. Do you need seperate CALs on Windows 2000, or does it work like other apps like Exchange, where you only need the CAL's for the application (or in this case, the Processor licences).

If you go with per processor licensing of SQL Server, that is all you need.

4. For a 3 processor server, does anyone think that for about 60-70 users that I should go with a CAL or Processor license model?

I found SQL2k Std per processor licensing at ~4500 multiplied by 3 you would be at ~$13,500

I found SQL2k Std 10 client for $2000. If you purchased 7 of these (70 user), you would be at ~$14,000

Right now, the price is about the same. You need to weigh the options, and look toward the future.

What happens if the 3 processor server needs 5 more processors? What happens if you add 40 more users? How will this impact your licenses (and cost)?

5. Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.

I don't know. What is the price difference between 2MB, 1MB, and 'normal' Xeons? We have an 8 way 700 with 2MB cache and things perform quite well. I am sure that we paid a premium for the 2MB chips...I do not know what performance would be like if we had lesser Xeons. Maybe someone else can help out here with some suggestions.

________________
Keith|||2. If you have more memory in the server than SQL Standard will support, is that OK? For example, can you say, if SQL Standard maxes out at 2GB, put in 3 GB, and let the OS have the rest? Our database will probably be around 2 GB to start.

Win2k and NT Standard see 4GIG max which is the memory space directly addressable by a a 32bit operating system.

The 2 gig limit for SQL Server comes from the fact that the NT architecture has a 2gig per process memory limit.

5. Should I spring for the 2MB cache on the processor? Planning on a 3 way 700 Mhz Xeon.

It's hard to say. All of our SQL Servers now have 2MB processor caches. We had one Dell 4-way server with 512K (standard) caches that severely under-performed and was replaced by a Compaq server. But the Compaq server was much more carefully specified so direct comparisons are tough. I know this. I've read of people with performance problems when CPU utilization is at 100% on all 4 processors. On our Compaq servers, when all four processors are at 100%, people can still work. It's a little slower, but almost goes unnoticed except by those that cause the performance spike (period end processes, some custom reports). On the Dell, if all 4 where at 100% no one moved. It would stop everyone in their tracks. Compaq's processor multi-processor architecture probably has something to do with it, but I bet the 2MB cache in each processor has something to do with it also.

If the cost is not unreasonable go for the 2mb cache. We choose slower processors with 2MB cache over a faster processor with 512k or 1mb and haven't looked back.|||Thanks for the repsonse. It will be a few thousand more to upgrade from the 1 MB to 2 MB cache, so it may be difficult to get that passed through our budget.

Out of curiosity, how many users are you guys dealing with? I'll be around 50 concurrent to start, while not expecting that number to go up to more than 75 any time soon (a few years off).|||We have about 60 users hitting the system full time.

Keep in mind, it is not so much the user count, but the way the application and database were created that really affects performance.

________________
Keith|||I don't even know exactly right now. Probably right around 35-60 depending on the time of day. But really it's not the number of users, but the work you expect the server to perform for those users.

Our primary server runs an accounting and distribution package that is heavily optimized for SQL Server and WAN environments relying on Triggers, and stored procedures, and temp tables for reports. (And not measly select * from thistable stored procedures, these are real ones that do real work.) This application works the server. 1MB may be just fine. At the time we specified the hardware we had the choice of 1mb cache and 650Mhz processors or 2mb Cache and 450mhz processors to fit in our price range. We went with the 450mhz processors and 2mb cache. 20

Make sure your application isn't just a fancy data store. We way equipped some hardware we have right now for a mission critical clustered solution. Quad processor Active/Active Compaq machines with 4gig each running SQL Enterprise and NT Enterprise, and we get the app installed on a test machine and I find it was ported from Oracle and AS/400 and is mostly flat files stored in the database. No stored procedures, no referential integrity, hardly even any indexes. The groupresponsible for getting the hardware obviously didn't do their researchfirst. Such a database will hardly be taxing the CPU's and is more likely to have blocking issues than performance issues. I can't wait until we go live...

counting with different conditions

Hi, I am unable to find a solution for this which I initially thought
would be easy
TABLE tbl { n1 INTEGER, n2 INTEGER }
I need to count for the whole domain
1) n1 < 1000
2) n2 < 1000
3) n1 < n2
more conditions ...
I don't want to create a view per condition. It will make it slow for
my client/server environment.
Is it possible to create a view { count(condition1),
count(condition2), ... }
Help please
RiyazOn 16.05.2007 10:08, riyaz.mansoor@.gmail.com wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
>
Use CASE.
robert|||On May 16, 1:08 pm, "riyaz.mans...@.gmail.com"
<riyaz.mans...@.gmail.com> wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
select (select count(*) from tbl where n1< 1000) as condition1 ,
(select count(*) from tbl where n2 < 1000) as condition2,
(select count(*) from t where n1 < n2) as condition3
Regards
Amish Shah
http://shahamishm.tripod.com|||Life Saver.

> select (select count(*) from tbl where n1< 1000) as condition1 ,
> (select count(*) from tbl where n2 < 1000) as condition2,
> (select count(*) from t where n1 < n2) as condition3
> Regards
> Amish Shahhttp://shahamishm.tripod.com|||On 16.05.2007 11:10, riyaz.mansoor@.gmail.com wrote:
> Life Saver.
[vbcol=seagreen]
Wait, 'till you see the performance of that. IMHO a solution involving
CASE is more efficient.
robert|||I'm just know basic SQL. Can u show me how to use CASE?

> Wait, 'till you see the performance of that. IMHO a solution involving
> CASE is more efficient.
> robert|||On 16.05.2007 12:24, riyaz.mansoor@.gmail.com wrote:[vbcol=seagreen]
> I'm just know basic SQL. Can u show me how to use CASE?
>
How about reading some documentation?
Hint: SUM(CASE ... END)
Cheers
robert
PS: Please do not top post.|||Below is an example of the CASE expression technique Riyaz mentioned. This
provides better performance than the subquery method in situations where the
entire table must be scanned because it's more efficient to scan once rather
than multiple times. However, with only sarable expressions (if you didn't
have n1 < n2), you could get better performance with the subqueries if n1
and n2 were both indexed.
SELECT
SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
FROM dbo.tbl
Hope this helps.
Dan Guzman
SQL Server MVP
<riyaz.mansoor@.gmail.com> wrote in message
news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
> I'm just know basic SQL. Can u show me how to use CASE?
>
>|||> Below is an example of the CASE expression technique Riyaz mentioned.
I meant to say "Robert mentioned". My apologies to both of you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:52EBF1DE-98E5-4882-8FFF-DC18B098640D@.microsoft.com...
> Below is an example of the CASE expression technique Riyaz mentioned.
> This provides better performance than the subquery method in situations
> where the entire table must be scanned because it's more efficient to scan
> once rather than multiple times. However, with only sarable expressions
> (if you didn't have n1 < n2), you could get better performance with the
> subqueries if n1 and n2 were both indexed.
> SELECT
> SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
> SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
> SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
> FROM dbo.tbl
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <riyaz.mansoor@.gmail.com> wrote in message
> news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
>

counting with different conditions

Hi, I am unable to find a solution for this which I initially thought
would be easy :(
TABLE tbl { n1 INTEGER, n2 INTEGER }
I need to count for the whole domain
1) n1 < 1000
2) n2 < 1000
3) n1 < n2
more conditions ...
I don't want to create a view per condition. It will make it slow for
my client/server environment.
Is it possible to create a view { count(condition1),
count(condition2), ... }
Help please
RiyazOn 16.05.2007 10:08, riyaz.mansoor@.gmail.com wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy :(
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
>
Use CASE.
robert|||On May 16, 1:08 pm, "riyaz.mans...@.gmail.com"
<riyaz.mans...@.gmail.com> wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy :(
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
select (select count(*) from tbl where n1< 1000) as condition1 ,
(select count(*) from tbl where n2 < 1000) as condition2,
(select count(*) from t where n1 < n2) as condition3
Regards
Amish Shah
http://shahamishm.tripod.com|||Life Saver. :)
> select (select count(*) from tbl where n1< 1000) as condition1 ,
> (select count(*) from tbl where n2 < 1000) as condition2,
> (select count(*) from t where n1 < n2) as condition3
> Regards
> Amish Shahhttp://shahamishm.tripod.com|||On 16.05.2007 11:10, riyaz.mansoor@.gmail.com wrote:
> Life Saver. :)
>> select (select count(*) from tbl where n1< 1000) as condition1 ,
>> (select count(*) from tbl where n2 < 1000) as condition2,
>> (select count(*) from t where n1 < n2) as condition3
Wait, 'till you see the performance of that. IMHO a solution involving
CASE is more efficient.
robert|||I'm just know basic SQL. Can u show me how to use CASE?
> Wait, 'till you see the performance of that. IMHO a solution involving
> CASE is more efficient.
> robert|||On 16.05.2007 12:24, riyaz.mansoor@.gmail.com wrote:
> I'm just know basic SQL. Can u show me how to use CASE?
>> Wait, 'till you see the performance of that. IMHO a solution involving
>> CASE is more efficient.
How about reading some documentation?
Hint: SUM(CASE ... END)
Cheers
robert
PS: Please do not top post.|||Below is an example of the CASE expression technique Riyaz mentioned. This
provides better performance than the subquery method in situations where the
entire table must be scanned because it's more efficient to scan once rather
than multiple times. However, with only sarable expressions (if you didn't
have n1 < n2), you could get better performance with the subqueries if n1
and n2 were both indexed.
SELECT
SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
FROM dbo.tbl
Hope this helps.
Dan Guzman
SQL Server MVP
<riyaz.mansoor@.gmail.com> wrote in message
news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
> I'm just know basic SQL. Can u show me how to use CASE?
>> Wait, 'till you see the performance of that. IMHO a solution involving
>> CASE is more efficient.
>> robert
>|||> Below is an example of the CASE expression technique Riyaz mentioned.
I meant to say "Robert mentioned". My apologies to both of you.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:52EBF1DE-98E5-4882-8FFF-DC18B098640D@.microsoft.com...
> Below is an example of the CASE expression technique Riyaz mentioned.
> This provides better performance than the subquery method in situations
> where the entire table must be scanned because it's more efficient to scan
> once rather than multiple times. However, with only sarable expressions
> (if you didn't have n1 < n2), you could get better performance with the
> subqueries if n1 and n2 were both indexed.
> SELECT
> SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
> SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
> SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
> FROM dbo.tbl
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <riyaz.mansoor@.gmail.com> wrote in message
> news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
>> I'm just know basic SQL. Can u show me how to use CASE?
>> Wait, 'till you see the performance of that. IMHO a solution involving
>> CASE is more efficient.
>> robert
>>
>

counting with different conditions

Hi, I am unable to find a solution for this which I initially thought
would be easy
TABLE tbl { n1 INTEGER, n2 INTEGER }
I need to count for the whole domain
1) n1 < 1000
2) n2 < 1000
3) n1 < n2
more conditions ...
I don't want to create a view per condition. It will make it slow for
my client/server environment.
Is it possible to create a view { count(condition1),
count(condition2), ... }
Help please
Riyaz
On 16.05.2007 10:08, riyaz.mansoor@.gmail.com wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
>
Use CASE.
robert
|||On May 16, 1:08 pm, "riyaz.mans...@.gmail.com"
<riyaz.mans...@.gmail.com> wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
select (select count(*) from tbl where n1< 1000) as condition1 ,
(select count(*) from tbl where n2 < 1000) as condition2,
(select count(*) from t where n1 < n2) as condition3
Regards
Amish Shah
http://shahamishm.tripod.com
|||Life Saver.

> select (select count(*) from tbl where n1< 1000) as condition1 ,
> (select count(*) from tbl where n2 < 1000) as condition2,
> (select count(*) from t where n1 < n2) as condition3
> Regards
> Amish Shahhttp://shahamishm.tripod.com
|||On 16.05.2007 11:10, riyaz.mansoor@.gmail.com wrote:
> Life Saver.
[vbcol=seagreen]
Wait, 'till you see the performance of that. IMHO a solution involving
CASE is more efficient.
robert
|||I'm just know basic SQL. Can u show me how to use CASE?

> Wait, 'till you see the performance of that. IMHO a solution involving
> CASE is more efficient.
> robert
|||On 16.05.2007 12:24, riyaz.mansoor@.gmail.com wrote:[vbcol=seagreen]
> I'm just know basic SQL. Can u show me how to use CASE?
How about reading some documentation?
Hint: SUM(CASE ... END)
Cheers
robert
PS: Please do not top post.
|||Below is an example of the CASE expression technique Riyaz mentioned. This
provides better performance than the subquery method in situations where the
entire table must be scanned because it's more efficient to scan once rather
than multiple times. However, with only sarable expressions (if you didn't
have n1 < n2), you could get better performance with the subqueries if n1
and n2 were both indexed.
SELECT
SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
FROM dbo.tbl
Hope this helps.
Dan Guzman
SQL Server MVP
<riyaz.mansoor@.gmail.com> wrote in message
news:1179311071.285296.39540@.e65g2000hsc.googlegro ups.com...
> I'm just know basic SQL. Can u show me how to use CASE?
>
>
|||> Below is an example of the CASE expression technique Riyaz mentioned.
I meant to say "Robert mentioned". My apologies to both of you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:52EBF1DE-98E5-4882-8FFF-DC18B098640D@.microsoft.com...
> Below is an example of the CASE expression technique Riyaz mentioned.
> This provides better performance than the subquery method in situations
> where the entire table must be scanned because it's more efficient to scan
> once rather than multiple times. However, with only sarable expressions
> (if you didn't have n1 < n2), you could get better performance with the
> subqueries if n1 and n2 were both indexed.
> SELECT
> SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
> SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
> SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
> FROM dbo.tbl
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <riyaz.mansoor@.gmail.com> wrote in message
> news:1179311071.285296.39540@.e65g2000hsc.googlegro ups.com...
>

Counting Customer Transactions

I have a question I'm hoping someone will be able to help me with - I've been working on it for a while and can't seem to find an obvious solution.

Basically I have a fairly standard transaction fact table, and customers may have made multiple transactions over the selected range.

I need to know how many customers made 1 order, 2 orders, 3 orders, etc. As far as I can tell, I'm effectively doing a count of a count.

It seems to me to be a fairly straightforward request, however I can't work out a way to do it in Analysis Services. If someone can help me out or point me in the right direction I would be very grateful

Thanks,
Matt

is the selected range fixed? if yes, you could create a set for this purpose.

If not, which i believe is the case, you can rely on the tool you're using as interface to do the filtering for you...

if you're writing an mdx query, did you try doing it in the where condition ?

|||

Hi Christina

Thanks for your response. You're right, the range isn't fixed, although I guess it probably could be for most purposes. Could you please give me an example of how you would do this with a set? I haven't had to use them yet.

The frontend we are using is web-based Dundas Charts for OLAP, and there is no provision for writing mdx queries. Ideally it would appear in the interface as just another attribute in my Customer dimension. Any other thoughts?

Thanks again for your help,

Matt

|||

i don't know if this is a good solution:

since you mentioned that it would appear as another attribute, then maybe you should create a dimension with the customer and the values per range. but the ranges should be predefined. you would lose the flexibility

this dimension would be based on a view in the DW based on this fact table. it should contain the counts and have its structure as such:

customer CountRange1 CountRange2 CountRange3 CountRange4...etc..

where the ranges are not overlapping, however they are the most used ranges. better if you have only 1 range, because on dundas, u'll have to add all these fields to use them as filter

i will think of another alternative and let you know..