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.
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.MoveoutDescriptionFROM 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 DATA140132 MSF 6/14/2014 123 1234 2 John Doe MyCity 7/12/2014 OUT NULL 1 Bldg1 1142049 OUT 7/12/2014 123 1234 6 John Doe MyCity 7/13/2014 MSF BldgTOBldg 2 Bldg1 2142052 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.MoveoutDescriptionFROM 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_IDGROUP BY O.Bldg, O.CompID, FL.First_MI, FL.Last_MO, O.Comp_Name, O2.MoveoutDescriptionORDER 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 and2. INSERT INTO statements to populate the tables with your test dataThat 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 and2. INSERT INTO statements to populate the tables with your test dataThat 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 HistoryUSE [Cottages]GO/****** Object: View [dbo].[OccupancyHistory] Script Date: 08/14/2014 13:12:35 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[OccupancyHistory]ASSELECT T .*, building.buildingName, ROW_NUMBER() OVER (PARTITION BY CompID, Comm_unitORDER BY TranSeq ASC) AS OccSeqFROM (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 TranSeqFROM CRM_CSLDB.dbo.CSL_Acct_TransWHERE 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 TranSeqFROM CRM_CSLDB.dbo.CSL_Acct_TransWHERE 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 JOINCRM_CSLDB.dbo.building ON T .Cottage = building.cottage AND T .comm_unit BETWEEN building.aptSTART AND building.aptENDGOEXEC 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 EndEnd' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'OccupancyHistory'GOEXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'OccupancyHistory'GO |
|
|
|
|
|
|
|