Need to add quantities

[snufse1]

Need to add gross and net quantities by load number (ie load). Cannot get it to work, still shows multiple row with same load number.

select "load",
date,
branded,
name,
sum(cast(gross as decimal(15,2))),
sum(cast(net as decimal(15,2)))
from [SFM-TP6000-1].TP6000.dbo.loadcomp as load1
inner join [SFM-TP6000-1].TP6000.dbo.product as prod1 on prod1.product = load1.branded
where load1.branded = 'ULS'
group by "load", date, branded, name

 

[markros]

What SQL Server version you're using?

SQL Server 2005 and up:

select "load",
date,
branded,
name,
sum(cast(gross as decimal(15,2))) OVER (Partition by Load1.Branded) as GrossTotal,
sum(cast(net as decimal(15,2))) OVER (Partition by Load1.Branded) as NetTotal
from [SFM-TP6000-1].TP6000.dbo.loadcomp as load1
inner join [SFM-TP6000-1].TP6000.dbo.product as prod1 on prod1.product = load1.branded

Not sure if you want to group by Branded or some other field.
where load1.branded = 'ULS'

 

[snufse1]

Need to group by load (which is the bol number)

select "load",
date,
branded,
name,
sum(cast(gross as decimal(15,2))) OVER (Partition by Load1."load") as GrossTotal,
sum(cast(net as decimal(15,2))) OVER (Partition by Load1."load") as NetTotal
from [SFM-TP6000-1].TP6000.dbo.loadcomp as load1
inner join [SFM-TP6000-1].TP6000.dbo.product as prod1 on prod1.product = load1.branded
where load1.branded = 'ULS'
and "load" = 16949

Result:
16949 2009-01-21 08:38:11.000 ULS DIESEL FUEL - 15 - MV 7528.00 7518.00
16949 2009-01-21 08:38:25.000 ULS DIESEL FUEL - 15 - MV 7528.00 7518.00
16949 2009-01-21 08:45:26.000 ULS DIESEL FUEL - 15 - MV 7528.00 7518.00
16949 2009-01-21 08:48:21.000 ULS DIESEL FUEL - 15 - MV 7528.00 7518.00
16949 2009-01-21 08:49:40.000 ULS DIESEL FUEL - 15 - MV 7528.00 7518.00

Still get multiple rows with same load number.

 

[markros]

I see, I was a bit confused with load in "".

I think you may want

select "load",
max(date) as Date,
max(branded) as Branded,
max(name) as Name,
sum(cast(gross as decimal(15,2))) OVER (Partition by Load1.Branded) as GrossTotal,
sum(cast(net as decimal(15,2))) OVER (Partition by Load1.Branded) as NetTotal
from [SFM-TP6000-1].TP6000.dbo.loadcomp as load1
inner join [SFM-TP6000-1].TP6000.dbo.product as prod1 on prod1.product = load1.branded group by "load

 

[snufse1]

Getting Incorrect syntax near the keyword 'load'.

select "load",
max(convert(varchar,Date,101)) as Date,
max(branded) as Branded,
max(name) as Name,
sum(cast(gross as decimal(15,2))) OVER (Partition by Load1.load) as GrossTotal,
sum(cast(net as decimal(15,2))) OVER (Partition by Load1.load) as NetTotal
from [SFM-TP6000-1].TP6000.dbo.loadcomp as load1
inner join [SFM-TP6000-1].TP6000.dbo.product as prod1 on prod1.product = load1.branded
where load1.branded = 'ULS'
and "load" = 16949

 

[markros]

Why do you need to put load in "" ? If you remove them, would it work by only grouping by Load and using max for all other fields?

 

[snufse1]

I think "load" is a protected word.

Think I got it working:

select [load],
convert(char(10), Date, 101),
branded,
name,
cast(sum(gross) as decimal(15, 2)) AS GrossTotal,
cast(sum(net) as decimal(15,2)) as NetTotal
from [SFM-TP6000-1].TP6000.dbo.loadcomp as load1
inner join [SFM-TP6000-1].TP6000.dbo.product as prod1 on prod1.product = load1.branded
where load1.branded = 'ULS'
and [load] = 16949
GROUP BY [load],
convert(char(10), Date, 101),
branded,
name