cursor or query

> Dear all,
> i had a table named saletable i had fields like
> itemgroupid,itemgroupname,saleqty,saleam
ount and saledate.
> i want to sum the saleqty and saleamount by every month and year.by every itemgoup
name
suppose that every year has january month and 31 days in january so i want
that for every year and every month i should sum the qty and sale amount for
Y/M but by Itemgroupname
do not want to give criteria
> for eaxmple
> itemgroupid itemgroupname saleqty saleamount date
> ----
--
> 23101102 HERO LINER 10.00 50467.29 2004-01
-07
> 23101102 HERO LINER 20.00 10924.58 2005-01
-07
> 23101102 HERO LINER 10.00 50467.29 2004-02
-07
> 23101102 HERO LINER 20.00 10924.58 2005-02
-07
> 23101102 HERO LINER 10.00 50467.29 2004-01
-07
> 23101102 HERO LINER 20.00 10924.58 2005-01
-07
> 23204101 Trading Mudgard 10.00 6000.00 2005-04-21
> 23204101 Trading Mudgard 10.00 50467.29 2004-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-02-07
> 23204101 Trading Mudgard 10.00 50467.29 2004-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-01-07
> I WANT like this
> for every month and year
> 23204101 Trading Mudgard 20.00 100934.58 2004-01
> 23204101 Trading Mudgard 60.00 2326000.00 2005-01
> 23204101 Trading Mudgard 10.00 6000.00 2005-07
> thanx
--
waiting for solution
from
SufianSELECT itemgroupid, itemgroupname, MIN(date),
SUM(saleqty), SUM(saleamount)
FROM SaleTable
GROUP BY YEAR(date), MONTH(date)
If that's not what you want then please post DDL, sample data, required
results. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thanx David it worked and once again thanx
--
waiting for solution
from
Sufian
"David Portas" wrote:

> SELECT itemgroupid, itemgroupname, MIN(date),
> SUM(saleqty), SUM(saleamount)
> FROM SaleTable
> GROUP BY YEAR(date), MONTH(date)
> If that's not what you want then please post DDL, sample data, required
> results. See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>

Cube, Build, Deploy, Process

Dear all,

I'd like to get simple and clear explanation of the cube in data mining, and 3 notions we encounter a lot : Build, Deploy, and Process.

(1) What is the cube that is created when we deploy a mining solution/project?
I wonder what type of cubes they are because although the dialog on deploy/process
show that cube, after successful deployment we still don't see the cube in Cubes folder
of the project.

(2) Why the SQL Server created that cube? Even though we process only one table
and only use case-table (without nested table)

(3) Can someone explain these 3 concepts with CLEAR differences between them?
(A) Build
(B) Deploy
(C) Process

As far as I know, the stages are like that : build, then deploy, then process. Also, it seems
to me that those operations do not create objects inside 'Relational' database, but create
objects (binary and text, with text files usually in XMLA programming language) in the
related project's folders and subfolders. Any good explanation is appreciated.

Bernaridho

1 & 2: The cube is an internal object used as a data structures in processing the mining structure and models. It is not visible externally and should be ignored. The only reason the cube is visible during processing is to surface potential processing errors, such as type mismatches.

3: Build - build takes the project's objects and builds a script that can be sent to the Analysis Services server. As with any "build" process, validation occurs to verify if any ovbious errors are present in the project

Deploy - Deploy sends the project to a server and creates any objects in that project on the server. Since the unit of deploymet is a database, any same-named database will be overwritten.

Process: Process is when the objects actually consume data and become useful. Prior to processing, the objects are "empty" and con not be queired.

|||Hi Jamie,

Thank you for replying. I'd like to add something about 'Process' that I found in your
book 'Data Mining with SQL Server 2005'. In the context of Data Mining, Process
also means 'Train'. That is, it's during 'Process' that the Analysis Service (background
process) trains the mining model. Hopefully this is useful for other member of this forum.

Thank you,

Bernaridho

Cube, Build, Deploy, Process

Dear all,

I'd like to get simple and clear explanation of the cube in data mining, and 3 notions we encounter a lot : Build, Deploy, and Process.

(1) What is the cube that is created when we deploy a mining solution/project?
I wonder what type of cubes they are because although the dialog on deploy/process
show that cube, after successful deployment we still don't see the cube in Cubes folder
of the project.

(2) Why the SQL Server created that cube? Even though we process only one table
and only use case-table (without nested table)

(3) Can someone explain these 3 concepts with CLEAR differences between them?
(A) Build
(B) Deploy
(C) Process

As far as I know, the stages are like that : build, then deploy, then process. Also, it seems
to me that those operations do not create objects inside 'Relational' database, but create
objects (binary and text, with text files usually in XMLA programming language) in the
related project's folders and subfolders. Any good explanation is appreciated.

Bernaridho

1 & 2: The cube is an internal object used as a data structures in processing the mining structure and models. It is not visible externally and should be ignored. The only reason the cube is visible during processing is to surface potential processing errors, such as type mismatches.

3: Build - build takes the project's objects and builds a script that can be sent to the Analysis Services server. As with any "build" process, validation occurs to verify if any ovbious errors are present in the project

Deploy - Deploy sends the project to a server and creates any objects in that project on the server. Since the unit of deploymet is a database, any same-named database will be overwritten.

Process: Process is when the objects actually consume data and become useful. Prior to processing, the objects are "empty" and con not be queired.

|||Hi Jamie,

Thank you for replying. I'd like to add something about 'Process' that I found in your
book 'Data Mining with SQL Server 2005'. In the context of Data Mining, Process
also means 'Train'. That is, it's during 'Process' that the Analysis Service (background
process) trains the mining model. Hopefully this is useful for other member of this forum.

Thank you,

Bernaridho

Cube back up

dear all
i have a list of 7 cubes on the production server and i intend to bring the back up of 1 cube to my local system(w/o data,just the structure).I took the xmla file by scripting that respective cube and brought that xmla file to my local system. tell me wat i need to do to restore this xmla file to get the structure of my cube

Cheers
sid

If you just need the structure I would suggest scripting the whole database and then running the script on your local server and deleting any cubes that you don't need. This is because you need to have a database to script the cube into and you need to bring the dsv, data sources and database dimensions along so that the cube will work.

I've been playing with a Powershell script to generate the xmla script for a single cube and all the dependant objects, but it's not quite finished. For the time being I would say to go with scripting the whole database and deleting what you don't need later.

csv matching problem

Dear all,

I have two table, both table have a col which stored the data in CSV format, ie "ab , bc, de", etc

I would like to select the item where "any one item" in first table is same as "any one item" on second table.

I have alread have the private CSV function which can convert the string and return as table. However, I find i cannot do it like this
select .... where dbo.CSVfunction(tableA.colA)

Thx
//CSV function
declare @.separator char(1)
set @.separator = ','

declare @.separator_position int
declare @.array_value varchar(1000)

set @.array = @.array + ','

while patindex('%,%' , @.array) <> 0
begin

select @.separator_position = patindex('%,%' , @.array)
select @.array_value = left(@.array, @.separator_position - 1)
select @.array_value = RTrim(@.array_value)
select @.array_value = LTrim(@.array_value)

Insert @.StringTable
Values (Cast(@.array_value as varchar(50)))

select @.array = stuff(@.array, 1, @.separator_position, '')
end
re @.separator char(1)
set @.separator = ','

declare @.separator_position int
declare @.array_value varchar(1000)

set @.array = @.array + ','

while patindex('%,%' , @.array) <> 0
begin

select @.separator_position = patindex('%,%' , @.array)
select @.array_value = left(@.array, @.separator_position - 1)
select @.array_value = RTrim(@.array_value)
select @.array_value = LTrim(@.array_value)

Insert @.StringTable
Values (Cast(@.array_value as varchar(50)))

select @.array = stuff(@.array, 1, @.separator_position, '')
end

The question is why are you storing data in CVS format in SQL Server when you can store the data as Char, Varchar, Nchar, Nvarchar, Text and Ntext or as Word file using Image. The reason CVS file have to be converted or tranformed with DTS before SQL server can use the file. Hope this helps.

Crytarl Report with Dabase access

dear all ,

i use crystal report from dot net 2003 (i use dotnet version architect).
with database access.

and i have problem to set parameter for query access in crystal report.

if i use database mssqlserver 2000 in crystal report i just select store procedure and the parameter for store precedure will otomatically add in crystal report.

sampel query store procedure like this

select * from tblcustomer
where CustName = @.custName

the parameter @.CustName otomatically add in crystal report.

how to implament that if i use database access.
so i can send a value for @.Custname from my applicationAs i have worked for VB.net so i'll how i used to send parameter value externally
first u need to define
Dim pv As New CrystalDecisions.Shared.ParameterValues
Dim pdv As New CrystalDecisions.Shared.ParameterDiscreteValue

pdv.Value = EntityCode
pv.Add(pdvScriptCode1)

RptViewer.DataDefinition.ParameterFields("@.Customer").ApplyCurrentValues(pv)

thats it...
Try it out n tell me the result whther its working......