about a WHERE

Hi, I think this is an easy stuff but not for me.. I have an application
running on a production server.
In my DB I have a Sales Table like this
SaleDate Product Price$
2005-02-18 00:00:00.000 1 500
2005-02-18 00:00:00.000 1 100
2005-02-18 00:00:00.000 3 200
Using SP I'm getting a total by Date by product (e.g For SaleDate =
2005-02-18 Product 1 = 600, Product 3 = 200)
Now also I need to Store the times like this
SaleDate Product Price$
2005-02-18 09:37:39.000 1 500
2005-02-18 09:30:09.000 1 100
2005-02-18 14:20:10.000 3 200
How should I modify my SP to get the same..I tried this:
Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
but it does nothing, the SP return a total for each row
thks.If you want daily totals per product for the given data, try:
select
convert (datetime, convert (char (8), SaleDate, 112), 112) as SaleDate
, Product
, sum (Price) as Total
from
Sales
group by
convert (datetime, convert (char (8), SaleDate, 112), 112)
, Product
order by
SaleDate
, Product
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:7D87C046-13C6-4DE7-8932-4BC5BE369155@.microsoft.com...
Hi, I think this is an easy stuff but not for me.. I have an application
running on a production server.
In my DB I have a Sales Table like this
SaleDate Product Price$
2005-02-18 00:00:00.000 1 500
2005-02-18 00:00:00.000 1 100
2005-02-18 00:00:00.000 3 200
Using SP I'm getting a total by Date by product (e.g For SaleDate =
2005-02-18 Product 1 = 600, Product 3 = 200)
Now also I need to Store the times like this
SaleDate Product Price$
2005-02-18 09:37:39.000 1 500
2005-02-18 09:30:09.000 1 100
2005-02-18 14:20:10.000 3 200
How should I modify my SP to get the same..I tried this:
Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
but it does nothing, the SP return a total for each row
thks.|||This should answer your question..
given that the SaleDate is a datetime (you have seconds in your second
snippet)
DECLARE @.Date datetime
SET @.Date = '20050218' -- no seconds in here
... WHERE SaleDate>=@.Date AND SaleDate<@.Date+1
-- note the >= on LHS and the < on RHS to prevent overlaps
If possible, dont cast the column in your table to compare it as that
precludes the optimiser from using an index s (I hate it when the
optimiser does implicit casts on the column rather than the variable).
Mr Tea
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:7D87C046-13C6-4DE7-8932-4BC5BE369155@.microsoft.com...
> Hi, I think this is an easy stuff but not for me.. I have an application
> running on a production server.
> In my DB I have a Sales Table like this
> SaleDate Product Price$
> 2005-02-18 00:00:00.000 1 500
> 2005-02-18 00:00:00.000 1 100
> 2005-02-18 00:00:00.000 3 200
> Using SP I'm getting a total by Date by product (e.g For SaleDate =
> 2005-02-18 Product 1 = 600, Product 3 = 200)
> Now also I need to Store the times like this
> SaleDate Product Price$
> 2005-02-18 09:37:39.000 1 500
> 2005-02-18 09:30:09.000 1 100
> 2005-02-18 14:20:10.000 3 200
> How should I modify my SP to get the same..I tried this:
> Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
> CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
> but it does nothing, the SP return a total for each row
> thks.
>
>

A view of two tables

Hi I am trying to create a view from two tables.

Table 1 Sales

Cust_ID | Name | Genre | Sales Person | Last Order |
-
A123 | John | Fiction | Bill | 543A |
A123 | John | Sci-Fi | Bill | 534G |
B432 | Mark | Music | Ted | 748H |
C991 | Kevin | Sci-Fi | Bob | 017S |
C991 | Kevin | Classics | Bob | 663H |
C991 | Kevin | Fiction | Bob | 882G |
D912 | Syd | Music | Ted | 917F |
G941 | Paul | Sci-Fi | Bill | 991C |
G941 | Paul | Music | Bill | 947D |

Each customer will only have one record for each Genre.

Table 2 Acc_holders

Cust_ID | Name | Account No | Balance |
-
A123 | John | ABT110234 | 12.34 |
B432 | Mark | ADE145521 | 53.32 |
C991 | Kevin | NDU11E234 | 55.90 |
F723 | Andy | GGE124349 | 22.60 |
H882 | Sammy | NJW310264 | 12.99 |
I731 | Jane | HAT219845 | 55.23 |

cUST_ID is unique in this table.
A customer may be in either one or both tables

I am looking to create a view that will contain the following

Cust ID | Name | Has Account | Balance | Sci-Fi | Fiction | Music | Classics |

A123 | John | Y | 12.34 | Y | Y | N | N |
B432 | Mark | Y | 53.32 | N | N | Y | N |
C991 | Kevin | Y | 55.90 | Y | Y | N | N |
D912 | Syd | N | NULL | Y | Y | N | Y |
F723 | Andy | Y | 22.60 | N | N | Y | N |
G941 | Paul | N | NULL | N | N | N | N |
H882 | Sammy | Y | 12.99 | N | N | N | N |
I731 | Jane | Y | 55.23 | N | N | N | N |

Ok so I am trying to figure out how I can create a summary view that contains all my customers from both tables.
I need a single record for each customer and for it to show a balance and if they have a account from tabel 2 and if there are any genres from table 1

so far I have

SELECT DISTINCT
TOP (100) PERCENT dbo.SALES.CUST_ID,
dbo.SALES.Name
FROM dbo.SALES FULL OUTER JOIN
dbo.Acc_holders ON dbo.SALES.CUST_ID, = dbo.Acc_holders.CUST_ID, AND dbo.SALES.Name = dbo.Acc_holders.Name
ORDER BY dbo.SALES.CUST_ID

This gives me the first two columns but I can't figure out how to do the rest.

Any help would be very much appreciated.

Cheers.

The example below returns the results that I think you are expecting (in accordance with your source data).

Incidentally, if you are intending to use the code inside a View then it is not recommended to include an ORDER BY clause within the View, as you displayed in your example. If ordering of the results is required then you should use ORDER BY when SELECTing from the View instead.

Chris

DECLARE @.Sales TABLE

(

Cust_ID CHAR(4) NOT NULL,

[Name] VARCHAR(100) NOT NULL,

[Genre] VARCHAR(20) NOT NULL,

[Sales Person] VARCHAR(20),

[Last Order] CHAR(4) NOT NULL

)

DECLARE @.Acc_holders TABLE

(

Cust_ID CHAR(4) NOT NULL PRIMARY KEY,

[Name] VARCHAR(100) NOT NULL,

[Account No] CHAR(9) NOT NULL,

[Balance] MONEY NOT NULL

)

INSERT INTO @.Sales

SELECT 'A123','John','Fiction','Bill','543A' UNION

SELECT 'A123','John','Sci-Fi','Bill','534G' UNION

SELECT 'B432','Mark','Music','Ted','748H' UNION

SELECT 'C991','Kevin','Sci-Fi','Bob','017S' UNION

SELECT 'C991','Kevin','Classics','Bob','663H' UNION

SELECT 'C991','Kevin','Fiction','Bob','882G' UNION

SELECT 'D912','Syd','Music','Ted','917F' UNION

SELECT 'G941','Paul','Sci-Fi','Bill','991C' UNION

SELECT 'G941','Paul','Music','Bill','947D'

INSERT INTO @.Acc_holders

SELECT 'A123','John','ABT110234',12.34 UNION

SELECT 'B432','Mark','ADE145521',53.32 UNION

SELECT 'C991','Kevin','NDU11E234',55.9 UNION

SELECT 'F723','Andy','GGE124349',22.6 UNION

SELECT 'H882','Sammy','NJW310264',12.99 UNION

SELECT 'I731','Jane','HAT219845',55.23

SELECT t.Cust_ID,

t.[Name],

CASE WHEN EXISTS (SELECT 1 FROM @.Acc_holders a WHERE a.Cust_ID = t.Cust_ID) THEN 'Y' ELSE 'N' END AS [Has Account],

ac.Balance,

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Sci-Fi') THEN 'Y' ELSE 'N' END AS [Sci-Fi],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Fiction') THEN 'Y' ELSE 'N' END AS [Fiction],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Music') THEN 'Y' ELSE 'N' END AS [Music],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Classics') THEN 'Y' ELSE 'N' END AS [Classics]

FROM

(SELECT Cust_ID, [Name]

FROM @.Sales

UNION

SELECT Cust_ID, [Name]

FROM @.Acc_holders) t

LEFT JOIN @.Acc_Holders ac ON ac.Cust_ID = t.Cust_ID

|||

That worked a treat.

Thank you very much!

Kevin.

|||This should be faster

select
Cust_ID = coalesce(s.Cust_ID, a.Cust_ID),
[Name] = coalesce(s.[Name], a.[Name]),
[Has Account] = case when a.Cust_ID is null then 'N' else 'Y' end,
a.Balance,
[Sci-Fi] = coalesce(s.[Sci-Fi], 'N'),
[Fiction] = coalesce(s.[Fiction], 'N'),
[Music] = coalesce(s.[Music], 'N'),
[Classics] = coalesce(s.[Classics], 'N')
from
(
select Cust_ID, [Name],
[Sci-Fi] = max(case when Genre = 'Sci-Fi' then 'Y' else 'N' end),
[Fiction] = max(case when Genre = 'Fiction' then 'Y' else 'N' end),
[Music] = max(case when Genre = 'Music' then 'Y' else 'N' end),
[Classics] = max(case when Genre = 'Classics' then 'Y' else 'N' end)
from @.Sales
group by Cust_ID, [Name]
) s full outer join @.Acc_Holders a
on s.Cust_ID = a.Cust_ID|||

Thanks,

I have it working for now but when it comes to optimising the code I will give it a go.

Cheers,

Kevin.

A view of two tables

Hi I am trying to create a view from two tables.

Table 1 Sales

Cust_ID | Name | Genre | Sales Person | Last Order |
-
A123 | John | Fiction | Bill | 543A |
A123 | John | Sci-Fi | Bill | 534G |
B432 | Mark | Music | Ted | 748H |
C991 | Kevin | Sci-Fi | Bob | 017S |
C991 | Kevin | Classics | Bob | 663H |
C991 | Kevin | Fiction | Bob | 882G |
D912 | Syd | Music | Ted | 917F |
G941 | Paul | Sci-Fi | Bill | 991C |
G941 | Paul | Music | Bill | 947D |

Each customer will only have one record for each Genre.

Table 2 Acc_holders

Cust_ID | Name | Account No | Balance |
-
A123 | John | ABT110234 | 12.34 |
B432 | Mark | ADE145521 | 53.32 |
C991 | Kevin | NDU11E234 | 55.90 |
F723 | Andy | GGE124349 | 22.60 |
H882 | Sammy | NJW310264 | 12.99 |
I731 | Jane | HAT219845 | 55.23 |

cUST_ID is unique in this table.
A customer may be in either one or both tables

I am looking to create a view that will contain the following

Cust ID | Name | Has Account | Balance | Sci-Fi | Fiction | Music | Classics |

A123 | John | Y | 12.34 | Y | Y | N | N |
B432 | Mark | Y | 53.32 | N | N | Y | N |
C991 | Kevin | Y | 55.90 | Y | Y | N | N |
D912 | Syd | N | NULL | Y | Y | N | Y |
F723 | Andy | Y | 22.60 | N | N | Y | N |
G941 | Paul | N | NULL | N | N | N | N |
H882 | Sammy | Y | 12.99 | N | N | N | N |
I731 | Jane | Y | 55.23 | N | N | N | N |

Ok so I am trying to figure out how I can create a summary view that contains all my customers from both tables.
I need a single record for each customer and for it to show a balance and if they have a account from tabel 2 and if there are any genres from table 1

so far I have

SELECT DISTINCT
TOP (100) PERCENT dbo.SALES.CUST_ID,
dbo.SALES.Name
FROM dbo.SALES FULL OUTER JOIN
dbo.Acc_holders ON dbo.SALES.CUST_ID, = dbo.Acc_holders.CUST_ID, AND dbo.SALES.Name = dbo.Acc_holders.Name
ORDER BY dbo.SALES.CUST_ID

This gives me the first two columns but I can't figure out how to do the rest.

Any help would be very much appreciated.

Cheers.

The example below returns the results that I think you are expecting (in accordance with your source data).

Incidentally, if you are intending to use the code inside a View then it is not recommended to include an ORDER BY clause within the View, as you displayed in your example. If ordering of the results is required then you should use ORDER BY when SELECTing from the View instead.

Chris

DECLARE @.Sales TABLE

(

Cust_ID CHAR(4) NOT NULL,

[Name] VARCHAR(100) NOT NULL,

[Genre] VARCHAR(20) NOT NULL,

[Sales Person] VARCHAR(20),

[Last Order] CHAR(4) NOT NULL

)

DECLARE @.Acc_holders TABLE

(

Cust_ID CHAR(4) NOT NULL PRIMARY KEY,

[Name] VARCHAR(100) NOT NULL,

[Account No] CHAR(9) NOT NULL,

[Balance] MONEY NOT NULL

)

INSERT INTO @.Sales

SELECT 'A123','John','Fiction','Bill','543A' UNION

SELECT 'A123','John','Sci-Fi','Bill','534G' UNION

SELECT 'B432','Mark','Music','Ted','748H' UNION

SELECT 'C991','Kevin','Sci-Fi','Bob','017S' UNION

SELECT 'C991','Kevin','Classics','Bob','663H' UNION

SELECT 'C991','Kevin','Fiction','Bob','882G' UNION

SELECT 'D912','Syd','Music','Ted','917F' UNION

SELECT 'G941','Paul','Sci-Fi','Bill','991C' UNION

SELECT 'G941','Paul','Music','Bill','947D'

INSERT INTO @.Acc_holders

SELECT 'A123','John','ABT110234',12.34 UNION

SELECT 'B432','Mark','ADE145521',53.32 UNION

SELECT 'C991','Kevin','NDU11E234',55.9 UNION

SELECT 'F723','Andy','GGE124349',22.6 UNION

SELECT 'H882','Sammy','NJW310264',12.99 UNION

SELECT 'I731','Jane','HAT219845',55.23

SELECT t.Cust_ID,

t.[Name],

CASE WHEN EXISTS (SELECT 1 FROM @.Acc_holders a WHERE a.Cust_ID = t.Cust_ID) THEN 'Y' ELSE 'N' END AS [Has Account],

ac.Balance,

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Sci-Fi') THEN 'Y' ELSE 'N' END AS [Sci-Fi],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Fiction') THEN 'Y' ELSE 'N' END AS [Fiction],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Music') THEN 'Y' ELSE 'N' END AS [Music],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Classics') THEN 'Y' ELSE 'N' END AS [Classics]

FROM

(SELECT Cust_ID, [Name]

FROM @.Sales

UNION

SELECT Cust_ID, [Name]

FROM @.Acc_holders) t

LEFT JOIN @.Acc_Holders ac ON ac.Cust_ID = t.Cust_ID

|||

That worked a treat.

Thank you very much!

Kevin.

|||This should be faster

select
Cust_ID = coalesce(s.Cust_ID, a.Cust_ID),
[Name] = coalesce(s.[Name], a.[Name]),
[Has Account] = case when a.Cust_ID is null then 'N' else 'Y' end,
a.Balance,
[Sci-Fi] = coalesce(s.[Sci-Fi], 'N'),
[Fiction] = coalesce(s.[Fiction], 'N'),
[Music] = coalesce(s.[Music], 'N'),
[Classics] = coalesce(s.[Classics], 'N')
from
(
select Cust_ID, [Name],
[Sci-Fi] = max(case when Genre = 'Sci-Fi' then 'Y' else 'N' end),
[Fiction] = max(case when Genre = 'Fiction' then 'Y' else 'N' end),
[Music] = max(case when Genre = 'Music' then 'Y' else 'N' end),
[Classics] = max(case when Genre = 'Classics' then 'Y' else 'N' end)
from @.Sales
group by Cust_ID, [Name]
) s full outer join @.Acc_Holders a
on s.Cust_ID = a.Cust_ID|||

Thanks,

I have it working for now but when it comes to optimising the code I will give it a go.

Cheers,

Kevin.

A query that is over my head...

Question:
How do I return a list of items that matches one or more criteria that I pass in?

Background:
A user enters a sales lead (a company is looking for a place to have their event). That lead has a number of
criteria elements (start date, end date, city, region, maximum room rate, one or more amenities, etc. - more
details below) that should be used when trying to find Hotels that match that criteria. Obviously, some
criteria is more important than others (city, start and end date are more important than the maximum room
rate) - and it's unlikely that many (if any) of the Hotels will match *all* of the criteria entered by the
user. So, I'm looking to return a list of Hotels that match at least one of the criteria - if possible,
ordered by how many criteria elements match.

What makes this query particularly difficult, is that some of the criteria to match are stored in multiple
tables. For example, each Hotel has "Amenities" (Golf, Spa, etc.) - that are stored in a seperate table.
When a user enters a lead, they select which amenities they want to match. Also, a lead specifies a number
of rooms to block for each day between the Arrival and Departure date - these numbers can change from day
to day - but for this query - I think it's acceptible to get the largest number of rooms needed from any of
the days and compare that one number against the "MaxDailyRoomBlock" field of a Hotel (represented by the
"Property" table). Also, since a Hotel has different rates defined for each season, the query will have to
match the "MaxRate" against the rate of the correct season based on the Arrival and Departure dates. Also,
the rate can be within 20% of the stated "MaxRate".

Here are the following variables that will be passed into the query as criteria items:
RequestCity, RegionINDEID, ArrivalDate, DepartureDate, MaxRate, MaxTheaterSeating, MaxBanquetSeating,
MaxSchoolSeating, MaxBreakoutRooms, MaxRoomBlock

I know this is a huge post - and I sincerly appreciate any help you can provide.

DDL for Tables:
*In the DDL.txt attachment

Sample Data:
* In the data.txt attachment

Previous Attempts:
Unfortunately, I don't even know where to begin, so I haven't tried anything yet.

Expected Results:

PropertyID Name NumOfMatches
------ ------ ----
1 Marriot San Diego 5
2 Hilton San Diego 3
3 Hilton San Diego Downtown 2

Thanks in advance, again...Sounds like you need weighted values, not just a count of how many matches.
You are going to need to do this in a stored procedure, and it will likely require serveral steps depending upon the complexity of the schema and business requirements.
I strongly suggest you find a DBA proficient in SQL programming to help you with this, as it could end up being a big job for you.|||I can give some enlightment to ur problem.place the mandatory certeria in ur where clause,(eg:regionID,startdate,enddate) and put the other certeria in ur case statement as follows.
I have given weightage 1 for every certeria,u can change as per ur requirement(u can see at 'case statment')

NB:I didnt understand ur 20% of Maxrate.give me a example


-- details--
select l.LeadID,p.PropertyID,p.Name,
case when l.MaxDailyRoomBlock<=p.MaxDailyRoomBlock then 1
else 0 end as block,
case when l.MaxBreakoutRooms<=p.MaxBreakoutRooms then 1
else 0 end as breaks,
case when l.MaxTheaterSeating<=p.MaxTheaterStyleSeats then 1
else 0 end as Theater,
case when l.MaxBanquetSeating<=p.MaxBanquetStyleSeats then 1
else 0 end as Banquet,
case when l.MaxSchoolSeating<=p.MaxSchoolStyleSeats then 1
else 0 end as School,

(select count(*) from LeadAmenity la,
PropertyAmenity pa where l.LeadID=la.LeadID
and la.AmenityID=pa.AmenityID and p.PropertyID=pa.PropertyID) as AmenityCount
from
Property p,
Lead l





-- summary---
select LeadID,PropertyID,Name,(block+breaks+Theater+Banqu et+School+AmenityCount) as NumOfMatches
from
(
select l.LeadID,p.PropertyID,p.Name,
case when l.MaxDailyRoomBlock<=p.MaxDailyRoomBlock then 1
else 0 end as block,
case when l.MaxBreakoutRooms<=p.MaxBreakoutRooms then 1
else 0 end as breaks,
case when l.MaxTheaterSeating<=p.MaxTheaterStyleSeats then 1
else 0 end as Theater,
case when l.MaxBanquetSeating<=p.MaxBanquetStyleSeats then 1
else 0 end as Banquet,
case when l.MaxSchoolSeating<=p.MaxSchoolStyleSeats then 1
else 0 end as School,

(select count(*) from LeadAmenity la,
PropertyAmenity pa where l.LeadID=la.LeadID
and la.AmenityID=pa.AmenityID and p.PropertyID=pa.PropertyID) as AmenityCount
from
Property p,
Lead l
) as tm order by LeadID,PropertyID


come back if u have any doubts|||Thanks a ton for your reply, and sorry for being so slow to say so - I've been overwhelmed by the same project that prompted this question. It's helped me out a lot... thanks!

Chad

a query

hello all,

im trying to build a query...

the target of this query is to show the sales man, and the balance of the salesman of credits of a single plan.

the data is stored in the table as following,

RowID,SalesManID,PlanID,Credit,TransactionType

the balanace of of a salesman of a single plan is

the (sum(credit) where Transactiontype = 0 ) - (sum(credit) where transaction = 1 )

ofcourse for every single plan and sales man...

please if u need any question to have more detials just ask

and thank u for ur careTry this:

SELECT PlanID, SalesManID,
SUM(CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN -credit ELSE 0.0 END) as Balance
From YourTable
Group By PlanID,SalesManID

|||thank you

its a great way

but mmm

it seems that it is not working

as if all of them has transaction type 0, but in the database there is 0 and 1

so is there anything i can do|||Did you get the minus sign in from of the second condition?

SELECT PlanID, SalesManID,
SUM(CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN-credit ELSE 0.0 END) as Balance
From YourTable
Group By PlanID,SalesManID

If it still doesn't work then try this as a diagnostic. Just run it in query analyzer to see what is going on. You should just see the unaggregated values with some have positive values corresponding to transactiontype=0 and negative for transactiontype=1.
SELECT PlanID, SalesManID,Transactiontype,
CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN -credit ELSE 0.0 END as Balance
From YourTable
</code>

a price range dimension question

I'm using sql2k.
I'm providing a simplified scenario here.
I'm trying to build a fact table on sales (ie. item, price, quantity,
price*quantity).
I'd like build a cube that I can look up the price by range ($0-$5,
$5-10, $10-$15, etc...).
What's the best way to handle this? do i need a price range
dimension? or should i keep the price range in fact table?
I can't predict what new price will be added to sales, it could be
from 1 cent to any pricing, so if I were to build a price range
dimension, how would it look like?=== Steve L === wrote:
> I'm using sql2k.
> I'm providing a simplified scenario here.
> I'm trying to build a fact table on sales (ie. item, price, quantity,
> price*quantity).
> I'd like build a cube that I can look up the price by range ($0-$5,
> $5-10, $10-$15, etc...).
> What's the best way to handle this? do i need a price range
> dimension? or should i keep the price range in fact table?
> I can't predict what new price will be added to sales, it could be
> from 1 cent to any pricing, so if I were to build a price range
> dimension, how would it look like?
>
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I believe the price range would be considered more a criteria than a
dimension. The only price-range dimension table I could come up w/
would be something like this:
CREATE TABLE PriceRange (
range_code INT NOT NULL PRIMARY KEY,
start_value DECIMAL (11,2) NOT NULL,
end_value DECIMAL (11,2) NOT NULL
)
The fact table would hold the range_code. When you made the CUBE you'd
include the range_code. It might be faster to use range_codes if all
you're doing is a retrieval of data based on ranges. Probably, you
could include both range_code and price in the cube.
But, it make more sense to only use the price in the CUBE then you could
do SUMs and change the price range criteria for each query. Using the
PriceRange dimension - what happens when you want to change the range
criteria of a query? The PriceRange dimension would have to be rebuilt,
then the cube. Are you always going to be using the same price ranges?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQuVvxYechKqOuFEgEQJxJACgwYIakvQZsqvI
WzXp1z6A6aFyMN8An3VU
aJ07XYAO+lukEymGqGnn8aQe
=E0wk
--END PGP SIGNATURE--|||Hi Steve ,
This might solve your problem
Here the gap is 10 ,You can easily make it 5 ,qty can be changed to
price
SELECT LowRange,HiRange,COUNT(*)
FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
,HiRange=((qty - 1) / 10) * 10 + 10
FROM sales) AS ds
GROUP BY lowRange ,HiRange
Please let me know if it solved your purpose
With warm regards
Jatinder