Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Grouping transactions by dd/mm/yyyy using SQL

Posted on 2007-08-08
12
Medium Priority
?
172 Views
Last Modified: 2010-03-20
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



0
Comment
Question by:svasilakos
  • 6
  • 5
12 Comments
 
LVL 39

Expert Comment

by:appari
ID: 19659074
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


0
 

Author Comment

by:svasilakos
ID: 19659109
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.
0
 
LVL 39

Accepted Solution

by:
appari earned 1000 total points
ID: 19659171
try

if (@transtype = 1)
begin
--round and cast function is for percentage calculation
   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
end
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 35

Expert Comment

by:David Todd
ID: 19659175
Hi,

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

Regards
  David
0
 

Author Comment

by:svasilakos
ID: 19659179
Thank you very much. You Experts are the best!!
0
 

Author Comment

by:svasilakos
ID: 19662101
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









0
 
LVL 39

Expert Comment

by:appari
ID: 19662247
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)?
0
 

Author Comment

by:svasilakos
ID: 19662399
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


0
 
LVL 39

Expert Comment

by:appari
ID: 19662434
so you want one record for each date?
0
 
LVL 39

Expert Comment

by:appari
ID: 19662456
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
 
0
 

Author Comment

by:svasilakos
ID: 19662462
Yes, I'm trying to sum up stuff for a day and show the totals for each day
0
 

Author Comment

by:svasilakos
ID: 19662537
Perfect!! Thank you
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question