Intricate SQL Statement

Hi there at the forum,
I have a table with the following structure
CREATE TABLE [dbo].[Demand] (
[ArtNr] [varchar] (20) NOT NULL ,
[Plandate] [datetime] NOT NULL ,
[Dispo_element] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[AmountReq] [decimal](18, 3) NULL ,
[AmountAvail] [decimal](18, 3) NULL ,
[PlannedDelivery] [decimal](18, 3) NULL ,
[Target_Inventory] [decimal](18, 3) NULL
) ON [PRIMARY]
GO
The table contains data pertaining to supply control.
[ArtNr] designates the article number
[Plandate] shows the date of a movment
[Dispo_element] contains a code that classifies the row in the tabel as bein
g:
- Inventory
- Demand
- Delivery
[AmountReq] is the amount of a demand
[AmountAvail] is the amount available after a demand or a delivery had been
accounted for
[PlannedDelivery] is the amount that is to be delivered
[Target_Inventory] displays the missing amount in order to fulfill the
demands of a given day. Should the available amout be larger than the
demand, this column displays 0.
It is possible to have more than one delivery and more than one demand for
an articel on a any given day. Target
Data Example is
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '1.1.2005', 'inventory', 0, 100, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '2.1.2005', 'demand', 50, 50, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, 0, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, -50, 0, 50)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'supply', 0, 150, 100, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '5.1.2005', 'demand', 200, -50, 0, 50)
At the moment I show this data in a grid which means that for a day with 10
demands and 10 deliveries, 20 rows are shown.
My Question is: Is it possible to show a row per day, displaying the
consolidated data
Date Req Avail Deliv Target Type
1.1.05 0 100 0 0 inventory
2.1.05 50 50 0 0 demand
4.1.05 0 200 150 0 supply
4.1.05 100 100 0 0 demand
5.1.05 200 -100 0 -100 demand
Thank you very much for any help you might provide. I thought at first
about doing this by the means of some cursor and a temp table but the result
were just too slow. I hope
that it is possible to do this using SELECT statements without having to use
a cursor.
Best regardsYour table doesn't have a primary key! Hopefully your intention is to
fix that. Try:
SELECT artnr, plandate,
SUM(amountreq),
SUM(amountavail),
SUM(planneddelivery),
SUM(target_inventory),
dispo_element
FROM Demand
GROUP BY plandate, artnr, dispo_element
David Portas
SQL Server MVP
--