Incrementing Dates And Displaying Range As Column
Mar 19, 2013
I'm using SQL Server 2000 sp2...I have created a view that gives me customer info from which I need to create a view and or table that gives me a 24 monthly columns of the sum of each account_number monthly revenues (going back 24 months from this month)..The columns I'm pulling from are these:
Customer_Name
Account_number
First_insert_date
Order_net_price
Here's what I have so far:
----------------------------
SELECT TOP 100 PERCENT Account_number, Customer_Name, SUM(Order_net_price) AS 'CM - 24'
FROM dbo.Customer_Feed
WHERE (First_insert_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 24, 0)) AND (First_insert_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 23,
0))
GROUP BY Account_number, Customer_Name
ORDER BY Account_number
I've basically hacked out a way to get the monthly totals for each account. What I would like to do is so be able to repeat the query but increment the date range 1 month until reaching the present or last FULL month and display these sums in individual columns named CM - n (where CM means current month and 'n' is the # of months back from current.how to make this query run over again the 23 other times I need it to and display those months sum totals in individual columns.
View 9 Replies
ADVERTISEMENT
Sep 15, 2004
Hey all, hopefully this question is in the right spot. I'm writing a .NET app talking to a MS SQL 2000 DB. I have two date range input boxes, and I want to display the data (probably in a dataGrid) from those 2 certain dates. How do I go about this with my SQL server??
My DB table has a date field that I would use to search for the data between those two user specified dates. Any tips, examples, etc. would be greatly appreciated!!!
View 14 Replies
View Related
Apr 23, 2008
I'm trying to create a simple attendance website where a user logs in and a time and user name (with unique ID) is stored into a table, and then the user is able to click "Check Attendance" and the site will display only the times they logged in (which wasn't too bad). I want a button we can click to "clean up the database" and delete duplicate dates.
If I have a user sign in 10 times in one day - I don't need all 10 times logged into the database, only one. So how do I create a query (needs to be a query so I can tack it onto a button in PHP) that will delete 'where date = the same as a previous date'?
I appreciate the help - and I'm sure I will use this forum for help in the future as it seems very robust with information.
View 5 Replies
View Related
Aug 6, 2014
I am designing a database to store data from a leak tester.
We want to display the results between dates, I mean, the results of the leaking test are going to be stored as well as the datetime in which they have been performed.
ID (int)
RESULT (float)
TS (datetime)
The query will be, of course:
SELECT * FROM TABLE Where TS BETWEEN DT1 and DT2
This table is growing by 10000 rows a day, it is possible that in a year getting the values between two dates became impossible.
Using a index with a datetime field sounds like a crazy idea.
View 2 Replies
View Related
Feb 28, 2007
I have a need to display the difference between two dates, a start date and a end date in the format HH:mm where the hours could be greater than 60.
For example:
Start Date - 30/01/2007 09:00:01
End Date - 01/02/2007 20:40:04
When i use the following code (=Fields!dateend.Value - Fields!DateStart.Value) i get 2.11:40:03 which i can easily understand, but the customer wants it as above!
I would like to be able to get it to be 59:40:03.
Any help would be much appreciated.
View 1 Replies
View Related
Feb 28, 2008
Hi,
Im not sure if this can/should be done through sql...but i have a sql statement and i need the first column returned to start at 1 - and then increment for each row.
I am then using the returned values in another app.
I tried using count(columnname) but this just stays at 1 for each row.
Thanks for an feedback...
View 5 Replies
View Related
Apr 24, 2015
What I want to see is how to show PO's who's due dates are > three
Select * from mytable
where myorderstatus = 'onorder'
This is my duedate format and what I want is any that past that date over 3 days
2014-08-11 00:00:00.000
View 11 Replies
View Related
Apr 2, 2008
I have a temp tbl and in col1 row1 i would like to have 1, col1 row2 value 2, col1 row3 value 3 and so on. how can i do this?
View 3 Replies
View Related
Oct 12, 2006
I'm trying to get a list of dates (actually, only the mondays) between a given range of two dates.Any help on the SQL statements I need to use, I'm having no luck.Thanks in advance.
View 3 Replies
View Related
Oct 8, 2005
Is there a way that I can get a resultset that contains unique dates ina given date range without the need to have a temporary table and acursor?perhaps something like:declare @start_date as datetimedeclare @end_date as datetimeset @start_date as '1/1/2005'set @end_date as '1/1/2006'select fn_getuniquedate(@start_date, @end_date)1/1/20051/2/20051/3/2005...12/31/2005
View 24 Replies
View Related
Jul 31, 2006
Hi,
I'm migrating data from access 2003 to sql server 2005. An access table contains dates like 5/1/0999. This is a date that is out of the datetime range from sql server.
Now my question is ... How do I replace these dates? I don't find something like IsDate in the expressions?
View 1 Replies
View Related
Mar 11, 2008
My SQL table has the
following columns:
ID – Auto IncrementingNumber_of_Records – IntAccount_Number – Varchar
(Format 1234)Account_Number_Instance – Varchar
(Format e.g. 1234-01)Other_Field1Other_Field2…etc.
When the Account_Number was
initially inserted into the table, a Stored procedure (not written be me) was used to concatenate the Account_Number
and Number_of_Records. As a result
the Account_Number_Instance became,
e.g. 1234-01, etc.
From the ASPX form we
retrieve each of the fields above, and based on the Number_of_Records an
appropriate number of records were inserted for the Account_Number.
For Example:
Account_Number = 12345Number_of_Records = 4
The result inserted into the
table were as follows:
ID # of Records Acct_Number
New_Num Acct_Number_Instance …
1 4 12345 12345-01
2 4 12345 12345-02
3 4 12345 12345-03
4 4 12345 12345-04
Now, I would like to UPDATE
this table, and based on the original Number_of_ Records (4) I want to ADD, for
Account_Number (1234) add an appropriate Number_of_Instances, beginning with
the next incremental number and adding what the New Number of Records input is…
For Example:
Account_Number = 12345 New_Num(ber_Of Records) = 3
ID # of Records Acct_Number New_Num Acct_Number_Instance …
1 4 12345 12345-01
2 4 12345 12345-02
3 4 12345 12345-03
4 4 12345 12345-04
5 12345 3 12345-05
6 12345 3 12345-06
7 12345 3 12345-07
I
do realize that the next time I have to update this Account_Number I am also
going to have to somehow ADD only one of the #_of_Records and New_Num for the
Acct_Number instances to obtain the next starting number, which in the example
above would be (8)…
Any
and all suggestions on how to accomplish this would be greatly appreciated!
Thanks
View 2 Replies
View Related
Feb 16, 2006
Hi,
I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?
View 1 Replies
View Related
Aug 29, 2007
Hi all,Right now I have this SELECT statement:SELECT MUSIC_PK, MUSIC_TITLE, MUSIC_TITLE2, MUSIC_ORIGINAL_SINGER Now I want to add a second SELECT statement (same as above, but with an additional column) if the record was added within the 10 days (while still select other records, I just need to select an additional column). How would I do that? The additional column that I want to select is called MUSIC_NEW (so I can display if the record is new within 10 days.) I have a column in my table named DATE_ADDED, how do I go to calculate if it's within the 10 days since the day it was added to the current time?Thank you very much,Kenny.
View 8 Replies
View Related
Jun 18, 2004
I currently have a stored procedure that returns a list of dates based on a date range a user enters.
CREATE PROCEDURE sp_GetContactScheduleDates
@MonthFrom int,
@YearFrom int,
@MonthTo int,
@YearTo int,
@DaysInMonth int
AS
Select distinct s.ScheduleMonth, s.ScheduleYear
From OnCall_Schedules s
Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
>= CAST(cast(@MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@YearFrom as nvarchar) as smalldatetime)
And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
<= CAST(cast(@MonthTo as nvarchar) + '/' + cast(@DaysInMonth as nvarchar) + '/' + cast(@YearTo as nvarchar) as smalldatetime)
Order by s.ScheduleYear, s.ScheduleMonth
GO
However, this only brings back those dates that are in the table. I need to get ALL dates within the range.
For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?
Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.
Can some sort of function be used? What would the code look like?
View 2 Replies
View Related
Apr 30, 2006
For some reason this is just not "clicking" with me and the longer I stare at it the more I overcomplicate things and then I just confuse myself further...
Provided this relation showing where "pkey" and "skey" met on "ServiceDate":
Code:
skey pkey ServiceDate
--------------------------------------
123 1 11/12/2005
124 1 10/12/2005 *
123 2 10/02/2005
124 2 10/12/2005
124 2 11/12/2005
123 2 11/02/2005
I need to select out a list of distinct pkeys and skeys only when there was no meeting between the two in November 2005. In other words, this query would produce only one record - the starred record - when run against this sample table. This is because even though, for example, skey 124 / pkey 2 has an entry outside the desired range, it also has one inside the desired range. The same goes for the record outside our range for 123 and 2 - it also has a record inside our range.
At this point, I've come to the conclusion that I can first select all the distinct pkeys and skeys where the servicedate is not in 11/2005 then join it to a selection of distinct pkeys and skeys where the servicedate IS in the desired date range. Does that seem like the most straightforward way through this?
I don't get the impression that this is that complicated a problem, but it's one of those deals where I goofed up somewhere along the line, and now I think I'm really overthinking the problem, so I'd be much obliged if someone could give me a hearty slap to clear the ol' noggin.
View 1 Replies
View Related
Jul 6, 2006
I am currently working in the sql server 2000 environment and I want towrite a function to pull all dates within a given date range. I havecreated several diferent ways to do this but I am unsatisfied withthem. Here is what I have so far:declare @Sdate as datetimedeclare @Edate as datetimeset @SDate = '07/01/2006'set @EDate = '12/31/2006'select dateadd(dd, count(*) - 1, @SDate)from [atable] vinner join [same table] v2 on v.id < v2.idgroup by v.idhaving count(*) < datediff(dd, @SDate, @EDate)+ 2order by count(*)this works just fine but it is dependent on the size of the table youpull from, and is really more or less a hack job. Can anyone help mewith this?thanks in advance
View 3 Replies
View Related
Jul 23, 2005
Sorry to bother you guys with what I though would be an easy task. Ihave a table in my database were I would like one of the rows toincrement a number for each row. I want the first row to start at 1000and keep on incrementing by 1 'till the end of the rows (about 2.7million rows). I though this would be a piece of cake, but I just can'tseem to find anything like it on the internet...weird.Anyways, I'm just a rookie in sql, any help would be appreciatedThanksJMT
View 6 Replies
View Related
Mar 30, 2015
How to show the dates in between a range. Its hard for me to explain so I have ddl with the original results and then ddl of how I would like the desired outcome.
On the side I have a visit ID I need to show each day logged for each ID. Sometime the Start and End are a single day and sometimes they are a range. I need a row for each date.
CREATE TABLE #Results (VisitID INT, DateFrom DATE, DateTo DATE)
INSERT INTO #Results VALUES (361, '2015-03-07', '2015-03-07'), (361, '2015-03-08', '2015-03-10')
,(48, '2015-03-18', '2015-03-18'),(48, '2015-03-19', '2015-03-23')
SELECT *
FROM #Results
DROP TABLE #Results
[Code] .....
View 5 Replies
View Related
Aug 20, 2015
Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.
All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.
My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?
View 14 Replies
View Related
Apr 1, 2013
Along with the data that I am pulling back from query, I also need to provide an ImportID column with the following requirements:
YYMMDDGP0001, YYMMDDGP0002, YYMMDDGP0003, and so on. The 0001, 0002, and 0003 part could get big into the hundreds of thousands.
I have the YYMMDDGP part down with the following expression:
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) + 'GP' AS [ImportID]
Now I need to get the Auto Incrementing number part of this. I have been researching this trying SELECT INTO a temp table using the Identity Function and declaring different variables all with no luck.
View 7 Replies
View Related
Nov 17, 2015
My scenario is: a person has many events, all based on a date. I need to aggregate the person to show min and max dates for a period, the period being defined as ending when there is not an event following the next date.
DECLARE @Events TABLE
(
PK_EventINTIDENTITY(1,1) PRIMARY KEY
,FK_UserINTNOT NULL
,EventDateDATENOT NULL
)
DECLARE @User TABLE
[Code] ....
I would expect the groups to look something like below:
Is this where a recursive CTE may be used?
View 6 Replies
View Related
Jul 15, 2015
I have four columns in my table, the first one is the identity column
col1 Col1 col2 col3
1 12 1 This is Test1
2 12 2 This is Test1
3 12 3 This is Test3
4 12 4 This is Test4
5 12 5 @@@@@
When, I see, @@@ sign in my col4, I need to restart the col3 from 1 again so it will look like this
col1 Col2 col3 col4
1 12 1 This is Test1
2 12 2 This is Test1
3 12 3 This is Test3
4 12 4 This is Test4
5 12 5 @@@@@
6 12 1 This is another test1
7 12 2 This is another Test2
Is it possible to do that?
View 8 Replies
View Related
Jun 15, 2015
I'm trying to add random dates to date column in existing table, but these need to be week days (Mon-Fri).I'm a beginner in TSQL, worked with MS Access many years - in Access I used to do something a bit different:
DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#)
Table had ID, I gave a date it would start from (31/08/2015) and then range of ID to apply new date:
UPDATE table1 SET table1 .date = DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#)
WHERE (((table1 .ID) Between 1 And 5456));
This was applying random dates in range of 31/08/2015 + 5 days, so I could give a starting date of Sunday to get random dates populated over given IDs from Monday to Friday.Now, how can I do it in TSQL?I have a table with ID and dates column. I would like to apply new random dates from some range, but making sure they will be week days.
View 3 Replies
View Related
Nov 16, 2015
I have a table ComponentPeriod. In it we have the combination of a component (e.g. A,B,C ) and a period (2014 Q1, 2014 Q2, 2014 January etc)I want the periods to be in descending order (2015 Q4, 2015 Dec, 2015 Nov, 2015 Oct, 2015 Q3 ... etc) and so I need to create a sequential number series to allow this to happen (as we can only order in the client tools by a single column - and so I guess the technique I'm looking for is used a lot to produce these types of "order by" columns)
I have done this in the past using
Period_Sequence =
calculate
(countrows(Period)
,filter
(Period
, Period[Start_Date] <= earlier(Period[Start_Date])
&& Period[Duration_String] = earlier(Period[Duration_String])
)
)
Which was fine when I was referring to a table where Periods where distinct directly but now I have denormalised this for ComponentPeriod so I need something a little more sophisticated Whats the best way to get a sequence with perhaps some partitions in across a subset of distinct columns (I guess from SUMMARIZE or similar)
E.g. I want
Period_Name, Type, Sequence
2015 Q4, Quarter, 1
2015 Dec, Month, 2
2015 Nov, Month, 3
2015 Oct, Month, 4
2015 Q3, Quarter, 5
2015 Sep, Month, 6
etc
even though there may be multiple records in ComponentPeriod that have the period 2015 Q4, but I want them all to have the value Sequence value of 1? I've got as far as:
Period_Sequence Desc =
calculate
(countrows(summarize(ComponentPeriod, ComponentPeriod[Period_End_Date]))
,filter
( ComponentPeriod
, ComponentPeriod[Period_End_Date] >= earlier(ComponentPeriod[Period_End_Date])
)
)
But this doesn't distinguish between the different types. I need an equivalent of the t-sql -
row_number() over (order by Period_End_Date desc, case when 'P3M' then 1 when 'P1M' then 2 end asc
View 7 Replies
View Related
Mar 29, 2007
Hi all,
I am trying to use the custom code in the report but I don't think I am understanding how this is being used.
I have a function to get starting months for a report parameter.
The function is below:-
--------------------------------------------------------------
Shared Function GetStartingMonths() as String
dim strDefault as string
dim CurrentMonth as String
Dim SqlString as String
'strDefault = Month(Now) & "/1/" & Year(Now)
CurrentMonth = "5/1/2002"
Do While CDate(CurrentMonth) <= Now
SqlString = SqlString + "Select " & CurrentMonth & " as value, " & MonthName(Month(CurrentMonth)) & " " & Year(CurrentMonth) & " as MonthYear"
CurrentMonth = dateadd("m",1,CDate(CurrentMonth))
if Cdate(CurrentMonth) = Now then
Exit Do
else
sqlString = SqlString & " Union "
end if
Loop
return SqlString
End Function
---------------------------------------------------------------
what i am trying to do here, and hopefully produce a sql string that would fill my dataset of dates and their representation.
In the dataset, I had put the following expression
=Code.GetStartingMonths()
However, I can't seem to get the parameter to display the dates. shows up as disabled in my report.
Am I doing something wrong here or is there a better way to doing this ?
Additionally, I was wondering whether there is a better SQL code that would achieve the same thing I am doing ?
thanks !
Bernard Ong
View 15 Replies
View Related
Sep 4, 2014
I have created range bar chart and I am not able to achieve the following tasks.
1. Change X-axis Label Format to Quarter:
I have x-axis with dates and y axis of project groups. I have changed x-axis interval type = month and interval=3.
Set the Maximum = Max(ProjectEndDate) and Minimum = Min(ProjectStartDate).
Now my chart showing 3 months x-axis interval dates in mm/dd/yyyy format. I want to change this interval date format to Quarter. The problem is LabelsFormat property is not recognize the "=Q or q or quarter" and also not accepting the expressions. How can I achieve this?
2. Placing series side by side when it is not overlapping
I want to place the same group series side by side only when the previous project end date is less than next project start date, otherwise place the next project to next row. How can I achieve this?
View 2 Replies
View Related
Mar 24, 2014
I'm using SQL Server 2008 R2 BIDS to accomplish this.
View 7 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
May 9, 2008
I have a flat text file. All the columns are set to redirect on error. But, when I set it to row, it gives the error column, ErrColumn. Is there a way to display the real column name-the column which has the error.
Thanks.
View 6 Replies
View Related
Jun 5, 2008
hi,
i have the following scenario,
100 tables in the database,i have to display the table names if the column name given by the user is repeated in the different tables...
e.g.: Let us say,the column name ID IS REPEATED in the 70 tables out of 1000 tables.Then i have to display all the 70 table names..
how to write sql query for it?
i am greateful if any one help me in solving this scenario..
View 9 Replies
View Related
Dec 13, 2007
Hi Everybody
I am working with SQL Server2000. I have one table named Table1 is described as below
Column1
----------------------------
Val1
Val2
Val3
Val4
Val5
Val6
Val7
Val8
Now I want result like that
Column1 Column2
-------------------------------------
Val1 Val2
Val3 Val4
Val5 Val6
Val7 Val8
How I acheive this result from a Query ?
View 4 Replies
View Related
Apr 13, 2012
Ihave 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though..the data i want is in columns that have the same name.pretty much what i want to do...is .they also need to be distinct so i dont count duplicates...i can get them as separate tables...but i cant get them together..I need them in 1 column because of how it is sent to the C3 code page and how it reads it...the structure has already been previously set..and there are about 5 other statments that are being executed in this one stored procedure like this (also i wasnt the one who set this up).
image 1 is what is currently set up
imgur: the simple image sharer
top part is what is stored in tables..bottom is more of the result
it basically runs this code to get the bottom
DECLARE @id INT;
DECLARE @invest nvarchar(50);
SET @id = '7633';
SET @invest = '';
SELECT 'a' + CONVERT(nvarchar, orderfindings.risk_rating) AS cat, COUNT(DISTINCT orderfindings.prnt_id) AS stat
FROM orderheader, orderaudits, orderfindings
WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id
AND orderheader.id = @id AND orderfindings.risk_rating > 0 AND orderaudits.Investor_Name LIKE '%' + @invest + '%'
GROUP BY orderfindings.risk_rating
If i want agencies instead of findings..just replace it..agencies and findings are the 2 tables..they are the pretty much identical column wise...but i want the result together..i've tried several ways..but i cant seem to get it.
image 2 - the table at the bottom is more what i'm looking for..it combines them both into 1
imgur: the simple image sharer
if an order has a finding or agency or both in it..then it gets marked as a 1 for that risk rating...if it doesnt..then 0 for that risk rating..and then sum them all up to see what i got..
I've been working with it...did this [SQL] compare2 - Pastebin.com ..got it to display 2 columns..but still not the right result...i'm getting a1 = 1...a2 = 1...so its not running through all the orders...or it needs a way to count it...i put a sum at beginning of case statement..erro because of counts...so i took counts out...
View 7 Replies
View Related
