May 24, 2010 at 4:16 pm
All,
This is driving me crazy. It should be easy but is giving me multiple records per day. Please point me in the right direction.
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = '2/1/2010'
set @EndDate = '2/20/2010'
select TransDate, sum(CR) as dailyTotal, paytype,
case
when PayType = 1 then 'Cash'
when PayType = 2 then 'Credit'
when PayType = 3 then 'Check'
end as paytypedesc
from ledger
where CR > 0 and TransDate between @StartDate and @EndDate
group by DatePart(dayofyear, TransDate), paytype, CR, transdate
order by transDate
------------------------------------------
Output (now)
------------------------------------------
TransDate DailyTotal PayType
2010-02-01 10:05:01.00025.00 Cash
2010-02-01 10:28:04.00026.00 Cash
2010-02-01 10:57:12.00017.00 Cash
2010-02-01 11:00:59.000110.00Cash
2010-02-01 11:02:11.000104.00 Cash
2010-02-01 11:02:37.00080.00Cash
2010-02-01 11:11:12.00080.00Credit
2010-02-01 11:32:14.000148.00Check
--------------------------------------------
Output (desired)
--------------------------------------------
TransDate
2/1/2010
Check $45
Cash $234
Credit $282
2/2/2010 (etc...)
---------------------------------------------------------------
DDL
----------------------------------------------------------------
USE [ClientManager]
GO
/****** Object: Table [dbo].[Ledger] Script Date: 05/24/2010 15:09:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ledger](
[TransID] [int] IDENTITY(1,1) NOT NULL,
[TransDate] [datetime] NULL,
[CR] [money] NULL,
[DB] [money] NULL,
[ReasonCodeID] [int] NULL,
[PartID] [int] NULL,
[CaseID] [int] NULL,
[ReceiptNumber] [nvarchar](15) NULL,
[Description] [nvarchar](50) NULL,
[FeeID] [int] NULL,
[PayType] [int] NULL,
[CheckNum] [nvarchar](25) NULL,
[UserID] [int] NULL,
CONSTRAINT [PK_Ledger] PRIMARY KEY CLUSTERED
(
[TransID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-------------------------------------------------
Inserts
-------------------------------------------------
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'4.00'
,'3')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'4.00'
,'3')
----------------------------------------------------
With this data you should get
----------------------------------------------------
2/10/2010
Cash $45 (paytype 1)
Credit $450 (paytpe 2)
Check $4 (paytype 3)
2/11/2010
Cash $45 (paytype 1)
Credit $450 (paytpe 2)
Check $4 (paytype 3)
May 24, 2010 at 6:33 pm
When I ran your code this was my result:
TransDate dailyTotalpaytypepaytypedesc
2010-02-10 14:08:48.00045.00 1Cash
2010-02-10 14:08:48.000450.00 2Credit
2010-02-10 14:08:48.0004.00 3Check
2010-02-11 14:08:48.00045.00 1Cash
2010-02-11 14:08:48.000450.00 2Credit
2010-02-11 14:08:48.0004.00 3Check
--This is what you said it should be:
----------------------------------------------------
With this data you should get
----------------------------------------------------
2/10/2010 Cash $45 (paytype 1)
$450 (paytpe 2)
$4 (paytype 3)
2/11/2010 $45 (paytype 1)
$450 (paytpe 2)
$4 (paytype 3)
I see no difference in the output of your code and what you stated where the desired results. Is it me .. haven't I had enough coffee?
May 24, 2010 at 7:25 pm
I guess I needed to add more records to the insert to really see the result. I need more than one record to roll up.
Still not working
May 25, 2010 at 10:33 am
To me, the format of your desired output makes it unclear whether you want one row per date or one row for each date/payment method combination.
Either way your 'group by' clause simply specifies too many things. Your 'group by' should specify what a row of output represents. That is, if you want one row per date then your 'group by' should contain only something representing the date (in your case, datepart(dayofyear, TransDate)). If you want a row of output for each paytype within each date then include paytype in the 'group by' clause as well.
You have also included CR (the dollar value) and TransDate (which is a datetime), which makes it so that you will only get similar amounts recorded at the exact same time grouped together.
Once you get the 'group by' sorted out, you can deal with the actual data columns that you want to see in your output. One hint: even though you know a value in a column is the same for all the rows that you are grouping together, SQL Server does not; use max() or min() anyway to help SQL Server out by specifying a single value out of the set of identical values.
May 25, 2010 at 10:55 am
I have never been able to find a way to use Group by without specifying all of the select fields. I only want to group by the TransDate and PayType.
----------------
NEW INSERTS
____________________
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/11/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/10/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/11/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/10/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/11/2010 2:08:48 PM'
,'4.00'
,'3')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/10/2010 2:08:48 PM'
,'4.00'
,'3')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/11/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/10/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/11/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/10/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/11/2010 2:08:48 PM'
,'4.00'
,'3')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/10/2010 2:08:48 PM'
,'4.00'
,'3')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/11/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/10/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/11/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/10/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/11/2010 2:08:48 PM'
,'4.00'
,'3')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType])
VALUES
('2/10/2010 2:08:48 PM'
,'4.00'
,'3')
May 25, 2010 at 11:00 am
You are correct in what you say you get. The problem is that I am getting multiple rows for a TransDate. The data I provided only had one row per TransDate, my bad.
These should provide 3 rows per date so you can see what I am missing.
-------------------------------------------------
Inserts
-------------------------------------------------
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'4.00'
,'3')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'4.00'
,'3')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'4.00'
,'3')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'4.00'
,'3')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'45.00'
,'1')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'450.00'
,'2')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/11/2010 2:08:48 PM'
,'4.00'
,'3')
INSERT INTO Ledger
([TransDate]
,[CR]
,[PayType]
VALUES
('2/10/2010 2:08:48 PM'
,'4.00'
,'3')
May 25, 2010 at 11:01 am
Then that's what you group on. Every field other than your group fields must be a function like max(), min(), sum() or count(); this can include your case statement (ie: max(case... end))
May 25, 2010 at 11:08 am
Ya I knew that but I need those other values too?
One new tidbit is that if the time portion of the TransDate is different it sees the whole thing as a different date and breaks my code.
May 25, 2010 at 11:13 am
That's not new; I already covered that in my first response. Your use of datepart is a working solution to that problem.
May 25, 2010 at 11:16 am
Ahh you would think it does but it does not.
May 25, 2010 at 11:26 am
Of course it only works if all your data is in the same year. There are many ways to deal with converting datetime values to dates. Datepart() can be part of the solution; you can convert to a formatted string and back; even creative use of dateadd() could be part of the solution.
I usually use the conversion method because it seems the simplest to understand:
convert(datetime, convert(char(10), my_date, 101))
but I'm sure it's not the most efficient.
May 25, 2010 at 12:12 pm
I have them all in the same year and used your conversion so the query is now:
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = '2/1/2010'
set @EndDate = '2/20/2010'
select convert(datetime, convert(char(10), TransDate, 101)) as tDate, sum(CR) as dailyTotal, paytype,
case
when PayType = 1 then 'Cash'
when PayType = 2 then 'Credit'
when PayType = 3 then 'Check'
end as paytypedesc
from ledger
where CR > 0 and TransDate between @StartDate and @EndDate
group by convert(datetime, convert(char(10), TransDate, 101)), paytype, TransDate
order by transDate
but the output is still wrong...
2010-02-10 00:00:00.00090.001Cash
2010-02-10 00:00:00.0001350.002Credit
2010-02-10 00:00:00.00012.003Check
2010-02-10 00:00:00.00045.001Cash <-- Should be in the number above
2010-02-11 00:00:00.000135.001Cash
2010-02-11 00:00:00.000450.002Credit
2010-02-11 00:00:00.00012.003Check
2010-02-11 00:00:00.000450.002Credit <-- Should be in the number above
2010-02-11 00:00:00.000450.002Credit <-- Should be in the number above
May 25, 2010 at 12:20 pm
As long as you insist on grouping by TransDate, you will have a new row for each distinct value of TransDate, a datetime that includes time.
You should be using:
group by convert(datetime, convert(char(10), TransDate, 101)), paytype
NOT
group by convert(datetime, convert(char(10), TransDate, 101)), paytype, TransDate
May 25, 2010 at 12:22 pm
How do I get the transDate back on the query? or limit the query by the TransDate? Sorry I am very new to T-SQL.
May 25, 2010 at 12:25 pm
... and ordering by the full TransDate is asking for trouble too!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply