sql – How do I extract data from a main table based on this specific scenario?

I am using SQL Server 2014. I have a table (T1) which contains the list of IDs of cancelled bookings and their equivalent re-bookings.

Extract of table T1:

CancelledID       Re-bookingID
  301                754
  387                801
  400                900
  ...

Each CancelledID has a unique equivalent in the Re-bookingID column.

I have another table (T2) which contains the list of ALL BookingIDs with additional information related to each ID. An extract of this table is shown below:

BookingID     MonthOfStay      RoomNights
...
301             2019-03-01        10
387             2019-04-01         7
400             2019-03-01         5
754             2019-08-01        10
801             2019-09-01         3
900             2019-07-01         5
900             2019-08-01         4
...   

I need a t-sql query which will me the following output:

  BookingID       Cancelled_MonthOfStay     Re-booking_MonthOfStay     RoomNights
    301                2019-03-01                                           10
    387                2019-04-01                                            7
    400                2019-03-01                                            5
    754                                           2019-08-01                10
    801                                           2019-09-01                 3
    900                                           2019-07-01                 5
    900                                           2019-08-01                 4

As you can see, a re-booking can span over 2 months with additional room nights.

I am thinking about “Joins” between the 2 tables but I am stuck at the logic to be used for the “Joins” (if that is the right way of tackling the problem).

Any help would be appreciated.

Is it possible to extract entire blogspot blog?

Is it possible to save/export/download/print/whatever all posts, including comments, from a blog into .pdf files?

I don’t mean my blog, with access to dashboard, but any blog, the one I access as reader.

I’d like to preserve as .pdf and/or to print some blogs I have been reading for a long time; they all have thousands of posts, so opening and exporting posts one by one is not an option. Several of those blogs are at blogspot, and several on self-hosted WP.

Site scrapper such as HTTrack is halfway to what I want; it downloads everything in one go, but then I have to convert .htm files to .pdf and merge them. Is there a neater way I can transform a blog into nice e-book?

Thanks in advance.
SEMrush

 

postgresql – In Postgres, what’s the most optimal way to extract and use a numeric value from within a JSONB field?

Most sources (e.g. this answer) seem to indicate that the canonical way to extract a numeric data type from a JSONB column with values like, say, {"foo": 1.2345} is to do (col->>'foo')::numeric.

Theoretically, though, the semantics are that this first needs to be converted from JSONB (which is fully capable of representing numerics in a binary format) to a textual representation of the characters, then parsed as a numeric.

If this is done in a tight loop, or as the basis for complicated comparisons, perhaps visiting the same row multiple times due to a join, does Postgres automatically know that it doesn’t need to format-and-parse these numbers every time, and does it optimize (or even JIT) accordingly? Is there a better way to do this?

sharepoint online – extract “all” numerics from text string

I have a Sharepoint Online list with a text field for a contact called Mobile Phone…
I want to extract the numerics only…

(111) 222-3333 would be 1112223333
+99 111-222-3333 would be 991112223333
111-222-3333 would be 1112223333
111 222 3333 would be 1112223333

I was expecting to be able to do this with a calculated column, but it’s not as obvious as I thought. Ultimately our dialing system needs a numerics only version of the value (no special characters or spacing). I have seen similar questions answered here but not this exact need. Thanks in advance…

python – Function to extract Top 10 in State

Was told to post this over here..

In the code below Ive been extracting the top ten non-profits based on Income from each state, putting them into a variable and then merging everything into a new list. In order for me to do it with my limited skills is to just go in a manually exchange the, for example, ny & ‘NY’. I’m wondering if theres a way to code with in a loop or something.. Appreciate any help!!

ma = ds1(ds1.STATE == 'MA').sort_values('INCOME_AMT', ascending=False)(('NAME', 'CITY', 'STATE', 'ASSET_AMT',
                                                     'INCOME_AMT','REVENUE_AMT')).head(10)
ny = ds1(ds1.STATE == 'NY').sort_values('INCOME_AMT', ascending=False)(('NAME', 'CITY', 'STATE', 'ASSET_AMT',
                                                     'INCOME_AMT','REVENUE_AMT')).head(10)

...
merged_state_list = (ma,ny,nj,me,nh,vt,ct,ri,ky,va,In,mi,md,dc,pa,sc,oh,wi,
                     nd,nc,il,mn,de,wv,ia,sd,ne,fl,mo,ca,tx,co,tn,ar,ks,ga,
                     al,az,ut,wa,ok,hi,la,nv,ms,Id,ak,nm,mt,Or,wy)

final_set = pd.concat(merged_state_list)

Better way to extract html table to dictionary using beautifulsoup in python

You’re repeating a lot of code in this program. It seems like as the length of acts gets larger, you expand to analyzing acts. This is a perfect opportunity for a loop. What I did was get the length of acts at the very beginning, then base my loop of that. Since you always want the last element you find, this works great. I’ll explain below what I did in places that seem confusing.

from bs4 import BeautifulSoup as bs

site_file = open('samplePage.html')
soup = bs(site_file, 'html.parser')

acts = soup.select('#act_table td:nth-of-type(1)')
sections = soup.select('#act_table td:nth-of-type(2)')
dictionary = {}

ipc = 'indian penal code'
poa = 'prevention of atrocities'
pcso = 'protection of children from sexual'
pcr = 'protection of civil rights'

code_dict = {ipc: "IPC", poa: "PoA", pcso: "PCSO", pcr: "PCR"}

ACT_LENGTH = len(acts) if len(acts) < 5 else 5

if len(acts) > 0:
    collected_acts = (tuple(acts(i).contents) for i in range(ACT_LENGTH))
    collected_sections = (tuple(sections(i).contents) for i in range(ACT_LENGTH))
else:
    print("No Act Mentioned")

for i in range(ACT_LENGTH):
    act = str(collected_acts(i)).lower()
    accepted = (code_dict(code) for code in code_dict.keys() if code in act)
    for code in accepted:
        dictionary(code) = collected_sections(i)
print(dictionary)

site_file.close()

The reason ACT_LENGTH is written that way is because once the length of acts is bigger than 5, you only go a set amount instead of the length. Because of this, we want to only loop up to four because of how range works. (range(INCLUSIVE, EXCLUSIVE)).

Instead of defining new variables based on how big acts is, we can simply use list comprehension to create a list of variables as big as acts.

Instead of checking each individual key with its own if statement, we can organize all the “accepted”, meaning codes that are in act, into a list and loop through those to add to the dictionary.

It’s always good practice to close a file once you’re done using it.

filtering – How to extract all possible sub-products which match specific pattern?

I have an expression of the form

expr = 3*(a.b)*(c.d)*f(x) + 5*(m.n)*(p.q)*(r.s)*f(y)

and want to extract all possible subexpressions matching the pattern ((_).(_))*f(_).
Expected result (in arbitrary order) is:

{(a.b)*f(x), (c.d)*f(x), (m.n)*f(y), (p.q)*f(y), (r.s)*f(y)}

Built-in function Cases seem to ignore Flat Attribute of Times and gives nothing:

Cases(expr, ((_).(_))*f(_), Infinity)

{}

How can I get all possible subexpressions matching the pattern ((_).(_))*f(_)?

google sheets – Extract Data from Sheeet2 and into Sheet1 (Tabs instead of actual spreadsheets)

This may be a little confusing to understand but in a simple word, I want to be able to extract selective data from Sheet2 into Sheet 1.

Example. in Sheet1 Cells A8:A50 are empty, I want to be able to either type in a sheet (name) into one of those columns and the sheet to extract data from Sheet2

UPDATED: Explanation with Images.

enter image description here

Here in the image one, you can see I am inside my Overview tab (Main Tab). I have selected a box around CELL A8. I would like to be able to type the Sheets name into CELL A8 from the Sheets I already own “AAPL”, “DIS” or have the entire row automatically update every time a new Sheet is made.

enter image description here

Let’s say, I typed AAPL inside CELL A8, I want the formula to automatically head inside the AAPL tab and grab content from there and bring it into the Overview TAB and paste it inside the box below the “Company Name”

enter image description here

Something like this, I know it’s possible but it’s beyond my ability and in need of some guidance on how to get this complete.

Update: A gentleman said this is possible using Google Script. I am new to the script side of this – anyone able to recreate something as to what I am asking I’ll send you £10 via cashapp or PayPal as part of an appreciation for your effort. Unfortunately, I cannot share the script as it’s my main stock portfolio.

Second Update: I have discovered the beautiful but yet simple function “=Sheet2!A1” – That grabs any information for Sheet2 and brings it straight into that Cell. With a little bit of creativity, it’s working pretty well so far.

SQL server – Extract the source of user permissions in a database

I have a developer who just accidentally ran an UPDATE and ALTER TABLE script he was working on in a production database instead of the development environment.

How do you know which security group has authorized such behavior? I have the AD connection used, as well as the target database.

I could go the long way, examining each unique AD group and subgroup of which this user is a member, and checking each one if it grants some sort of security on my SQL server. But it will take hours. And that would only cover one user out of a dozen.

I wish to follow the authorizations (other than READ or SEE THE DEFINITION) and their AD members. Either you select an AD user and look at what they have access to, or the other way around, by selecting a database and getting everyone who has access to it (and how).

I can also manage a Powershell solution if someone has code to share (beginner PS).

Thank you

blockchain – Is there a method to extract transactions for a bitcoin address which is not in my wallet using the bitcoin kernel?

I have configured the Bitcoin kernel and am trying to create an API to retrieve all transactions under one address (not in my wallet).

The Bitcoin core RPC documentation says that an address that is not in my wallet should be imported. So I did the same using importaddress when defining the new analysis, for example importaddress '35z3Re9SgLHBfeg7y1HdR35t9HrTqTsKFV' 'test' true. It took more than a week to reanalyze the entire blockchain database.

After importing the address, when I issued the getaddressinfo RPC order I received an empty set of transactions in response.

Am I missing something? Can you help me here with the steps?