Two Count Queries - Calculate % Change In Number Of Orders
Mar 12, 2015
I have a requirement to calculate the % change in the number of orders received today with the number of orders that were received 3 days back. All data is in the same table. There is a received date column.
I have two count(*) queries - one for today and one for 3 days back running separately and getting the results. Is it possible I can get the % change in orders received from 3 days back and today in one query.Also if I want to get the number of orders received today between 12:00am today and current time. How would I modify the query.
I have two tables customers and orders. customerID is the foreign key for order table. If I pass customername I need to get information about each customer how many orders holding?
I'm wanting to get the average number of days between orders in my orders tbl - so I've done a search and found the following sql coded that I have modified for my db tbl's and columns. But when I try and parse it - I get 'Incorrect syntax new the keyword Group' - what am I missing.
SELECT custId, AVG(invDate - priorDate) FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate FROM orders) Group BY custId
I have a table of orders. I was asked to count the orders that fall into specific dollar buckets. Normally I would use a CASE statement for this, but in this case, there are over 100 different buckets!
For example, I need to count the orders in $5 increments up to $400. The CASE statement would look like this:
Code: CASE WHEN sum(revenue) BETWEEN 0.01 AND 5.00 THEN [0.01 to 5.00] WHEN sum(revenue) BETWEEN 5.01 AND 10.00 THEN [5.01 to 10.00] ... WHEN sum(revenue) BETWEEN 395.01 AND 400.00 THEN [395.01 to 400.00]
Is there an easier way to do this, maybe with a loop?
I am trying to create a script file that will get me the total number of orders in july. How exactly would i say july because i know my syntax is wrong and I would be using sum instead of count right?
What i tried
use Cis11101_Northwind Declare @Julycount int Set @Julycount= (Select sum(*) From orders Where OrderDate = 'july') print 'The total orders for july is ' + Cast(@JulyCount as varchar)
I need to write down a sql query wherein in one particular day(user will enter manually), i need to find out a 15 minutes slot wherein purchase order's created or updated are the highest.
i.e. out of 96 slots(15 minute slot each)-I need to find the slot which has maximum number of Purchase orders created or updated.
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?
I need to add days to a date field, my date field is as varchar(20041030 for example) and I need to add 4 days to it, my result should be 20041103, result field is also in varchar,how would I do that, can anyone pls help?
I am writing some scripts to calculate mean (avg) stdevs count and percentile_cont for some data..I am partitioning over multiple columns e.g. partition by year,month, day order by value..I performing unnecessary overhead as I throw away all but the last (or max) value in each window. (I use cte then select max from cte).That is I only care about the avg of the window in its entirety not specific subwindows.
What is the piece of SQL which looks at the average date difference for each enquiry and then sums it all up to give an overall average number of days it takes?
How do I output a row number for a table solely for the purpose ofquerying for a unique row?In my problem, the table from a legacy system does not have a primarykey, so it limits various querying I'd like to do that identifiesuniqueness in the table.The problem is that since I'm using DTS to simply copy the table toSQL, I don't want to create identity rows.
I've got a string of numbers, say 123456 (the actually number is 12 digitslong). I need to calculate the sum of each individual number in the stringof numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).Thanks
There are three methods to consider when calculating the days to pay logic.
Method 1 - Simple : Look for Document Type 2 (Invoice), if "closed at date" > "posting date" then number of days = ("closed at date" - "posting date")
Method 2 - A Document Type 1 (payment) closes a Document Type 2 (Invoice) For this method the formula would be: Payment Record (1) "posting date" - Invoice Record (2) "posting date"
Method 3 - An Invoice closes the payment.
On a payment entry “closed by entry no.” refers to an Invoice entry.
a. In our code we are not on the payment looking for the invoice, we are on the invoice. i. Because of this we need to find the entry that our current invoiced has closed.
I am taking this from a page that has the pascal code that I need to translate to SQL.
IF (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Invoice) AND NOT CustLedgEntry2.Open THEN IF CustLedgEntry2."Closed at Date" > CustLedgEntry2."Posting Date" THEN UpdateDaysToPay(CustLedgEntry2."Closed at Date" - CustLedgEntry2."Posting Date")
[Code] ....
I am also including create table and insert data scripts ...
I have client table which has client_id Eff_from and Eff_to columns.Eff_from and Eff_to are the dates that client is eligible for service. I need to know the average number of days from the day that he became not eligible and new eligibility date .
I have a report that has 41 fields one of these fields is the Admit Date. I need to create a field that shows a count of Admits in the past 12 months .
I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.
AdjusterNameSpecialtyDatestartDateEnd Test Inside Property2009-08-29 2010-07-31 Test Management2010-08-012012-07-31
If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .
Aim – Calculate the number of days between the [CreatedDate] and getdate, however if stage name = ‘Live Transactions’ then Calculate the number of days between [CreatedDate] & [CloseDate]
This is my query so far
SELECT [CreatedDate] ,[StageName] ,[CloseDate] ,DATEDIFF(dd,CONVERT(datetime,[CreatedDate]),GETDATE()) as Age FROM [FDMS].[Dan].[Raz_Reporting_LCS]
Hi, I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)
I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse
declare @WorkingDate as datetime
SELECT @WorkingDate=dt FROM tblCalendar AS c WHERE (@WorkingDays = (SELECT COUNT(*) AS Expr1 FROM tblCalendar AS c2 WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1) AND (IsHoliday = 0)
-- Return the result of the function RETURN convert(varchar(12),@WorkingDate,106)
I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot.I do the same in SQl using the following query
DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) - CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END + CASE WHEN DATEPART(dw, GETDATE()) = 1 THEN 1 ELSE 0 END END
Our fiscal year starts on July 1st. Each month they call a period - so July is period 1, August is period 2, etc.
They are wanting a report that pulls numbers for a given period. There are parameters for them to select the fiscal year and the fiscal period, and then it calculates the numbers for that period. That part works fine.
Now they want me to do some calculations, and one of them is to divide one of the numbers by the # of days since the fiscal year. So if they choose July, it would be 31 days. If they choose August, it would be 61 days, etc. How can I set this up to calculate the number of days when they really aren't entering a start date, it's just a fiscal year and period.
Is there a way to calculate a date field that is 07/01/xxxx where xxxx is the fiscal year they chose? Also a way to calculate a date field that would be the last date of the month for the fiscal period and year they chose?
I suppose I could add 2 other parameters where they enter the start of the current fiscal year, and the last day of the period they're running it for, and use a datediff to calculate that. Just seems kind of redundant.
I have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.
CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime) RETURNS int AS BEGIN /* Description: Function designed to calculate the number of business days (In hours) between two dates.
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
Hi, I'm having problems manipulating the results from multiple count(*) queries.
I have a table 'RECOMMENDATIONS' holding a field called 'SCORE'. This field holds a number from 1 to 10 and has no null or zero figures.
I currently have the following three queries: 1) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6) 2) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (9,10) 3) SELECT COUNT(*) FROM RECOMMENDATIONS
I now need to combine these three queries so that i can divide and multiply the resulst: (query 2/query 1)*query 3
My initial idea was: SELECT (COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)))* (SELECT COUNT(*) FROM RECOMMENDATIONS) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... but this gives a result of "0". I then stripped out the multiplication section to see if the divide was working: SELECT COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
I'm currently in the middle of building quite a large CMS using ASP.NET and MSSQL2K and have began to question if the amount of queries I am using for one page to be built is too many?
For one page (View Forum) I am getting all of the templates and checking access then pulling a list of threads, getting the first and last posts, then user info for the first and last posts... anyway to view 10 threads on the page the number of queries comes to about 54 and the page takes 0.064 seconds to load.
My question is, Is this to many queries to be running for a single page load? All queries are using Stored Procedures.
This is Ravi. I'm working on SSIS 2005 version. I have created the DTSX file from the SQL Server and executed it successfully from my .NET 2005 code.
Now I have a requirement that I need to dynamically change the Source database query. ie., based on the user selection I need to get the data from different tables of SQL and put it into an Excel file.
