# Thread: SQL Query

1. Registered User
Join Date
Feb 2013
Posts
6

## Unanswered: SQL Query

New to this site. Any help would be appreciated, thank you.
We would like to create a report on MS SQL server v2008 R2

We have run the following code to generate an extract of data: -

SELECT Member_ID, Visit_ID, Start_Date, End_Date
FROM tblVisit
ORDER BY Staff_ID, Visit_ID DESC

Please see the attached .pdf file attached for full details

From this code we would like to achieve the following : -

1 - The day difference between the members latest Start_Date and previous End_Date (e.g. Row 2; Member_ID 7469; the day difference between 18/05/2012 and 10/05/2012. We have calculated the result being 8 days in excel this formula =IF(A2=A3,SUM(D3-C2),"")

2 – Continue point 1 until the Member_Id row changes

3 - If next row is a different Member_ID to the previous row then calculate for the new Member_ID

We would like the SQL code to generate the same results as in column E (as in the snapshot above) which was generated using the excel formula =IF(A2=A3,SUM(D3-C2),"")

2. Registered User
Join Date
Apr 2012
Posts
213
Try something like this:

Code:
```with CTE_R as
(
select
Member_ID,
Visit_ID,
Start_Date,
End_Date,
ROW_NUMBER() OVER(PARTITION BY Member_ID
ORDER BY Staff_ID, Visit_ID DESC) as RowNum
FROM tblVisit
)

select
r.Member_ID,
r.Visit_ID,
r.Start_Date,
r.End_Date,
case when r.RowNum = 1
then null
else DATEDIFF(day, r.Start_Date, p.End_Date)
end as QtyDays
from CTE_R as r
left join CTE_R as p
on p.Member_ID = r.Member_ID and
p.RowNum = r.RowNum - 1```
Hope this helps.

3. Registered User
Join Date
Feb 2013
Posts
6
Thank you very much for this.
Just testing this out now. We need a few tweaks to suit our needs, which we are looking at but its about 90% there.
We may get back to you but thank you for this great start.

4. Registered User
Join Date
Feb 2013
Posts
6
One last question, how do we apply a filter on QtyDays to the code previously provided?
i.e. where QtyDays between 0 and 50
Thank you

5. Registered User
Join Date
Apr 2012
Posts
213
Try something like this:

Code:
```where
case when r.RowNum = 1
then null
else DATEDIFF(day, r.Start_Date, p.End_Date)
end between 0 and 50```
Hope this helps.

6. Registered User
Join Date
Feb 2013
Posts
6
This totally solves the issues we had. Thank you very much for your help

7. Registered User
Join Date
Jan 2013
Posts
355
New to this site. Any help would be appreciated, thank you.
Well, you made a bad first impression Let's fix it.

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you did not). Temporal data should use ISO-8601 formats (wrong again!). Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums.

>> We would like to create a report on MS SQL server v2008 R2<<

SQL does queries, not reports. This is like asking how to use a saw as a screwdriver on a woodworking forum

>> We have run the following code to generate an extract of data: -<<

I have to laugh a "tblVisit"; it is singular so there is only one visit! An SQL programmer would use a collecitve name for the SET in the table. The use of "tbl-" is so bad it has a name -- "tbling"!

SELECT member_id, visit_nbr, visit_start_date, visit_end_date
FROM Member_Visits;

Please see the attached .pdf file attached for full details
NO!! Your posting should be complete in itself, in text, without artwork and colors. Do you open every attachment you get from strangers? Do you like to re-type the PDF from a stranger in order to do his job for him? Want to open that file from Nigeria?

>> 1 - The day difference between the members latest Start_Date and previous End_Date (e.g. Row 2; Member_ID 7469; the day difference between '2012-18-05' and '2012-10-05'. We have calculated the result being 8 days in excel this formula IF(A2=A3,SUM(D3-C2),"") <<

I corrected your dates to ISO Standards; this is a vital standard in IT and you need to learn it. The dialect you picked is the third worst possible format! Before you ask, Slavic names and Roman numeral month are the least readable.

You posted color! Why? It looks childish. What is EXCEL? This is an SQL forum and we do not speak anything but SQL. The reason for this is simple; a spreadsheet has ordering and a table does not. This is a huge difference!

>> 2 – Continue point 1 until the Member_Id row changes <<

That is a procedural algorithm, with loops and sequence. Too bad SQL is declarative and does not work that way. We do not loop or sequence; we declare!

>> 3 - If next row is a different Member_ID to the previous [sic] row then calculate for the new Member_ID <<

SQL is a set-oriented language, so there is no concept of "previous" without an ordered value in columns. Look up the "information Prinicple" which is open of Codd's 12 laws of RM. You think of the physical layout of a spreadsheet! ARRGH!

Do you watch FUTURAMA? Dr. Zoidberg? "Of course I know about humans! Now lift your gill slits so I can check for parasites." I could give you a kludge (and someone will), but I would rather give you an education. Hey, what is the fun of getting free time with one of creators of SQL and not learning something?

Here is your data, done correctly:

CREATE TABLE Visits
(member_id CHAR(4)NOT NULL,
visit_nbr CHAR(5)NOT NULL,
PRIMARY KEY(member_id, visit_nbr),
visit_start_date DATE NOT NULL,
visit_end_date DATE,
CHECK(visit_start_date <= visit_end_date));

INSERT INTO Visits
VALUES
('7469', '10246', '2012-05-18', NULL),
('7469', '10245', '2012-09-05', '2012-10-05')
('7475', '10252', '2012-08-14', NULL),
('7480', '10262', '2012-09-30', '2011-02-13'),
('7483', '10266', '2012-06-29', '2012-08-21'),
('7486', '10278', '2012-05-17', '2012-07-06'),
('7491', '10276', '2012-07-11', NULL);

An SQL programmer would not do this!! We will write declarative code to prevent NULLs. There are tricks in the DDL to prevent gaps, etc

CREATE TABLE Events
(event_id CHAR(10) NOT NULL,
previous_event_end_date DATE NOT NULL
CONSTRAINT Chained_Dates
REFERENCES Events (event_end_date),
event_start_date DATE NOT NULL,
event_end_date DATE UNIQUE, -- null means event in progress
PRIMARY KEY (event_id, event_start_date),
CONSTRAINT Event_Order_Valid
CHECK (event_start_date <= event_end_date),
CONSTRAINT Chained_Dates
CHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date)
);

-- disable the Chained_Dates constraint
ALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates;
GO

-- insert a starter row
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');
GO

-- enable the constraint in the table
ALTER TABLE Events CHECK CONSTRAINT Chained_Dates;
GO

-- this works
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');

-- this fails
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15');

8. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Originally Posted by Celko
Well, you made a bad first impression Let's fix it.
No. He was courteous and informative.
Originally Posted by Celko
Please post DDL, ....blah blah blah.
No. He posted enough information to allow a responder to pose intelligent questions for further information.

Originally Posted by Celko
This is minimal polite behavior on SQL forums.
Not on this forum. As I have pointed out before. It is inappropriate of you, Joe, to jump into this forum and immediately start dictating to other people what is and what is not considered polite behavior.
Originally Posted by Celko
>> We would like to create a report on MS SQL server v2008 R2<<

SQL does queries, not reports. This is like asking how to use a saw as a screwdriver on a woodworking forum
I understood exactly what the poster meant.
Originally Posted by Celko

>> We have run the following code to generate an extract of data: -<<

I have to laugh a "tblVisit"; it is singular so there is only one visit! An SQL programmer would use a collecitve name for the SET in the table. The use of "tbl-" is so bad it has a name -- "tbling"!
Inappropriate abusive, and outside the topic of the post. The poster was not asking about naming conventions, and may have no control over the design of the database he is using.

The rest of you reply was similarly abusive.
Gratuitously so, as the poster's question had already been answered to his satisfaction.
I've asked you before to stop this behavior, and if you continue to alienate guests then I will start blocking your posts.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•