Running Total
-
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
-
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 -
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 -
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 -
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 -
Use row_number function to get the ranks and add the current value to previous value
-
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/ -
Appreciate the nudge in the right direction guys.
Hope this helps...
Ford Fairlane
Rock and Roll Detective -
rakesh ghodasara (1/14/2014)
Use row_number function to get the ranks and add the current value to previous valueThis 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!
-
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 -
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.
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Is the same true if you use a common table expression to calculate the running total?
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply