SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Just one row for each patientid 2


Just one row for each patientid 2

Author
Message
mattech06
mattech06
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 236
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

90003212 Anita Button Invisalign 2013-04-18 10:04:59.000 Google Hodgson 1 0.0000 0.0000 -5235.0000
90003212 Anita Button Invisalign 2013-04-18 10:04:59.000 Google Hodgson 2 5070.0000 0.0000 0.0000
90003212 Anita Button Invisalign 2013-04-18 10:04:59.000 Google Hodgson 3 2160.0000 0.0000 0.0000
90003212 Anita Button Invisalign 2013-04-18 10:04:59.000 Google Hodgson 13 220.0000 0.0000 0.0000
90003212 Anita Button Invisalign 2013-04-18 10:04:59.000 Google Hodgson 24 0.0000 -5070.0000 0.0000
90003212 Anita Button Invisalign 2013-04-18 10:04:59.000 Google Hodgson 24 0.0000 -220.0000 0.0000
90003212 Anita Button Invisalign 2013-04-18 10:04:59.000 Google Hodgson 26 0.0000 -2160.0000 0.0000

And I only want one row with the sum totals for the monetary columns

thanks
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10006 Visits: 5155
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
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11768 Visits: 7443
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
PatientID PatientFirstName PatientLastName dbStatusDesc dbAddDate LastName dbStaffLastName (SUM)
90003212 Anita Button Invisalign 2013-04-18 10:04:59.000 Google Hodgson 5235.00


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
mattech06
mattech06
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 236
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
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10006 Visits: 5155
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
dbPatID PatientFirstName patientlastname dbstatusdesc dbAddDate LastName dbStaffLastName Charges Payments
90003212 Anita Button Invisalign 2013-04-18 10:04:59.000 Google Hodgson 3725.0000 -3725.0000


Regards,
Igor

Igor Micev,
My blog: www.igormicev.com
mattech06
mattech06
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 236
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 


mattech06
mattech06
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 236
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.
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11768 Visits: 7443
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; They'll drag you down to their level and beat you with experience" ;-)
mattech06
mattech06
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 236
sorry, I must have misunderstood. have a good weekend too!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search