CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
njtd
njtd

Reputation: 115

Need Help about format output of SQL Server stored procedure

Need help about format output of SQL Server stored procedure.

I want to loop check invoice amount day by day all 30 day of month.

This is my code

Declare @SDate Date
Declare @EDate Date

SET @SDate = '2014-11-01'
SET @EDate = '2014-11-30'

WHILE @SDate <= @EDate
BEGIN 
   --print @SDate
   select 
      (select sum(amt) 
       from VEntry
       where ACode = '111111' and Source = 'AP_DOC_AMT' 
         and VDate = @SDate) as INV,
     (select sum(AFDisAmt) 
      from DO_Item
      where Client_No = '999999'
        and DO_Date = @SDate) as INV_T 

   SET @SDate = DATEADD(D,1,@SDate)
END

This code is working but output show header for each day.

I want to show only one header as normal output.

enter image description here

Upvotes: 1

Views: 133

Answers (2)

radar
radar

Reputation: 13425

if you have a date values table ( one way is using recursive cte like below) you can do left join and group by to get the result you want.

Declare @SDate Date
Declare @EDate Date
SET @SDate = '2014-11-01'
SET @EDate = '2014-11-30'

;with cte 
as
( select @SDate as iDate 
  UNION ALL
  select dateadd(day,1,iDate) as iDate from 
  cte
  where iDate < @EDate
) 
select iDate as Date, ISNULL(sum(amt),0) as INV , ISNULL(sum(AFDisAmt),0) as INV_T 
from cte
left join VEntry 
on cte.iDate = VEntry.VDate
and ACode = '111111' and Source = 'AP_DOC_AMT'
left join DO_Item 
on cte.iDate = DO_Item.DO_Date
and Client_No = '999999'
group by cte.iDate

Upvotes: 1

DavidG
DavidG

Reputation: 118947

There is no need for any looping at all. Just use BETWEEN to get the data for all dates and JOIN the results together with a FULL OUTER JOIN:

Declare @SDate Date
Declare @EDate Date
SET @SDate = '2014-11-01'
SET @EDate = '2014-11-30'

SELECT COALECE(VEntries.VDate, DO_Items.DO_Date) AS Date
       VEntries.Amount,
       DOData.Amount
FROM
    (select VDate, 
            sum(amt) AS Amount
    from VEntry
    where ACode = '111111' 
    and Source = 'AP_DOC_AMT' 
    and VDate BETWEEN @SDate AND @EDate
    group by VDate) AS VEntries
FULL OUTER JOIN
    (select DO_Date,
            sum(AFDisAmt) AS Amount
    from DO_Item
    where Client_No = '999999'
    and DO_Date BETWEEN @SDate AND @EDate
    group by DO_Date) AS DO_Items
ON VEntries.VDate = DO_Items.DO_Date

Upvotes: 2

Related Questions