Complicated Query to create views or reports? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complicated Query to create views or reports?

Author

Topic

cwwhite
Starting Member

1 Post

Posted - 2012-04-27 : 13:46:14

Problem Background:

ALL, I am new to SQL and have a complicated request that i hope is not really as complicated as i have made it. I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.

Ideally, we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points. I have created two VIEWS for each of the below situation. Further down in this post I will post specific goals & problems encountered thus far.

Thank you in advance for your help and im sorry for posing soo much information but it seems the more i browse the forums, that people are always asking for more detail to help solve problems.

ABSENCES
AWAY = Start of an absence from the building
RETURN = End of an absence from the building
A person during their reisdency will have multiple Absences
An absence could start on the same day a previous absence ends
Absences vary in length
CS
CS0-CS10 (each is a different TYPE) of communication
CS0 indicates that the person has not moved in yet, or has moved out
CS1-10 has a value attached to it for daily time to assist that individual
CS may go both up and down over time

VIEW 1) AWAYS
Length of Absence
Time between absences ** (no progress so far)
Frequency of Absence ** (no progress so far)
Does frequency increase when CS increases?** (no progress so far)
Relate CS to specific AWAY to ( IE: CS at time of absence is the CS triggered before away)

Query in DESIGN View

SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER()
OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM CRM_CSLDB.dbo.vCommunication
WHERE (comm_trantype LIKE N'AWAY%') OR
(comm_trantype LIKE N'RETURN%')
GROUP BY CmLi_Comm_CompanyID, comm_trantype, comm_trandate, Comm_CommunicationId, Comm_Deleted
HAVING (Comm_Deleted IS NULL)
SELECT TOP 100 PERCENT t1.*, DATEDIFF(day, t1.date, t2.date) AS AwayDays, t2.Date AS ReturnDate
FROM [AwayTemp] AS t1 INNER JOIN
[AwayTemp] AS t2 ON [t1].[Compid] = [t2].[Compid] AND (t1.Seq + 1 = t2.Seq)
WHERE t2.Type = 'Return'
ORDER BY t1.CompID, t1.CommID, t1.[Date]

Results from QUERY in DESIGN VIEW

TYPE DATE COMPID COMMID SEQ
AWAY 2011-02-24 00:00:00.000 51 74753 1
RETURN 2011-03-31 00:00:00.000 51 74754 2
AWAY 2011-03-28 00:00:00.000 64 74068 1
RETURN 2011-04-30 00:00:00.000 64 75297 2
AWAY 2011-05-01 00:00:00.000 64 75298 3
RETURN 2011-05-31 00:00:00.000 64 76332 4
AWAY 2011-06-01 00:00:00.000 64 76336 5
RETURN 2011-06-30 00:00:00.000 64 76337 6
AWAY 2011-12-13 00:00:00.000 72 85805 1
RETURN 2012-01-20 00:00:00.000 72 86814 2
AWAY 2012-03-20 00:00:00.000 78 89964 1
RETURN 2012-03-30 00:00:00.000 78 89965 2
AWAY 2012-04-19 00:00:00.000 78 90898 3
AWAY 2011-10-02 00:00:00.000 152 83383 1
RETURN 2011-10-11 00:00:00.000 152 83818 2
AWAY 2011-10-31 00:00:00.000 152 84248 3
RETURN 2011-11-21 00:00:00.000 152 87347 4
AWAY 2012-02-24 00:00:00.000 152 88614 5
RETURN 2012-03-20 00:00:00.000 152 89866 6
AWAY 2012-02-23 00:00:00.000 161 89254 1
AWAY 2011-06-20 00:00:00.000 169 77564 1
RETURN 2011-06-28 00:00:00.000 169 77566 2
AWAY 2011-08-02 00:00:00.000 172 78323 1

RESULTS FROM QUERY in SELECT TOP 11

Type Date CompID CommID Seq AwayDays ReturnDate
AWAY 2011-06-20 00:00:00.000 169 77564 1 8 2011-06-28 00:00:00.000
AWAY 2011-12-23 00:00:00.000 1505 90504 4 112 2012-04-13 00:00:00.000
AWAY 2011-10-09 00:00:00.000 3534 83946 1 16 2011-10-25 00:00:00.000
AWAY 2012-01-27 00:00:00.000 3983 89036 3 94 2012-04-30 00:00:00.000
AWAY 2012-03-09 00:00:00.000 4064 90061 5 20 2012-03-29 00:00:00.000
AWAY 2011-10-29 00:00:00.000 4620 84278 1 32 2011-11-30 00:00:00.000
AWAY 2012-02-09 00:00:00.000 4675 87780 5 4 2012-02-13 00:00:00.000
AWAY 2011-06-06 00:00:00.000 4713 77205 1 24 2011-06-30 00:00:00.000
AWAY 2011-12-15 00:00:00.000 4938 87776 1 19 2012-01-03 00:00:00.000
AWAY 2012-04-01 00:00:00.000 5424 90194 1 29 2012-04-30 00:00:00.000
AWAY 2012-04-14 00:00:00.000 5649 90637 1 6 2012-04-20 00:00:00.000

VIEW 2) CARE STEP HISTORY **(No progress)
LENGTH OF TIME IN EACH CS
PREVIOUS and/or NEXT CS
FREQUENCY OF CHANGE
The analysis goal of this table is to predict CS over time.
Relate CS to specific AWAY to (CS is the most immediate preciding CS

QUERY in DESIGN VIEW

SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate,
dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name,
Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM dbo.vCommunication INNER JOIN
dbo.CSL_resident ON dbo.vCommunication.CmLi_Comm_CompanyID = dbo.CSL_resident.Compid
WHERE (dbo.vCommunication.comm_trantype LIKE N'CS%')
GROUP BY dbo.vCommunication.CmLi_Comm_CompanyID, dbo.vCommunication.comm_trantype, dbo.vCommunication.comm_trandate,
dbo.vCommunication.Comm_CommunicationId, dbo.CSL_resident.Name
ORDER BY CompanyID, csTranDate, Comm_ID, csTrans

QUERY RESULTS IN SELECT

csTrans csTranDate CompanyID Comm_ID Name CommID Seq
CS1 2009-07-27 00:00:00.000 11 76147 Harrison Bailey 76147 1
CS3 2007-08-27 00:00:00.000 26 73777 Dorothy Wheeler 73777 1
CS4 2011-12-02 00:00:00.000 26 85087 Dorothy Wheeler 85087 2
CS5 2012-01-01 00:00:00.000 26 85446 Dorothy Wheeler 85446 3
CS3 2011-03-31 00:00:00.000 51 74759 Bonnie Clawson 74759 1
CS3 2008-04-19 00:00:00.000 62 73352 Tressie Desmond 73352 1
CS4 2009-04-01 00:00:00.000 64 70436 Mary Jane Hamm 70436 1
CS4 2009-02-22 00:00:00.000 72 73854 Cleveland Hester 73854 1
CS2 2010-10-20 00:00:00.000 78 73040 Lola Hamilton 73040 1
CS1 2011-04-04 00:00:00.000 130 73794 Irra Dell Pinkard 73794 1

Mar
Starting Member

47 Posts

Posted - 2012-05-01 : 08:20:44

Here's a suggestion to help you get the answers you need. You are unlikely to get someone to do your work for you, it may happen, but to get results it is best to post a question. One question per post.

I read your post twice and can't figure out what you want. I'll bet I'm not the only one.

Even your title is confusing. Do you want a view or report? If you really don't care, then you really want SQL and that is what you should ask for.

The people here are helping others for free and their time is limited. If your post is confusing then they will just move on to the next one.

It is also bad practice to post personal information (people's names).

Also sometimes it is better to let people ask for what they need then to post too much. Again, it takes time to sort through everything to find a single piece of information. If you post too much sometimes you will get asked anyway because the information is too hard to find. Remember, the stuff you post looks familiar to you, but not to anyone else. Maybe you didn't post too much, i don't know because i can't find your question. You may need to break it down into several posts.

Try posting a question and I'll try to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources