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_DailySumm ary_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, PresentAmo unt,Return Number,Ret urnAmount)
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,P resentAmou nt,ReturnN umber,Retu rnAmount
from #reptable
end
drop table #reptable
END
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_DailySumm
-- 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,
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,P
from #reptable
end
drop table #reptable
END
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
try this:
select convert( varchar( 10 ), getdate(), 121 )
Regards
David
try this:
select convert( varchar( 10 ), getdate(), 121 )
Regards
David
ASKER
Thank you very much. You Experts are the best!!
ASKER
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,Presen tNumber,Pr esentAmoun t,ReturnNu mber,Retur nAmount
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
Here is the statement:
select
convert(varchar(10), convert(datetime, convert(varchar(6),getdate
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)?
GROUP BY DATEPART (dd, transactiondate)?
ASKER
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
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_DailySumm ary_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, PresentAmo unt,Return Number,Ret urnAmount)
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,P resentAmou nt,ReturnN umber,Retu rnAmount
from #reptable
end
drop table #reptable
END
ALTER PROCEDURE [dbo].[sp_REPORT_DailySumm
-- 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,
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,P
from #reptable
end
drop table #reptable
END
ASKER
Yes, I'm trying to sum up stuff for a day and show the totals for each day
ASKER
Perfect!! Thank you
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
DayofMonth,PresentNumber,P
from #reptable
end