For asset 22624, the current (most recent) status = RV. Before that, it
last
changed status on 2003-11-02, when it was IN. So it was IN from
2003-11-02 to
2004-05-02.
I need to produce a report that counts the devices by type that were IN
on a given report date,
like so:
I am unsure how to approach this. I came up with one
Einsteinian-Newtonian method that involved three temp tables, a garden
hose, and a duck, and it was getting uglier by the minute. Has anyone
had 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 have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:
I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.
Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.
By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.
As you can see, I will need to count the number of occurences for analysis for each day, such as Special has 5 and 2 for those two days, we can have difference analysis done each day done. A Union Join will not work because the table is huge and I will need to do at least 30 days. Is interval the best approach, I plan to use Access but can do it in SQL Server if necessary.
I have a table (tbl_entries) in my db that has a timestamp field (startDate). What I need to do is run a query that counts the number of records on a 15 min interval.
something like
start_date 2008-01-01 00:00:00.000 2008-01-01 00:00:00.000 2008-01-01 00:00:00.000 2008-01-01 00:01:00.000 2008-01-01 00:01:00.000 2008-01-01 00:01:00.000 2008-01-01 00:14:00.000 EVERY THING ABOVE HERE IS IN GROUP 1 2008-01-01 00:35:00.000 EVERY THING ABOVE HERE IS IN GROUP 2 2008-01-01 01:01:00.000 2008-01-01 01:03:00.000 2008-01-01 01:03:00.000 2008-01-01 01:04:00.000 EVERY THING ABOVE HERE IS IN GROUP 3 2008-01-01 01:29:00.000 EVERY THING ABOVE HERE IS IN GROUP 4 2008-01-01 01:41:00.000 EVERY THING ABOVE HERE IS IN GROUP 5 2008-01-01 02:25:00.000 2008-01-01 02:28:00.000 2008-01-01 02:31:00.000 2008-01-01 02:33:00.000 EVERY THING ABOVE HERE IS IN GROUP 6 Hope this is enough Info
as you can see, the records have a 30minutes time interval. i need to create a query to know if there are missing records in the table. so basically the result should be this:
I'm trying to create a report which will give me a break down of how many unique vehicles have been seen between two dates via a 15 minute interval and what Lane they were seen. My current script looks like this
SELECT l.Name [Name], count(l.Name) Total, p.Created FROM PlateReads p inner join Lanes l on p.Lane_ID = l.ID where LicencePlate in (Select Plate from LPRnet_MelAir_C.dbo.TempVehiclePlates) group by Name Name being the Lane they were in and the Total being the amount of times a unique vehicle has been seen and p.Created being the date they were seen (thats what I need the interval powered off)
Ideally the output would look like this
16/03/201408:00 to 08:15Bus Lane 15 16/03/201408:00 to 08:15Elevated Road150 16/03/201408:00 to 08:15Public Pickup75
16/03/201408:15 to 08:30Bus Lane 13 16/03/201408:15 to 08:30Elevated Road120 16/03/201408:15 to 08:30Public Pickup55
All the way to 12/04/2014
I’ve got it so it says Lane and Count just can’t get the interval part
hi there i am using .net framework 1.1 with SQL 2000 . i want the data in table to get deleted automatically after 30 days of inserting data. so how do i achieve this?
I have a simple table as shown: I want to have values on the last column to represent the time interval between the 2 date columns (visits); i.e for event-ID 2 for example, I will have
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts FROM log_directory_contacts GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
I have a table called Cartype and there is field called typeid contained 5 rows such as A,B,D,C,E,F Ihave another table called transaction that the field called typeid and the data contains 3 rows of A and 3 rows of B , and 1 rows of E I want to write the sql that output something like this
I am using a foreach file to loop through mdb files kept in a folder and then transfer them to Sql server.
I want to do the following -
Sum the total no. of rows in mdb while the package executes Sum the total no of rows transferred to Sql server. Write them to a database (InitialRowCnt, FinalRowCnt, PackageNm, UserNm)
Note: Total = Total rows in all mdb files. so if there are 10 mdb files with 10 rows each, total = 100.
I saw a RowCount transformation..but just dont know where to place and what to do with it.
I have a view that I want to find all the rows that have a matching itemid and have more than 3 rows in them and group them by the itemid. I am not quite sure how to do this. Any ideas? ~mike~
SELECT ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHERE EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita
hi, in this query (in which I extract all ID_AnagraficaRivendita who have a correspondence in table Flussi_Rivendite with CodiceProdotto = 631 AND CodiceProdotto = 615), I would like to receive also a count of extracted rows... have you any idea?? Thank you ;)
Hi, I am trying to get the first row of what might be a group of any size of rows within an id that may contain a specific field. For eg
Row A = NoteID 1, FK_JobID 1, UnRead
Row B = NoteID 2, FK_JobID 1, UnRead
Row C = NoteID 3, FK_JobID 1, UnRead
I need the sql to return to just one Job (row) even though the job as 3 UnRead fields. But its returning 3 because its only doing what I'm asking. What I need it to do is just get the one Job (row) where any of the notes = UnRead.
I tried using Top 1, but that will only ever return one row and since I need it to return more than one job (row) it won't work.
Heres my attempt
DECLARE @UserID INT
SET @UserID = 4
SELECT User_Notes.BeenRead, Master_Jobs.By_Who, Master_Jobs.Next_Action, Master_Jobs.Due_Time, Master_Jobs.Due_Date, Master_Jobs.Contact, Master_Jobs.Job_Title, Master_Jobs.JobID FROM User_Notes INNER JOIN Note ON User_Notes.FK_UN_NoteID = Note.NoteID INNER JOIN Master_Jobs ON Note.FK_JobID = Master_Jobs.JobID
WHERE Note.FK_UserID = User_Notes.FK_UN_UserID AND BeenRead = 'UnRead'
I'm new to MS SQL having used MySQL for several years now. I just can't figure out the syntax on counting or selecting rows
a) in the Last X (days, weeks, months...)
or
b) between "date X" and "date Y"
I've tried this to get the last week: SELECT COUNT(*) FROM contacts WHERE CONVERT(datetime,timestamp) < (GETDATE() - DATEADD(DAY,7,GETDATE())) ...but I always get a total that includes rows outside the span.
The following query returns 2142 rows which is correct.
Code: select COUNT(*), sum(v.currentMarket) from TRMaster m inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A' where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1 group by m.Year, m.Parcel having SUM(v.currentmarket) > 0
How can I convert this query so that it returns just the count of 2142?
This is my sql string. It counts all the rows in Questions table but it should only count the rows where id in Quizzes matches the quiz column in Questions table.
"select Quizzes.name, Quizzes.id, count(Questions.quiz) as total from Quizzes inner join Questions on Quizzes.id=Questions.quiz"
I am trying to get the number of rows for each month.
table name:sitez ID Name crDate access =========================== 1 Bob .. 2014-01-11 .. 1 2 Jerry .. 2014-01-22 .. 2 3 Jim .. 2014-05-06 .. 1 4 Jason .. 2014-12-11 .. 1 5 Jen .. 2014-11-21 .. 3
I am using the results to make a bar graph so I am querying the database for a given year and expecting 12 results back (running SQL 2012).
Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access = 1 group by all month(crdate)
This should return: 1 0 0 0 1 0 0 0 0 0 0 1
However when testing the script I was only getting back: 1 1 1
which didn't knowing which months were 0
By changing the GROUP BY to GROUP BY ALL, it now puts in the zeroes. However I'm still having issues with incorrect results.
When I query the database, the results for December 2014 shows '13' when I execute:
Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access =3 group by all month(crdate)
There are ZERO rows made with a year of 2014 and an access of 3.I verified this by going a straightforward select * from sitez where crdate = 2014 and access = 3
Why I'm seeing ghost results?
All I need is 12 results, ordered by crdate month.
I am doing a SSIS package to transfer rows from access db to sql server using foreach file. Before loading the data, I want to count the number of rows in a table of the access db. How do I do that?
i have a table like thisStoreId DateClicked1 1/1/20042 1/1/20041 1/1/20041 1/1/20041 1/1/20043 1/1/20042 1/1/2004I want the result of the query to be this:NumberOfClicks StoreId4 12 21 3what is the syntax for this? Thanks.
I built a report with one field as a group. I want to count the number of rows in each field so I can add it to the group field or somw where in the report.
How can I count how many rows do I have in each group?
Hi everyone: I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting? SELECT COUNT(Id_Field), Field2 FROM Table1 WHERE Code_Field = 1 GROUP BY Field2 This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all. Thanks for any help.
My understanding from a previous thread was that ExecuteNonQuery() could be used to display the number of rows returned. Does this also work when calling stored procedures and passing parameters? I have code (shown) that perfectly calls and returns Distinct models downloaded by Country. Yet the rowCount variable displays a -1. What should I do?Dim myCommand As New SqlClient.SqlCommand myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime" myCommand.CommandType = CommandType.StoredProcedure myCommand.Parameters.AddWithValue("@selectDate", dateEntered) myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry)) myCommand.Connection = con con.Open() Dim rowCount As Integer = myCommand.ExecuteNonQuery() numberParts.Text = rowCount.ToString con.Close() Thank you.
