logo
down
shadow

Finding max(date) from the resultant query output


Finding max(date) from the resultant query output

By : user2956286
Date : November 22 2020, 10:54 AM
wish help you to fix your issue The answers given by Hadi and Sarath seem to be working, But the cursor seems to affect the performance. So I did this using TEMP Table as below:
code :
DECLARE @OBJECT_NAME VARCHAR(50) = 'ObjName'      

BEGIN
DECLARE @Query NVARCHAR(1000),@COUNT INT, @MAX_Count INT, @rptName VARCHAR(255)
DECLARE @tblName varchar(100),@dateColName varchar(100), @max_Date Date,@fileName varchar(100),@refID INT

DECLARE @Temp_Table TABLE([RowNumber] INT,rptName VARCHAR(500),SrcTblName VARCHAR(500), DateColName VARCHAR(100), SrcFileName VARCHAR(600), RefID INT)
INSERT INTO @Temp_Table
SELECT
    ROW_NUMBER()OVER(ORDER BY rptName)
    ,rptName
    ,srcTblName
    ,srcDateColName
    ,srcFileName
    ,ID
FROM
    table1
WHERE
    objName  = @OBJECT_NAME

SELECT @MAX_Count = MAX(RowNumber) FROM @Temp_Table
SET @COUNT = 1
WHILE (@COUNT <= @MAX_Count)
BEGIN                   
    SELECT   
        @rptName = rptName,
        @tblName = SrcTblName,
        @fileName = SrcFileName,
        @dateColName = DateColName,
        @refID = RefID    
    FROM   
        @Temp_Table   
    WHERE   
        RowNumber = @COUNT 

    IF @tblName IS NOT NULL AND @fileName IS NOT NULL AND @dateColName IS NOT NULL
    BEGIN
        SET @Query = 'SELECT @max_Date = MAX(CONVERT(DATE, ' + @dateColName + ')) FROM ' + @tblName
            EXEC SP_EXECUTESQL @Query, N'@max_Date DATE OUTPUT', @max_DATE OUTPUT

    END
SET @COUNT = @COUNT + 1
END

END


Share : facebook icon twitter icon
Skipping rows in sql query (finding end date based on start date and worked days)

Skipping rows in sql query (finding end date based on start date and worked days)


By : Kenny
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , You could have a where clause that says there must be N working days between the start and the end day. Unlike the row_number() variants, this should work in MS Access. For example:
code :
declare @Task table (taskid int, empid int, start date, days int)
insert @Task values (1, 1, '2010-01-01', 1)
insert @Task values (2, 1, '2010-01-01', 2)
insert @Task values (3, 1, '2010-01-01', 3)

declare @WorkableDays table (empid int, day date)
insert @WorkableDays values (1, '2010-01-01')
insert @WorkableDays values (1, '2010-01-02')
insert @WorkableDays values (1, '2010-01-05')

select  t.taskid
,       t.start
,       endday.day as end
from    @Task t
join    @WorkableDays endday
on      endday.empid = t.empid
where   t.days = 
        (
        select  COUNT(*)
        from    @WorkableDays wd
        where   wd.empId = t.empId
                and wd.day between t.start and endday.day
        )
taskid   start       end
1        2010-01-01  2010-01-01
2        2010-01-01  2010-01-02
3        2010-01-01  2010-01-05
I have an sql query for finding a nearest free time after specific date. How can I make it to also look for a date BEFOR

I have an sql query for finding a nearest free time after specific date. How can I make it to also look for a date BEFOR


By : Carlos Vega Melendez
Date : March 29 2020, 07:55 AM
To fix the issue you can do A simple solution for this would be to use date_sub with an order by statement, limiting the results to show only 1 record.
This is the result:
code :
SELECT date_sub(start_time, interval duration second) as free_before FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1
select * from (SELECT date_sub(start_time, interval duration second) as free_times FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1) a
union
(SELECT (a.start_time + INTERVAL a.duration SECOND) AS free_times FROM notes a
WHERE
NOT EXISTS ( SELECT 1 FROM notes b WHERE b.start_time
BETWEEN (a.start_time + INTERVAL a.duration SECOND) AND
(a.start_time + INTERVAL a.duration SECOND) + INTERVAL 15 SECOND - INTERVAL 1 MICROSECOND) AND
(a.start_time + INTERVAL a.duration SECOND) BETWEEN '2015-10-21 19:41:30' AND '2015-10-21 19:43:50')
SELECT date_sub(start_time, interval 10 second) as free_times FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1
SELECT date_sub(start_time, interval 15 second) as free_times FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1
Join 3 maps (2 master map, 1 resultant map), to map the composite key in the resultant map to have values from the maste

Join 3 maps (2 master map, 1 resultant map), to map the composite key in the resultant map to have values from the maste


By : Melissa Donahue
Date : March 29 2020, 07:55 AM
Hope that helps I have one map containing some master data(id->description): val map1: Map[String, String] = Map("001" -> "ABCD", "002" -> "MNOP", "003" -> "WXYZ") , This should be it :
code :
map3.map{
    case((key1, key2), d) => ((map1.getOrElse(key1, key1), map2.getOrElse(key2, key2)),d)
  }
I have a table and i resultant output as I shown in description

I have a table and i resultant output as I shown in description


By : user3124945
Date : March 29 2020, 07:55 AM
this will help Oracle has a convenient function NVL2() that does this simply:
code :
SELECT empid, empname, SUM(Tax) as Total_tax,
       MAX(Aadhar) as Aadhar_value,
       NVL2(MAX(Aadhar), 'Y', 'N') as Aadhar
FROM emp_workout
GROUP BY empid, empname;
mysql query for finding date of 4days after current date

mysql query for finding date of 4days after current date


By : Nahom Kibreab
Date : March 29 2020, 07:55 AM
hope this fix your issue what is the query to find out date which should be after 4 days of curdate I tried , You can use this:
Related Posts Related Posts :
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • SQL Server error: Column name or number of supplied values does not match table definition
  • How to find a table name which's column name consist cl_
  • Cannot access existing table from WCF Code
  • SQL CREATE VIEW Error
  • Many Statement in the same transaction PLSQL
  • Tables are not joining properly in sql server 2008
  • DB2 how to sum two column from two different table
  • SQL msAccess: list unfinished courses of a student
  • SQL Server delete multiple rows
  • updating multiple columns using case statement in sql server
  • Nested SQL query having select subquery in update statement
  • "putting" a certain object to the screen from inside an array
  • Retrieve segment from value
  • Error while creating a table in oracle 11g
  • Transaction with Sequelize doesn't work
  • Search time complexity of this sql query
  • How to return unique records between two tables without using distinct and union?
  • Is it possible to maximum occurences of a value for each key in my table all in one query?
  • Divide table into groups of 5000 based on rank
  • rolling back exec and sp_OA methods
  • How to convert multiple rows to one row in SQL Server?
  • How to use substring and charindex in google query language
  • Find table information where part of column matches char variable
  • Need to retrieve rows from table where the following condition was not satisfied column A = column B and column B = colu
  • Assigning an index to a constraint
  • Retrieve records from multiple Records returned by Sub-Query
  • SQL DELETE group of records based on opposite group being empty
  • SQL GetDate() returns wrong time
  • Total sales amount for customers in cities
  • SQL Server -- updating the `sys.*` tables and not just reading them
  • SQL Order By using concat
  • SQL server 2008 - subquery returned more than one value
  • Group Data Hourly and Insert Into a Summary Table in Postgres
  • Execute SQL Server Pass-Through Query From Access VBA
  • MS Access SQL Transform Aggregate Manipluation of Values for Pivot
  • SQL "if exists..." dynamic query
  • HANA: Split string?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org