Link to home
Start Free TrialLog in
Avatar of svasilakos
svasilakos

asked on

Grouping transactions by dd/mm/yyyy using SQL

Simple problem that I'm not sure what to do. I very new to TSQL . Anyway.  I have a stored procedure I am writting and I want to rollup some date into each day of the month and display it as follows

Date           PresentNumber    PresentAmount  ReturnNumber ReturnAmount
------            -----------------   ------------------------ ---------------- ---------------
08/01/07          10                      3500.00                 4                   300.00
08/02/07           8                       1600.00                 0                     0.00

I've tried a ccouple different date selection and group by statements but the only one I can get to work is below. The problem is this only displays the  "dd"  , 1, 2, 3 etc...

I'm know the statement is simple but I'm not sure how and I'm running out of time.

I included the whole query which is a little long, sorry.

------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_REPORT_DailySummary_PAPER]
      -- Add the parameters for the stored procedure here
      @sinceWhen datetime,
      @untilWhen datetime,      
      @siteid int,
                     @transtype int
AS
BEGIN
SET NOCOUNT ON;
create table #reptable(
        DayofMonth int,
              PresentNumber int,
        PresentAmount smallmoney,
            ReturnNumber int,
            ReturnAmount smallmoney,
        )
 
insert into #reptable (DayofMonth,PresentNumber,PresentAmount,ReturnNumber,ReturnAmount)
 
select
    DATEPART (dd, transactiondate) as DayofMonth,
-- checks presented

sum(case when (Checks.dsdrCheckStatus = 1 or Checks.dsdrCheckStatus = 13) then 1
         when (Checks.dsdrCheckStatus = 2 or Checks.dsdrCheckStatus = 14) then 1
         when (Checks.dsdrCheckStatus = 3 or Checks.dsdrCheckStatus = 15) then 1
         else 0 end),

sum(case when (Checks.dsdrCheckStatus = 1 or Checks.dsdrCheckStatus = 13) then checks.dsdrAmount
         when (Checks.dsdrCheckStatus = 2 or Checks.dsdrCheckStatus = 14) then checks.dsdrAmount
         when (Checks.dsdrCheckStatus = 3 or Checks.dsdrCheckStatus = 15) then checks.dsdrAmount
         else 0 end),

sum(case when (Checks.dsdrCheckStatus = 4 or Checks.dsdrCheckStatus = 16) then 1
         when (Checks.dsdrCheckStatus = 5 or Checks.dsdrCheckStatus = 17) then 1
         when (Checks.dsdrCheckStatus = 6 or Checks.dsdrCheckStatus = 18) then 1
         else 0 end),

sum(case when (Checks.dsdrCheckStatus = 4 or Checks.dsdrCheckStatus = 16) then checks.dsdrAmount
         when (Checks.dsdrCheckStatus = 5 or Checks.dsdrCheckStatus = 17) then checks.dsdrAmount
         when (Checks.dsdrCheckStatus = 6 or Checks.dsdrCheckStatus = 18) then checks.dsdrAmount
         else 0 end)

FROM
TerminalInfo
INNER JOIN Site ON TerminalInfo.SiteID = Site.SiteID
INNER JOIN Trans ON TerminalInfo.TerminalID = Trans.TerminalID
INNER JOIN Checks ON Checks.transactionID = Trans.TransactionID

WHERE
    Site.SiteID = @siteID and
   (Trans.TransactionDate >= @sinceWhen AND
      Trans.TransactionDate < @untilWhen) AND
      Checks.checkType = 1 AND Checks.checkCountry = 0  
GROUP BY DATEPART (dd, transactiondate)

order by dayofmonth
if (@transtype = 1)
begin
--round and cast function is for percentage calculation
   select
            DayofMonth,PresentNumber,PresentAmount,ReturnNumber,ReturnAmount
    from #reptable
end
drop table #reptable
END



Avatar of appari
appari
Flag of India image

so you want to add current month and year to the dayofmonth returned from the query?
if that the case try like this

if (@transtype = 1)
begin
--round and cast function is for percentage calculation
   select
      convert(datetime, convert(varchar(6),getdate(), 112) +  right('00' + convert(varchar(2), DayofMonth),2) , 112)
            DayofMonth,PresentNumber,PresentAmount,ReturnNumber,ReturnAmount
    from #reptable
end


Avatar of svasilakos
svasilakos

ASKER

Thanks that works.  I'd love to strip off the time portion from the date.

The date show's not as

08/01/2007 00:00:00

Not a big deal I cam possiable strip it in C, but it would be nice.
ASKER CERTIFIED SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David Todd
Hi,

try this:
select convert( varchar( 10 ), getdate(), 121 )

Regards
  David
Thank you very much. You Experts are the best!!
Whoops. I just noticed my results are displayed wrong.
Here is the statement:
select
      convert(varchar(10),  convert(datetime, convert(varchar(6),getdate(), 112) +  right('00' + convert(varchar(2), DayofMonth),2) , 112),101)Dayofmonth,PresentNumber,PresentAmount,ReturnNumber,ReturnAmount
    from #reptable

If I put in a time period across months it displays everything as one month. For example
If my date range is 07/30/2007 thru 08/02/2007 than the dates are displayed as follows

08/01/2007
08/02/2007
08/30/2007
08/31/2007









so you mean you want to display the date as it is. in that case why are you grouping by
GROUP BY DATEPART (dd, transactiondate)?
Becuase I do not know what I am doing :-(. If I group by Trans.transactiondate and change the select statement for the Insert statement to be Trans.TransactionDate,  then my results are like this

07/30/2007
07/30/2007
07/31/2007
07/31/2007
07/31/2007
and so on.......

-Steve


so you want one record for each date?
try this

ALTER PROCEDURE [dbo].[sp_REPORT_DailySummary_PAPER]
      -- Add the parameters for the stored procedure here
      @sinceWhen datetime,
      @untilWhen datetime,      
      @siteid int,
                     @transtype int
AS
BEGIN
SET NOCOUNT ON;
create table #reptable(
        DayofMonth char(10),
              PresentNumber int,
        PresentAmount smallmoney,
            ReturnNumber int,
            ReturnAmount smallmoney,
        )
 
insert into #reptable (DayofMonth,PresentNumber,PresentAmount,ReturnNumber,ReturnAmount)
 
select
    convert(varchar(10), transactiondate,101) as DayofMonth,
-- checks presented

sum(case when (Checks.dsdrCheckStatus = 1 or Checks.dsdrCheckStatus = 13) then 1
         when (Checks.dsdrCheckStatus = 2 or Checks.dsdrCheckStatus = 14) then 1
         when (Checks.dsdrCheckStatus = 3 or Checks.dsdrCheckStatus = 15) then 1
         else 0 end),

sum(case when (Checks.dsdrCheckStatus = 1 or Checks.dsdrCheckStatus = 13) then checks.dsdrAmount
         when (Checks.dsdrCheckStatus = 2 or Checks.dsdrCheckStatus = 14) then checks.dsdrAmount
         when (Checks.dsdrCheckStatus = 3 or Checks.dsdrCheckStatus = 15) then checks.dsdrAmount
         else 0 end),

sum(case when (Checks.dsdrCheckStatus = 4 or Checks.dsdrCheckStatus = 16) then 1
         when (Checks.dsdrCheckStatus = 5 or Checks.dsdrCheckStatus = 17) then 1
         when (Checks.dsdrCheckStatus = 6 or Checks.dsdrCheckStatus = 18) then 1
         else 0 end),

sum(case when (Checks.dsdrCheckStatus = 4 or Checks.dsdrCheckStatus = 16) then checks.dsdrAmount
         when (Checks.dsdrCheckStatus = 5 or Checks.dsdrCheckStatus = 17) then checks.dsdrAmount
         when (Checks.dsdrCheckStatus = 6 or Checks.dsdrCheckStatus = 18) then checks.dsdrAmount
         else 0 end)

FROM
TerminalInfo
INNER JOIN Site ON TerminalInfo.SiteID = Site.SiteID
INNER JOIN Trans ON TerminalInfo.TerminalID = Trans.TerminalID
INNER JOIN Checks ON Checks.transactionID = Trans.TransactionID

WHERE
    Site.SiteID = @siteID and
   (Trans.TransactionDate >= @sinceWhen AND
      Trans.TransactionDate < @untilWhen) AND
      Checks.checkType = 1 AND Checks.checkCountry = 0  
GROUP BY     convert(varchar(10), transactiondate,101)
order by dayofmonth

if (@transtype = 1)
begin
--round and cast function is for percentage calculation
   select
            DayofMonth,PresentNumber,PresentAmount,ReturnNumber,ReturnAmount
    from #reptable
end
drop table #reptable
END
 
Yes, I'm trying to sum up stuff for a day and show the totals for each day
Perfect!! Thank you