Getting Count Per Date Base! Query Problem.
Apr 17, 2008
My table contains following data
date====isActive(bit)
04/04/2005 12:15:300====0
04/04/2005 12:10:300====0
04/04/2005 12:20:300====0
04/04/2005 12:20:300====1
04/04/2005 12:20:300====1
04/05/2005 12:15:300====0
04/05/2005 12:10:300====0
04/05/2005 12:20:300====0
04/05/2005 12:20:300====0
04/05/2005 12:20:300====1
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....?
Muhammad Saifullah
View 2 Replies
ADVERTISEMENT
Mar 8, 2008
Hi,
My tables:
Product
- productID
- name
- price
Inventory
- productID
- stockCount
- timestamp
So each day the Inventory table has a new row for each productID with its stock count.
How can I create a report for the total products sold from one day to another? Or from what a dateStart from a dateEnd (i.e. a range)
Example:
ProductID StockCount TimeStamp
1 10 2008/03/07
1 7 2008/03/08
So you can see that 3 products were sold in the last day.
View 4 Replies
View Related
Jul 20, 2006
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".
Hope that made sense, thanks.
View 3 Replies
View Related
Oct 27, 2000
Hi,
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
myFileDate=#27-10-2000 16:00#
myDate = Format(myFileDate, "mm-dd-yyyy")
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
Regards,
May
View 1 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Jul 27, 2004
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
View 2 Replies
View Related
Aug 21, 2007
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
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
View 1 Replies
View Related
Jan 19, 2007
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
View 5 Replies
View Related
Mar 5, 2007
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
where ActiveDate between @FromDate and @ToDate
group by ActiveDate
e.g. output
01/02/07 52
02/02/07 63
etc...
hope this makes sence
Neil
View 1 Replies
View Related
Aug 18, 2012
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
2) CREATE TABLE [dbo].[tblProject](
[ProjectID] [int] IDENTITY(1,1) NOT NULL, [CAD] [bit] NULL, [RMS] [bit] NULL, [JMS] [bit] NULL,
and
CREATE TABLE [dbo].[tblProjectMilestones]([MilestoneID] [int] IDENTITY(1,1) NOT NULL, [ProjectID] [int] NULL, [ProjectSignedByCustomer] [datetime] NULL,
[ProjectCompleted] [datetime] NULL,
3) tblProject - CAD, RMS and JMS = True/False variations
tblProjectMilestones - ProjectCompleted = datetime
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:
Code:
CADCount | RMSCount| JMSCount|
5 10 3
View 1 Replies
View Related
May 12, 2014
I have a table containing some events. Those events all have a start date and an end date.
CREATE TABLE #events
(
eventID int,
eventname char(30),
startdate date,
enddate date
[Code] ....
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.
View 9 Replies
View Related
May 7, 2015
I want to count the values of 2 field on particular date
I have user table with 3 field
1.from_userid
2.to_userid
3.Date
Now i want to count the no. of files from_userid have send, no. of files to user_id have received on particular day. The data is like this
From_userid to_userid date
3953 6274 10/22/2014
3953 6152 10/22/2014
1112 2710 10/22/2014
3953 1851 10/23/2014
3953 4302 10/23/2014
4302 2710 10/23/2014
View 2 Replies
View Related
Jul 20, 2005
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!
View 1 Replies
View Related
Aug 31, 2006
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
select
@startdate = dateadd (mm, -2, getdate())
SELECT
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
SELECT
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))
select
@enddate = dateadd (mm, 2 , @startdate)
select distinct
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
group by
pe1.patev_loc_id , pp_cproc_id_r
View 1 Replies
View Related
Dec 28, 2007
Hi All,
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?
Thanks for the help.
Regards,
Fadzli
View 14 Replies
View Related
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
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.
View 2 Replies
View Related
Jun 18, 2015
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
My problem is the WHEN.
View 6 Replies
View Related
Mar 27, 2008
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)
RETURN
View 5 Replies
View Related
Nov 26, 2015
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)
[Code] ....
This is the output I get :
PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 04-11-2015 8 2359
1 05-11-2015 8 2332
1 06-11-2015 8 2008
1 07-11-2015 8 1964
1 08-11-2015 8 2139
1 09-11-2015 8 54
[Code] ....
But I am expecting
PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 09-11-2015 8 54
1 11-11-2015 9 10
1 11-11-2015 10 4
2 11-11-2015 8 10
2 11-11-2015 9 2
2 11-11-2015 10 1
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.
View 5 Replies
View Related
Nov 5, 2015
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.
CountofQ1:=COUNTAX(DATESBETWEEN(DumpLoad1[Start Date],[StrtDate],[EndDate]),DumpLoad1[Start Date])
View 4 Replies
View Related
Nov 13, 2015
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)
View 3 Replies
View Related
May 27, 2015
My data has 2 fields: Customer Telephone Number, Date of Visit.
Basically I want to add a field ([# of Visits]), which tells me what number of visit the current record is within 6 months.
Customer TN | Date of Visit | # of Visits (Within 6 month - 180 days)
1111 | 01-Jan-2015 | 1
1111 | 06-Jan-2015 | 2
1111 | 30-Jan-2015 | 3
1111 | 05-Apr-2015 | 4
1111 | 07-Jul-2015 | 3
As you can see, the last visit would counts as 3rd because 180 days from 07-Jul-2015 would be Jan-8-2015.
View 3 Replies
View Related
Sep 26, 2014
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
[code]....
View 2 Replies
View Related
Sep 29, 2015
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).
View 3 Replies
View Related
Aug 6, 2006
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
View 5 Replies
View Related
Jul 23, 2005
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)
View 2 Replies
View Related
Nov 3, 2006
Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?
View 2 Replies
View Related
Feb 26, 2007
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
View 1 Replies
View Related
Jun 2, 2008
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)"
View 2 Replies
View Related
May 22, 2004
If I want to find out how many items of a field is in a table like Type1, Type2... to build a table like
Type1, 10
Type2, 25
...
Is is better to open the table -> Select Type From Table
and then go down each row and record the count of a type or is it better to
Select Distinct Type from table to get the types and then do each type's select to get the count
Select Count (1) from table where type = 'Type?'
Thanks,
Frank
View 1 Replies
View Related
Nov 3, 2005
I tried searching, but no luck.
I am trying to return a row count of a query...
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.
Thanks
Larry
View 4 Replies
View Related
Dec 20, 2005
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.
View 1 Replies
View Related
Nov 9, 2006
hello people... sorry im new with this stuff, cant get it right... i need help...
suppose i have a table that has the stores in columns and and order count as a row:
Store1 Store2 Store3 Store4
ordercount 100 null null 30
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...
View 3 Replies
View Related
