How to selectively count days.

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

How to selectively count days.

Author

Topic

ttwettlaufer
Starting Member

5 Posts

Posted - 2014-07-30 : 13:01:38

Ok, so I started trying to figure out what this query is doing and I think I understand.
So here is the question, on the last line, MAX(LAST T.LastDate) has a date, or it's NULL.

How can I change the statement to say, IF MAX(LAST T.LastDate) IS NULL use NULL?

SELECT DISTINCT
TOP (100) PERCENT O.CompID, O.Comp_Name, COUNT(DISTINCT O.Comm_Unit) AS Apartments, FL.First_MI, FL.Last_MO, O.Bldg, DATEDIFF(day, FL.First_MI,
FL.Last_MO) AS days, O2.MoveoutDescription
FROM dbo.OccupancyHistory AS O RIGHT OUTER JOIN
(SELECT O.CompID, MIN(FIRST_T.FirstDate) AS First_MI, MAX(LAST_T.LastDate) AS Last_MO, MAX(LAST_T.LAST_ID) AS LAST_ID

ttwettlaufer
Starting Member

5 Posts

Posted - 2014-08-01 : 10:31:22

I see a lot of people have read the post, but no comments? Is it too difficult to understand? Not enough information? I would think this should be pretty easy to figure out.

thanks.

mhorseman
Starting Member

44 Posts

Posted - 2014-08-13 : 06:24:50

Happy to have a go at helping, but with Distincts, Top (100) Percent, a Right Outer Join without an ON I'm not really sure what you're trying to do. Anyway, your MAX on LAST_T.LastDate will return the latest date if you've got any, or a null if every entry is null, so you shouldn't need to make any changes to the MAX.

Perhaps you could post the whole query and some sample data that could be used, as well as what you want to be returned from the sample data.

Mark

ttwettlaufer
Starting Member

5 Posts

Posted - 2014-08-13 : 08:52:48

quote:
Originally posted by mhorseman

Happy to have a go at helping, but with Distincts, Top (100) Percent, a Right Outer Join without an ON I'm not really sure what you're trying to do. Anyway, your MAX on LAST_T.LastDate will return the latest date if you've got any, or a null if every entry is null, so you shouldn't need to make any changes to the MAX.

Perhaps you could post the whole query and some sample data that could be used, as well as what you want to be returned from the sample data.

Mark

Mark,
Thanks for trying, here is all the information I have. BTW, I didn't write this code, I have just been asked to see if anybody can help.
So, basically all that is "supposed" to happen is to count the total number of days that a person stays at the facility and show the correct move out date if there is one.
Here is the catch,
residents can transfer from room to room or apartment to apartment, this will result in a transaction in the database, as a move in/move out as well, however, if the resident still lives there, then the move out shows as NULL.
Ok, so when the results come out (see the very bottom) the move in/move out shows as dates, but won't show the NULL for a resident that still lives there, it seems to drop or skip that NULL value. (I would think that is normal, but I'm not sure)

SAMPLE DATA
140132 MSF 6/14/2014 123 1234 2 John Doe MyCity 7/12/2014 OUT NULL 1 Bldg1 1
142049 OUT 7/12/2014 123 1234 6 John Doe MyCity 7/13/2014 MSF BldgTOBldg 2 Bldg1 2
142052 MSF 7/13/2014 213 1234 8 John Doe MyCity NULL NULL NULL 3 Bldg2 1

/**Query that Creates the View for Length of Stay. It should take the first transaction for each COMPID out of the above data set. The length of stay should be null given that does not have a final OUT **/

SELECT DISTINCT
TOP (100) PERCENT O.CompID, O.Comp_Name, COUNT(DISTINCT O.Comm_Unit) AS Apartments, FL.First_MI, FL.Last_MO, O.Bldg, DATEDIFF(day, FL.First_MI,
FL.Last_MO) AS days, O2.MoveoutDescription
FROM dbo.OccupancyHistory AS O RIGHT OUTER JOIN
(SELECT O.CompID, MIN(FIRST_T.FirstDate) AS First_MI, MAX(LAST_T.LastDate) AS Last_MO, MAX(LAST_T.LAST_ID) AS LAST_ID
FROM dbo.OccupancyHistory AS O LEFT OUTER JOIN
(SELECT TranDate AS FirstDate, CommID AS FIRST_ID, CompID
FROM dbo.OccupancyHistory
WHERE (TranSeq = 1) AND (OccSeq = 1)) AS FIRST_T ON O.CompID = FIRST_T.CompID AND O.CommID = FIRST_T.FIRST_ID LEFT OUTER JOIN
(SELECT MAX(TranDate) AS LastDate, MAX(CommID) AS LAST_ID, CompID, MAX(TranSeq) AS maxtran
FROM dbo.OccupancyHistory AS OccupancyHistory_1
GROUP BY CompID, TranSeq, TranType
HAVING (MAX(TranSeq) = TranSeq) AND (TranType = 'OUT')) AS LAST_T ON O.CompID = LAST_T.CompID AND O.CommID = LAST_T.LAST_ID
GROUP BY O.CompID) AS FL ON O.CompID = FL.CompID LEFT OUTER JOIN
dbo.OccupancyHistory AS O2 ON O2.CommID = FL.LAST_ID
GROUP BY O.Bldg, O.CompID, FL.First_MI, FL.Last_MO, O.Comp_Name, O2.MoveoutDescription
ORDER BY O.CompID

/**Then I run the select statement for this view**/

SELECT [CommID]
,[TranType]
,[TranDate]
,[Comm_Unit]
,[CompID]
,[Seq]
,[Comp_Name]
,[Bldg]
,[NextDate]
,[NextType]
,[MoveoutDescription]
,[TranSeq]
,[buildingName]
,[OccSeq]
FROM [Bldgs].[dbo].[OccupancyHistory]
WHERE Comp_Name LIKE 'Cust Name%'
GO

/** the result of this query are below**/

1111 Cust Name 2 indate outdate mycity 28 (number of days) Bldg to Bldg

/**I would expect the results to be **
1111 Cust Name 2 indate NULL somecity 28 (number of days)

Again,
thanks.

mhorseman
Starting Member

44 Posts

Posted - 2014-08-13 : 12:58:19

Your sample data doesn't make it clear what belongs to which column in the table. For example 140132 on the first line is presumably CompID, but what are the others? Also the results of your final query have 1111 as the first column, but I can't see 1111 anywhere in the sample data. Also I thought dbo.OccupancyHistory was your table, but there's mention of a view as well.

To make it a bit clearer, please could you post:

1. CREATE TABLE statements for the table involved
and
2. INSERT INTO statements to populate the tables with your test data

That way people can run your Select statement against some real data and get an idea of what's going on with the code. Sorry for all the questions, but at present I'm still very much in the dark!

Mark

ttwettlaufer
Starting Member

5 Posts

Posted - 2014-08-13 : 13:46:37

Mark,
thanks for the info so far. sorry about the darkness, I'm a network/sys admin and don't do anything with sql.
some of the numbers won't match as I needed to take out potentially proprietary info and try to make things generic.
Also, I just spoke with the creator of the query and he will get the extra info you requested. Probably won't be until tomorrow though.

Again,
thanks for the help.

quote:
Originally posted by mhorseman

Your sample data doesn't make it clear what belongs to which column in the table. For example 140132 on the first line is presumably CompID, but what are the others? Also the results of your final query have 1111 as the first column, but I can't see 1111 anywhere in the sample data. Also I thought dbo.OccupancyHistory was your table, but there's mention of a view as well.

To make it a bit clearer, please could you post:

1. CREATE TABLE statements for the table involved
and
2. INSERT INTO statements to populate the tables with your test data

That way people can run your Select statement against some real data and get an idea of what's going on with the code. Sorry for all the questions, but at present I'm still very much in the dark!

Mark

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-13 : 19:36:06

I'm guessing the following sql might work for you. Have in mind, I have not tested this, as I didn't have proper data to test on and I don't have access to a mssql server at the moment.

with occin
  as (select CompID
            ,CommID
            ,row_number() over (order by CompID,CommID) as rn
        from Bldgs.dbo.OccupancyHistory
       where TranSeq=1
         and OccSeq=1
         and Comp_Name like 'John Doe%'
     )
select b.CompID
      ,b.Comp_Name
      ,count(distinct e.Comm_Unit) as Apartments
      ,b.TranDate as First_MI
      ,d.TranDate as Last_MO
      ,b.Bldg
      ,datediff(dd,b.TranDate,isnull(d.TranDate,getdate())) as days
  from occin as a
       inner join Bldgs.dbo.OccupancyHistory as b
               on b.CommID=a.CommID
       left outer join occin as c
                    on c.CompID=a.CompID
                   and c.rn=a.rn+1
       left outer join Bldgs.dbo.OccupancyHistory as d
                    on d.CompId=a.CompID
                   and d.CommID>a.CommID
                   and d.CommID<c.CommID
                   and d.NextDate is null
 group by b.CompID
         ,b.Comp_Name
         ,b.TranDate
         ,d.TranDate
         ,b.Bldg
         ,datediff(dd,b.TranDate,isnull(d.TranDate,getdate())) as days

ttwettlaufer
Starting Member

5 Posts

Posted - 2014-08-15 : 10:49:42

extra information. Ok, so the guy that did the original sql query gave me this to pass on.
again, thanks for the help everyone.

Create Statement for Occupancy History

USE [Cottages]
GO

/****** Object: View [dbo].[OccupancyHistory] Script Date: 08/14/2014 13:12:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[OccupancyHistory]
AS
SELECT T .*, building.buildingName, ROW_NUMBER() OVER (PARTITION BY CompID, Comm_unit
ORDER BY TranSeq ASC) AS OccSeq
FROM (SELECT CUR.CommID, Cur.TranType AS TranType, Cur.TranDate, CASE WHEN cur.comm_unit IS NULL
THEN prev.Comm_unit ELSE cur.comm_unit END AS Comm_Unit, CUR.CompID, CUR.Seq, CUR.Comp_Name, CUR.Cottage, Next.TranDate AS NextDate,
Next.TranType AS NextType, MOR.MoveoutDescription, Cur.TranSeq
FROM (SELECT CommID, TranType, TranDate, comm_unit, CompID, Seq, Comp_Name, Cottage, Unit, BuildingName, ROW_NUMBER() OVER (PARTITION BY
CompID
ORDER BY TranDate ASC,
CASE WHEN TranType LIKE 'MSF%' THEN 1 WHEN TranType LIKE 'RENT%' THEN 1 WHEN TranType = 'TERM' THEN 2 WHEN TranType = 'OUT' THEN
3 END) AS TranSeq
FROM CRM_CSLDB.dbo.CSL_Acct_Trans
WHERE TranType IN
(SELECT DISTINCT TRANTYPE
FROM CRM_CSLDB.dbo.CSL_Acct_Trans
WHERE TranType LIKE 'MSF%' OR
TranType LIKE 'RENT%' OR
TranType IN ('OUT', 'TERM'))) CUR LEFT JOIN
/* partition by customerID and put termination in front of OUT*/ (SELECT CommID, TranType, TranDate, comm_unit, CompID, Seq, Comp_Name, Cottage, Unit,
BuildingName, ROW_NUMBER() OVER (PARTITION BY CompID
ORDER BY TranDate ASC,
CASE WHEN TranType LIKE 'MSF%' THEN 1 WHEN TranType LIKE 'RENT%' THEN 1 WHEN TranType
= 'TERM' THEN 2 WHEN TranType = 'OUT' THEN 3 END) AS TranSeq
FROM CRM_CSLDB.dbo.CSL_Acct_Trans
WHERE TranType IN
(SELECT DISTINCT TRANTYPE
FROM CRM_CSLDB.dbo.CSL_Acct_Trans
WHERE TranType LIKE 'MSF%' OR
TranType LIKE 'RENT%' OR
TranType IN ('OUT', 'TERM'))) NEXT ON CUR.TranSeq + 1 = Next.TranSeq AND Cur.CompID = Next.CompID LEFT JOIN
/* partition by customerID and put termination in front of OUT*/ (SELECT CommID, TranType, TranDate, comm_unit, CompID, Seq, Comp_Name, Cottage, Unit, BuildingName,
ROW_NUMBER() OVER (PARTITION BY CompID
ORDER BY TranDate ASC,
CASE WHEN TranType LIKE 'MSF%' THEN 1 WHEN TranType LIKE 'RENT%' THEN 1 WHEN TranType =
'TERM' THEN 2 WHEN TranType = 'OUT' THEN 3 END) AS TranSeq
FROM CRM_CSLDB.dbo.CSL_Acct_Trans
WHERE TranType IN
(SELECT DISTINCT TRANTYPE
FROM CRM_CSLDB.dbo.CSL_Acct_Trans
WHERE TranType LIKE 'MSF%' OR
TranType LIKE 'RENT%' OR
TranType IN ('OUT', 'TERM'))) Prev ON CUR.TranSeq - 1 = Prev.TranSeq AND Cur.CompID = prev.CompID LEFT JOIN
(SELECT moveoutdescription, vCommunication.Comm_CommunicationID CommID
FROM Cottages.dbo.MoveOutReasons LEFT JOIN
CRM_CSLDB.dbo.vCommunication ON vCommunication.comm_movereason = moveoutreasons.MoveOutCode) MOR ON Cur.CommID = MOR.CommID) T LEFT JOIN
CRM_CSLDB.dbo.building ON T .Cottage = building.cottage AND T .comm_unit BETWEEN building.aptSTART AND building.aptEND

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'OccupancyHistory'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'OccupancyHistory'
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources