04/06/2005 12:15:300====0
04/06/2005 12:10:300====1
04/06/2005 12:20:300====0
04/06/2005 12:20:300====1
04/06/2005 12:20:300====0
============================================
I want to count number of Active and Inactive Items per day. How can i do this. Following is the desired result set according to the above data..
DATE====InActive_count(0)====ActiveCount(1)===Total
04/04/2008====3===2===5
04/05/2008====4===1===5
04/06/2008====3===2===5
==========================
I can get count total count per day using following query.
Select Dateadd(day, Datediff(day, 0, date), 0) as date, Count(id) as [Count]
from myTable
group by Dateadd(day, Datediff(day, 0, date), 0)
order by date desc
==========
how does i change this query to get desired result....?
I have a problem where I need to join multiple tables. Part of the query needs me to make the following relationship in a join.
Say I have a table with items, prices, and dates.
PriceTable
ITEM PRICE DATE
A 1.00 5/5/2000
B 3.00 1/1/2000
A 2.50 6/5/2004
....
This table represents an items price from the date on. So Item 'A' costed 1.00 from 5/5/2000 through 6/5/2004, then it costed 2.50 from that day on.
Now say I have a table of transactions with items, amount bought, and dates.
TransactionTable
ITEM AMOUNT DATE
A 5 6/6/2003
A 1 8/5/2003
A 2 8/5/2004
The total for A should come out to be 11.00. There are multiple Items and multiple price changes.
If someone could point out how a inner join of this nature would work it will help me in my query. I guess the reason i say inner join is because I am joining multiple other tables to do my query.
My current Price Table has a start date and an end date and my query looks something like this. "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable Inner Join TransactionTable ON TransactionTable.Date Between PriceTable.StartDate and PriceTable.EndDate AND PriceTable.Item = TransactionTable.Item".
I want something like this because the tables need to be in the format above "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable INNER JOIN TransactionTable ON Max(PriceTable.Date) WHERE PriceTable.Date <= TransactionTable.Date AND PriceTable.Item = TransactionTable.Item".
The following is a VB code which I use it to check the existence of the record before I insert a new record into my SQL 7.0 database. When I run the vb code, I got an error which is "-2147217913 [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I couldn't figure out what's wrong with my code especially the WHERE clause. Please help me look into this problem. TQ.
Private Sub AddDate() Dim rstDate As ADODB.Recordset Dim myDate As Date Dim Criteria As String
Criteria = "SELECT Date " _ & "FROM DateGen " _ & "WHERE Date = '" & myDate & "'"
Set rstDate = New ADODB.Recordset
With rstDate .ActiveConnection = cn .CursorType = adOpenDynamic .LockType = adLockOptimistic .Open Criteria End With
If rstDate.RecordCount > 0 Then RecordExist = True Else RecordExist = False rstDate.AddNew rstDate("Date") = Format(myFileDate, "dd-mm-yyyy") rstDate.Update End If rstDate.Close End Sub
himy self avicurrently i am developing one application in vb 6 and back end assqlserver 7 which is used on lan.i have some problem. like in my database i have one table salesvoucherwhich has 'voucherno' field. when 2-3 user will work on salesform at atime [since the softwrae will run on lan] then the same voucherno willsave for all users data which is wrong i need to save different vouchernofor each no. is there any way in sqlserver to apply some condition ondatabase or to set some its property[tables] so that at a time only oneusers data will save depend on firstcome first serve base or is there anyway to make changes in programme.plz help me regarding this i need it very badly.since its my first lan based software plz give me some books nameregarding this software for vb6 and sqlserver7
plz help me to upload my sqlserver data base with query analyzer method plz write the code i should write in query analyzer box to uplode my database(db) in my server(h_server) and is it neccessary to uplode log file or not ,or guide me with intruducing a site
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select -- count returns unit data Count(substring(m.PTNumber,3,3)) as PTCnt, -- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt, -- attempting to calculate the percent by PTCnt/TotalCnt returns 0 (Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct -- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
iam new to SQL i mostly do select on DB but now iam asked to do this...need Help I have a task to calculate the cost of a lead depending on conditions Condition: The leads form PhoneLeads channel (of many channels) First 30 records per day, type A will cost 20$. From 31st record its 50% off First 30 records per day, type B will cost 10$. From the 31st record 50% off Its straight and simple if the count is less than 30 on a day but how to change the cost from the 31st lead….How do I do this as an update to the table. Initially the cost is null for all
Sample output data: ChannelTypedatecost PhoneLeadsA2007-01-17 00:00:0020 PhoneLeadsB2007-01-17 00:00:0010 PhoneLeadsB2007-01-17 00:00:0010 PhoneLeadsB2007-01-17 00:00:0010 PhoneLeadsA2007-01-17 00:00:0020 …..….. …..….. After the 30th lead on the same day the cost should be 50% discounted PhoneLeadsA2007-01-17 00:00:0010 PhoneLeadsA2007-01-17 00:00:0010 PhoneLeadsB2007-01-17 00:00:005 PhoneLeadsA2007-01-17 00:00:0010 PhoneLeadsA2007-01-17 00:00:0010 PhoneLeadsB2007-01-17 00:00:005 PhoneLeadsB2007-01-17 00:00:005
Hi all, i am prity new to sql and am just learning (that hard way)
I have this code and i am trying to get out of it: a date with the number of calles that have been taken on that date between 2 dates.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @DisplayFrom as DATETIME DECLARE @DisplayTo as DATETIME DECLARE @FromDate as DATETIME DECLARE @ToDate as DATETIME
SET @DisplayFrom = <%FromDate|Enter start date%> SET @DisplayTo = <%ToDate|Enter end date%> SET @FromDate = dbo.fUniversalTime(@DisplayFrom) SET @ToDate = dbo.fUniversalTime(@DisplayTo)
SELECT SUM(ActiveDate), ActiveDate FROM (SELECT DISTINCT (ActiveDate) from [case])as T
I'm starting with the question but my end would be to store the results of this data so I can query it from my Access front end using between dates to determine totals.
1) How do I create a SQL view/temp table to query count totals from an MS Access 2010
4) To calculate the count for each deliverable with no dates: Select (Select COUNT (*) as CountCAD from tblProject where CAD='true') as CADNumber, (Select COUNT (*) as CountRMS from tblProject where RMS='true')as RMSNumber
5) When I run the query between any dates, using tblProjectMilestonses.ProjectCompleted, what I would like is:
When looking at the data some days have multiple events. Now I want to generate a new table that show all the dates in this month showing the number of running events for that specific day.
I have a problem that I can't quite get started on solving.I have a table of asset statuses. Each time the asset status changes, anew row is inserted into the table.CREATE TABLE dbo.Tbl_EMStatusHistory (EMStatusID int IDENTITY (1, 1) NOT NULL ,EMSessionID int NULL ,AssetID int NOT NULL ,AssetStatus int NOT NULL ,StatusComment varchar (250) NULL ,StatusDate smalldatetime NOT NULL ,InsertUser sysname NOT NULL ,InsertDate smalldatetime NULL ,CONSTRAINT PK_EMStatusHistory PRIMARY KEY NONCLUSTERED(EMStatusID))Here is some sample data:EMStatusID AssetID AssetStatus StatusDate DeviceType4772622624OUT2003-10-05Monitor3810022624IN2003-10-16Monitor4726122624OUT2003-10-25 Monitor3819322624IN2003-11-02 Monitor3917122624RV2004-05-02 MonitorFor asset 22624, the current (most recent) status = RV. Before that, itlastchanged status on 2003-11-02, when it was IN. So it was IN from2003-11-02 to2004-05-02.I need to produce a report that counts the devices by type that were INon a given report date,like so:ReportDateMonitorsTransmitters05/01/2004342605/02/2004373005/03/2004393205/04/20043531The end user supplies the date range.I am unsure how to approach this. I came up with oneEinsteinian-Newtonian method that involved three temp tables, a gardenhose, and a duck, and it was getting uglier by the minute. Has anyonehad experience with a requirement like this? Thanks, Dave"Are you thinking what I'm thinking, Pinky?"*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
I am currently using this SQL code to capture some records over the last 2 months and it has been working great. I am now being asked if I can change this code with specifications:
1) Scan the records in the system until the count (*) as Volume reaches 30 because they prefer that as a denominator when figuring an average
2) Only run the scan for a maximum of 6 months.
So, there will most likely be some records that do not reach a volume number of 30 in this date range. In this instance we will just take the maximum volume number reached at 6 months.
So, how can I write this so it will build the file each time a record has reached the maximum of 30 and keep scanning back until we reach 6 months? If someone could lead me in the right direction on the proper order of the methodology in my code to accomplish these results it would be greatly appreciated. Desperate!
declare @startdate smalldatetime, @enddate smalldatetime , @month int, @year int
pe1.patev_loc_id as LocID, pp_cproc_id_r as ProcID, count (*) as Volume, sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time)) as Minutes, sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time))/count(*) as AvgMin
from risdb_rch08_stag..performed_procedure (index pp_serv_time_r_ndx), risdb_rch04_stag..patient_event pe1, risdb_rch04_stag..patient_event pe2
where pp_service_time_r between @Startdate and @Enddate and pp_asn_req_no = pe1.patev_asn_req_no and pp_asn_seq_no = pe1.patev_asn_seq_no and pp_status_v = 'CP' and pp_rep_id > 0 and pe1.patev_event_code = 'PB' and (pp_asn_req_no = pe2.patev_asn_req_no and pp_asn_seq_no = pe2.patev_asn_seq_no and pe2.patev_event_code = 'PL') and datediff (mi, pe1.patev_event_time, pe2.patev_event_time) > 0
I am wondering how to achieve a running count on the rows being displayed in a table list. Not sure how to get it to show 1 for first row, 2 for second row, 3 for third row and so on. Example
No | Name 1 | John 2 | Jane 3 | Jim
I am also wondering on the date format in SSRS. When I try to format the date 10/31/2007 in a DD/MM/YYYY format it does not come out so good. Basically I go to the cell property and choose custom formatting, input the formatting string d/mm/yyyy but it only shows 31/00/2007. Can you guys tell me what I am doing wrong?
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
I have conducted a thorough search in the forums and cannot quite find my answer. I have a date field called open_date. If the open_date is more than 30 days old, I need to count it. I have started with the following code:
SELECT 'Older_Than_30Days' = CASE WHEN open.date >= 30 THEN '1' ELSE '0" END
I need to count the records in a table with a datetime field equal to system datetime. It looks like it is trying to match time also since time is in field too. I just want to match date only. My sql is below. Does anyone have some suggestions on how to handle this? Date from code
DateTime todaydt = DateTime.Now;
Table Data format
[cal_str_tm] [datetime] not null, Data in cal_str_tm field - 3/27/2008 9:43:16 PM
Thanks, Ron
ALTER PROCEDURE SP_DpCount
(
@todaydt datetime
)
AS
SELECT @total = COUNT(cal_int_id)
from dpcalldtl
where (cal_callstat != 'COMPLETED' and cal_str_tm = @todaydt)
This is the code I have written and I am trying to retrieve minimum count of PQpageId for every hour for a given date of range.
WITH CTE AS ( SELECT PQIM.PQPageID ,PQIM.PageURL as PageDescription ,CONVERT(Date,NCPI.RequestDateTime) AS [Date] ,DATEPART(HOUR,NCPI.RequestDateTIme) AS [HOUR] ,ISNULL (COUNT(NCPI.PQPageID),0)AS HourlyPQPageIdCount FROM dbo.NewCarPurchaseInquiries AS NCPI WITH (NOLOCK)
For ex: Pqpageid 1, at 8am on date 4-11-2015 has a count of 2359 and on 9-11-2015 at 8 am it has count 54then it should return date 9-11-2015 and count 54 for hour 8 like wise for every pageid and hour from 8 to 23 it should return the min count from given date of range.
I am try to count number of items that will result by filtering a date column (one Date column). Ex Column "Created Date" between 1-Sep-2015 To 30-Nov-2015.
I am unable to get any function that is getting right value. The below function return 40, however the actual value when i do manual filter and count is 132.
I have two tables - one with dates at the end of each month (10/31, 11/30, 12/31, et al) that's linked to a data table containing client signups. While I can get the count per month in a pivot table, I'm trying to calculate the beginning count of each month.
How do I use calculate or something similar to sum the count of records created minus count of records closed prior to the beginning of the period?
The pivot looks like this right now and works by month:
beginning active records ????? count of records created 100 count of records deleted 50 net records created 50 running balance in net records created 100 (theoretically the next month's beginning balance, but can't figure out how to reference or calculate)
I am trying to write a stored procedure that loops through the list of user tables, gets the record count for each one and write a record to an audit table with DATE, TABLENAME, RECORDCOUNT.I keep getting an error "Conversion failed when converting date and/or time from character string".Here is the script...
DECLARE @table nvarchar(500) DECLARE @sql nvarchar(520) DECLARE CursorSelect CURSOR FOR select table_name from INFORMATION_SCHEMA.tables where table_name not like 'sys%' order by table_name
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29 if we pass year =2015 and month=09 then we have to get 2015-09-29 if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
I have a pretty simple query: Select title, avg(score) from votes group by title order by avg(score) Desc This returns a result like this: Abbey Road 4.0The White Album 3.5Meet the Beatles 3.0 The values in the score field are always 1, 2, 3, 4, or 5 What I really need from my query is 5 more columns (1, 2, 3, 4, and 5) with a count of how many votes each of those columns received. So the result set might be like this. Abbey Road 4.0 0 0 1 1 1 The White Album 3.5 0 1 0 0 1Meet the Beatles 3.0 2 2 2 2 2 I have tried to use the count function, but it counts all the values in the field. I can't figure out how to just count the values that match a certain criteria. When I tried to create subqueries, I got an error that said my subqueries where returning a multiple result sets. Any suggestions would be greatly appreciated, Chris
I am trying to count a column in my tbl. I have a table with the following, What i am trying to do is count the number of days of dtAttendance for an individual where bitpresent = 1. So this individual should have 2 days of attendance. I wrote some code belwo but it does not seem to count correctly, some it does and some it does not. intAssignedPersonnelID intUICID strSSN dtAttendance bitPresent intDrillStatus intMileage bitDatePayed210 1 333333333 5/26/2008 1 2 210 1 333333333 5/27/2008 1 2 210 1 333333333 5/28/2008 0 2
& "(select count(*) from saddotnet.dbo.tblAssignedPersonnel h where p.strSSN = h.strSSN and h.dtattendance <= p.dtattendance) " _ & "as count_attendance_as_at_dtattendance, p.dtattendance, bitpresent, intdrillstatus, " _ & "'<input name=chbxPresent' + convert(varchar(10), p.intAssignedPersonnelID) + ' type=checkbox>' as 'theCheckBox' " _ & "from cms.dbo.tblSIDPERS as s INNER JOIN " _ & "saddotnet.dbo.tblAssignedPersonnel as p on p.strSSN = s.sidstrSSN_SM where sidstrSSN_SM = left('" & SSN & "', 9)"
I have a table that is grouped by a date. Some records have the same date. I am just trying to return the total row count of the query.
Here is the sql:
SELECT ActiveWeek FROM wcWARS WHERE CreatedBy = 9034 AND FlagComplete = 1 GROUP BY ActiveWeek
I just want to return the total grouped column.... I thought Oracle had a ROWCOUNT function that returned the total rows of the query. I was unable to find one for SQL Server.
**Note: I already tried @@ROWCOUNT ~ This returns the total rows of the table.
Hello All, I have two tables A and B. A and B have column x in common. The relationship between A and B is one to many. I would like to count the number number of x's in table B and group by the x and then join this result back to table a. My attempt : SELECT COUNT(T1.x), T1.x, A.meeting_invite_idFROM (SELECT meeting_invite_idFROM REMINDER) T1, AWHERE A.meeting_invite_id *= T1.meeting_invite_idGROUP BY A.meeting_invite_id Anyone with any ideas.
assume that there are many stores listed in the table... how can i have the number of stores that has an ordercount?... ive tried some mdx queries but i still cant get it right...
