logo
down
shadow

SQL Server : unsure how to retrieve selected records


SQL Server : unsure how to retrieve selected records

Content Index :

SQL Server : unsure how to retrieve selected records
Tag : sql , By : Karina
Date : November 26 2020, 01:01 AM


Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Retrieve Last n number of records in SQL Server 2008


Tag : sql-server , By : MP.
Date : March 29 2020, 07:55 AM
around this issue
SELECT TOP n * FROM tblCurrent_locations ORDER BY "Date" DESC, "Time" DESC
SELECT TOP n * FROM tblCurrent_locations ORDER BY [Date] DESC, [Time] DESC

Sql Server retrieve last date for multiple records


Tag : sql , By : mgz
Date : March 29 2020, 07:55 AM
Hope this helps I have a table with 5 columns Emp, Surname, CSD, Term Rsn and Date. The first 4 columns will stay the same but the date will change eg. There can be 30 records for an Emp with 30 different dates. , Write as below:
Create  PROCEDURE testPro(    
@reportStartDate date,
@reportEndDate date    
)    
AS
BEGIN  
select Emp, Surname, CSD,[Working Days] , Max(Date) as [Date]
From (
SELECT DISTINCT Emp, Surname, CSD, CASE WHEN CSD<= @reportStartDate
THEN 

CASE WHEN [Term Rsn] != 0 THEN
CASE WHEN [Date] <= @reportEndDate THEN
(DATEDIFF(dd,@reportStartDate, [Date]) - (2* 
DATEDIFF(wk,@reportStartDate,[Date])))+1 
ELSE (DATEDIFF(dd,@reportStartDate, @reportEndDate) - (2* 
DATEDIFF(wk,@reportStartDate,@reportEndDate)))+1 END  
ELSE (DATEDIFF(dd,@reportStartDate, @reportEndDate) - (2* 
DATEDIFF(wk,@reportStartDate,@reportEndDate)))+1 END


ELSE 

CASE WHEN [Term Rsn] != 0 THEN 
CASE WHEN [Date] <= @reportEndDate THEN
(DATEDIFF(dd,CSD, [Date]) - (2* 
DATEDIFF(wk,CSD,[Date])))+1 
ELSE (DATEDIFF(dd,CSD, @reportEndDate) - (2* 
DATEDIFF(wk,CSD,@reportEndDate)))+1 END 
ELSE (DATEDIFF(dd,CSD, @reportEndDate) - (2* 
DATEDIFF(wk,CSD,@reportEndDate)))+1 END 

END AS [Working Days],[Date] -- Add all Dates here

FROM GyPremiumsTemp
WHERE CSD < @reportEndDate AND [Date] >  @reportStartDate
) T
Group By Emp, Surname, CSD,[Working Days] 
ORDER BY Emp ASC
END


EXEC  testPro '2012-01-01', '2012-12-31'

How to retrieve multiple records of a particular id in one row using SQL Server


Tag : sql , By : Janne Laine
Date : March 29 2020, 07:55 AM
around this issue using the stuff() with select ... for xml path ('') method of string concatenation.
select 
    issue_num
  , issue_desc
  , assigned_to_user_id = stuff((
    select distinct ','+u.user_id
    from Users u 
      inner join IssueAssigned sia
        on u.id = sia.assigned_to_user_id
    where sia.issue_num = i.issue_num
    for xml path (''), type).value('.','nvarchar(max)')
    ,1,1,'')
  , x.comment 
  , x.assign_date
from Issues i
  cross apply (
    select top 1 ia.comment, ia.assign_date
    from IssueAssigned ia
    where ia.issue_num = i.issue_num
    ) as x
+-----------+---------------+---------------------+-------------------+---------------------+
| issue_num |  issue_desc   | assigned_to_user_id |      comment      |     assign_date     |
+-----------+---------------+---------------------+-------------------+---------------------+
| abc123    | login issue   | ray234,yash123      | replicating issue | 2017-03-15 00:00:00 |
| abc345    | session issue | aniket,ray234       | replicating issue | 2017-03-15 00:00:00 |
| abc334    | logeger issue | aniket              | replicating issue | 2017-03-15 00:00:00 |
+-----------+---------------+---------------------+-------------------+---------------------+

SQL Server : self join - retrieve records matching the where clause as well as when no records in alias table


Tag : sql , By : Scott Everts
Date : March 29 2020, 07:55 AM
it should still fix some issue I have a table like below: , interpret directly from your 2 conditions in WHERE clause
select  *
from    TABLE2  t
where   warehouseid = 576
and (
        exists   -- condition 1
        (
            select  *
            from    TABLE2 x
            where   x.itemcode  = t.itemcode
            and x.warehouseid   = 276
            and x.flag      = 'Y'
        )
    or  not exists -- condition 2
        (
            select  *
            from    TABLE2 x
            where   x.itemcode  = t.itemcode
            and x.warehouseid   = 276
        )
    )

Retrieve parent child records in SQL Server


Tag : sql , By : Marianisho
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • Select inner join returns wrong result
  • Group By showing duplicates
  • Error in using subquery in access join query
  • How do I use a composite pk in a stored procedure?
  • Oracle Unique Indexes
  • Spooling data to CSV truncates
  • How do views reduce code duplication?
  • SQL: Gather right hand values from a join
  • SQL Agent Logon - What is going on?
  • How do I combine two rows of same part, but add quantities?
  • How to optimize this SQL query for a rectangular region?
  • Pass data from ssrs subreport to parent
  • how to enter values in rowguid column?
  • Read multiple tables from dataset in Powershell
  • In SQL how to I exclude a record if there are more than 3 characters after a dash
  • What is the difference between check and foreign key?
  • How to return a record from function, executed by INSERT/UPDATE rule (trigger)?
  • SQL order by oldest from Unix Timestamps
  • How to convert decode function in oracle to standard big query
  • Postgresql: An alternative to subqueries to make the query more efficient?
  • Use returned values from a query in the same procedure
  • Sum all numeric columns in database and log results
  • Release item in current year
  • Conditional function not working as intended postgresql
  • SQL trigger, results not Grouped by
  • getting more records than expected with simple query in postgresql
  • get first occurrence of a certain combination of columns
  • Converting string timestamp to local-time zone in Oracle
  • Redshift - Filter out rows that are blank to left or right of a character
  • Teradata - Know the load volume by times of a table
  • How do I replace duplicate data with random data of same length in SQL
  • How to group by using pivot table
  • How can I achieve a Join for merging two tables FULL OUTER JOIN and avoid duplicates?
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • In Django, how can I build a set of criteria from a list?
  • Using replace() inside a loop
  • select user on create view
  • Calculate and aggregate column depending on a value in another colum (SQL)
  • Trigger to track all the insert or update on Oracle Table
  • Update column - remove first and last characters
  • T-SQL - Adding FK to table, make it do drop/null/efault automagically?
  • Get constraints and indexes on materialized views
  • SQL perform AVG after MAX
  • How do you join two tables A and B in order to get a non join column in A with data not in B?
  • Move Non-NULL Values Up Within Partition
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com