Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  • Users: imex
  • Order by date
  1. imex

    Inserting XML data into SQLServer returns only NULL values

    Hi, Try: select MY_XML.document.value('(@ArchivedFileName)[1]', 'VARCHAR(100)'), MY_XML.document.value('(@DocumentId)[1]', 'VARCHAR(100)'), MY_XML.document.value('(@FileSize)[1]', 'VARCHAR(100)'), MY_XML.document.value('(@OriginalFileName)[1]', 'VARCHAR(100)')...
  2. imex

    split a sql field based on carriage return

    Hi, If you are using SQL Server 2016 or later you can use the String_Split function: select value from SL_ADDRESSES cross apply STRING_SPLIT((AD_ADDRESS, CHAR(13)) where RTRIM(value) <> '' Hope this help https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
  3. imex

    Aggregate Values By Group

    Hi, Try: with CTE_Sum as ( select [Claim ID], [Location ID], [Reserve Date], sum([Reserve amt]) as [Reserve amt] from test gropu by [Claim ID], [Location ID], [Reserve Date] ) select [Claim ID], [Location ID], [Reserve Date], sum([Reserve amt])...
  4. imex

    Get distinct group, please help urgent.

    Hi, Try something like this: with CTE_Concat as ( SELECT DISTINCT a.secu_ID, STUFF( (SELECT ', ' + s.Owner_ID FROM MyTable as s WHERE s.secu_ID = a.secu_ID FOR XML PATH(''), TYPE).value('.', 'varchar(max)')...
  5. imex

    concatenate group by, pls help

    Hi, Try something like this: SELECT DISTINCT a.paymnt_group_id, STUFF( (SELECT ', ' + s.paymnt_desc FROM MyTable as s WHERE s.paymnt_group_id = a.paymnt_group_id FOR XML PATH(''), TYPE).value('.', 'varchar(100)') ,1, 2, '')...
  6. imex

    Update column with min value from another tow / column

    Hi, Try something like this: with CTE_RK as ( select *, RANK() OVER(ORDER BY bookpage) as RK from MyTable ) update CTE_RK set mincounter = RK Hope this helps. http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
  7. imex

    UPDATE query multipart idnetifier error

    Try: UPDATE FY set FY.mon_shnm = pd.mon_shnm FROM rptdata_monthly.dbo.rpt_FYInfo FY INNER JOIN rptdata_monthly.dbo.dic_Period pd ON pd.pd = FY.rptpd WHERE FY.rptpd = 391 and FY.uci='BPA' and FY.mon_shnm is null Hope this helps...
  8. imex

    question about a query [ get all null and filled answers by question and category ]

    Hi, Try: select u.userID, u.firstName, u.lastName, q.questionID, q.description, c.categoryID, c.description, a.answer_id, a.answerText from tbl_users as u cross join tbl_questions as q inner join tbl_category as c on c.categoryID = q.categoryID left...
  9. imex

    How Do You Find the Max in a Sub-Set?

    Hi, I do not know if I understood correctly but try: SELECT MBR_SSN_NBR, max(MBR_HIST_SVC_CR_DT) as MBR_HIST_SVC_CR_DT FROM DSNP.PR01_T_MBR_HIST where left(agty_id_cd, 1) = '9' group by MBR_SSN_NBR Hope this helps. http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
  10. imex

    CTE(common table expressions)...help needed

    Try: with CTE_Recursive as ( select i.ITEMID, i.ITEMREFERENCEID, h.PARENTNODEID from @ITEM as i inner join @ITEM_HIERARCHY as h on h.ITEMID = i.ITEMID where i.ITEMREFERENCEID = 59 union all select h.ITEMID...
  11. imex

    CTE(common table expressions)...help needed

    Hi, Sorry but I do not understand what you said. Doing a test with the script below got 4 rows in the result: declare @ITEM Table (ITEMID INT, ITEMREFERENCEID INT); insert into @ITEM values (1, 48), (2, 50), (3, 51), (4, 52), (5, 53), (6, 54), (7, 55), (8, 56), (9, 57), (10, 58), (11, 59)...
  12. imex

    CTE(common table expressions)...help needed

    Try: with CTE_Recursive as ( select i.ITEMID, i.ITEMREFERENCEID, h.PARENTNODEID from ITEM as i inner join ITEM_HIERARCHY as h on h.ITEMID = i.ITEMID where i.ITEMREFERENCEID = 59 union all select h.ITEMID...
  13. imex

    CTE(common table expressions)...help needed

    Hi, Try something like this: with CTE_Recursive as ( select i.ITEMID, i.ITEMREFERENCEID, h.PARENTNODEID from ITEM as i inner join ITEM_HIERARCHY as h on h.ITEMID = i.ITEMID where i.ITEMREFERENCEID = 48 union all...
  14. imex

    need help filtering returned rows

    Hi, Try something like this: with CTE_Q as ( -- your query without order by ), CTE_T as ( select UPC from CTE_Q where Type in ('adjustment', 'sales') group by UPC having COUNT(distinct Type) = 2 ) select q.* from CTE_T...
  15. imex

    Delete duplicate data from table

    Hi, bmacbmac, you can also try: ;with CTE_RN as ( select *, ROW_NUMBER() OVER(PARTITION BY recordid, docno, bookno, pageno order by relatedid) as RN from #temp ) delete from CTE_RN where RN > 1 Hope this helps...
  16. imex

    Help with Query

    Try: SELECT t.HostID FROM dbo.tblHosts as t WHERE not exists (SELECT d.HostID FROM dbo.tblHolidays as d WHERE d.HostID = t.HostID AND d.DateFrom <= @DepartureDate AND d.DateTo >= @ArrivalDate) Hope...
  17. imex

    Extract date from datetime field

    Hi, Try something like this: select DATEADD(DAY, DATEDIFF(DAY, 0, DateTimeColumn), 0) as DateTest, -- ... Hope this helps. http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
  18. imex

    Importing to table from another table

    Hi, If you want to enter a fixed value in this column try something like this: insert into Table2 (Col1, Col2, Col3NotNull) select Col1, Col2, 'D' from Table1 Hope this helps. http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
  19. imex

    Updating / Inserting to an empty table using a 2nd table as a key

    Try changing the line below: set @Last_LOC_Primary = coalesce((select top 1 LOC_Primary from STK_Location order by LOC_Primary desc), 0); Hope this helps. http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
  20. imex

    Updating / Inserting to an empty table using a 2nd table as a key

    Try: declare @Last_LOC_Primary int; set @Last_LOC_Primary = (select top 1 LOC_Primary from STK_Location order by LOC_Primary desc); insert into STK_Location (LOC_Primary, LOC_Code, LOC_Stock_Code) select ROW_NUMBER() OVER(ORDER BY s.STKCode) + @Last_LOC_Primary, 'MAIN'...

Part and Inventory Search

Back
Top