logo
down
shadow

Join on month if available else join on latest month


Join on month if available else join on latest month

Content Index :

Join on month if available else join on latest month
Tag : sql , By : Ronnie Carlin
Date : November 25 2020, 07:27 PM

will help you You need the req that is valid on or before the date. Let me assume that B has a unique identifier for each row. Then you can get the corresponding row using a correlated subquery:
select a.*,
       (select b.b_id
        from b
        where b.user = a.by and b.AvailableinOrgMonth <= a.CreatedMth 
        limit 1
       ) as b_id
from a;
select a.*, b.*
from (select a.*,
             (select b.b_id
              from b
              where b.user = a.by and b.AvailableinOrgMonth <= a.CreatedMth 
              limit 1
             ) as b_id
      from a
     ) a left join
     b
     on a.b_id = b.b_id;

Comments
No Comments Right Now !

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

Share : facebook icon twitter icon

SQL Group by month and join


Tag : sql , By : Guid
Date : March 29 2020, 07:55 AM
wish of those help I have 3 different queries and need to find a way to display results in a single gridview, but this way is creating 3 different grids. My code is this and try to find a way to join in a sinlge one with rows showing months and columns showing "Laptop", "Pc" and "Total". , Try this
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, activation_date), 0) AS MyMonth, 
COUNT(case when stb_group = 'Laptop' then stb_group end) AS Laptop,
COUNT(case when stb_group = 'PC' then stb_group end) AS PC, 
COUNT(stb_group) AS Units
FROM inventary 
WHERE (activation_date > '10-01-2013')  
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, activation_date), 0)

SQL query to join data based on transaction date to get this month's & previous month's cost


Tag : sql , By : user185751
Date : March 29 2020, 07:55 AM
hope this fix your issue I have a transactional data set with columns like product_id, cost_unit, date, etc. The cost of the product changes month to month. I would like to have a table which has the following structure , If you are using SQL Server 2012 or higher, LEAD or LAG can be used:
DECLARE @Products TABLE (
    Product VARCHAR(100),
    Cost_Unit MONEY,
    [Date] DATETIME
)
INSERT @Products VALUES
    ('Widget', 10, '1/1/2014'),
    ('Widget', 12, '2/1/2014'),
    ('Widget', 15, '3/1/2014'),
    ('Widget', 17, '4/1/2014'),
    ('Gizmo', 20, '1/1/2014'),
    ('Gizmo', 25, '2/1/2014'),
    ('Gizmo', 26, '3/1/2014'),
    ('Gizmo', 27, '4/1/2014')

SELECT
    Product,
    [Date],
    Products.Cost_Unit AS [Cost_This_Month],
    LAG(Products.Cost_Unit) OVER (PARTITION BY Product ORDER BY [Date]) AS [Cost_Last_Month]
FROM @Products Products
ORDER BY Product, [Date] DESC
Product         Date                    Cost_This_Month       Cost_Last_Month
--------------- ----------------------- --------------------- ---------------------
Gizmo           2014-04-01 00:00:00.000 27.00                 26.00
Gizmo           2014-03-01 00:00:00.000 26.00                 25.00
Gizmo           2014-02-01 00:00:00.000 25.00                 20.00
Gizmo           2014-01-01 00:00:00.000 20.00                 NULL
Widget          2014-04-01 00:00:00.000 17.00                 15.00
Widget          2014-03-01 00:00:00.000 15.00                 12.00
Widget          2014-02-01 00:00:00.000 12.00                 10.00
Widget          2014-01-01 00:00:00.000 10.00                 NULL
;WITH CTE AS (
    SELECT
        Product,
        [Date],
        Cost_Unit,
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY [Date] DESC) AS RowNum
    FROM @Products
)
    SELECT
        CM.Product,
        CM.Product,
        CM.Cost_Unit AS [Cost_This_Month],
        LM.Cost_Unit AS [Cost_Last_Month]
    FROM CTE CM
        LEFT OUTER JOIN CTE LM
            ON CM.Product = LM.Product
                AND CM.RowNum = LM.RowNum - 1
-- LAG Solution
SELECT
    Product,
    [Date],
    Products.Cost_Unit AS [Cost_This_Month],
    LAG(Products.Cost_Unit) OVER (
        PARTITION BY Product ORDER BY [Date]) AS [Cost_Last_Month]
FROM (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY Product, YEAR([Date]), MONTH([Date])
        ORDER BY [Date] DESC) AS RowNum
    FROM @Products
    ) Products
WHERE RowNum = 1
ORDER BY Product, [Date] DESC

-- ROW_NUMBER() Solution
;WITH CTE AS (
    SELECT
        Product,
        [Date],
        Cost_Unit,
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY [Date] DESC) AS RowNum
    FROM (
        SELECT *, ROW_NUMBER() OVER (
            PARTITION BY Product, YEAR([Date]), MONTH([Date])
            ORDER BY [Date] DESC) AS RowNum
        FROM @Products
        ) Products
    WHERE Products.RowNum = 1
)
    SELECT
        CM.Product,
        CM.[Date],
        CM.Cost_Unit AS [Cost_This_Month],
        LM.Cost_Unit AS [Cost_Last_Month]
    FROM CTE CM
        LEFT OUTER JOIN CTE LM
            ON CM.Product = LM.Product
                AND CM.RowNum = LM.RowNum - 1

Get all entries younger than one month and the latest entrie older than one month in a single query with mongoose


Tag : node.js , By : Karina
Date : March 29 2020, 07:55 AM
should help you out I'm afraid you can't do this in a single query as the queries are mutually exclusive through the non-overlapping date ranges; you want a latest entry older than a month with a query for entries younger than a month which is not possible.
What you can do however is avoid the callback hell by using the async.parallel utility to run the two queries in parallel. The following example demonstrates this:
var monthAgo = new Date();
monthAgo.setMonth(monthAgo.getMonth() - 1);
var older_query = {
        "createdAt": { "$lt": monthAgo }
    },
    younger_query = {
        "createdAt": { "$gte": monthAgo }
    };

async.parallel({
    older: function(callback) {
        Model.findOne(older_query).sort({"createdAt": -1}).exec(function(err, doc) {            
            callback(null, doc);
        });        
    },
    younger: function(callback) {
        Model.find(younger_query).exec(function(err, docs) {            
            callback(null, docs);
        });
    }
}, function(err, results) {
    console.log(results); // prints for example { older: { createdAt: "date", entry: "foo" }, younger: [{ createdAt: "date", entry: "foo" }, { createdAt: "date", entry: "bar" }, ...]}
});

How do I display the Month, Month + 1, Month +2, Month +3, Month +4 in a separate column depending on the date?


Tag : sql , By : lamberms
Date : March 29 2020, 07:55 AM
will be helpful for those in need You could use CROSS APPLY to calculate the 5 dates in your final SELECT statement and calculate the month-year-strings using DATENAME:
SELECT hotel_id, snapdate, month, year,
  DATENAME(MM, snapdateex) + N' ' + DATENAME(YYYY, snapdateex) AS Month_TY
FROM @snaps
CROSS APPLY (VALUES
    (snapdate),
    (DATEADD(month, 1, snapdate)),
    (DATEADD(month, 2, snapdate)),
    (DATEADD(month, 3, snapdate)),
    (DATEADD(month, 4, snapdate))) AS hlp(snapdateex)

Left Join Column Filter by Month then cross join to a master table


Tag : mysql , By : Tink
Date : March 29 2020, 07:55 AM
it should still fix some issue It's not possible to give a full answer unless you can provide sample data from all your tables but you almost certainly do not need a sub query for every income month and I would make life simpler by unioning month numbers rather than names and then label afterward. For example
drop table if exists maintable;
create table maintable(
Address varchar(10),      Val1 int,        Mat varchar(10));
insert into maintable values
(     'tbla'    ,    10    ,      'Mat1'),  
(     'tbla'    ,     2    ,      'Mat2'),     
(     'tbla'    ,    23    ,      'Mat3'),    
(     'tbla'    ,     5    ,      'Mat4');

drop table if exists income;
create table income(id int auto_increment primary key, mat varchar(10),dt date,amount int);

insert into income (mat,dt,amount) values
('mat2','2018-01-01',10),('mat2','2018-01-01',20),('mat3','2018-01-01',10),
('mat1','2018-02-01',1),('mat2','2018-02-01',7),('mat3','2018-02-01',8),('mat4','2018-02-01',9),
('mat3','2018-10-01',10);


select s.address,s.mat,s.val1,
        coalesce(i.income,0) income, 
        case    when s.monthcreated = 1 then 'jan'
                when s.monthcreated = 2 then 'feb'
                when s.monthcreated = 3 then 'mar'
                when s.monthcreated = 4 then 'apr'
                when s.monthcreated = 5 then 'may'
                when s.monthcreated = 6 then 'jun'
                when s.monthcreated = 7 then 'jul'
                when s.monthcreated = 8 then 'aug'
                when s.monthcreated = 9 then 'sep'
                when s.monthcreated = 10 then 'oct'
                when s.monthcreated = 11 then 'nov'
                when s.monthcreated = 1 then 'dec'
        end as mmm

from
(
select all_months.* , maintable.*
from
(SELECT 1 AS MonthCreated UNION ALL
     SELECT 2 UNION ALL 
     SELECT 3 UNION ALL
     SELECT 4 UNION ALL
     SELECT 5 UNION ALL
     SELECT 6 UNION ALL
     SELECT 7 UNION ALL
     SELECT 8 UNION ALL
     SELECT 9 UNION ALL
     SELECT 10 UNION ALL
     SELECT 11 UNION ALL
     SELECT 12) AS all_months 
cross join maintable
) s
left join
(
select mat mati,month(dt) mm ,sum(Amount) income
from income
where dt between '2018-01-01' and '2018-12-31'
group by mat,month(dt)
) i
on i.mati = s.mat and i.mm = s.monthcreated
order by s.monthcreated,s.mat;
+---------+------+------+--------+------+
| Address | Mat  | Val1 | income | mmm  |
+---------+------+------+--------+------+
| tbla    | Mat1 |   10 |      0 | jan  |
| tbla    | Mat2 |    2 |     30 | jan  |
| tbla    | Mat3 |   23 |     10 | jan  |
| tbla    | Mat4 |    5 |      0 | jan  |
| tbla    | Mat1 |   10 |      1 | feb  |
| tbla    | Mat2 |    2 |      7 | feb  |
| tbla    | Mat3 |   23 |      8 | feb  |
| tbla    | Mat4 |    5 |      9 | feb  |
| tbla    | Mat1 |   10 |      0 | mar  |
| tbla    | Mat2 |    2 |      0 | mar  |
| tbla    | Mat3 |   23 |      0 | mar  |
| tbla    | Mat4 |    5 |      0 | mar  |
| tbla    | Mat1 |   10 |      0 | apr  |
| tbla    | Mat2 |    2 |      0 | apr  |
| tbla    | Mat3 |   23 |      0 | apr  |
| tbla    | Mat4 |    5 |      0 | apr  |
| tbla    | Mat1 |   10 |      0 | may  |
| tbla    | Mat2 |    2 |      0 | may  |
| tbla    | Mat3 |   23 |      0 | may  |
| tbla    | Mat4 |    5 |      0 | may  |
| tbla    | Mat1 |   10 |      0 | jun  |
| tbla    | Mat2 |    2 |      0 | jun  |
| tbla    | Mat3 |   23 |      0 | jun  |
| tbla    | Mat4 |    5 |      0 | jun  |
| tbla    | Mat1 |   10 |      0 | jul  |
| tbla    | Mat2 |    2 |      0 | jul  |
| tbla    | Mat3 |   23 |      0 | jul  |
| tbla    | Mat4 |    5 |      0 | jul  |
| tbla    | Mat1 |   10 |      0 | aug  |
| tbla    | Mat2 |    2 |      0 | aug  |
| tbla    | Mat3 |   23 |      0 | aug  |
| tbla    | Mat4 |    5 |      0 | aug  |
| tbla    | Mat1 |   10 |      0 | sep  |
| tbla    | Mat2 |    2 |      0 | sep  |
| tbla    | Mat3 |   23 |      0 | sep  |
| tbla    | Mat4 |    5 |      0 | sep  |
| tbla    | Mat1 |   10 |      0 | oct  |
| tbla    | Mat2 |    2 |      0 | oct  |
| tbla    | Mat3 |   23 |     10 | oct  |
| tbla    | Mat4 |    5 |      0 | oct  |
| tbla    | Mat1 |   10 |      0 | nov  |
| tbla    | Mat2 |    2 |      0 | nov  |
| tbla    | Mat3 |   23 |      0 | nov  |
| tbla    | Mat4 |    5 |      0 | nov  |
| tbla    | Mat1 |   10 |      0 | NULL |
| tbla    | Mat2 |    2 |      0 | NULL |
| tbla    | Mat3 |   23 |      0 | NULL |
| tbla    | Mat4 |    5 |      0 | NULL |
+---------+------+------+--------+------+
48 rows in set (0.00 sec)
Related Posts Related QUESTIONS :
  • 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
  • Selecting rows based on two many-to-many relations
  • I have a table on which i want to do some advance calculation and get the table in a new shape
  • How to convert decimal to time in Oracle SQL?
  • Combine numbers to 1 row
  • Error: column does not exist when in fact it does? why?
  • How to fix: "Operation must use an updateable query" on update statement in Access VBA
  • Does information passes to a new table via CREATE TABLE update itself?
  • How to write a SQL statement which filters between two dates/times, with the date and time in separate columns
  • MS-Access-2016 Query Language 'ORDER BY' Clause
  • How to get totals for each age category?
  • get the first n characters of getdate()
  • Query to handle the dates
  • SQL grouping/counting on a string split function
  • ORDER BY and TOP statements in SQL works different on listing down the records and why?
  • SQL Server Computed Column: how to set it to NOT NULL, without persistence
  • Is nested select statement better than where statement?
  • Postgresql sequential scan slow performance on 500 million rows
  • Aggregate first order data to all orders - Microsoft SQL Server
  • Sorting comma delimited datasets in row
  • Question about the correct data structure
  • Showing two temp tables data in a single table
  • How can I find out the query execution time in Oracle SQL using Toad?
  • convert quarter number to month name in sql
  • Find max value from column that has a json object with key-value pairs
  • case statement based on 3 columns in 1 table
  • How to write a SQL query to select batsman wiht highest score than the previous year
  • Find Substring in SQL
  • Using VALUE to make temporary table
  • What is the correct way of writing an exclusive left-join on two tables?
  • Is it possible to run a make table query using a recordset as the source in MS Access (VBA)?
  • INSERT INTO select distinct null PostgreSQL
  • Why converting date from nvarchar(max) to date throws error?
  • how to fix wm_concat in oracle 12c
  • How to UNION a list of tables retrieved from another table with a single query?
  • Oracle query to get a relational dataset result from multimensional table
  • What algorithm is used in a hash partition that it can divide up the data into similar size partitions before seeing any
  • Why does my SQL return unwanted records with my OR in Where clause
  • SQL Multiple Joins Query-Query tuning
  • How to choose max of one column per other column
  • Query that returns rows that have both values in an IN statement
  • Select Unique records from table
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com