[SQL Server] First, Last row and other data from those rows |
- First, Last row and other data from those rows
- Trying to convert varchar to datetime
- Using a Temporary Table in a View in Order to Combine three Queries
- Creating a view with Declare
| First, Last row and other data from those rows Posted: 22 Jul 2013 06:39 PM PDT Hi,I have the following data in tables[code="plain"]T1:ID Dept Cat1 WER 22 TTR 7[/code][code="plain"]T2:ID Date Type1 2013-07-01 GA2 2013-07-04 FS2 2013-07-08 TR2 2013-07-01 TT1 2013-04-05 RT1 2013-05-13 GG2 2013-04-18 TT[/code]I would like to query first table and then join to the second to have first and last transaction and some other data from the first and last transaction row. I would like to have the output similar to the following:[code="plain"]ID Cat FirstDate FirstType LastDate LastType1 2 2013-04-05 RT 2013-07-01 GA2 7 2013-04-18 TT 2013-07-08 TR[/code]I was trying to use MIN MAX to query the First and Last Date (T2.Date) but then I can't to extract the related First and Last Type (T2.Type) |
| Trying to convert varchar to datetime Posted: 23 Jul 2013 08:31 AM PDT I am trying to convert 20114 which is stored as varchar to datetime but I am getting the following errorSyntax error during explicit conversion of VARCHAR value '20114' to a DATETIME field.Can some one help me in solving this? |
| Using a Temporary Table in a View in Order to Combine three Queries Posted: 23 Jul 2013 08:45 AM PDT Hi all. I am trying to create a view using the three queries below and I get the error message[b] Views or functions are not allowed on temporary tables[/b]. Is there a way to do that or is there a way to combine the three queries below so I don't have to use a temp table so I create a view?Thanks! [b]--Query 1 [/b]SELECT * INTO #MOVEMENTS FROM [GW_DW].[dbo].[DimStatusHistory] dWHERE TransferFromToProgram<>'' AND d.Status=12;[b]--Query 2 [/b]SELECT DISTINCT n.[CLT_NBR] ,n.[CHILD_NAME] ,n.[ReasonKey] ,n.[ReasonDesc] ,n.[EFFECT_DT] ,n.[Status] ,n.[STATUS_DESC] ,n.[DESCRIPT] ,n.[TRAN_TYPE] ,n.[OTYPE] ,n.[Old_FID] ,n.[NTYPE] ,n.[New_FID] ,n.[TransferFromToProgram] ,[ECMS].dbo.[FN_PRIOR_EFFECT_DT_FOR_STATUS](n.[EFFECT_DT],n.[CLT_NBR],12) AS PRIOR_EFFECT_DT ,[ECMS].dbo.[FN_NEXT_EFFECT_DT_FOR_STATUS](n.[EFFECT_DT],n.[CLT_NBR],12) AS FUTURE_EFFECT_DT ,n.[TOTAL_DAYS] INTO #NEW_MOVEMENTS FROM [GW_DW].[dbo].[DimStatusHistory] n LEFT OUTER JOIN #MOVEMENTS m ON n.CLT_NBR=m.CLT_NBR WHERE n.TransferFromToProgram NOT IN ('','FBH - TFBH','TFBH - FBH') AND n.EFFECT_DT BETWEEN @from_dt AND @to_dt AND n.COUNTY='NYC' AND n.OTYPE NOT IN ('RTC', 'SLIP') AND n.NTYPE NOT IN ('RTC', 'SLIP') AND n.Status=12 ; [b] --Query 3 [/b] SELECT n.[CLT_NBR] ,n.[CHILD_NAME] ,c.CIN ,cl.DOB ,c.Age ,c.Sex ,c.PlcSource ,w.PLACED_DT AS APD ,w.IPD ,n.[ReasonKey] ,n.[ReasonDesc] ,n.[EFFECT_DT] ,n.[TransferFromToProgram] ,n.[OTYPE] ,n.[Old_FID] ,h1.Rsrc_Name AS Old_FP_Name ,h1.Orig_Cert AS Old_FP_Orig_Cert ,n.[NTYPE] ,n.[New_FID] ,h2.Rsrc_Name AS New_FP_Name ,h2.Orig_Cert AS New_FP_Orig_Cert ,n.PRIOR_EFFECT_DT ,m.Old_FID AS PRIOR_Old_FID ,n.FUTURE_EFFECT_DT ,f.New_FID AS FUTURE_New_FID ,c.STF_NBR ,c.Planner ,s.UNIT ,s.SupervisorName ,s.[SITE] ,s.DirectorName ,CASE WHEN (n.NTYPE='KINS' AND n.OTYPE <> 'KINS') THEN 1 WHEN n.ReasonKey IN ('TE','TJ') THEN 1 WHEN ((n.New_FID=m.Old_FID) AND (n.EFFECT_DT<DATEADD(D,22,n.PRIOR_EFFECT_DT))) THEN 1 ELSE 0 END AS POS_SCORE ,CASE WHEN (n.NTYPE='KINS' AND n.OTYPE <> 'KINS') THEN 0 WHEN n.ReasonKey IN ('TE','TJ') THEN 0 WHEN n.New_FID=m.Old_FID AND n.EFFECT_DT<DATEADD(D,22,n.PRIOR_EFFECT_DT)THEN 0 WHEN ((n.Old_FID=f.New_FID) AND (DATEADD(D,22,n.EFFECT_DT)>n.FUTURE_EFFECT_DT)) THEN 0 ELSE -1 END AS NEG_SCORE FROM #NEW_MOVEMENTS n LEFT OUTER JOIN #MOVEMENTS m ON n.CLT_NBR=m.CLT_NBR AND n.PRIOR_EFFECT_DT=m.EFFECT_DT LEFT OUTER JOIN #MOVEMENTS f ON n.CLT_NBR=f.CLT_NBR AND n.FUTURE_EFFECT_DT=f.EFFECT_DT LEFT OUTER JOIN GW_DW.dbo.DimClient c ON c.CLT_NBR=n.CLT_NBR LEFT OUTER JOIN [ECMS].[dbo].[WFR_CLIENT] w ON w.CLT_NBR=n.CLT_NBR LEFT OUTER JOIN [ECMS].[dbo].[CLIENT] cl ON cl.CLT_NBR=n.CLT_NBR LEFT OUTER JOIN GW_DW.dbo.DimStaff s ON s.ECMS_Wrkr_ID=c.STF_NBR LEFT OUTER JOIN [GW_DW].[dbo].[DimHome_FHD] h1 ON h1.Facility_ID=n.Old_FID LEFT OUTER JOIN [GW_DW].[dbo].[DimHome_FHD] h2 ON h2.Facility_ID=n.New_FID WHERE s.[SITE]<>'CGS' ORDER BY n.CHILD_NAME,n.EFFECT_DT DESC |
| Posted: 22 Jul 2013 04:29 PM PDT I am trying to create a view with the below query. it should be a view as data in other tables will be updated and I want to refer to this often. Or as a last resort schedule the creation of this table over night some how?DECLARE @@string1 varchar (20) = ' Number='DECLARE @@string2 varchar (20) = ' And Id='SELECT f.linkid, f.docname, f.title, dc.catname, t.template, r.extlink ,f.libid,substring(extlink, charindex(@@string1, extlink)+len(@@string1), charindex(@@string2, extlink) -len(@@string1) - charindex(@@string1, extlink)) as IDfrom AL_TEMPLATE t INNER JOIN AL_RELATION r ON t.tempID=r.tempidINNER JOIN AL_RELATED_FILE rf ON r.relID=rf.relid INNER JOIN al_file f ON rf.linkid=f.linkID INNER JOIN al_filecats fc ON f.linkid=fc.linkid INNER JOIN AL_DOCCAT dc ON fc.catid=dc.catIDwhere charindex(@@string2, extlink) -len(@@string1) - charindex(@@string1, extlink) > 0Thanks |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment