Query to Insert Data Based on CustomerID from one table to another
-
Hello All, Below is the my existing table structure from which I need to insert records to a new table
I need a query to insert records into a new table for each customer for same date. For example considering above data structure query should insert records into a new table as below based on date and customer id.
Total Employee count for customerid 4 for date 22-04-2023 would be 33+7=40(sum of emp count for the customer id for specific date) in the same way for the customer id 5 for date 22-04-2023 would be 40+1=41 in the new table and since this will be first records in the new table Movedin & Movedout count will be 0.
From the later dates based on the previous days emp count moved in or moved out count has be inserted.
Any help here would be really appreciated.
-
If you want tested code, please provide a script to create a sample data and expected results in the form of a script to create temp tables with insert scripts. That being said, the first part of your question seems fairly straightforward. What have you tried? The second part you need to use
LAG()(orLEAD()) to get the previous days values.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
What happens if/when there are gaps in the CreatedDate column for particular CustomerID's?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
declare @torig table (custid int, employeecount int, datadate date)
insert into @torig values
(1, 10, '1-Jan-2023'), -- 21
(1, 11, '1-Jan-2023'),
(2, 11, '1-Jan-2023'),
(1, 15, '2-Jan-2023'), -- 28
(1, 11, '2-Jan-2023'),
(1, 2, '2-Jan-2023'),
(2, 11, '2-Jan-2023'),
(1, 14, '3-Jan-2023'), -- 14
(1, 19, '3-Jan-2023'), -- 14
(2, 14, '3-Jan-2023')
;with groups as (
select
custid, datadate, SUM(employeecount) as totalemps
from @torig group by custid, datadate
)
select
custid, datadate, totalemps,
totalemps - COALESCE(LAG(totalemps, 1) OVER
(PARTITION BY custid order by datadate),totalEmps) as NumInOrOut
from groupsI was interested so I threw my own example together. This would ignore gaps in the ranges and just take whatever was the last data point for a given customer. Hope this is helpful. Fundamentally you need to get the base data into one row per customer per date, then you can use LAG (other options are available here) to compare to the value in the previous row. The coalesce to "totalEmps" on the end of the second-to-last line is so that on the first entry you don't see any "In" count. If you wanted to see them as an "In", coalesce to zero instead.
The CTE could just be done as a derived table - I just think CTEs are clearer to read sometimes.
-------------------------------Oh no!
-
If you'd take the time to convert the data for BaseTable_A into a "Readily Consumable" format (please see the article at the first link in my signature line below for one way to do such a thing), you'd get coded answers instead of suggestions.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Kevin Gill wrote:
declare @torig table (custid int, employeecount int, datadate date)
insert into @torig values
(1, 10, '1-Jan-2023'), -- 21
(1, 11, '1-Jan-2023'),
(2, 11, '1-Jan-2023'),
(1, 15, '2-Jan-2023'), -- 28
(1, 11, '2-Jan-2023'),
(1, 2, '2-Jan-2023'),
(2, 11, '2-Jan-2023'),
(1, 14, '3-Jan-2023'), -- 14
(1, 19, '3-Jan-2023'), -- 14
(2, 14, '3-Jan-2023')
;with groups as (
select
custid, datadate, SUM(employeecount) as totalemps
from @torig group by custid, datadate
)
select
custid, datadate, totalemps,
totalemps - COALESCE(LAG(totalemps, 1) OVER
(PARTITION BY custid order by datadate),totalEmps) as NumInOrOut
from groupsI was interested so I threw my own example together. This would ignore gaps in the ranges and just take whatever was the last data point for a given customer. Hope this is helpful. Fundamentally you need to get the base data into one row per customer per date, then you can use LAG (other options are available here) to compare to the value in the previous row. The coalesce to "totalEmps" on the end of the second-to-last line is so that on the first entry you don't see any "In" count. If you wanted to see them as an "In", coalesce to zero instead.
The CTE could just be done as a derived table - I just think CTEs are clearer to read sometimes.
The
COALESCE()is unnecessary if you use the third argument forLAG()totalemps - LAG(totalemps, 1, totalemps) OVER (PARTITION BY custid order by datadate) as NumInOrOut
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - Jeff Moden wrote:
If you'd take the time to convert the data for BaseTable_A into a "Readily Consumable" format (please see the article at the first link in my signature line below for one way to do such a thing), you'd get coded answers instead of suggestions.
@pingeli dileep ,
Just imagine... if you were to do as above, you could get the perfect answer to your question. Even the column names would be correct. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ah nice I've used LAG so infrequently that I wasn't aware of that one. Thanks
-------------------------------Oh no!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply