Database

"1" Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Get create table statements for a database under a schema. SELECT TABLE_NAME, 'CREATE TABLE '||TABLE_NAME|| ' ('||STRING_AGG(CONCAT( C1, C2, C3, C4, C5, C6 ),', ')||')' AS QUERY FROM ( SELECT C.TABLE_NAME, '"'||C.COLUMN_NAME||'"' || ' ' || CASE WHEN DATA_TYPE='ARRAY' THEN LTRIM(UDT_NAME,'_')||'[]' ELSE DATA_TYPE END AS C1, CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN '(' || CHARACTER_MAXIMUM_LENGTH || ')' END AS C2, CASE WHEN NUMERIC_PRECISION > 0 AND NUMERIC_SCALE < 1 THEN NULL END AS C3, CASE WHEN NUMERIC_PRECISION > 0 AND NUMERIC_SCALE > 0 THEN NULL END AS C4, CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' END AS C5, CASE WHEN COLUMN_DEFAULT IS NOT NULL AND COLUMN_DEFAULT NOT LIKE 'nextval%' THEN ' DEFAULT' END || ' ' || REPLACE(COLUMN_DEFAULT, '::CHARACTER VARYING', '') AS C6 FROM INFORMATION_SCHEMA.COLUMNS C, INFORMATION_SCHEMA.TABLES T WHERE C.TABLE_CATALOG='tpch' AND T.TABLE_CATALOG='tpch' AND T.TABLE_SCHEMA='public' AND C.TABLE_NAME=T.TABLE_NAME AND C.TABLE_SCHEMA='public' AND T.TABLE_TYPE IN ('BASE TABLE') ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION ) AS STRING_COLUMNS GROUP BY TABLE_NAME

You can't use FOR JSON to generate recursive JSON documents. You'll need to create a recursive user-defined function instead. CREATE OR ALTER FUNCTION dbo.fn_OrganizationJson(@ParentId int) RETURNS nvarchar(max) As BEGIN DECLARE @json nvarchar(max); If @ParentId Is Null BEGIN SET @json = (SELECT Id, Name, dbo.fn_OrganizationJson(Id) As Children FROM dbo.Organizations WHERE ParentId Is Null FOR JSON AUTO); END Else BEGIN SET @json = (SELECT Id, Name, dbo.fn_OrganizationJson(Id) As Children FROM dbo.Organizations WHERE ParentId = @ParentId FOR JSON AUTO); END Return @json; END GO SQL Fiddle[^] NB: In some versions of SQL, you can't create a function that refers to itself if the function doesn't already exists. You may need to CREATE FUNCTION first with a dummy body, and then ALTER FUNCTION to add the implementation. "These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

The language you use does not matter. Your app "description" smells of homework. You're not going to find a "pre-written" program to do this. If you did, and you used it in your own app, and turned it in, you would get a failing grade. Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles. Dave Kreskowiak

Search:"Microsoft.Ace.OLEDB.12.0" [^]

Thanks. I hadn't realised that. OP's sample output has multiple 20220717 dates (minus the suffixes) in it, so I had read it as 'sort all by date + Ort, ignoring the sequence no after the date'/

Well, having multiple back-ends would do the job for some months more, but like you said, this is not a permanent solution.

Assuming your timesheet entry is flat and there is no conflict between in house and of site work I would store it locally in a copy of the server DB. Then simply bulk copy from the local DB to the server. Or It should be a simple exercise to write an off site storage and entry app (excel would do) that you can then load into the server DB. Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

1. Using SQL Query To show the list of tables with the corresponding schema name, run this statement: SELECT * FROM information_schema.tables; or in a particular schema: SELECT * FROM information_schema.tables WHERE table_schema = 'schema_name'; 2. Using psql To list all tables: In all schemas: \dt *.* In a particular schema: \dt schema_name.*

An .SQL file is just a text file with SQL statements in it. You can execute them in C# just like you do for any other query. Just read the file and load the text into a variable and pass that as your statement to an SqlCommand. Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles. Dave Kreskowiak

They both worked great, but I went with the non-pivot version. Thanks, man. ".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013

First filter by PDate = datevariable, order by PDate Descending. Select the top 1 record. Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

Mycroft Holmes wrote: the younger generation like to promote Where that typically means one or more of the following. 1. They are inexperienced. 2. They have no idea how to solve problems that show up 3. They have no expectation that problems will even show up. 4. They have no intention of being the one tasked with fixing the problems.

As you have not shared your attempt at solving this for yourself, nor responded to Craig, I am only going to give you hints to a possible solution. For my test data I used this declare @demo table (startdate date, enddate date) insert into @demo (startdate, enddate) values ('2022-03-01', '2022-04-10'), ('2021-12-01', '2022-03-11'), ('2022-01-01', '2022-04-05'); I also hard-coded the start and end dates for the month I am interested in declare @1stDay date = '2022-04-01'; declare @LastDay date = '2022-04-30'; I then wrote some sql that would convert the dates I had on the table to only those that fell into the month I am looking at. E.g. Something like this select startdate, enddate ,case when startdate < @1stDay then @1stDay when startdate > @lastDay then @LastDay else startdate end as AmendedStartDate ,case when enddate < @1stDay then @1stDay when enddate > @LastDay then @LastDay else enddate end as AmendedEndDate from @demo; I then used those Amended dates in a datediff calculation to get the number of days in each range that fell in April 2022. This approach is flawed - in that I get 9, 0 and 4 as the results instead of 10, 0, 5. I will leave that as an exercise for you to sort out. I also coded this in MSSQL so you may need to make minor syntactical changes

Here is the syntax for two tables INSERT INTO answers (user_id, question_id) (SELECT u.id AS user_id, q.id AS question_id FROM users u, questions q); To learn more about cross join in SQL check this out. Cross Join in SQL | SQL Cross Join - Scaler Topics[^]

Firstly - take note of the previous comment about SQL Injection! Quote: I want the array `$user_tree` to hold (user_id 5) first, then (user_id 6), how to do that? Then you need to order your results by [user_id] e.g. select * from @demo where upline = 2 order by [User_id]; Quote: I want to print **Left_id** first, then only print **Right_id**. May I know how to do it? I'm not entirely sure what you mean here, but it sounds like you want the columns in a different order in your result. In the example above I used select * - that is not actually good practice. It is much better to list the columns that you want, in the order that you want them - this helps protect any code that is using the results from any subsequent changes to the table schema, such as adding a new column. However, if you mean you want to sort by one column within the "grouping" of another column then you simply add a list of things to sort by. This example covers both the scenarios above select Left_id,Right_id from @demo order by Upline, [User_id]; If you meant something else then reply to this solution and I will try to help

You posted it less than 12 hours ago. Half the world won't have woken up and had a chance to to read it yet. And since questions here are answered by volunteers, nobody is under any obligation to answer it at all, let alone within a defined time limit. "These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

The message lists all the values it is trying to use to connect. So it is reasonably safe to assume that one or more of them is not correct. That is the only information you have to work with, so you need to investigate further to discover which one it is.