I have data in a table in this format – where date range is multi-month: I want to create a view/ insert into a new table – the above record broken by month as shown below: Please advise. Answer Just another option using a CROSS APPLY and an ad-hoc tally table Example Returns
Tag: sql-server
MS SQL Server Date Month Output
SQL will output a month part as a number but I’d like to out put the 3 char version (Jan, Feb, Mar etc) My query thus far is Answer For each DBMS the way is different Here are few 4 DBMS’s MS SQL Server MySQL Postgresql Oracle So in your case use the appropriate date field instead of current date
AWS Glue always send a ‘select * ….’ to the SQL Server , why and how to change that?
I’ve an aws Glue JDBC connection to a SQL server in a EC2 server. After crwaling the whole schema I created a job to query some table and used the activity monitor to check what is glue sending to the database, and the queries are just a select * into the whole table… The code that does that is be…
Soundex search with like operator is not working in SQL Server
Soundex search with like operator is not working in SQL Server Answer This line Is wrong, it checks if Country equals the 75 char long string : “US and (Firstname like ‘Ronald%’ or DIFFERENCE (Firstname,’Ronald’) in (4))” What you want is something like
Is there a way to find the highest 90 day total value for any 90 day period in the last year?
I’m thinking the only way to do it is to sum the values between (today – 365) and (today -65 + 90) then move on by 1 day each time, but that would be impractical. Is there a way around it? Answer If you have one row on each day:
SQL Server INNER JOIN and GROUP BY
In sql server I’m trying to group by each sales people some infos as follow: I have 2 tables: Positions and Clients In Positions table, I have the following columns: Client_Id, Balance, Acquisition_Cost and in the Clients table I use the following columns: Client_Id and Sales_person. I want to group by …
Is there a way to have SQL SELECT a column with a different date?
I’m trying to pull a pretty simple report that reviews the last day’s data. It looks similar to this: SELECT Name, Date, Count FROM dbo.X WHERE Date BETWEEN DATEADD(DD, -1, GETDATE()) AND DATEADD(DD, …
Exclude specific rows from table in Stored Procedure
I have a table named Blacklist and table named Order. Both have CustomerId column. Stored Procedure ExecOrder manipulates Order table. My goal is to exclude Orders that have Blacklisted CustomerId (meaning : Order’s CustomerId is in Blacklist table). I edited ExecOrder SP like this: @Temp table returns …
Select * into #Temp not working in dynamic SQL
When I create a temporary table and insert data into that temporary table through dynamic SQL its work fine. But when I use select * into #TempTable1 from YourTable in dynamic SQL it throw error. I am unable to understand the cause of this error. Table: Working Code:- Not Working Code: Error: Msg 208 Level 16…
Iterate over two loops (CURSOR and WHILE) and print the records grouped by location
I want to print the records grouped by each location (in this case they are 2), duly ordered. I’m testing with a cursor but it prints the same location twice. How can I fix this? DECLARE @…