Need help with SQL Query

Question

Post reply

Need help with SQL Query

denis.gendera

Right there with Babe

Points: 733
More actions
August 31, 2012 at 4:53 am

#261012

I've got following query
select machine_id, MONTHS =
CASE
WHEN DATEDIFF("m", firstseen, getdate()) = 0 then 'AUG'
WHEN DATEDIFF("m", firstseen, getdate()) = 1 then 'JULY'
WHEN DATEDIFF("m", firstseen, getdate()) = 2 then 'JUN'
WHEN DATEDIFF("m", firstseen, getdate()) = 3 then 'MAY'
WHEN DATEDIFF("m", firstseen, getdate()) = 4 then 'APR'
WHEN DATEDIFF("m", firstseen, getdate()) = 5 then 'MAR'
WHEN DATEDIFF("m", firstseen, getdate()) = 6 then 'FEB'
WHEN DATEDIFF("m", firstseen, getdate()) = 7 then 'JAN'
END
FROM firstseen
inner join inv_os on firstseen.machine_id = inv_os.machine_id
where firstseen > '1/1/2012' and inv_os.product like '%XP%'
Output is following
machine_id MONTHS
123 JUN
4343 MAR
565565 JUN
....
I would like now to use COUNT Function so I can get totals for each month.
when I try to use COUNT (machine_id) GROUP BY MONTH I get error as the MONTHS column calculated table, how can I get the totals for each month.
Thx.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

Do you mean something like this:

SELECT

CASE

WHEN DATEDIFF('m', firstseen, getdate()) = 0 then 'AUG'

WHEN DATEDIFF('m', firstseen, getdate()) = 1 then 'JULY'

WHEN DATEDIFF('m', firstseen, getdate()) = 2 then 'JUN'

WHEN DATEDIFF('m', firstseen, getdate()) = 3 then 'MAY'

WHEN DATEDIFF('m', firstseen, getdate()) = 4 then 'APR'

WHEN DATEDIFF('m', firstseen, getdate()) = 5 then 'MAR'

WHEN DATEDIFF('m', firstseen, getdate()) = 6 then 'FEB'

WHEN DATEDIFF('m', firstseen, getdate()) = 7 then 'JAN'

END AS 'Months'

,COUNT(machine_id) AS 'Count'

FROM firstseen

inner join inv_os on firstseen.machine_id = inv_os.machine_id

where firstseen > '1/1/2012' and inv_os.product like '%XP%'

GROUP BY

CASE

WHEN DATEDIFF('m', firstseen, getdate()) = 0 then 'AUG'

WHEN DATEDIFF('m', firstseen, getdate()) = 1 then 'JULY'

WHEN DATEDIFF('m', firstseen, getdate()) = 2 then 'JUN'

WHEN DATEDIFF('m', firstseen, getdate()) = 3 then 'MAY'

WHEN DATEDIFF('m', firstseen, getdate()) = 4 then 'APR'

WHEN DATEDIFF('m', firstseen, getdate()) = 5 then 'MAR'

WHEN DATEDIFF('m', firstseen, getdate()) = 6 then 'FEB'

WHEN DATEDIFF('m', firstseen, getdate()) = 7 then 'JAN'

END

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

This is identical to Andy's query, I just find it easier to read. Just a preference thing 🙂

SELECT COUNT(machine_id), [Month]

FROM (SELECT machine_id,

CASE WHEN DATEDIFF('m', firstseen, getdate()) = 0 THEN 'AUG'

WHEN DATEDIFF('m', firstseen, getdate()) = 1 THEN 'JULY'

WHEN DATEDIFF('m', firstseen, getdate()) = 2 THEN 'JUN'

WHEN DATEDIFF('m', firstseen, getdate()) = 3 THEN 'MAY'

WHEN DATEDIFF('m', firstseen, getdate()) = 4 THEN 'APR'

WHEN DATEDIFF('m', firstseen, getdate()) = 5 THEN 'MAR'

WHEN DATEDIFF('m', firstseen, getdate()) = 6 THEN 'FEB'

WHEN DATEDIFF('m', firstseen, getdate()) = 7 THEN 'JAN' END

FROM firstseen

INNER JOIN inv_os ON firstseen.machine_id = inv_os.machine_id

WHERE firstseen > '1/1/2012' AND inv_os.product LIKE '%XP%'

)a(machine_id,[Month])

GROUP BY [Month];

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

denis.gendera (8/31/2012)
I've got following query
select machine_id, MONTHS =
CASE
WHEN DATEDIFF("m", firstseen, getdate()) = 0 then 'AUG'
WHEN DATEDIFF("m", firstseen, getdate()) = 1 then 'JULY'
WHEN DATEDIFF("m", firstseen, getdate()) = 2 then 'JUN'
WHEN DATEDIFF("m", firstseen, getdate()) = 3 then 'MAY'
WHEN DATEDIFF("m", firstseen, getdate()) = 4 then 'APR'
WHEN DATEDIFF("m", firstseen, getdate()) = 5 then 'MAR'
WHEN DATEDIFF("m", firstseen, getdate()) = 6 then 'FEB'
WHEN DATEDIFF("m", firstseen, getdate()) = 7 then 'JAN'
END
FROM firstseen
inner join inv_os on firstseen.machine_id = inv_os.machine_id
where firstseen > '1/1/2012' and inv_os.product like '%XP%'
Output is following
machine_id MONTHS
123 JUN
4343 MAR
565565 JUN
....
I would like now to use COUNT Function so I can get totals for each month.
when I try to use COUNT (machine_id) GROUP BY MONTH I get error as the MONTHS column calculated table, how can I get the totals for each month.
Thx.

What happens when you run this query tomorrow? Try this instead:

-- sample data

DROP TABLE #inv_os

SELECT machine_id, product

INTO #inv_os

FROM (SELECT machine_id = 10, product = 'XP' UNION ALL SELECT 20, 'XP') d

DROP TABLE #firstseen

SELECT machine_id, firstseen

INTO #firstseen

FROM (

SELECT machine_id = 10, firstseen = CONVERT(DATETIME,'2012-08-31 12:24:59.713',121) UNION ALL

SELECT machine_id = 10, firstseen = CONVERT(DATETIME,'2012-08-15 12:24:59.713',121) UNION ALL

SELECT machine_id = 10, firstseen = CONVERT(DATETIME,'2012-08-01 12:24:59.713',121) UNION ALL

SELECT machine_id = 10, firstseen = CONVERT(DATETIME,'2012-07-30 12:24:59.713',121) UNION ALL

SELECT machine_id = 10, firstseen = CONVERT(DATETIME,'2012-07-15 12:24:59.713',121) UNION ALL

SELECT machine_id = 20, firstseen = CONVERT(DATETIME,'2012-07-01 12:24:59.713',121) UNION ALL

SELECT machine_id = 20, firstseen = CONVERT(DATETIME,'2012-06-30 12:24:59.713',121) UNION ALL

SELECT machine_id = 20, firstseen = CONVERT(DATETIME,'2012-06-15 12:24:59.713',121) UNION ALL

SELECT machine_id = 20, firstseen = CONVERT(DATETIME,'2012-06-01 12:24:59.713',121) UNION ALL

SELECT machine_id = 20, firstseen = CONVERT(DATETIME,'2012-05-30 12:24:59.713',121)

) d

-- solution

SELECT

f.machine_id,

x.[MonthName],

[TotalsForMonth] = COUNT(*)

FROM #firstseen f

INNER JOIN #inv_os inv_os

ON f.machine_id = inv_os.machine_id

CROSS APPLY (

SELECT [MonthName] = DATENAME(month,f.firstseen), [MonthNumber] = DATEPART(month,f.firstseen)

) x

WHERE f.firstseen > '1/1/2012'

AND inv_os.product LIKE '%XP%'

GROUP BY f.machine_id, x.[MonthName], x.[MonthNumber]

ORDER BY f.machine_id, x.[MonthNumber]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jeetendrajaiswal2000 Valued Member Points: 73 More actions · Answer 4

Try this

WITH cte AS

(

SELECT machine_id,

CASE WHEN DATEDIFF('m', firstseen, getdate()) = 0 THEN 'AUG'

WHEN DATEDIFF('m', firstseen, getdate()) = 1 THEN 'JULY'

WHEN DATEDIFF('m', firstseen, getdate()) = 2 THEN 'JUN'

WHEN DATEDIFF('m', firstseen, getdate()) = 3 THEN 'MAY'

WHEN DATEDIFF('m', firstseen, getdate()) = 4 THEN 'APR'

WHEN DATEDIFF('m', firstseen, getdate()) = 5 THEN 'MAR'

WHEN DATEDIFF('m', firstseen, getdate()) = 6 THEN 'FEB'

WHEN DATEDIFF('m', firstseen, getdate()) = 7 THEN 'JAN' END [Month]

FROM firstseen

INNER JOIN inv_os ON firstseen.machine_id = inv_os.machine_id

WHERE firstseen > '1/1/2012' AND inv_os.product LIKE '%XP%'

)

SELECT [Month],COUNT(machine_id) [Count] FROM CTE

GROUP BY [Month]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

denis.gendera (8/31/2012)
I've got following query
select machine_id, MONTHS =
CASE
WHEN DATEDIFF("m", firstseen, getdate()) = 0 then 'AUG'
WHEN DATEDIFF("m", firstseen, getdate()) = 1 then 'JULY'
WHEN DATEDIFF("m", firstseen, getdate()) = 2 then 'JUN'
WHEN DATEDIFF("m", firstseen, getdate()) = 3 then 'MAY'
WHEN DATEDIFF("m", firstseen, getdate()) = 4 then 'APR'
WHEN DATEDIFF("m", firstseen, getdate()) = 5 then 'MAR'
WHEN DATEDIFF("m", firstseen, getdate()) = 6 then 'FEB'
WHEN DATEDIFF("m", firstseen, getdate()) = 7 then 'JAN'
END
FROM firstseen
inner join inv_os on firstseen.machine_id = inv_os.machine_id
where firstseen > '1/1/2012' and inv_os.product like '%XP%'
Output is following
machine_id MONTHS
123 JUN
4343 MAR
565565 JUN
....
I would like now to use COUNT Function so I can get totals for each month.
when I try to use COUNT (machine_id) GROUP BY MONTH I get error as the MONTHS column calculated table, how can I get the totals for each month.
Thx.

-- look at what this returns:

SELECT UPPER(LEFT(DATENAME(MONTH,GETDATE()),3))

-- ask yourself if this will work in 2 months' time:

DATEDIFF("m", firstseen, getdate()) = 0 then 'AUG'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden