Mike Yearwood
Toronto, Ontario, Canada
General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Environment versions
SQL Server:
SQL Server 2000
This is the kind of example I would like you to show.
packout
outdt, packsetid, shetkariid, qty ...
fill in some values!
frtinhdr
indate, packsetid ...
fill in some values.
result:
date, packsetid, shetkariid, inqty, outqty
fill in some values
>This is the exact stored procedure I am trying
>
>*********************************************************************
>
>CREATE proc get_packset_ledger_sp
> @p_packsetid char(13),
> @p_shetkariid char(13)
>as
>CREATE TABLE #MyTempTable (date datetime, packsetid char(13) , shetkariid char(13), inqty float, outqty float)
>
>INSERT INTO #MyTempTable select outdt as date ,packsetid, shetkariid, sum(qty) as inqty, 0.000 as outqty from packout where packsetid = @p_packsetid and shetkariid = @p_shetkariid group by packsetid, shetkariid, outdt order by packsetid, shetkariid, outdt
>
>INSERT INTO #MyTempTable select frtinhdr.indate as date , frtindtl.packsetid, frtinhdr.shetkariid, 0.000 as inqty, sum(frtindtl.boxrecdqty) as outqty
>from frtindtl left join frtinhdr on frtindtl.hdrid = frtinhdr.id
>where frtindtl.packsetid = @p_packsetid and frtinhdr.shetkariid = @p_shetkariid
>group by frtindtl.packsetid, frtinhdr.shetkariid,frtinhdr.indate order by frtindtl.packsetid, frtinhdr.shetkariid,frtinhdr.indate
>
>select date, packsetid, shetkariid, sum(isnull(inqty,0.000)) as inqty, sum(isnull(outqty,0)) as outqty from #MyTempTable group by date, packsetid, shetkariid order by date, packsetid, shetkariid
>GO
>
>*********************************************************************
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only