June 14, 2012 at 12:48 pm
Hi guys
I am a water engineer new to SQL. I know the basics but now i want to do something a little bit more tricky.
I have a table holding information about water treatment plants & samples taken at these treatment plants.
These samples need to be taken once every X days for each plant & process, otherwise the plant does not comply with monitoring guidelines. I wish to check the time (days) that have elapsed between these samples for each treatment plant (ref) & process (template_descr)
INSERT INTO [Quality_Monitoring_RowSequence]
([ref]
,[SAMPLE_ID]
,[SAMPLE_DATE]
,[template_descr]
,[template_id]
,[STARTING_DATE]
,[FREQ_UNIT]
,[FREQ_VALUE])
VALUES
(<ref, nvarchar(60),>
,<SAMPLE_ID, int,>
,<SAMPLE_DATE, datetime,>
,<template_descr, nvarchar(512),>
,<template_id, int,>
,<STARTING_DATE, datetime,>
,<FREQ_UNIT, int,>
,<FREQ_VALUE, int,>)
GO
-------------------------------------------------------------
refSAMPLE_IDSAMPLE_DATEtemplate_descrtemplate_id
Churchill Treatment Plant133342011-09-03 00:00:00.000Treatment Works final measurement1005
Churchill Treatment Plant133352011-09-10 00:00:00.000Treatment Works final measurement1005
Churchill Treatment Plant133362011-09-17 00:00:00.000Treatment Works final measurement1005
Churchill Treatment Plant133372011-09-24 00:00:00.000Treatment Works final measurement1005
Churchill Treatment Plant132832011-09-03 00:00:00.000Treatment Works raw measurements
Churchill Treatment Plant132842011-09-10 00:00:00.000Treatment Works raw measurements
Churchill Treatment Plant132852011-09-17 00:00:00.000Treatment Works raw measurements
Churchill Treatment Plant132862011-09-24 00:00:00.000Treatment Works raw measurements
-----------------------------------------------------------------------------------------
The expected output should be something like:
Churchill Treatment Plant 13335 2011-09-10 00:00:00.000 Treatment Works final measurement 7 days
Churchill Treatment Plant 13336 2011-09-10 00:00:00.000 Treatment Works final measurement 7 days
Churchill Treatment Plant 13337 2011-09-10 00:00:00.000 Treatment Works final measurement 7 days
Churchill Treatment Plant 13284 2011-09-10 00:00:00.000 Treatment Works raw measurements 7 days
etc etc.
Any help would be greatly appreciated.
Cruz
June 14, 2012 at 12:56 pm
So... You want to GROUP BY ref and then compute the difference (DATEDIFF()) between the two dates when the data is ordered by the date? What does template have to do with this? It seems those are all unique. Are they ever increasing, even if ref is different? Or do they only increase within a ref grouping?
Jared
CE - Microsoft
June 14, 2012 at 1:02 pm
Can you please give the DDL for the table (CREATE TABLE .... )?
Jared
CE - Microsoft
June 14, 2012 at 1:14 pm
Hi Jared
Thanks for the quick reply.
I want to group by ref (description of the plant) , and by template_descr (there are actually different monitoring processes).
So each sample is basically taken at each plant for a specific template (process)
So I basically want to check only the like samples (by like I mean the same ref & template) for the date differences between them.
The sample_ID will always be unique & increasing, this is a primary key. So to answer your question. Yes ID will increase & might have different refs because new samples might be taken at any given plant. (actually should have included plant_id but i wanted to keep it simple)
To use a real life scenario, a lab technician tests the water (takes a new sample)
at a plant, then this sample is linked to a template (monitoring process)
I hope that helps.
Cruz
June 14, 2012 at 1:26 pm
Thanks for that. Maybe you didn't see my post directly before yours, but after my first post... Can you please post the create table statement for the table? Also, I noticed that the samle data that you provided has less columns than the insert statement you provided. Why is that?
Jared
CE - Microsoft
June 14, 2012 at 1:26 pm
CREATE TABLE [dbo].[SAMPLE_TEST](
[ref] [nvarchar](60) NULL,
[SAMPLE_ID] [int] NOT NULL,
[SAMPLE_DATE] [datetime] NOT NULL,
[template_descr] [nvarchar](512) NULL,
[template_id] [int] NOT NULL,
[STARTING_DATE] [datetime] NOT NULL,
[FREQ_UNIT] [int] NULL,
[FREQ_VALUE] [int] NULL
) ON [PRIMARY]
GO
June 14, 2012 at 1:33 pm
SQLKnowItAll (6/14/2012)
Also, I noticed that the samle data that you provided has less columns than the insert statement you provided. Why is that?
Jared
CE - Microsoft
June 14, 2012 at 1:36 pm
Not the OP, but got this far and had to attend to other more important items, and I want to help the OP so posted what I had done, with the hope that I interpreted the OPs posting correctly
CREATE TABLE #Quality_Monitoring_RowSequence(
ref nvarchar(60),
SAMPLE_ID int,
SAMPLE_DATE datetime,
template_descr varchar(512),
template_id int,
STARTING_DATE datetime,
FREQ_UNIT int,
FREQ_VALUE int)
INSERT INTO #Quality_Monitoring_RowSequence(ref,SAMPLE_ID,SAMPLE_DATE,template_descr,template_id)
SELECT 'Churchill Treatment Plant', 13334, '2011-09-03 00:00:00.000', 'Treatment Works final measurement', 1005 UNION ALL
SELECT 'Churchill Treatment Plant', 13335, '2011-09-10 00:00:00.000', 'Treatment Works final measurement', 1005 UNION ALL
SELECT 'Churchill Treatment Plant', 13336, '2011-09-17 00:00:00.000', 'Treatment Works final measurement', 1005 UNION ALL
SELECT 'Churchill Treatment Plant', 13337, '2011-09-24 00:00:00.000', 'Treatment Works final measurement', 1005
INSERT INTO #Quality_Monitoring_RowSequence(ref,SAMPLE_ID,SAMPLE_DATE,template_descr)
SELECT 'Churchill Treatment Plant', 13283, '2011-09-03 00:00:00.000', 'Treatment Works raw measurements' UNION ALL
SELECT 'Churchill Treatment Plant', 13284, '2011-09-10 00:00:00.000', 'Treatment Works raw measurements' UNION ALL
SELECT 'Churchill Treatment Plant', 13285, '2011-09-17 00:00:00.000', 'Treatment Works raw measurements' UNION ALL
SELECT 'Churchill Treatment Plant', 13286, '2011-09-24 00:00:00.000', 'Treatment Works raw measurements'
June 14, 2012 at 1:38 pm
Hi Jared
Ah, the reason for that is, I didn't want to give you irrelevant information.
I don't really care about the other columns. The only result I really want to see is the date differences
in the samples when the A) the plant is the same and B) the process is the same.
Thanx for all your help.
Cruz
June 14, 2012 at 1:45 pm
Ok, here is my assembly of your sample data. Let me continue to work on a solution. Have you tried anything yet?
USE tempdb
GO
DECLARE @Quality_Monitoring_RowSequence TABLE
(ref nvarchar(60)
,SAMPLE_ID int
,SAMPLE_DATE datetime
,template_descr nvarchar(512)
,template_id int
,STARTING_DATE datetime
,FREQ_UNIT int
,FREQ_VALUE int)
INSERT INTO @Quality_Monitoring_RowSequence (ref, SAMPLE_ID, SAMPLE_DATE, template_descr, template_id)
SELECT 'Churchill Treatment Plant', 13334, '2011-09-03 00:00:00.000', 'Treatment Works final measurement', 1005
UNION ALL
SELECT 'Churchill Treatment Plant', 13335, '2011-09-10 00:00:00.000', 'Treatment Works final measurement', 1005
UNION ALL
SELECT 'Churchill Treatment Plant', 13336, '2011-09-17 00:00:00.000', 'Treatment Works final measurement', 1005
UNION ALL
SELECT 'Churchill Treatment Plant', 13337, '2011-09-24 00:00:00.000', 'Treatment Works final measurement', 1005
UNION ALL
SELECT 'Churchill Treatment Plant', 13283, '2011-09-03 00:00:00.000', 'Treatment Works raw measurements', 1006
UNION ALL
SELECT 'Churchill Treatment Plant', 13284, '2011-09-10 00:00:00.000', 'Treatment Works raw measurements', 1006
UNION ALL
SELECT 'Churchill Treatment Plant', 13285, '2011-09-17 00:00:00.000', 'Treatment Works raw measurements', 1006
UNION ALL
SELECT 'Churchill Treatment Plant', 13286, '2011-09-24 00:00:00.000', 'Treatment Works raw measurements', 1006
SELECT * FROM @Quality_Monitoring_RowSequence
Jared
CE - Microsoft
June 14, 2012 at 2:04 pm
Hi Jared
Yes I have tried my @$$ off lol
Basically, I know you can compare a table to itself using a self join, and I know you can use the datediff function in SQL Server to check the date differences between two dates. But in this scenario it helps me didly squat coz I don't know how to compare the current record to the next record!
I would imagine you would use some kind of a temp variable & I've also read something about cursors but I am by no means proficient in these advanced concepts yet.
Once again, thank you for your assistance. This is the first time I'm posting for help, usually I can figure out these queries myself with some research but this baby has me stumped lol
Cruz
June 14, 2012 at 2:06 pm
Try this:
USE tempdb
GO
DECLARE @Quality_Monitoring_RowSequence TABLE
(ref nvarchar(60)
,SAMPLE_ID int
,SAMPLE_DATE datetime
,template_descr nvarchar(512)
,template_id int
,STARTING_DATE datetime
,FREQ_UNIT int
,FREQ_VALUE int)
INSERT INTO @Quality_Monitoring_RowSequence (ref, SAMPLE_ID, SAMPLE_DATE, template_descr, template_id)
SELECT 'Churchill Treatment Plant', 13334, '2011-09-03 00:00:00.000', 'Treatment Works final measurement', 1005
UNION ALL
SELECT 'Churchill Treatment Plant', 13335, '2011-09-10 00:00:00.000', 'Treatment Works final measurement', 1005
UNION ALL
SELECT 'Churchill Treatment Plant', 13336, '2011-09-17 00:00:00.000', 'Treatment Works final measurement', 1005
UNION ALL
SELECT 'Churchill Treatment Plant', 13337, '2011-09-24 00:00:00.000', 'Treatment Works final measurement', 1005
UNION ALL
SELECT 'Churchill Treatment Plant', 13283, '2011-09-03 00:00:00.000', 'Treatment Works raw measurements', 1006
UNION ALL
SELECT 'Churchill Treatment Plant', 13284, '2011-09-10 00:00:00.000', 'Treatment Works raw measurements', 1006
UNION ALL
SELECT 'Churchill Treatment Plant', 13285, '2011-09-17 00:00:00.000', 'Treatment Works raw measurements', 1006
UNION ALL
SELECT 'Churchill Treatment Plant', 13286, '2011-09-24 00:00:00.000', 'Treatment Works raw measurements', 1006
;WITH cte (ref, template_id, sample_id, sample_date, template_descr, rankOrder)
AS
(
SELECT ref, template_id, sample_id, sample_date, template_descr, RANK() over(partition by ref, template_id ORDER BY sample_date) FROM @Quality_Monitoring_RowSequence
),
cte2 (ref2, template_id2, sample_id2, sample_date2, rankOrder2)
AS
(
SELECT ref, template_id, sample_id, sample_date, RANK() over(partition by ref, template_id ORDER BY sample_date) - 1 FROM @Quality_Monitoring_RowSequence
)
SELECT c.ref, c.sample_id, c.sample_date, c.template_descr, ISNULL(DATEDIFF(DAY, c.sample_date, c2.sample_date2),DATEDIFF(DAY, c.sample_date, GETDATE())) as txDiff
FROM cte c
--INNER JOIN cte2 c2
LEFT JOIN cte2 c2
ON c.ref = c2.ref2
AND c.template_id = c2.template_id2
and c.rankOrder = c2.rankOrder2
Jared
CE - Microsoft
June 14, 2012 at 2:31 pm
holy crap!! Obi-Wan has taught you well!!
Thanks a million Jared I thought I wouldn't be able to solve this!
You are my new personal SQL Jesus lol or what ever flavor of modern day savior holds your fancy.
I need to brush up on these new functions though, I hate sucking lol
Thanks again Jared
🙂
June 14, 2012 at 2:35 pm
lol No problem! The ranking functions are on my blog (sqlknowitall.com). I haven't really been good at updating it since I recently started it and am not good at figuring things to write about yet. However, the current post may help you with ranking.
Jared
CE - Microsoft
June 14, 2012 at 2:44 pm
Ok I am having a look at it now, I will definitely need to apply similar logic in the future, so best I brush up now.
Feel free to update the blog liberally 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply