Help with an SQL statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with an SQL statement

Author

Topic

rweinstein
Starting Member

3 Posts

Posted - 2004-04-16 : 11:53:07

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.

P.S.-I am not using the "Count" reserved word for anything other than the column title and this is not the cause of the problem.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-16 : 12:07:56

post some sample input data and some sample (matching and accurate) expected results.....to speed up the process of getting a solution...and also some DDL of the 2 tables involved.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-16 : 12:50:11

SUM(case when T1."Type" = 'Meal' then 1 else 0 end)

RobinCox
Starting Member

4 Posts

Posted - 2004-04-16 : 19:39:24

What about this as a stab in the dark
-- DELCARE VARIABLES FOR PERIOD OF ANALYSIS
DECLARE @DateFrom AS SMALLDATETIME, @DateTO AS SMALLDATETIME

-- SET THE PERIOD OF ANALYSIS
SET @DateFrom = CONVERT( SMALLDATETIME, '1/1/2004', 103 )
SET @DateTo = CONVERT( SMALLDATETIME, '1/4/2004', 103 )

-- RETRIEVE DATA AND GROUP THE COUNT OF MEALS
-- BY DAY BY MONTH BY YEAR
SELECT DATEPART(YEAR, t1."Date") AS [Year] ,
DATEPART(MONTH, t1."Date") AS [Month] ,
DATEPART(DAY, t1."Date") AS [Day] ,
SUM( CASE
WHEN t1."Type" = 'Meal' THEN 1
ELSE 0
END
) AS [Meals Per Day] -- This will give you the number of
-- meals on that specific day

FROM t1

WHERE t1."Date" BETWEEN @DateFrom AND @DateTo

GROUP BY DATEPART(YEAR, t1."Date") ,
DATEPART(MONTH, t1."Date") ,
DATEPART(DAY, t1."Date")

Hope I understood your question correctly.

rweinstein
Starting Member

3 Posts

Posted - 2004-04-16 : 22:30:47

I looked through your statement and I don't think that is quite what I need. Here is an example:

My raw data looks like this

Date Type
20040101 Meal
20040101 Meal
20040101 Meal

I want the SQL to add a Count column and the Fact table data should look like this (one count per day).

Date Type Count
20040101 Meal 1
20040101 Meal 0
20040101 Meal 0

The only way I have been able to come close is to have a count of '1' for each line item, or to have to sum all the meals and have only one line per day. Is this possible to create without having to sum?

Thanks. Sorry I wasn't more clear.

RobinCox
Starting Member

4 Posts

Posted - 2004-04-17 : 01:29:21

Cut and past the following and see if that matches your sample output..

USE tempdb
GO

/****************** CREATE SAMPLE DATA ******************/
SET NOCOUNT ON

IF NOT EXISTS( SELECT * FROM tempdb.dbo.sysobjects WHERE [Name] = '##MySampleData' )
BEGIN
CREATE TABLE dbo.##MySampleData
(
[Date] SMALLDATETIME ,
[Type] VARCHAR(20)
)

CREATE INDEX IX_SampleData ON dbo.##MySampleData( [Date], [Type] )
END
ELSE
TRUNCATE TABLE dbo.##MySampleData

DECLARE @i AS SMALLINT

SET @i = 1

WHILE @i <= 10
BEGIN

if @i % 2 = 0

INSERT INTO dbo.##MySampleData
SELECT '20040101' ,
'Meal'

ELSE

INSERT INTO dbo.##MySampleData
SELECT '20040101' ,
'Drink'

Set @i = @i + 1

END

IF NOT EXISTS( SELECT * FROM tempdb.dbo.sysobjects WHERE [Name] = '##MyFactTable' )
BEGIN
CREATE TABLE dbo.##MyFactTable
(
[Date] SMALLDATETIME ,
[Type] VARCHAR(20) ,
[Count] TINYINT
)

CREATE INDEX IX_MyFactTable ON dbo.##MyFactTable( [Date], [Type], [Count] )
END
ELSE
TRUNCATE TABLE dbo.##MyFactTable
GO
/************** PROCESS THE RAW DATA TO CREATE THE FACT TABLE *************/
DECLARE @Date AS SMALLDATETIME, @Type AS VARCHAR(20)

DECLARE rsSet CURSOR FOR
SELECT [Date] ,
[Type]

FROM dbo.##MySampleData

OPEN rsSet

FETCH NEXT FROM rsSet INTO @Date, @Type

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO dbo.##MyFactTable
SELECT @Date ,
@Type ,
CASE
WHEN NOT EXISTS( SELECT *
FROM dbo.##MyFactTable
WHERE dbo.##MyFactTable.[Date] = @Date
AND
[Type] = @Type
AND
[Count] = 1 )
THEN 1
ELSE 0
END

FETCH NEXT FROM rsSet INTO @Date, @Type

END

CLOSE rsSet
DEALLOCATE rsSet

SELECT * FROM dbo.##MyFactTable

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-19 : 04:34:08

SELECT DISTINCT [Date],Type,1 [Count] FROM <tablename> WHERE Type = 'Meal'

;-]

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-19 : 04:42:52

Create Table items ([id] int identity(1,1) primary key, [date] int, type varchar(16),[count] int DEFAULT(0))

insert into items ([date],type)
values (20040101, 'Meal')
insert into items ([date],type)
values (20040101, 'Meal')
insert into items ([date],type)
values (20040101, 'Meal')

Update items set [count]=1
from (select min([id]) [id], [date], type from items where type='Meal' group by [date], type) Meals
where items.[id]=Meals.[id]

select * from items

;-]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources