CODEWITHSUNDEEP
sqlsql-server-2008datetimegroup-by
user736893
user736893

Reputation:

SQL Statement to get grouped data from every 30 days, grouped by date

I have a table with the following two columns

ExportTime              | Manufacturer
2009-11-16 21:30:10.000 | IBM
2009-11-16 21:30:05.000 | VMWare
2009-11-16 21:30:12.000 | HP
2009-11-17 21:30:10.000 | Dell
2009-11-17 21:30:05.000 | VMWare
2009-11-17 21:30:12.000 | VMWare

I'm trying to create a chart to visualize the percentage of VMs over time. So basically I want a SQL result that looks like this:

Date       | Virtual | Physical
2009-11-16 | 1       | 2
2009-11-16 | 2       | 1

This is the base query I've started with that gives me all virtual and all physical

SELECT a.vmcount, b.physcount FROM (
    SELECT count(*) as vmcount FROM ServerDataHistorical
    WHERE Manufacturer LIKE 'VM%') a, (
    SELECT count(*) as physcount FROM ServerDataHistorical
    WHERE Manufacturer NOT LIKE 'VM%'
    ) b

I tried this:

SELECT DATEADD(dd,(DATEDIFF(dd,0,ServerDataHistorical.ExportTime)),0) as date, a.vmcount, b.physcount FROM (
    SELECT count(*) as vmcount FROM ServerDataHistorical
    WHERE Manufacturer LIKE 'VM%') a, (
    SELECT count(*) as physcount FROM ServerDataHistorical
    WHERE Manufacturer NOT LIKE 'VM%'
    ) b,
    ServerDataHistorical
GROUP BY DATEADD(dd,(DATEDIFF(dd,0,ServerDataHistorical.ExportTime)),0)

And it tells me that vmcount is invalid because it's not contained in the group by clause. I also tried this:

Declare @myDate varchar
Set @myDate = '2009-11-16'
SELECT ExportTime, a.vmcount, b.physcount FROM (
    SELECT count(*) as vmcount FROM ServerDataHistorical
    WHERE Manufacturer LIKE 'VM%' AND ExportTime LIKE @myDate + '%') a, (
    SELECT count(*) as physcount FROM ServerDataHistorical
    WHERE Manufacturer NOT LIKE 'VM%' AND ExportTime LIKE @myDate + '%'
    ) b,
    ServerDataHistorical
Group by ExportTime

I thought I'd just programatically step through dates and get the numbers, but this gives the same error as the last one.

Upvotes: 0

Views: 593

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

Your basic query would be much simpler with conditional summation:

SELECT sum(case when sdh.Manufacturer like 'VM%' then 1 else 0 end) as vmcount,
       sum(case when sdh.Manufacturer not like 'VM%' then 1 else 0 end) as physical
FROM ServerDataHistorical sdh

If you want this by date, just add in a group by clause:

SELECT cast(sdh.ExportTime as date) as thedate,
       sum(case when sdh.Manufacturer like 'VM%' then 1 else 0 end) as vmcount,
       sum(case when sdh.Manufacturer not like 'VM%' then 1 else 0 end) as physical
FROM ServerDataHistorical sdh
group by cast(sdh.ExportTime as date)
order by 1;

And, if you want this for a particular date period, just add in a where clause:

where ExportTime >= cast(@myDate as datetime) and
      ExportTime < cast(@MyDate as datetime) + 1

By the way, the variable @MyDate should be declared as a date or datetime. Get out of the habit of storing date/time values in strings.

Upvotes: 2

Related Questions