First Record Selection 1

[DrHabi]

Hello,
I am trying t create a view where I am summing up a bunch of number then group location and items. What I am also trying to do is grab the first cost that is attached to my first record and store it for each item and location.
here is my select statement. Let me know if there is something I can do.
Thanks

SELECT some.intnum, activity.item, activity.location,
SUM(activity.qty1 + activity.qty3 + activity.qty5)
AS quantitychanged,
SUM(activity.value1 + activity.value3 + activity.value5)
AS valuechanged, cost( here is where I need to be able to grab first cost for each item and location and I am not sure how to do it)


FROM some INNER JOIN
activity ON activity.postdate > some.date2

GROUP BY activity.location,activity.item, some.intnum

 

[jbenson001]

Can you post some data that you are summing and the desired ouput results?

 

[DrHabi]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwDailyChange]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwDailyChange]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwDailyChange
AS
SELECT dbo.reportheaders.intnum, dbo.ingactsummary.item, dbo.ingactsummary.location,
SUM(dbo.ingactsummary.qty1 + dbo.ingactsummary.qty2 - dbo.ingactsummary.qty3 + dbo.ingactsummary.qty4 - dbo.ingactsummary.qty5)
AS quantitychanged,
SUM(dbo.ingactsummary.value1 + dbo.ingactsummary.value2 - dbo.ingactsummary.value3 + dbo.ingactsummary.value4 - dbo.ingactsummary.value5)
AS valuechanged

FROM dbo.reportheaders INNER JOIN
dbo.ingactsummary ON dbo.ingactsummary.postdate > dbo.reportheaders.date2

GROUP BY dbo.ingactsummary.location,dbo.ingactsummary.item, dbo.reportheaders.intnum
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


here is the table I am referencing

[dbo].[ingactsummary] (
[item] [int] NOT NULL ,
[location] [int] NOT NULL ,
[postdate] [datetime] NOT NULL ,
[qty1] [real] NULL ,
[qty2] [real] NULL ,
[qty3] [real] NULL ,
[qty4] [real] NULL ,
[qty5] [real] NULL ,
[qty6] [real] NULL ,
[qty7] [real] NULL ,
[qty8] [real] NULL ,
[value1] [money] NULL ,
[value2] [money] NULL ,
[value3] [money] NULL ,
[value4] [money] NULL ,
[value5] [money] NULL ,
[value6] [money] NULL ,
[value7] [money] NULL ,
[value8] [money] NULL ,
[useq2] [real] NULL ,
[useq3] [real] NULL ,
[useq4] [real] NULL ,
[usev2] [money] NULL ,
[usev3] [money] NULL ,
[usev4] [money] NULL ,
[currentcost] [real] NULL

what I need to get is the first current cost closest to my date selected. for each item and location

 

[amrita418]

Try this:

select         b.intnum,  
               b.item, 
               b.location, 
               b.quantitychanged, 
               b.valuechanged, 
               a.currentcost
from           dbo.ingactsummary a,
               (SELECT        dbo.reportheaders.intnum intnum,
                              dbo.ingactsummary.item item,
                              dbo.ingactsummary.location location,
                              SUM(dbo.ingactsummary.qty1 + dbo.ingactsummary.qty2 - dbo.ingactsummary.qty3 + dbo.ingactsummary.qty4 - dbo.ingactsummary.qty5)  AS quantitychanged, 
                              SUM(dbo.ingactsummary.value1 + dbo.ingactsummary.value2 - dbo.ingactsummary.value3 + dbo.ingactsummary.value4 - dbo.ingactsummary.value5)  AS valuechanged,
                              MIN(dbo.ingactsummary.postdate) as PostDate
               FROM            dbo.reportheaders
                               INNER JOIN dbo.ingactsummary 
                               ON  dbo.ingactsummary.postdate >  dbo.reportheaders.date2
               GROUP BY       dbo.reportheaders.intnum,
                              dbo.ingactsummary.item, 
                              dbo.ingactsummary.location) b
where          a.item = b.item
               and a.location = b.location
               and a.postdate = b.postdate

PS: Code not tested so you might have to tweak with it.

Regards,
AA

 

[DrHabi]

Thanks that works great!! I'd buy ya a beer if you're around Emeryville.