Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Just one row for each patientid 2 Expand / Collapse
Author
Message
Posted Saturday, February 15, 2014 5:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:14 AM
Points: 96, Visits: 159
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


Post #1541840
Posted Saturday, February 15, 2014 6:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:13 PM
Points: 2,933, Visits: 2,954
The current result set from the sql produces


Can you send the sql statement that produces the result set?




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1541841
Posted Saturday, February 15, 2014 8:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,900, Visits: 7,138
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"
Post #1541851
Posted Saturday, February 15, 2014 10:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:14 AM
Points: 96, Visits: 159
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
Post #1541869
Posted Saturday, February 15, 2014 12:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:13 PM
Points: 2,933, Visits: 2,954
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,
SQL Server developer at Seavus
www.seavus.com
Post #1541877
Posted Sunday, February 16, 2014 3:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:14 AM
Points: 96, Visits: 159
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 

Post #1541893
Posted Sunday, February 16, 2014 3:37 AM This worked for the OP Answer marked as solution
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:14 AM
Points: 96, Visits: 159
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.
Post #1541894
Posted Sunday, February 16, 2014 9:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,900, Visits: 7,138
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"
Post #1541907
Posted Sunday, February 16, 2014 9:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:14 AM
Points: 96, Visits: 159
sorry, I must have misunderstood. have a good weekend too!
Post #1541908
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse