February 15, 2014 at 5:54 am
Hi there,
I would like my result set to have one row for each patienid. We CAN lose the ledgertype if this is an issue. I've only included data for one patient but the TESTTransactions table would normally have loads of patients in.
my tables/data etc
/****** Object: Table [dbo].[TESTReferralKPIs] Script Date: 02/15/2014 11:32:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TESTReferralKPIs](
[RefID] [int] IDENTITY(1,1) NOT NULL,
[dbPatID] [varchar](8) NULL,
[dbPatLastName] [varchar](25) NULL,
[dbPatBirthday] [datetime] NULL,
[dbAddDate] [datetime] NULL,
[FirstName] [varchar](15) NULL,
[LastName] [varchar](25) NULL,
[dbStaffLastName] [varchar](25) NULL,
[dbStatusDesc] [varchar](25) NULL,
[dbOtherRefType] [smallint] NULL,
[RefTypeWord] [varchar](25) NULL,
CONSTRAINT [PK_TESTReferralKPIs] PRIMARY KEY CLUSTERED
(
[RefID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET QUOTED_IDENTIFIER ON
GO
INSERT INTO [OESuite1].[dbo].[TESTReferralKPIs]
([dbPatID],[dbPatLastName],[dbPatBirthday],[dbAddDate],[FirstName],[LastName],[dbStaffLastName],[dbStatusDesc],[dbOtherRefType], [RefTypeWord])
VALUES ('90003212','Button' ,'1964-03-29 00:03:00.000','2013-04-18 10:04:59.000' ,'','Google','Hodgson','Invisalign' ,1, 'OTHER')
GO
/****** Object: Table [dbo].[TESTTransactions] Script Date: 02/15/2014 11:54:54 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TESTTransactions]') AND type in (N'U'))
DROP TABLE [dbo].[TESTTransactions]
GO
USE [OESuite1]
GO
/****** Object: Table [dbo].[TESTTransactions] Script Date: 02/15/2014 11:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TESTTransactions](
[TransactionID] [int] NOT NULL,
[TransactionDate] [datetime] NOT NULL,
[PatientID] [int] NOT NULL,
[PatientFirstName] [varchar](50) NOT NULL,
[PatientLastName] [varchar](50) NOT NULL,
[Doctor] [varchar](50) NOT NULL,
[LedgerDescription] [varchar](100) NOT NULL,
[LedgerAmount] [money] NOT NULL,
[LedgerType] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TESTTransactions] Script Date: 02/15/2014 12:47:02 ******/
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (133763, CAST(0x0000A1AF00D75758 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'Card', -110.0000, 24)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (136199, CAST(0x0000A1D200B72DC0 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'PPP contract - Initial Fee (Contract)', 2535.0000, 2)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (137111, CAST(0x0000A1E300F8E65C AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'Card', -2535.0000, 24)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (137660, CAST(0x0000A1EE0001EC30 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'Standing Order', -135.0000, 26)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (138001, CAST(0x0000A1EE0001EC30 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'PPP contract - Contract Charge', 135.0000, 3)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (139524, CAST(0x0000A20D00023280 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'Standing Order', -135.0000, 26)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (139860, CAST(0x0000A20D00023280 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'PPP contract - Contract Charge', 135.0000, 3)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (141164, CAST(0x0000A22C000278D0 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'Standing Order', -135.0000, 26)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (142977, CAST(0x0000A24A0002BF20 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'Standing Order', -135.0000, 26)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (144929, CAST(0x0000A26900030570 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'Standing Order', -135.0000, 26)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (146768, CAST(0x0000A28700034BC0 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'Standing Order', -135.0000, 26)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (148245, CAST(0x0000A2A600004650 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'Standing Order', -135.0000, 26)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (149874, CAST(0x0000A2C500008CA0 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'Standing Order', -135.0000, 26)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (141495, CAST(0x0000A22C000278D0 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'PPP contract - Contract Charge', 135.0000, 3)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (143306, CAST(0x0000A24A0002BF20 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'PPP contract - Contract Charge', 135.0000, 3)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (145285, CAST(0x0000A26900030570 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'PPP contract - Contract Charge', 135.0000, 3)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (147113, CAST(0x0000A28700034BC0 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'PPP contract - Contract Charge', 135.0000, 3)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (148688, CAST(0x0000A2A600004650 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'PPP contract - Contract Charge', 135.0000, 3)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (150548, CAST(0x0000A2C500008CA0 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'PPP contract - Contract Charge', 135.0000, 3)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (133762, CAST(0x0000A1AF00D7724C AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'Private Exam Adult', 110.0000, 13)
INSERT [dbo].[TESTTransactions] ([TransactionID], [TransactionDate], [PatientID], [PatientFirstName], [PatientLastName], [Doctor], [LedgerDescription], [LedgerAmount], [LedgerType]) VALUES (136198, CAST(0x0000A1D200B72DC0 AS DateTime), 90003212, N'Anita', N'Button', N'Hodgson', N'PPP contract - Contract', 5235.0000, 1)
The current result set from the sql produces
90003212AnitaButtonInvisalign 2013-04-18 10:04:59.000GoogleHodgson10.00000.0000-5235.0000
90003212AnitaButtonInvisalign 2013-04-18 10:04:59.000GoogleHodgson25070.00000.00000.0000
90003212AnitaButtonInvisalign 2013-04-18 10:04:59.000GoogleHodgson32160.00000.00000.0000
90003212AnitaButtonInvisalign 2013-04-18 10:04:59.000GoogleHodgson13220.00000.00000.0000
90003212AnitaButtonInvisalign 2013-04-18 10:04:59.000GoogleHodgson240.0000-5070.00000.0000
90003212AnitaButtonInvisalign 2013-04-18 10:04:59.000GoogleHodgson240.0000-220.00000.0000
90003212AnitaButtonInvisalign 2013-04-18 10:04:59.000GoogleHodgson260.0000-2160.00000.0000
And I only want one row with the sum totals for the monetary columns
thanks
February 15, 2014 at 6:27 am
The current result set from the sql produces
Can you send the sql statement that produces the result set?
Igor Micev,My blog: www.igormicev.com
February 15, 2014 at 8:41 am
If you want only 1 record, you will need to remove the LedgerType (otherwise you will get a SUM for each record by Ledger Type) so you could try SELECT a.PatientID ,
a.PatientFirstName ,
a.PatientLastName ,
b.dbStatusDesc ,
b.dbAddDate ,
b.LastName ,
b.dbStaffLastName ,
SUM(a.LedgerAmount)
FROM TESTTransactions a
INNER JOIN dbo.TESTReferralKPIs b ON a.PatientID = b.dbPatID
GROUP BY PatientID ,
PatientFirstName ,
PatientLastName ,
b.dbStatusDesc ,
b.dbAddDate ,
b.LastName ,
b.dbStaffLastName This will give you
PatientIDPatientFirstNamePatientLastNamedbStatusDescdbAddDateLastNamedbStaffLastName(SUM)
90003212AnitaButtonInvisalign2013-04-18 10:04:59.000GoogleHodgson5235.00
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 15, 2014 at 10:59 am
sorry yeah, forgot the sql!
SELECT
r.dbPatID, t.PatientFirstName, t.patientlastname, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName,
SUM(CASE WHEN t.LedgerAmount > 0 AND t.LedgerType != 1 THEN t.LedgerAmount ELSE 0.00 END) AS Charges,
SUM(CASE WHEN t.LedgerAmount < 0 AND t.LedgerType != 1 THEN t.LedgerAmount ELSE 0.00 END) AS Payments
(CASE WHEN t.LedgerType = 1 THEN t.LedgerAmount ELSE 0.00 END) * -1 AS Contracts
FROM
TESTReferralKPIs r
LEFT JOIN TESTTransactions t ON t.PatientID = r.dbPatID
GROUP BY
r.dbPatID, t.PatientFirstName, t.patientlastname, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName, t.LedgerAmount
February 15, 2014 at 12:12 pm
Hi
What about this
r.dbPatID, t.PatientFirstName, t.patientlastname, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName,
SUM(CASE WHEN t.LedgerAmount > 0 AND t.LedgerType != 1 THEN t.LedgerAmount ELSE 0.00 END) AS Charges,
SUM(CASE WHEN t.LedgerAmount < 0 AND t.LedgerType != 1 THEN t.LedgerAmount ELSE 0.00 END) AS Payments
FROM
TESTReferralKPIs r
LEFT JOIN TESTTransactions t ON t.PatientID = r.dbPatID
GROUP BY
r.dbPatID, t.PatientFirstName, t.patientlastname, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName
--Output
dbPatIDPatientFirstNamepatientlastnamedbstatusdescdbAddDateLastNamedbStaffLastNameChargesPayments
90003212AnitaButtonInvisalign2013-04-18 10:04:59.000GoogleHodgson3725.0000-3725.0000
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
February 16, 2014 at 3:25 am
Thanks Igor, but unfortunately we don't have our Contracts column with that sql.
re
(CASE WHEN t.LedgerType = 1 THEN t.LedgerAmount ELSE 0.00 END) * -1 AS
February 16, 2014 at 3:37 am
I've solved this now simply by putting this statement within a SUM statement (the same as for charges and payments)
(CASE WHEN t.LedgerType = 1 THEN t.LedgerAmount ELSE 0.00 END) * -1 AS Contracts
that way we don't need to include the LedgerType or LedgerAmount in the select and group by's.
I'm not really sure why that works but it does.
February 16, 2014 at 9:11 am
MyDoggieJessie (2/15/2014)
If you want only 1 record, you will need to remove the LedgerType (otherwise you will get a SUM for each record by Ledger Type)
I believe that's what I originally suggested 🙂 Glad you got it figured out! Have a great weekend.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 16, 2014 at 9:18 am
sorry, I must have misunderstood. have a good weekend too!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply