November 4, 2009 at 5:18 am
i track diskspaces of my servers into the database
Now, sometimes the script runs multiple days a day and a server can have multiple disks,
What i need as result is
the last data for all disks per server
This is,
for each server, show me te latest caputered data for each disk on a certain date
For example:
For server SRV_ID 17 : captured date 26/10/2009 this would be the records with DSP_ID :41,42,43
CREATE TABLE [Informatica].[tblDiskSpace](
[DSP_ID] [int] IDENTITY(1,1) NOT NULL,
[SRV_ID] [int] NULL,
[DSP_DISKNAAM] [varchar](2) NULL,
[DSP_MAXSPACE_GB] [numeric](14, 4) NULL,
[DSP_SPACEFREE_GB] [numeric](14, 4) NULL,
[DSP_PROCENTFREE] [numeric](14, 4) NULL,
[DSP_DATUM] [datetime] NOT NULL,
CONSTRAINT [PK_tblDiskSpace] PRIMARY KEY CLUSTERED
(
[DSP_ID] 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 IDENTITY_INSERT [Informatica].[tblDiskSpace] ON
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (1, 13, N'C:', CAST(297.0775 AS Numeric(14, 4)), CAST(237.9588 AS Numeric(14, 4)), CAST(80.0999 AS Numeric(14, 4)), CAST(0x00009CAA010B5764 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (2, 13, N'D:', CAST(1.0015 AS Numeric(14, 4)), CAST(0.9765 AS Numeric(14, 4)), CAST(97.4976 AS Numeric(14, 4)), CAST(0x00009CAA010B5793 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (3, 13, N'F:', CAST(465.7593 AS Numeric(14, 4)), CAST(24.6400 AS Numeric(14, 4)), CAST(5.2903 AS Numeric(14, 4)), CAST(0x00009CAA010B57B8 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (4, 20, N'C:', CAST(10.0044 AS Numeric(14, 4)), CAST(5.5811 AS Numeric(14, 4)), CAST(55.7860 AS Numeric(14, 4)), CAST(0x00009CAA010B5B08 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (5, 15, N'C:', CAST(103.7775 AS Numeric(14, 4)), CAST(15.9317 AS Numeric(14, 4)), CAST(15.3518 AS Numeric(14, 4)), CAST(0x00009CAA010B5EB7 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (6, 15, N'D:', CAST(8.0101 AS Numeric(14, 4)), CAST(0.4320 AS Numeric(14, 4)), CAST(5.3930 AS Numeric(14, 4)), CAST(0x00009CAA010B5EDC AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (7, 22, N'C:', CAST(29.0712 AS Numeric(14, 4)), CAST(15.3241 AS Numeric(14, 4)), CAST(52.7123 AS Numeric(14, 4)), CAST(0x00009CAA010B6265 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (8, 22, N'D:', CAST(38.6390 AS Numeric(14, 4)), CAST(20.8565 AS Numeric(14, 4)), CAST(53.9778 AS Numeric(14, 4)), CAST(0x00009CAA010B628F AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (9, 22, N'H:', CAST(299.9961 AS Numeric(14, 4)), CAST(197.2199 AS Numeric(14, 4)), CAST(65.7408 AS Numeric(14, 4)), CAST(0x00009CAA010B62B5 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (10, 18, N'C:', CAST(19.5332 AS Numeric(14, 4)), CAST(5.2291 AS Numeric(14, 4)), CAST(26.7706 AS Numeric(14, 4)), CAST(0x00009CAA010B6E7B AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (11, 18, N'D:', CAST(113.1914 AS Numeric(14, 4)), CAST(55.2936 AS Numeric(14, 4)), CAST(48.8497 AS Numeric(14, 4)), CAST(0x00009CAA010B6EA0 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (12, 18, N'S:', CAST(4.0039 AS Numeric(14, 4)), CAST(0.9832 AS Numeric(14, 4)), CAST(24.5565 AS Numeric(14, 4)), CAST(0x00009CAA010B6EC1 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (13, 14, N'C:', CAST(15.0067 AS Numeric(14, 4)), CAST(1.7058 AS Numeric(14, 4)), CAST(11.3669 AS Numeric(14, 4)), CAST(0x00009CAA010B74AB AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (14, 21, N'C:', CAST(4.9869 AS Numeric(14, 4)), CAST(1.3925 AS Numeric(14, 4)), CAST(27.9231 AS Numeric(14, 4)), CAST(0x00009CAA010B7818 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (15, 21, N'E:', CAST(199.9974 AS Numeric(14, 4)), CAST(177.6328 AS Numeric(14, 4)), CAST(88.8176 AS Numeric(14, 4)), CAST(0x00009CAA010B7839 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (16, 23, N'C:', CAST(45.0048 AS Numeric(14, 4)), CAST(1.8375 AS Numeric(14, 4)), CAST(4.0829 AS Numeric(14, 4)), CAST(0x00009CAA010B7C44 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (17, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2556 AS Numeric(14, 4)), CAST(17.1405 AS Numeric(14, 4)), CAST(0x00009CAE01009703 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (18, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE01009DC4 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (19, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE01009DFD AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (20, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2556 AS Numeric(14, 4)), CAST(17.1409 AS Numeric(14, 4)), CAST(0x00009CAE010138EF AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (21, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE01013927 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (22, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE0101394D AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (23, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2556 AS Numeric(14, 4)), CAST(17.1409 AS Numeric(14, 4)), CAST(0x00009CAE01018237 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (24, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE01018261 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (25, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE01018290 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (26, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2546 AS Numeric(14, 4)), CAST(17.1278 AS Numeric(14, 4)), CAST(0x00009CAE01046462 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (27, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE01046495 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (28, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE010464C4 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (29, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2546 AS Numeric(14, 4)), CAST(17.1278 AS Numeric(14, 4)), CAST(0x00009CAE010512A1 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (30, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE010512D9 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (31, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE0105130C AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (32, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2546 AS Numeric(14, 4)), CAST(17.1278 AS Numeric(14, 4)), CAST(0x00009CAE0105F0FA AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (33, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE0105F12E AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (34, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE0105F158 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (35, 20, N'C:', CAST(10.0044 AS Numeric(14, 4)), CAST(5.5689 AS Numeric(14, 4)), CAST(55.6648 AS Numeric(14, 4)), CAST(0x00009CAE0106295B AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (36, 15, N'C:', CAST(103.7775 AS Numeric(14, 4)), CAST(15.5394 AS Numeric(14, 4)), CAST(14.9738 AS Numeric(14, 4)), CAST(0x00009CAE01063131 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (37, 15, N'D:', CAST(8.0101 AS Numeric(14, 4)), CAST(0.4320 AS Numeric(14, 4)), CAST(5.3930 AS Numeric(14, 4)), CAST(0x00009CAE01063173 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (38, 22, N'C:', CAST(29.0712 AS Numeric(14, 4)), CAST(15.3224 AS Numeric(14, 4)), CAST(52.7065 AS Numeric(14, 4)), CAST(0x00009CAE01063518 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (39, 22, N'D:', CAST(38.6390 AS Numeric(14, 4)), CAST(20.7474 AS Numeric(14, 4)), CAST(53.6955 AS Numeric(14, 4)), CAST(0x00009CAE0106355A AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (40, 22, N'H:', CAST(299.9961 AS Numeric(14, 4)), CAST(197.2138 AS Numeric(14, 4)), CAST(65.7388 AS Numeric(14, 4)), CAST(0x00009CAE01063588 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (41, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2546 AS Numeric(14, 4)), CAST(17.1278 AS Numeric(14, 4)), CAST(0x00009CAE01063B23 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (42, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE01063B48 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (43, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE01063B72 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (44, 18, N'C:', CAST(19.5332 AS Numeric(14, 4)), CAST(6.8323 AS Numeric(14, 4)), CAST(34.9780 AS Numeric(14, 4)), CAST(0x00009CAE01063ED6 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (45, 18, N'D:', CAST(113.1914 AS Numeric(14, 4)), CAST(55.2156 AS Numeric(14, 4)), CAST(48.7807 AS Numeric(14, 4)), CAST(0x00009CAE01063F17 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (46, 18, N'S:', CAST(4.0039 AS Numeric(14, 4)), CAST(0.9832 AS Numeric(14, 4)), CAST(24.5565 AS Numeric(14, 4)), CAST(0x00009CAE01063F42 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (47, 14, N'C:', CAST(15.0067 AS Numeric(14, 4)), CAST(1.7019 AS Numeric(14, 4)), CAST(11.3410 AS Numeric(14, 4)), CAST(0x00009CAE010645C6 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (48, 21, N'C:', CAST(4.9869 AS Numeric(14, 4)), CAST(1.3900 AS Numeric(14, 4)), CAST(27.8736 AS Numeric(14, 4)), CAST(0x00009CAE0106497E AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (49, 21, N'E:', CAST(199.9974 AS Numeric(14, 4)), CAST(174.0268 AS Numeric(14, 4)), CAST(87.0145 AS Numeric(14, 4)), CAST(0x00009CAE010649AD AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (50, 19, N'C:', CAST(136.7149 AS Numeric(14, 4)), CAST(68.2211 AS Numeric(14, 4)), CAST(49.9002 AS Numeric(14, 4)), CAST(0x00009CAE01064D81 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (51, 23, N'C:', CAST(45.0048 AS Numeric(14, 4)), CAST(1.8177 AS Numeric(14, 4)), CAST(4.0390 AS Numeric(14, 4)), CAST(0x00009CAE01065188 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (52, 25, N'C:', CAST(19.5328 AS Numeric(14, 4)), CAST(8.0782 AS Numeric(14, 4)), CAST(41.3572 AS Numeric(14, 4)), CAST(0x00009CAE010655E8 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (53, 25, N'D:', CAST(48.7969 AS Numeric(14, 4)), CAST(2.4570 AS Numeric(14, 4)), CAST(5.0351 AS Numeric(14, 4)), CAST(0x00009CAE0106562A AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (54, 25, N'E:', CAST(68.3258 AS Numeric(14, 4)), CAST(26.7084 AS Numeric(14, 4)), CAST(39.0898 AS Numeric(14, 4)), CAST(0x00009CAE0106564F AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (55, 24, N'C:', CAST(15.4050 AS Numeric(14, 4)), CAST(9.3587 AS Numeric(14, 4)), CAST(60.7508 AS Numeric(14, 4)), CAST(0x00009CAE0106CAB6 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (56, 24, N'D:', CAST(52.3051 AS Numeric(14, 4)), CAST(23.1079 AS Numeric(14, 4)), CAST(44.1791 AS Numeric(14, 4)), CAST(0x00009CAE0106CAE0 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (57, 24, N'E:', CAST(67.7484 AS Numeric(14, 4)), CAST(12.4204 AS Numeric(14, 4)), CAST(18.3331 AS Numeric(14, 4)), CAST(0x00009CAE0106CB0B AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (58, 24, N'H:', CAST(272.4570 AS Numeric(14, 4)), CAST(84.5100 AS Numeric(14, 4)), CAST(31.0177 AS Numeric(14, 4)), CAST(0x00009CAE0106CB39 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (59, 13, N'C:', CAST(297.0775 AS Numeric(14, 4)), CAST(235.7953 AS Numeric(14, 4)), CAST(79.3717 AS Numeric(14, 4)), CAST(0x00009CAE010A56A4 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (61, 13, N'C:', CAST(297.0775 AS Numeric(14, 4)), CAST(235.7689 AS Numeric(14, 4)), CAST(79.3628 AS Numeric(14, 4)), CAST(0x00009CAE010ADDE2 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (62, 13, N'D:', CAST(1.0015 AS Numeric(14, 4)), CAST(0.9763 AS Numeric(14, 4)), CAST(97.4789 AS Numeric(14, 4)), CAST(0x00009CAE010ADFC5 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (63, 13, N'F:', CAST(465.7593 AS Numeric(14, 4)), CAST(24.6400 AS Numeric(14, 4)), CAST(5.2903 AS Numeric(14, 4)), CAST(0x00009CAE010AE133 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (64, 13, N'C:', CAST(297.0775 AS Numeric(14, 4)), CAST(234.7917 AS Numeric(14, 4)), CAST(79.0338 AS Numeric(14, 4)), CAST(0x00009CAF00C02410 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (65, 13, N'D:', CAST(1.0015 AS Numeric(14, 4)), CAST(0.9762 AS Numeric(14, 4)), CAST(97.4751 AS Numeric(14, 4)), CAST(0x00009CAF00C02448 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (66, 13, N'F:', CAST(465.7593 AS Numeric(14, 4)), CAST(24.6400 AS Numeric(14, 4)), CAST(5.2903 AS Numeric(14, 4)), CAST(0x00009CAF00C0247C AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (67, 15, N'C:', CAST(103.7775 AS Numeric(14, 4)), CAST(15.5859 AS Numeric(14, 4)), CAST(15.0186 AS Numeric(14, 4)), CAST(0x00009CAF00C03397 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (68, 15, N'D:', CAST(8.0101 AS Numeric(14, 4)), CAST(0.4320 AS Numeric(14, 4)), CAST(5.3930 AS Numeric(14, 4)), CAST(0x00009CAF00C033D8 AS DateTime))
INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (69, 15, N'M:', CAST(232.8266 AS Numeric(14, 4)), CAST(11.5426 AS Numeric(14, 4)), CAST(4.9576 AS Numeric(14, 4)), CAST(0x00009CAF00C03411 AS DateTime))
SET IDENTITY_INSERT [Informatica].[tblDiskSpace] OFF
/****** Object: Default [DF_tblDiskSpace_DSP_DATUM] Script Date: 11/04/2009 13:00:44 ******/
ALTER TABLE [Informatica].[tblDiskSpace] ADD CONSTRAINT [DF_tblDiskSpace_DSP_DATUM] DEFAULT (getdate()) FOR [DSP_DATUM]
GO
I tried to capture the data with group by's but this does not the trick:
WITH cteSelectIDs AS (
SELECT MAX(DSP_ID) AS DSP_ID FROM Informatica.tblDiskSpace WHERE dbo.DateOnly(DSP_DATUM) = '2009-10-26' AND SRV_ID = 17
GROUP BY DSP_DISKNAAM, SRV_ID, DSP_DATUM
)
SELECT * FROM Informatica.tblDiskSpace AS a INNER JOIN cteSelectIDs AS b ON a.DSP_ID = b.DSP_ID
Can someone point me out in the right ways of handling stuff like this.
Tnx in advance for any remark,
Wkr,
Van Heghe Eddy
November 4, 2009 at 6:43 am
I changed the name from your table name to #t, see if this is what you want!
SELECT * FROM
(Select *, Row_number() OVER (partition by SRV_ID, DSP_DiskNaam
ORDER BY DSP_Datum desc) as row_num
from #t) t
Where row_num = 1
---------------------------------------------------------------------------------
November 4, 2009 at 7:14 am
Hi Nabha,
Tnx a lot,
Thats idd what i wanted to get as result.
I really need to learn the Partition By statement, it is so powerfull when you need stuff like this.
With the example provided by you in a real live example it makes it directly a lot more clear on how it works.
Tnx again,
Wkr,
Eddy
November 4, 2009 at 7:38 am
eddy-644184 (11/4/2009)
Hi Nabha,Tnx a lot,
Thats idd what i wanted to get as result.
I really need to learn the Partition By statement, it is so powerfull when you need stuff like this.
With the example provided by you in a real live example it makes it directly a lot more clear on how it works.
Tnx again,
Wkr,
Eddy
Glad to help you. You were ready with your data and expectation, so it was easy for me. Just put it in tag, it would make bit more sense. (whenever you are posting the code)
Yes you are right, partition by is very useful!
Thanks
---------------------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply