Hopefully a quick one on BigQuery I’ve tried intervals and days but can’t quite seem to get what I want. For date row on the example table below I want and adjacent row in a new column that just adds 42 days to the original date and time (time is always 00:00:00 if that helps). Desired output belo…
Tag: date
Hive trunc date format issues
I am trying to convert GP to Hive migration logic but below statement giving the wrong output while execute query: output for GP : 2021-12-31 similar if we converted Hive query out put if Hive query : 2022-01-02 i could see the difference of the date. please help me. Thanks Answer You are subtracting interval…
How to carrying over values for missing dates in time series using last value windows analytical functions in mysql
How to carrying over values for missing dates postcode/indicator_category to create full monthly time series. Im trying to use last_value to carry over values but not able to make it. Is my approach correct? Any help would by highly appreciated. Example given a table: INSERT INTO value to indicator_data table…
How to cast this integer value to date in MySQL
i have airbnb data and i want to cast column last_reviews (which datatype is int) to date this is my sql code http://sqlfiddle.com/#!9/b5ea42/31 how do i cast int > date? or how do i create last_reviews column as datatype date? Answer The last_review date seems to be the number of days since 1900-01-01 so …
How to calculate sum of a value with only “day of week” using a date-range?
I have 2 tables namely Item table with details of item_id, store_id, offer_start_Date and offer_end_date Store table has store_id, day_of_week, store_hours The structure of both tables are follows – 1) Item_Table : Store ID Item ID offer_start_Date offer_end_date NY0001 FMC0001 2021-10-30 2021-11-04 NY0…
Raw SELECT (without FROM) of most recent 7 days to current
I want to get the query result (e.g. to populate table) of last 7 dates (without times). I know that we can select some scalars without FROM statement. So I ended up with following solution: Please point me to better (and more elegant) solution if there is one. Answer The VALUES, table value constructor, is a…
SQL Server – Retrieve list of month dates between two columns
I want to add a new column that should contain months between startdate & enddate present in two separate columns. My current data looks something like this : Case Name StartDate EndDate 1 ABC 2021-01-15 2021-03-15 2 DEF 2021-03-15 2021-05-15 My desired output is : Case Name StartDate EndDate MonthList 1 …
Oracle procedure saving SYSDATE
As part of a larger procedure I’m trying to write some code, which saves SYSDATE into a variable but I’m running into a problem. I narrowed it down to the following. Can someone please help me out. Answer In PLSQL you can just assign: if you insist on sql all you have to do is to add into after se…
Oracle SQL Developer – group by and count within specific date
SQL noob, trying to get some help grouping by unique name, providing a count, filtered for only start dates during 2021. This is what I have so far, and why I’m getting lost. my_table: Trying to get count of the Name, filtered for only 2021 start dates. This is what I want my output to look like: My cod…
How can I partition by group that falls within a time range?
I have the following table showing when customers bought a certain product. The data I have is CustomerID, Amount, Dat. I am trying to create the column ProductsIn30Days, which represents how many products a customer bought in the range Dat-30 days inclusive the current day. For example, ProductsIn30Days for …