T-SQL (SS2K8) :: Query That Can Provide 6 Month Forecast?
Dec 17, 2014
Background is that I am recreating charts in SSRS that were being created in Excel. The source data is residing in a SQL Server database. I'm having a hard time coming up with a SQL query to provide a 6 month forecast. I can get my data into a pivot (within a stored procedure) in the following format:
Period---------Date--------------------------------Percent
1--------------2013-11-01 00:00:00.000---------0.3762
2--------------2013-12-01 00:00:00.000---------0.3584
3--------------2014-01-01 00:00:00.000---------0.3604
4--------------2014-02-01 00:00:00.000---------0.3292
5--------------2014-03-01 00:00:00.000---------0.3519
[Code] ....
I need to forecast the next 6 dates 12/1/2014 thru 5/1/2015 using the last 6 months in the data set (periods 8 thru 13)
Period---------Date--------------------------------Percent-------Forecast
1--------------2013-11-01 00:00:00.000---------0.3762
2--------------2013-12-01 00:00:00.000---------0.3584
3--------------2014-01-01 00:00:00.000---------0.3604
4--------------2014-02-01 00:00:00.000---------0.3292
5--------------2014-03-01 00:00:00.000---------0.3519
6--------------2014-04-01 00:00:00.000---------0.4064
[code].....
how to use the first table to generate the forecast values in the second table.
View 4 Replies
ADVERTISEMENT
Apr 22, 2015
following table global_usage
ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01
I've tried a few thing like group,having even row_number() but I keep getting wrong results
View 6 Replies
View Related
Sep 10, 2014
This is my table and data
CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484
I need to convert into this result
CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months
View 5 Replies
View Related
Jun 2, 2008
hi!
select min(dbo.FS_ItemInventory.Bin) from
(SELECT MIN(dbo.FS_LotTrace.LotNumber),dbo.FS_ItemInventory.Bin FROM
dbo.FS_LotTrace RIGHT OUTER JOIN dbo.FS_MOHeader AS h INNER JOIN
dbo.FS_MOLine AS l ON l.MOHeaderKey = h.MOHeaderKey INNER JOIN
dbo.FS_Item AS i ON i.ItemKey = l.ItemKey INNER JOIN
dbo.FS_MOLineData ON l.MOLineKey = dbo.FS_MOLineData.MOLineKey LEFT OUTER JOIN
dbo.FS_DemandSupply AS ds ON l.MOLineKey = ds.TopLevelDemandSupplyKey LEFT OUTER JOIN
dbo.FS_ItemInventory RIGHT OUTER JOIN
dbo.FS_Item AS di ON dbo.FS_ItemInventory.ItemKey = di.ItemKey on ds.DemandItemKey = di.ItemKey on
dbo.FS_LotTrace.LotTraceKey = dbo.FS_ItemInventory.LotTraceKey
where (dbo.FS_ItemInventory.InventoryCategory!='H' or dbo.FS_ItemInventory.InventoryCategory is null) group by di.ItemNumber,dbo.FS_ItemInventory.Bin)
when i tried this query,
i get a error like
Incorrect syntax near ')'
pls provide the correct query for this.
reg,
s.srini
View 3 Replies
View Related
Jun 2, 2008
hi!
select min(dbo.FS_ItemInventory.Bin) from
(SELECT MIN(dbo.FS_LotTrace.LotNumber),dbo.FS_ItemInventory.Bin FROM
dbo.FS_LotTrace RIGHT OUTER JOIN dbo.FS_MOHeader AS h INNER JOIN
dbo.FS_MOLine AS l ON l.MOHeaderKey = h.MOHeaderKey INNER JOIN
dbo.FS_Item AS i ON i.ItemKey = l.ItemKey INNER JOIN
dbo.FS_MOLineData ON l.MOLineKey = dbo.FS_MOLineData.MOLineKey LEFT OUTER JOIN
dbo.FS_DemandSupply AS ds ON l.MOLineKey = ds.TopLevelDemandSupplyKey LEFT OUTER JOIN
dbo.FS_ItemInventory RIGHT OUTER JOIN
dbo.FS_Item AS di ON dbo.FS_ItemInventory.ItemKey = di.ItemKey on ds.DemandItemKey = di.ItemKey on
dbo.FS_LotTrace.LotTraceKey = dbo.FS_ItemInventory.LotTraceKey
where (dbo.FS_ItemInventory.InventoryCategory!='H' or dbo.FS_ItemInventory.InventoryCategory is null) group by di.ItemNumber,dbo.FS_ItemInventory.Bin)
when i tried this query,
i get a error like
Incorrect syntax near ')'
pls provide the correct query for this.
reg,
s.srini
View 1 Replies
View Related
May 19, 2014
I've the table like
create table expense
(
bill_date datetime,
travel int,
fixed int,
food int,
lodge int
)
insert into expense values('01-04-2014',1200,250,0,0)
('02-04-2014','0',0,500,600)
('0-04-2014','800',300,0,0)
Like I've 30th onwards.....
Expecting o/p:
month Travel Fixed Food Lodge
apr-14 200 550 500 600
These cum column values how to make a code ?????
View 5 Replies
View Related
Jun 26, 2014
I would like to get the first and last day of any month in a given date range.
Ex: Display the first and last day of the months between @startDate and @EndDates.
Input Params= @StartDate='2016-06-21 16:57:11.093'
@EndDate = '2016-09-30 00:00:00.000'
OutPut should be:-
MonthStartDateMonthEndDate
1/06/201630/06/2016
1/07/201631/07/2016
1/08/201631/08/2016
1/09/201630/09/2016
View 1 Replies
View Related
May 13, 2015
I have
Issues | Category | Date
I1 | A | 1/1/2015
I2 | A | 2/2/2015
I3 | B | 2/1/2015
I4 | C | 3/3/2015
I5 | B | 4/3/2015
I6 | A | 5/4/2015
I want to convert to
A | B | C
JAN 1 | 1 | 0
FEB 1 | 0 | 0
MAR 0 | 1 | 1
APR 1 | 0 | 0
Where numbers under neath A, B, C are the count that falls in the particular month.
View 3 Replies
View Related
Apr 16, 2014
I have to create a report and I want all activity for the previous month.
I need to calculate the First and Last Day Prior Month to be used as Input Parameters.
Would something like this be the case or is there a better solution?
[code="sql"]
SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) as FirstDayPreviousMonthWithTimeStamp,
DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) as LastDayPreviousMonthWithTimeStamp
[/code]
I was thinking get the first day of the previous and current month to exclude the Timestamp and use a less then first day of current month?
View 3 Replies
View Related
Sep 24, 2014
I have a Table with Financial Data in it and for Certain Accounts (the Key Field is actindx Column)there is no data for Certain Months.
I need a query to fill in the data for each Calendar_Month. This should look at the actindx column and Calendar_Month column if there is no data for a specific Month for that actindx I want it to copy all columns for that actindx and insert into a new row, but just puts Zero Dollars for ActivityDebit, ActivityCredit, and NetAmount.
I have created a CalendarMonth_Lookup Table. I assume there is a way to outer Join the two and insert rows or use an "IF" ,"THEN" type of Statement and just manually add the Calendar Month data.
I am including all the code to make the tables and Insert Data into them.
CODE TO CREATE GPBalances Table
CREATE TABLE [dbo].[GPBalances](
[GP_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
[FISCALYEAR] [smallint] NULL,
[ACCTPERIOD] [varchar](255) NULL,
[YEAR_MONTH_TEXT] [varchar](255) NULL,
[Code] ......
View 3 Replies
View Related
Dec 16, 2014
i have a monthly production script that requires two forms of the prior month's date.'yyyymm' or '201411' and 'yyyy-mm-dd' or '2014-11-01'. The second date must have a day value of '1'. I have to use these date values in my script a half dozen times. Ive tried to declare two variables to accomodate but i can not get them correct.
I can execute the following:
SELECT LEFT(CONVERT(varchar, DATEADD(MONTH,-1,GetDate()),112),6)
---------------------------
201411
When I try this as a declared variable:
DECLARE @RPT_DTA VARCHAR
SET @RPT_DTA = CONVERT(varchar, DATEADD(MONTH,-1,GetDate()),112)
SELECT @RPT_DTA
--------------------------
2
DECLARE @RPT_DTB VARCHAR
SET @RPT_DTB = DATEPART(YEAR,DATEADD(MONTH,-1,GetDate())) + DATEPART(month,DATEADD(MONTH,-1,GetDate()))
SELECT @RPT_DTB
-----------------------------
*
View 3 Replies
View Related
Feb 22, 2015
I m creating P&L(profit and Loss ) Reports of accounts its consists of 2 levels
in level2:
my procedure display the output of (Actuals in lakhs)
RESPONSIBILITY DEPT CATEGORY CURRENT YTD ACTUALS
SALES Sales Net Sales 444.65
Sales Sales LESS TRD 22.55
SALES NET RETURNS NET RETURNS 422.10 (net sales - TRD)
Finance LESS ED LESS ED 40
Sales Totals Sales 382.10(RETURNS - ED)
(only calculation for above dept only remaining dept values display sum of relvenat accounts ,and if i click the category relvent account codes shown here)
Materials .... ... ..
production ..... ............ ........
i made a procedure for above
create procedure Pl_level2
@fmdate datetime,
@todate datetime,
@category varchar(200)
as
begin
create table #temp1
[code]....
like i m inserted so many accounts in temp tables .
IF (@category IS NULL or @catagory=' ' )
begin
select
responsibility,
dept,
category,
round(Max(Actuals)/100000,1,2) as Actuals from #temp
[code]....
here i can display only current YTD only how to display previous year (13-14) YTD in level1
How to do?
View 1 Replies
View Related
Oct 15, 2014
This statement adds two additional months to which is fine :
DATENAME(MM,dd.date)+ ' ' + DATENAME(D,dd.date) + ', ' + DATENAME(YY,dd.date)
but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.
View 7 Replies
View Related
Mar 25, 2014
I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.
Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013
View 2 Replies
View Related
May 28, 2014
i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'
i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06
select c.* from (
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join
)
View 4 Replies
View Related
Mar 30, 2015
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory](
[username] [varchar](50) NULL,
[current_tenure] [int] NULL,
[move_in_date] [smalldatetime] NULL,
[Cnt_Lead_id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
[code]....
View 9 Replies
View Related
May 11, 2015
I have table in which month & year are stored, Like this
Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015
I wanted a query in which it should return the value in a status field which has latest year & month.
View 9 Replies
View Related
Apr 5, 2008
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
View 3 Replies
View Related
May 24, 2006
Hi
I am new to Data mining in SQL Server. I am using SQL 2005 to create a forecast model for Product sales. I two fact tables that I am using. One has all the Orders historically with the line item details. The other table is a time dimension table which has the value of each of the time values referenced in the Orders. So the time dimesion has multiple values for one day as it goes down to the hour the order was placed.
Can I create a forecast using the time series algorithm in the Data mining module. When I tried to use this algorithm, I got an error that the time is not synchronized with starting series "Unknown" and I should try to set the missing_value_Substitution parameter to previous.
Can some one explain to me how this will resolve my issue. I read in one of the articles on Data mining that in order to use the time series algorithm , I need to have unique set of values for the time. Can some one help me with this
Thanks
AY
View 4 Replies
View Related
Nov 16, 2006
Using the TimeSeries algorithm, how do I forecast more than one time period ahead? I read in you book on page 182 that PredictTimeSeries function can take a parameter for the number of time periods you want to predict. Fore example, SELECT PredictTimeSeries(Bread,5) tells the algorithm to predict the next 5 time periods. Can you tell me how to change that parameter using the graphic interfaces?
Thanks!
View 1 Replies
View Related
Aug 5, 2006
I have few more clarification regarding time series.
Firstly
In my model the month level product sales value represented across 1st day of every month. So that the key time column is of datetime datatype containing a sequence of dates representing the 1st day of every month of the year.
Eg: 2006-01-01, 2006-02-01¦¦¦. etc. all in (yy-mm-dd) format
But
when I make prediction for next five months, though it makes monthly
predictions the date part for the months are random whereas I expect
the date part to be 1st of every month. What is the reason for this and how can I overcome it.
Secondly
Predicted
sales values for some time period are negative though I do not have any
negative value in the training data. What is the reason for this and
how can I rectify it?
Thirdly
In
one of your earlier posts you had said that the time series algorithm
does not have any built in time intelligence but uses the key time
column as a time sequence stamp. So If have to make predictions for a
particular time period where the time slice for each time period is 25
days or 50 days etc, then I understand that the input data used to
train the model should be in the same time sequence.
Or
Can I specify the span of the time period according to which the prediction needs to be made?
Basically
how can I use the same time series model to make monthly, yearly,
quarterly, daily or predictions or for custom time period like I have
mentioned above.
View 3 Replies
View Related
Oct 29, 2014
Attached is a file that generates a sample dataset from which I want to forecast a value for column 'Parcel Count 2014' from tomorrow (30/10/2014) till the end of December (31/12/2014). The forecast should be based on values from column 'Parcel Count 2013' for the same Month.
View 9 Replies
View Related
Jul 23, 2014
I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.
=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,
[Code] ....
Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.
SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID
View 9 Replies
View Related
Aug 2, 2006
Hi,
I have built a time series model to forecast sales value
I have data from jan 2004 to jan 2006 and the sales value is
at a day level in my database. But I am aggregating it to month level in the
DSV of the mining model.
I am required to make only historical predictions using the
above model starting form jan 2004 to jan 2006 for every month.
I have set Historical_Model_Count
and Historical_Model_Gap parameter
values to 24 and 10 respectively, and trying to predict for the past few months
(PredictTImeseries(SalesValue,-1,1))
But its throwing me the following error
Error(Data Mining): A time series
prediction was requested with a start time further in the past than the
internal models of the mining model, Sales Forecast, specified in the
HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process
In fact it throws the above error irrespective of what the Historical_Model_Count and Historical_Model_Gap parameter values
are
I am not able to figure our why this problem is happening?
What should the parameter values for the above scenario?
It would also be helpful if I can get an explanation on how
these two parameters affect the historical predictions. I kind of understand
that these two parameters are important for historical predictions but dont
know why or how.
View 4 Replies
View Related
Oct 12, 2007
I have a result set that looks like this:
Code Block
Quarter
Year
EstimatedValue
ClosePercent
EstimatedCloseDate
4
2007
100000
50
12/31/07 5:00 AM
4
2007
20000
50
11/30/07 5:00 AM
4
2007
20000
90
10/30/07 5:00 AM
1
2008
278000
50
3/31/08 5:00 AM
4
2007
200000
50
11/30/07 5:00 AM
4
2007
225000
90
10/31/07 5:00 AM
4
2007
36500
90
10/31/07 5:00 AM
4
2007
80000
90
10/31/07 5:00 AM
4
2007
107200
90
10/31/07 5:00 AM
4
2007
225000
75
12/31/07 5:00 AM
4
2007
35000
50
12/31/07 5:00 AM
I have create a simple tabular rolling forecast report (with cumulative totals) from today (October) thru the next 12 months that looks like this. It smartly works no matter when the report is generated, by starting with this CurrentMonth and moving forward by using 1,2,3,4,etc. in the dateadd: =MonthName(datepart("m",dateadd("m",1,Now())))
The report sample (formatting lost in dropping it in here):
Code Block
Close Pct
October
November
December
January
February
25.%
$0
$0
$26,625
$0
$0
50.%
$237,500
$110,000
$262,500
$0
$0
75.%
$56,250
$0
$891,075
$0
$0
90.%
$1,051,830
$0
$0
$0
$0
Monthly Total
$1,345,580
$110,000
$1,180,200
$0
$0
Cumulative Total
$1,345,580
$1,455,580
$2,635,780
$2,635,780
$2,635,780
It is working fine....there doesn't seem to be anything wrong with it (all numbers total correctly, etc.), but it is very unelegant.....and I know there must be a better way.
In the righthand most month (which would be September 2008) column, I have a formula that produces the amount (the Monthly Total amount is the same):
Code Block=sum(iif(datepart("m",dateadd("m",11,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0)))
and for the Cumulative Total Amount it gets really hideous, as it is trying to add up all of the totals across the board:
Code Block
=sum(iif(datepart("m",dateadd("m",0,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",1,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",2,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",3,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",4,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",5,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",6,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",7,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",8,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",9,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",10,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",11,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0)))
I have searched high and low for examples of reports that do something similar.
Can anyone offer any advice?
Thanks.
View 2 Replies
View Related
Aug 3, 2006
Hello...
I am new to SSAS and i want to try to build a "Sales" model. I will have some "Usage" data for some timespanns, but I am not quite sure how to tackle this. Is there somewhere a "Howto" for this?
Edit: There are several locations, and for each location a forecast is needed. And the Icing would be If I would be able to tell where my supplies must go 1st to achieve the best sales...
The potential Client wants to use Oracle but I would like to show them that SQL Server is the better tool for this ;)
View 1 Replies
View Related
Oct 2, 2015
Is there any way to copy my Data of 2015 to the Planning/Forecasting Value of 2016?
My question is based on that we use INFOR ION BI right now and there we can just add an Button in our reports wich physically copies the value from one year to the next year based on some other rules in the cube.
Now I need to make this example work with SSAS and Excel PivotTables but I cant figure out how.
I have absolutely no clue where and how to accomplish it. Do I use Calculations, do I use Actions, do i make it in the Dataview, Cube or directly in Excel?
View 4 Replies
View Related
Apr 22, 2015
What is the best way to forecastestimate space for non-clustered index on a table?
Example :
Table name : Test123
Row : 170000000
Reserved : 18000000 KB
Data : 70000000 KB
Index: 40000000 KB
Note: Test123 already has clustered index and 2 non clustered indexes.
View 7 Replies
View Related
Jul 9, 2004
Looking advice on how to query for just a single day of the month. Example is for billing sent on the same day of each month, need to get all accounts that started on that day of the month no matter which month. If a account started January 7th, and it is July 7th, I would like it to list all accounts that started on the 7th day of the month. The database has a start date field in short date format.
View 1 Replies
View Related
Aug 8, 2013
Is my query below the most efficient way to query the first day of each month?
SELECT base_rate, doc_pt, chgdate
FROM producthistory
WHERE chgdate IN
(
SELECT MIN(chgdate)
FROM ProductHistory
WHERE prod_id =1199812
and base_rate > 0
GROUP BY MONTH(chgdate), YEAR(chgdate)
)
AND prod_id = 1199812
ORDER BY chgdate DESC
View 5 Replies
View Related
Aug 16, 2007
Hi alli want result as month name from my queryi tried with the following query but it give result as month numberlike (8)select (month(getdate())) as expri want result as month name (Augest)..give me the proper query...from Sachin
View 2 Replies
View Related
Mar 20, 2004
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
View 1 Replies
View Related
Mar 17, 2004
Hello,
I have made a query that does a cross-tab of data. The date data is normal like 02/04/2004, 03/04/2004 etc.
I am using Month Name to group the dates by Month name.
My problem is the query result sorts the month names in alphabetical order, not month order.
I am using the query to generate a graph and the months are not in order.
Is there a way to sort the months by month order not alphabetical order.
Thanks
Goong
View 4 Replies
View Related
