January 13, 2014 at 11:57 pm
Hi, I have a query -
SELECT
YEAR(CAST (HOLELOCATION.ENDDATE AS DATETIME)) AS [YEAR],
MONTH(CAST (HOLELOCATION.ENDDATE AS DATETIME)) AS [MONTH],
COUNT(DISTINCT DRILLHOLEDIAMDETAILS.VALUE) AS [RIGS],
SUM(HOLELOCATION.DEPTH) AS [DRILLED_METRES],
'' AS[CUMULATIVE_DRILLED_METRES]
FROM DRILLHOLEDIAMDETAILS, HOLELOCATION
WHERE DRILLHOLEDIAMDETAILS.HOLEID = HOLELOCATION.HOLEID
AND CAST(HOLELOCATION.ENDDATE AS DATETIME) BETWEEN CAST('01/10/2013' AS DATETIME) AND CAST('01/11/2014' AS DATETIME)
AND DRILLHOLEDIAMDETAILS.NAME = 'Rig_ID'
AND NOT DRILLHOLEDIAMDETAILS.VALUE LIKE '%28'
GROUP BY
YEAR(CAST (HOLELOCATION.ENDDATE AS DATETIME)),
MONTH(CAST (HOLELOCATION.ENDDATE AS DATETIME))
ORDER BY 1,2 ASC
Which returns result set
YEAR MONTH RIGSDRILLED_METRES CUMULATIVE_DRILLED_METRES
2013 11 2 7768
2013 12 2 10448
2014 1 2 4086
What I am trying to do is the the running totals of DRILLED_METER displayed in the CUMULATIVE_DRILLED_METRES column
So what I would like to see is
CUMULATIVE_DRILLED_METRES
7768
18216 (7768 + 10448)
22302 (18216 + 4086)
TIA
Hope this helps...
Ford Fairlane
Rock and Roll Detective
January 14, 2014 at 12:00 am
What's the question?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2014 at 12:06 am
Apologies,
Question is how to I write the code such that my SQL will return the running totals under the [CUMULATIVE_DRILLED_METRES] column.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
January 14, 2014 at 12:19 am
First you got to help us so that we can help you.
It would be great if you could post the DDL for the table and some sample data + desired output.
See the first link in my signature on how to do this.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2014 at 1:16 am
Hi,
OK, SO there are two tables that are used.
/****** Object: Table [dbo].[HOLELOCATION] *****/
CREATE TABLE [dbo].[HOLELOCATION] (
[HOLEID] varchar(20) NOT NULL,
[PROJECTCODE] varchar(10) NOT NULL,
[TENEMENTID] varchar(30) NULL,
[GRIDNAME] varchar(20) NULL,
[HOLETYPE] varchar(10) NOT NULL,
[EAST] float NULL,
[NORTH] float NULL,
[RL] float NULL,
[DEPTH] float NULL,
[PROSPECT] varchar(20) NULL,
[STARTDATE] varchar(20) NULL,
[ENDDATE] varchar(20) NULL)
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);
END;
GO
ALTER TABLE [dbo].[HOLELOCATION] SET (LOCK_ESCALATION = TABLE);
GO
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('EC0038', 'RH', NULL, NULL, 'DRILLHOLE', 808095.412, 7470857.471, 433.762, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('EC0039', 'RH', NULL, NULL, 'DRILLHOLE', 799010.882, 7463604.219, 444.756, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('EC0040', 'RH', NULL, NULL, 'DRILLHOLE', 791549.28, 7462535.14, 442.491, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('EC0041', 'RH', NULL, NULL, 'DRILLHOLE', 792505.233, 7469483.023, 435.372, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('EC0042', 'RH', NULL, NULL, 'DRILLHOLE', 798363.856, 7469645.868, 437.165, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('EC0043', 'RH', NULL, NULL, 'DRILLHOLE', 802243.234, 7470042.787, 435.826, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('EC0044', 'RH', NULL, NULL, 'DRILLHOLE', 814875.79, 7471985.876, 428.498, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPB0001', 'RH', 'E47/1609', 'GDA94_50', 'DRILLHOLE', 791999.419, 7466326.993, 438.954, 85, NULL, '08-Jul-2009', '11-Jul-2009')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPB0001A', 'RH', 'E47/1609', 'GDA94_50', 'DRILLHOLE', 791994.426, 7466332.395, 439.188, 102, NULL, '9-Sep-2009', '12-Sep-2009')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPB0002', 'RH', 'E47/1609', 'GDA94_50', 'DRILLHOLE', 795282.37, 7470104.875, 435.846, 106, NULL, '20-Aug-2009', '25-Aug-2009')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPB0003', 'RH', 'E47/1610', 'GDA94_50', 'DRILLHOLE', 799024.634, 7459337.434, 451.018, 100, NULL, '31-Aug-2009', '4-Sep-2009')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPB0004', 'RH', 'E46/685', 'GDA94_50', 'DRILLHOLE', 799955.163, 7481134.261, 423.978, 101, NULL, '20-Sep-2009', '28-Sep-2009')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0002A', 'RH', 'E46/686', 'GDA94_50', 'DRILLHOLE', 816470.233, 7477960.78, 421.482, 50, NULL, '29-Oct-2008', '29-Oct-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0003', 'RH', 'E46/685', 'GDA94_50', 'DRILLHOLE', 797033.878, 7476216.014, 428.777, 100, NULL, '30-Oct-2008', '30-Oct-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0004', 'RH', 'E46/685', 'GDA94_50', 'DRILLHOLE', 799924.885, 7481160.707, 423.192, 100, NULL, '31-Oct-2008', '1-Nov-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0005', 'RH', 'E46/685', 'GDA94_50', 'DRILLHOLE', 806244.872, 7483765.423, 417.657, 100, NULL, '1-Nov-2008', '1-Nov-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0006', 'RH', 'E46/687', 'GDA94_50', 'DRILLHOLE', 807782.222, 7467405.47, 437.671, 100, NULL, '3-Nov-2008', '4-Nov-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0007', 'RH', 'E46/685', 'GDA94_50', 'DRILLHOLE', 808095.237, 7478065.474, 424.062, 106, NULL, '7-Nov-2008', '7-Nov-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0008', 'RH', 'E46/686', 'GDA94_50', 'DRILLHOLE', 818765.049, 7474060.629, 423.207, 100, NULL, '8-Nov-2008', '8-Nov-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0009', 'RH', 'E46/687', 'GDA94_50', 'DRILLHOLE', 821112.673, 7470002.264, 424.555, 100, NULL, '9-Nov-2008', '9-Nov-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0010', 'RH', 'E46/689', 'GDA94_50', 'DRILLHOLE', 830289.795, 7462095.528, 428.502, 100, NULL, '14-Nov-2008', '14-Nov-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0011', 'RH', 'E46/689', 'GDA94_50', 'DRILLHOLE', 834008.7, 7462600.806, 427.477, 100, NULL, '16-Nov-2008', '16-Nov-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0012', 'RH', 'E46/688', 'GDA94_50', 'DRILLHOLE', 829505.25, 7466007.135, 424.721, 100, NULL, '17-Nov-2008', '17-Nov-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0013', 'RH', 'E46/688', 'GDA94_50', 'DRILLHOLE', 834026.213, 7470260.442, 423.755, 100, NULL, '18-Nov-2008', '19-Nov-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0014', 'RH', 'E46/688', 'GDA94_50', 'DRILLHOLE', 829484.385, 7470356.653, 422.871, 100, NULL, '20-Nov-2008', '21-Nov-2008')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0015', 'RH', 'E47/1609', 'GDA94_50', 'DRILLHOLE', 795275.634, 7470112.331, 435.022, 100, NULL, '24-Mar-2009', '24-Mar-2009')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0016', 'RH', 'E47/1609', 'GDA94_50', 'DRILLHOLE', 791989.502, 7466309.483, 438.603, 106, NULL, '25-Mar-2009', '26-Mar-2009')
INSERT INTO [dbo].[HOLELOCATION] ([HOLEID], [PROJECTCODE], [TENEMENTID], [GRIDNAME], [HOLETYPE], [EAST], [NORTH], [RL], [DEPTH], [PROSPECT], [STARTDATE], [ENDDATE])
VALUES ('ECPZ0017', 'RH', 'E47/1610', 'GDA94_50', 'DRILLHOLE', 792064.892, 7457481.875, 450.141, 100, NULL, '27-Mar-2009', '27-Mar-2009')
/****** Object: Table [dbo].[DRILLHOLEDIAMDETAILS] ******/
CREATE TABLE [dbo].[DRILLHOLEDIAMDETAILS] (
[HOLEID] varchar(20) NOT NULL,
[PROJECTCODE] varchar(10) NOT NULL,
[STARTDEPTH] float NOT NULL,
[DIAMETERTYPE] varchar(10) NOT NULL,
[NAME] varchar(20) NOT NULL,
[VALUE] varchar(100) NOT NULL)
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);
END;
GO
ALTER TABLE [dbo].[DRILLHOLEDIAMDETAILS] SET (LOCK_ESCALATION = TABLE);
GO
**** DATA
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('MB95/RC2-3', 'RH', 60, '50', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB139/20-3', 'RH', 72, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB141/21-1', 'RH', 60, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB148/22-1', 'RH', 60, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB15/2-1', 'RH', 60, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB155/23-1', 'RH', 78, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB166/25-2', 'RH', 72, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB190/29-2', 'RH', 66, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB195/30-5', 'RH', 60, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB221/35-3', 'RH', 54, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB222/35-1', 'RH', 64, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB268/43-1', 'RH', 72, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB28/3-2', 'RH', 60, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB43/ 6-2', 'RH', 84, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB51/8-4', 'RH', 60, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB58/9-1', 'RH', 78, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB95/RC2-3', 'RH', 64, '200', 'Casing', 'PVC')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0022B', 'RH', 0, '311', 'Casing_Depth', '112')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0032', 'RH', 0, '165', 'Casing_Depth', '112')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPB0009', 'RH', 0, '406.4', 'Casing_Depth', '115.55')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0010', 'RH', 0, 'NR', 'Casing_Depth', '13.25')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0009', 'RH', 0, 'NR', 'Casing_Depth', '23.85')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0018SS', 'RH', 0, '311', 'Casing_Depth', '24')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0024SS', 'RH', 0, '311', 'Casing_Depth', '26')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0028SS', 'RH', 0, '311', 'Casing_Depth', '28')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0026SS', 'RH', 0, '311', 'Casing_Depth', '29')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0015A', 'RH', 0, '311', 'Casing_Depth', '30')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0016SS', 'RH', 0, '311', 'Casing_Depth', '30')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0027SS', 'RH', 0, '311', 'Casing_Depth', '30')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0029', 'RH', 0, '311', 'Casing_Depth', '30')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPB0002', 'RH', 0, '533', 'Casing_Depth', '30.02')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0022ASS', 'RH', 0, '311', 'Casing_Depth', '32')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('PB49/7-2', 'RH', 33, '200', 'Casing_Depth', '32.77')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0025SS', 'RH', 0, '311', 'Casing_Depth', '34')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0020SS', 'RH', 0, '311', 'Casing_Depth', '36')
INSERT INTO [dbo].[DRILLHOLEDIAMDETAILS] ([HOLEID], [PROJECTCODE], [STARTDEPTH], [DIAMETERTYPE], [NAME], [VALUE])
VALUES ('RHPZ0023B', 'RH', 0, '311', 'Casing_Depth', '38.2')
My select statement is
SELECT
YEAR(CAST (HOLELOCATION.ENDDATE AS DATETIME)) AS [YEAR],
MONTH(CAST (HOLELOCATION.ENDDATE AS DATETIME)) AS [MONTH],
COUNT(DISTINCT DRILLHOLEDIAMDETAILS.VALUE) AS [RIGS],
SUM(HOLELOCATION.DEPTH) AS [DRILLED_METRES],
Helped needed here --> '' AS[CUMULATIVE_DRILLED_METRES]
FROM DRILLHOLEDIAMDETAILS, HOLELOCATION
WHERE DRILLHOLEDIAMDETAILS.HOLEID = HOLELOCATION.HOLEID
AND CAST(HOLELOCATION.ENDDATE AS DATETIME) BETWEEN CAST('01/10/2013' AS DATETIME) AND CAST('01/11/2014' AS DATETIME)
AND DRILLHOLEDIAMDETAILS.NAME = 'Rig_ID'
AND NOT DRILLHOLEDIAMDETAILS.VALUE LIKE '%28'
GROUP BY
YEAR(CAST (HOLELOCATION.ENDDATE AS DATETIME)),
MONTH(CAST (HOLELOCATION.ENDDATE AS DATETIME))
ORDER BY 1,2 ASC
With the desired output to be -- where CUMULATIVE_DRILLED_METRES is a running total of DRILLED METERS
Year | Month | RIGS | DRILLED_METERS | CUMULATIVE_DRILLED_METRES
2013 | 11 | 2 | 7768 | 7768
2013 | 11 | 2 | 10448 | 18216
2014 | 1 | 2 | 4086 | 22302
Hope this helps...
Ford Fairlane
Rock and Roll Detective
January 14, 2014 at 3:45 am
Use row_number function to get the ranks and add the current value to previous value
January 14, 2014 at 7:52 am
Take a look at this article. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
It explains how to use the "quirky update" for running totals. Make sure you pay attention to the details as there are some extremely important caveats to using this technique.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2014 at 4:29 pm
Appreciate the nudge in the right direction guys.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
January 15, 2014 at 6:37 am
rakesh ghodasara (1/14/2014)
Use row_number function to get the ranks and add the current value to previous value
This would be nice! I would make a small wager that its not possible with SQL because I can't think of a way to access a row from within the very query that has just emitted it! If you can post some code to disprove this, I would certainly be interested!
January 15, 2014 at 4:31 pm
Refined and resolved !!!
Thanks for all your input.
This is the working query.
SELECT
YEAR(CAST (a.ENDDATE AS DATETIME)),
MONTH(CAST (a.ENDDATE AS DATETIME)),
(
SELECT
SUM(b.DEPTH)
FROM
HOLELOCATION b
WHERE
b.HOLEID LIKE 'RHRC%'
AND ( CAST(YEAR(CAST(b.ENDDATE AS DATETIME)) AS VARCHAR) + RIGHT('00'+ CAST( MONTH(CAST (b.ENDDATE AS DATETIME)) AS VARCHAR(2)),2)) <= ( CAST(YEAR(CAST(a.ENDDATE AS DATETIME)) AS VARCHAR) + RIGHT('00'+ CAST( MONTH(CAST (a.ENDDATE AS DATETIME)) AS VARCHAR(2)),2))
AND YEAR(CAST (b.ENDDATE AS DATETIME)) IS NOT NULL AND MONTH(CAST (b.ENDDATE AS DATETIME)) IS NOT NULL
) AS DEPTH
FROM
HOLELOCATION a
WHERE a.HOLEID LIKE 'RHRC%' AND a.ENDDATE IS NOT NULL
GROUP BY
YEAR(CAST (a.ENDDATE AS DATETIME)),
MONTH(CAST (a.ENDDATE AS DATETIME))
ORDER BY
YEAR(CAST (a.ENDDATE AS DATETIME)) ASC,
MONTH(CAST (a.ENDDATE AS DATETIME)) ASC
Hope this helps...
Ford Fairlane
Rock and Roll Detective
January 17, 2014 at 10:04 pm
refer to this post...
http://sqlsaga.com/sql-server/how-to-calculate-running-totals-in-sql-server/[/url]
step by step explained...
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
January 18, 2014 at 9:24 am
Ford Fairlane (1/15/2014)
Refined and resolved !!!Thanks for all your input.
This is the working query.
SELECT
YEAR(CAST (a.ENDDATE AS DATETIME)),
MONTH(CAST (a.ENDDATE AS DATETIME)),
(
SELECT
SUM(b.DEPTH)
FROM
HOLELOCATION b
WHERE
b.HOLEID LIKE 'RHRC%'
AND ( CAST(YEAR(CAST(b.ENDDATE AS DATETIME)) AS VARCHAR) + RIGHT('00'+ CAST( MONTH(CAST (b.ENDDATE AS DATETIME)) AS VARCHAR(2)),2)) <= ( CAST(YEAR(CAST(a.ENDDATE AS DATETIME)) AS VARCHAR) + RIGHT('00'+ CAST( MONTH(CAST (a.ENDDATE AS DATETIME)) AS VARCHAR(2)),2))
AND YEAR(CAST (b.ENDDATE AS DATETIME)) IS NOT NULL AND MONTH(CAST (b.ENDDATE AS DATETIME)) IS NOT NULL
) AS DEPTH
FROM
HOLELOCATION a
WHERE a.HOLEID LIKE 'RHRC%' AND a.ENDDATE IS NOT NULL
GROUP BY
YEAR(CAST (a.ENDDATE AS DATETIME)),
MONTH(CAST (a.ENDDATE AS DATETIME))
ORDER BY
YEAR(CAST (a.ENDDATE AS DATETIME)) ASC,
MONTH(CAST (a.ENDDATE AS DATETIME)) ASC
That will certainly work but be advised that it's not scalable. If you end up with a not so large number of rows, it will eat the face off your server because it uses a thing known as a "Triangular Join", which is about half as bad as a full Cartesian Join (also known as a "Square Join").
Please see the following article on Triangular Joins.
http://www.sqlservercentral.com/articles/T-SQL/61539/
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2014 at 9:28 am
a4apple (1/17/2014)
refer to this post...http://sqlsaga.com/sql-server/how-to-calculate-running-totals-in-sql-server/[/url]
step by step explained...
Same thing there. It's a Triangular Join. No matter what, it will always use more resources than you could possibly imagine. In pre-2012 SQL Server, it's far better to learn how to use a "Quirky Update" correctly. If you take exception to that unsupported method, then write a Cursor or While loop to do the running total. See the following URL for more on the problems with Triangular Joins.
http://www.sqlservercentral.com/articles/T-SQL/61539/
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2014 at 5:23 pm
Is the same true if you use a common table expression to calculate the running total?
January 18, 2014 at 9:45 pm
pietlinden (1/18/2014)
Is the same true if you use a common table expression to calculate the running total?
Yes except that it's likely that the CTE will use more reads than a Cursor or While Loop. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply