August 10, 2015 at 8:23 am
I'm using the SQL below to work out recruitment figures. The query works well, but I now need to build this query into a DotNet application as a webpage. I therefore need to declare and manipulate the values such as 'Total', 'Jan' to 'Dec' and 'YearRecruited' in order to write these values into my chosen data control. I therefore need to know the datatypes of these calculated values.
Total I think is straightforward as DNAScreeningID is an int datatype. But what about the others?
Thanks for any help.
SELECT YEAR(SamplingDate) AS YearRecruited,
COUNT(CASE WHEN month(SamplingDate) = 1 THEN DNAScreeningID END) AS 'Jan',
COUNT(CASE WHEN month(SamplingDate) = 2 THEN DNAScreeningID END) AS 'Feb',
COUNT(CASE WHEN month(SamplingDate) = 3 THEN DNAScreeningID END) AS 'Mar',
COUNT(CASE WHEN month(SamplingDate) = 4 THEN DNAScreeningID END) AS 'Apr',
COUNT(CASE WHEN month(SamplingDate) = 5 THEN DNAScreeningID END) AS 'May',
COUNT(CASE WHEN month(SamplingDate) = 6 THEN DNAScreeningID END) AS 'Jun',
COUNT(CASE WHEN month(SamplingDate) = 7 THEN DNAScreeningID END) AS 'Jul',
COUNT(CASE WHEN month(SamplingDate) = 8 THEN DNAScreeningID END) AS 'Aug',
COUNT(CASE WHEN month(SamplingDate) = 9 THEN DNAScreeningID END) AS 'Sep',
COUNT(CASE WHEN month(SamplingDate) = 10 THEN DNAScreeningID END) AS 'Oct',
COUNT(CASE WHEN month(SamplingDate) = 11 THEN DNAScreeningID END) AS 'Nov',
COUNT(CASE WHEN month(SamplingDate) = 12 THEN DNAScreeningID END) AS 'Dec',
COUNT(DNAScreeningID) AS 'Total'
FROM tblPatients
WHERE YEAR(SamplingDate)=2015
GROUP BY Year(SamplingDate)
ORDER BY YearRecruited ASC
August 10, 2015 at 8:43 am
M Joomun (8/10/2015)
I'm using the SQL below to work out recruitment figures. The query works well, but I now need to build this query into a DotNet application as a webpage. I therefore need to declare and manipulate the values such as 'Total', 'Jan' to 'Dec' and 'YearRecruited' in order to write these values into my chosen data control. I therefore need to know the datatypes of these calculated values.Total I think is straightforward as DNAScreeningID is an int datatype. But what about the others?
Thanks for any help.
SELECT YEAR(SamplingDate) AS YearRecruited,
COUNT(CASE WHEN month(SamplingDate) = 1 THEN DNAScreeningID END) AS 'Jan',
COUNT(CASE WHEN month(SamplingDate) = 2 THEN DNAScreeningID END) AS 'Feb',
COUNT(CASE WHEN month(SamplingDate) = 3 THEN DNAScreeningID END) AS 'Mar',
COUNT(CASE WHEN month(SamplingDate) = 4 THEN DNAScreeningID END) AS 'Apr',
COUNT(CASE WHEN month(SamplingDate) = 5 THEN DNAScreeningID END) AS 'May',
COUNT(CASE WHEN month(SamplingDate) = 6 THEN DNAScreeningID END) AS 'Jun',
COUNT(CASE WHEN month(SamplingDate) = 7 THEN DNAScreeningID END) AS 'Jul',
COUNT(CASE WHEN month(SamplingDate) = 8 THEN DNAScreeningID END) AS 'Aug',
COUNT(CASE WHEN month(SamplingDate) = 9 THEN DNAScreeningID END) AS 'Sep',
COUNT(CASE WHEN month(SamplingDate) = 10 THEN DNAScreeningID END) AS 'Oct',
COUNT(CASE WHEN month(SamplingDate) = 11 THEN DNAScreeningID END) AS 'Nov',
COUNT(CASE WHEN month(SamplingDate) = 12 THEN DNAScreeningID END) AS 'Dec',
COUNT(DNAScreeningID) AS 'Total'
FROM tblPatients
WHERE YEAR(SamplingDate)=2015
GROUP BY Year(SamplingDate)
ORDER BY YearRecruited ASC
The easy way to get the data type is to use the query as the source for a table insert, then just look at the table. Use a temp table if you don't have CREATE TABLE permissions. However, I was thinking that if you were wondering about the data types, maybe you just need to structure your query another way:
WITH ADDED_DATA AS (
SELECT YEAR(SamplingDate) AS YearRecruited,
MONTH(SamplingDate) AS MonthRecruited),
LEFT(DATENAME(month, SamplingDate), 3) AS Month_Name,
DNAScreeningID
FROM tblPatients
),
GROUPED_DATA AS (
SELECT D.YearRecruited, D.MonthRecruited, D.Month_Name, COUNT(D.DNAScreeningID) AS TheCount
FROM ADDED_DATA AS D
WHERE YEAR(SamplingDate) = 2015
GROUP BY D.YearRecruited, D.MonthRecruited, D.Month_Name
)
SELECT YearRecruited, [Jan], [Feb], [Mar], [Apr], [May], [Jun],
[Jul], [Aug], [Sep], [Oct], [Nov], [Dec],
[Jan] + [Feb] + [Mar] + [Apr] + [May] + [Jun] +
[Jul] + [Aug] + [Sep] + [Oct] + [Nov] + [Dec] AS Total
FROM GROUPED_DATA
PIVOT (SUM(TheCount) FOR Month_Name IN
([Jan], [Feb], [Mar], [Apr], [May], [Jun],
[Jul], [Aug], [Sep], [Oct], [Nov], [Dec])) AS PVT
This query separates out the month and year and both of those values, as well as the COUNT, will be integers. As the month value is not in the final SELECT, there's no need to worry about it's data type. I also took out your ORDER BY clause, as you'll only get one record back, by virtue of your WHERE clause.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 10, 2015 at 9:11 am
In this case, you don't need to know the types from the columns as they won't matter. The important part is to know the type the function returns.
COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value.
COUNT and YEAR will always return int, no matter what the input is.
August 10, 2015 at 9:31 am
By the way, you should change the WHERE clause to make it "sargable":
...
WHERE SamplingDate >= '20150101' AND SamplingDate < '20160101'
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 11, 2015 at 1:48 am
Luis Cazares (8/10/2015)
In this case, you don't need to know the types from the columns as they won't matter. The important part is to know the type the function returns.
COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value.COUNT and YEAR will always return int, no matter what the input is.
Of course, that makes perfect sense. I'll try out and let you know if it works.
August 11, 2015 at 6:49 am
sgmunson (8/10/2015)
M Joomun (8/10/2015)
I'm using the SQL below to work out recruitment figures. The query works well, but I now need to build this query into a DotNet application as a webpage. I therefore need to declare and manipulate the values such as 'Total', 'Jan' to 'Dec' and 'YearRecruited' in order to write these values into my chosen data control. I therefore need to know the datatypes of these calculated values.Total I think is straightforward as DNAScreeningID is an int datatype. But what about the others?
Thanks for any help.
SELECT YEAR(SamplingDate) AS YearRecruited,
COUNT(CASE WHEN month(SamplingDate) = 1 THEN DNAScreeningID END) AS 'Jan',
COUNT(CASE WHEN month(SamplingDate) = 2 THEN DNAScreeningID END) AS 'Feb',
COUNT(CASE WHEN month(SamplingDate) = 3 THEN DNAScreeningID END) AS 'Mar',
COUNT(CASE WHEN month(SamplingDate) = 4 THEN DNAScreeningID END) AS 'Apr',
COUNT(CASE WHEN month(SamplingDate) = 5 THEN DNAScreeningID END) AS 'May',
COUNT(CASE WHEN month(SamplingDate) = 6 THEN DNAScreeningID END) AS 'Jun',
COUNT(CASE WHEN month(SamplingDate) = 7 THEN DNAScreeningID END) AS 'Jul',
COUNT(CASE WHEN month(SamplingDate) = 8 THEN DNAScreeningID END) AS 'Aug',
COUNT(CASE WHEN month(SamplingDate) = 9 THEN DNAScreeningID END) AS 'Sep',
COUNT(CASE WHEN month(SamplingDate) = 10 THEN DNAScreeningID END) AS 'Oct',
COUNT(CASE WHEN month(SamplingDate) = 11 THEN DNAScreeningID END) AS 'Nov',
COUNT(CASE WHEN month(SamplingDate) = 12 THEN DNAScreeningID END) AS 'Dec',
COUNT(DNAScreeningID) AS 'Total'
FROM tblPatients
WHERE YEAR(SamplingDate)=2015
GROUP BY Year(SamplingDate)
ORDER BY YearRecruited ASC
The easy way to get the data type is to use the query as the source for a table insert, then just look at the table. Use a temp table if you don't have CREATE TABLE permissions. However, I was thinking that if you were wondering about the data types, maybe you just need to structure your query another way:
WITH ADDED_DATA AS (
SELECT YEAR(SamplingDate) AS YearRecruited,
MONTH(SamplingDate) AS MonthRecruited),
LEFT(DATENAME(month, SamplingDate), 3) AS Month_Name,
DNAScreeningID
FROM tblPatients
),
GROUPED_DATA AS (
SELECT D.YearRecruited, D.MonthRecruited, D.Month_Name, COUNT(D.DNAScreeningID) AS TheCount
FROM ADDED_DATA AS D
WHERE YEAR(SamplingDate) = 2015
GROUP BY D.YearRecruited, D.MonthRecruited, D.Month_Name
)
SELECT YearRecruited, [Jan], [Feb], [Mar], [Apr], [May], [Jun],
[Jul], [Aug], [Sep], [Oct], [Nov], [Dec],
[Jan] + [Feb] + [Mar] + [Apr] + [May] + [Jun] +
[Jul] + [Aug] + [Sep] + [Oct] + [Nov] + [Dec] AS Total
FROM GROUPED_DATA
PIVOT (SUM(TheCount) FOR Month_Name IN
([Jan], [Feb], [Mar], [Apr], [May], [Jun],
[Jul], [Aug], [Sep], [Oct], [Nov], [Dec])) AS PVT
This query separates out the month and year and both of those values, as well as the COUNT, will be integers. As the month value is not in the final SELECT, there's no need to worry about it's data type. I also took out your ORDER BY clause, as you'll only get one record back, by virtue of your WHERE clause.
Thanks, I will give that a try.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply