Categories
Mastering Development

How to populate a row in MS SQL Server based on a condition (query optimization)

I was working on this problem, Was trying to find all the people who took a timeoff for previous months, I got most of it. But if nobody took a timeoff in the current month, I need to populate a row saying 0 to get my visuals right. This is what my data looks like: […]

Categories
Mastering Development

How to SELECT exact timestamp minutes in SQL? [closed]

I am substracting daily values. For example, I got: timestamp value 2020-03-23 00:00:00.000 3000 2020-03-23 00:15:00.000 3100 2020-03-24 00:00:00.000 3500 2020-03-24 00:15:00.000 4000 and with: SELECT tagtimeutc,description,tagname,unit, ( (LEAD (TagValue) OVER (order by tag_id,TagTimeUTC) – TagValue)),prik_sifr,tag_id FROM vwDMData where TagTimeUTC=DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) and unit=’m3′ or TagTimeUTC=DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0) and unit=’m3′ I […]

Categories
Mastering Development

Distribute Days Between Date Range

I have a table like such: LeaveRequestID | DateAllocated | DateFrom | DateTo | TotalLeaveDays 1 | 2020-03-02 | 2020-03-02 | 2020-03-05 | 3 2 | 2020-03-02 | 2020-03-02 | 2020-03-04 | 2 And here’s my query: SELECT t1.DateAllocated, t2.DateFrom, t2.DateTo, DATEDIFF(DAY, t2.DateFrom, t2.DateTo) TotalLeaveDays FROM tblLeave_Allocation t1 INNER JOIN tblLeave_Requests t2 ON CONVERT(VARCHAR, t1.DateAllocated, […]

Categories
Development

Time Range in SQL Server only for date

I have a table Named Transactions and it holds data like this : transaction_id cust_id tran_date 80712190438 270351 28-02-14 29258453508 270384 27-02-14 58387181112 275068 31-12-13 21300411973 274568 31-12-13 26229626485 267624 31-12-12 26229126485 267634 31-12-12 47557596721 273764 31-12-11 28966519600 273899 31-12-11 Now, what is the Time Range of the Transaction Data available for analysis? and 2nd […]

Categories
Development

I am getting SQL Syntax error which can’t be identified by me

The errors I am getting are: “Msg 102, Level 15, State 1, Line 8 Incorrect syntax near ‘.00’. Msg 102, Level 15, State 1, Line 15 Incorrect syntax near ‘Provider’.”** I am unable to fix the errors. The code is: SET DATEFORMAT ‘dmy’ SELECT d.[ProviderName] AS [Service Provider], [Location Group] AS [‘+@strLocation+’], MIN(c.[From Date]) AS […]

Categories
Development

I am getting SQL Syntax error which can’t be identified by me

The errors I am getting are “Msg 102, Level 15, State 1, Line 8 Incorrect syntax near ‘.00’. Msg 102, Level 15, State 1, Line 15 Incorrect syntax near ‘Provider’.” I am unable to fix the errors. The code is: SET DATEFORMAT ‘dmy’ SELECT d.[ProviderName] AS [Service Provider], [Location Group] AS [‘+@strLocation+’], MIN(c.[From Date]) AS […]

Categories
Development

How Can Create a Pivot for Order Report in SQL Server?

I need T-SQL code for this report, I used pivot table but can’t How Can Create a Pivot for Order Report in SQL Server? I want like this schema SELECT Sum(o.OrderTotal) AS ‘Today’ FROM [Order] AS o WHERE (CreatedOnUtc >= DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))) AND (CreatedOnUtc <= DATEADD(DAY, 1, DATEADD(Hour, […]

Categories
Development

How Can Create a Pivot for Order Report in SQL Server?

I need T-SQL code for this report, I used pivot table but can’t I want like this schema SELECT Sum(o.OrderTotal) AS ‘Today’ FROM [Order] AS o WHERE (CreatedOnUtc >= DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))) AND (CreatedOnUtc <= DATEADD(DAY, 1, DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)))) GROUP BY DATEPART(hour, […]

Categories
Development

Syntax problem with aggregate functions and subqueries

I’m trying to query a 3 table data base but I’m having trouble getting syntax right and I’m not entirely sure what’s the cause behind it. My expected result would be something like this: +—————-+—————–+———————————–+———————————–+———————————–+———————–+——————-+ | Salesperson ID | Salesperson Age | Number of Orders Bigger Than 1000 | Had Order Bigger Than 500? (y/n) […]

Categories
Development

Easter / Good Friday for Sowflake Date Dim

Further Evolved : The code below satisfies what was wanted in earlier comments [if you follow history] :), my new question right below is stumping me. Question: I want to have ‘Week_Of_Year’ Adjust every Sunday, the code below does it every Monday.. I have tried 1 and 0 but via alter session per Snowflake but […]