Just trying to get the count that meets the criteria
-
I struggled with this in the past, like 10 years ago, and it's been quite awhile since I've needed something like this. I'm trying to get the count, the number of records that meet this criteria. But I get an array of records because of the JOIN.I searched the internet, but didn't really see anything that came close, or the examples were very simple. I don't really need anything in SELECT, except the count. $designerId is PHP 8
SELECT
project.project_no,
COUNT(commission_summary.project_no)
FROM project
INNER JOIN commission_summary ON commission_summary.project_no = project.project_no
WHERE project.sales_no = '$designerId'
AND (project.status = 'construction' OR project.status = 'finished')
AND (commission_summary.startup_check_date is NULL OR CONVERT(char(10), commission_summary.startup_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.startup_check_date, 120) >= '2021-01-01')
AND (commission_summary.finished_check_date is NULL OR CONVERT(char(10), commission_summary.finished_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.finished_check_date, 120) >= DATEADD(month, -1, GETDATE()) )
GROUP BY project.project_noIf it ain't broke don't fix it Discover my world at jkirkerx.com
-
I struggled with this in the past, like 10 years ago, and it's been quite awhile since I've needed something like this. I'm trying to get the count, the number of records that meet this criteria. But I get an array of records because of the JOIN.I searched the internet, but didn't really see anything that came close, or the examples were very simple. I don't really need anything in SELECT, except the count. $designerId is PHP 8
SELECT
project.project_no,
COUNT(commission_summary.project_no)
FROM project
INNER JOIN commission_summary ON commission_summary.project_no = project.project_no
WHERE project.sales_no = '$designerId'
AND (project.status = 'construction' OR project.status = 'finished')
AND (commission_summary.startup_check_date is NULL OR CONVERT(char(10), commission_summary.startup_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.startup_check_date, 120) >= '2021-01-01')
AND (commission_summary.finished_check_date is NULL OR CONVERT(char(10), commission_summary.finished_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.finished_check_date, 120) >= DATEADD(month, -1, GETDATE()) )
GROUP BY project.project_noIf it ain't broke don't fix it Discover my world at jkirkerx.com
You could use a CTE to identify the records that match then count. Bit hard to help without sample data though. Example:
;with cte as
(
SELECT distinct
p.project_no
FROM @project p
INNER JOIN @commission_summary cs ON cs.project_no = p.project_no
WHERE p.sales_no = @designerId
AND (p.status = 'construction' OR p.status = 'finished')
AND (cs.startup_check_date is NULL OR CONVERT(char(10), cs.startup_check_date, 120) = '1900-01-01' OR CONVERT(char(10), cs.startup_check_date, 120) >= '2021-01-01')
AND (cs.finished_check_date is NULL OR CONVERT(char(10), cs.finished_check_date, 120) = '1900-01-01' OR CONVERT(char(10), cs.finished_check_date, 120) >= DATEADD(month, -1, GETDATE()) )
)
select COUNT(*)
from cteI would question all those
CONVERTs - surelycommission_summary.startup_check_dateis adatenot a string? -
You could use a CTE to identify the records that match then count. Bit hard to help without sample data though. Example:
;with cte as
(
SELECT distinct
p.project_no
FROM @project p
INNER JOIN @commission_summary cs ON cs.project_no = p.project_no
WHERE p.sales_no = @designerId
AND (p.status = 'construction' OR p.status = 'finished')
AND (cs.startup_check_date is NULL OR CONVERT(char(10), cs.startup_check_date, 120) = '1900-01-01' OR CONVERT(char(10), cs.startup_check_date, 120) >= '2021-01-01')
AND (cs.finished_check_date is NULL OR CONVERT(char(10), cs.finished_check_date, 120) = '1900-01-01' OR CONVERT(char(10), cs.finished_check_date, 120) >= DATEADD(month, -1, GETDATE()) )
)
select COUNT(*)
from cteI would question all those
CONVERTs - surelycommission_summary.startup_check_dateis adatenot a string?That's a pretty good idea, I'll give it a try. I knew somebody would question the converts. It's a PHP program, and PHP8 doesn't support SmallDateTime very well. I'm rewriting a companies PHP4 app written from 2003 to 2012, and I didn't want to change the database because it has 26 gigs of data. I needed a consistent way of working with dates already written, so I choose the ISO120 format. ISO120 just makes it easier to fabricate new dates, and do comparisons in PHP8. Let me try out your ideas.
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
You could use a CTE to identify the records that match then count. Bit hard to help without sample data though. Example:
;with cte as
(
SELECT distinct
p.project_no
FROM @project p
INNER JOIN @commission_summary cs ON cs.project_no = p.project_no
WHERE p.sales_no = @designerId
AND (p.status = 'construction' OR p.status = 'finished')
AND (cs.startup_check_date is NULL OR CONVERT(char(10), cs.startup_check_date, 120) = '1900-01-01' OR CONVERT(char(10), cs.startup_check_date, 120) >= '2021-01-01')
AND (cs.finished_check_date is NULL OR CONVERT(char(10), cs.finished_check_date, 120) = '1900-01-01' OR CONVERT(char(10), cs.finished_check_date, 120) >= DATEADD(month, -1, GETDATE()) )
)
select COUNT(*)
from cteI would question all those
CONVERTs - surelycommission_summary.startup_check_dateis adatenot a string?Thanks @Chill60 Works like a champ! I thought more about what you said about dates and strings. I've concluded that in PHP8, or really what's stored in the database (SQL Server) as a Date or DATETIME column, is just a string formatted to a particular ISO format, labeled as something special or unique. I could be wrong here, there really isn't much help or support available, and I'm on my own here with PHP8. I've seemed to have gone so far beyond the common PHP programmer, that I'm in uncharted waters. This code gets a list of qualified swimming pool designers that have actual projects (Swimming Pools) to pay commission on, so they get their paycheck. I got tired of going through the entire pool of designers that didn't have projects to test with.
$designerId = (rtrim($row1[0]));
$query2 = "
WITH cte AS
(
SELECT distinct
project.project_no
FROM project
RIGHT JOIN commission_summary ON commission_summary.project_no = project.project_no
WHERE project.sales_no = '$designerId'
AND (project.status = 'construction' OR project.status = 'finished')
AND (commission_summary.startup_check_date is NULL OR CONVERT(char(10), commission_summary.startup_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.startup_check_date, 120) >= '2021-01-01')
AND (commission_summary.finished_check_date is NULL OR CONVERT(char(10), commission_summary.finished_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.finished_check_date, 120) >= DATEADD(month, -1, GETDATE()) )
)
SELECT COUNT(*) FROM cte";$result2 = sqlsrv_query($conn, $query2) or die(" getDesignersWithProjectsKeyValuesByLastName " . __LINE__ . " - " . $query2 . " - " . print_r(sqlsrv_errors()));
if (sqlsrv_has_rows($result2)) {$row2 = sqlsrv\_fetch\_array($result2); $projectCount = $row2\[0\]; if ($projectCount > 0) { $keyValue = new KeyValue(); $keyValue->setKey(rtrim($row1\[1\])); $keyValue->setValue(rtrim($row1\[0\])); $keyValues->add($keyValue); }}
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
Thanks @Chill60 Works like a champ! I thought more about what you said about dates and strings. I've concluded that in PHP8, or really what's stored in the database (SQL Server) as a Date or DATETIME column, is just a string formatted to a particular ISO format, labeled as something special or unique. I could be wrong here, there really isn't much help or support available, and I'm on my own here with PHP8. I've seemed to have gone so far beyond the common PHP programmer, that I'm in uncharted waters. This code gets a list of qualified swimming pool designers that have actual projects (Swimming Pools) to pay commission on, so they get their paycheck. I got tired of going through the entire pool of designers that didn't have projects to test with.
$designerId = (rtrim($row1[0]));
$query2 = "
WITH cte AS
(
SELECT distinct
project.project_no
FROM project
RIGHT JOIN commission_summary ON commission_summary.project_no = project.project_no
WHERE project.sales_no = '$designerId'
AND (project.status = 'construction' OR project.status = 'finished')
AND (commission_summary.startup_check_date is NULL OR CONVERT(char(10), commission_summary.startup_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.startup_check_date, 120) >= '2021-01-01')
AND (commission_summary.finished_check_date is NULL OR CONVERT(char(10), commission_summary.finished_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.finished_check_date, 120) >= DATEADD(month, -1, GETDATE()) )
)
SELECT COUNT(*) FROM cte";$result2 = sqlsrv_query($conn, $query2) or die(" getDesignersWithProjectsKeyValuesByLastName " . __LINE__ . " - " . $query2 . " - " . print_r(sqlsrv_errors()));
if (sqlsrv_has_rows($result2)) {$row2 = sqlsrv\_fetch\_array($result2); $projectCount = $row2\[0\]; if ($projectCount > 0) { $keyValue = new KeyValue(); $keyValue->setKey(rtrim($row1\[1\])); $keyValue->setValue(rtrim($row1\[0\])); $keyValues->add($keyValue); }}
If it ain't broke don't fix it Discover my world at jkirkerx.com
Quote:
what's stored in the database (SQL Server) as a Date or DATETIME column, is just a string formatted to a particular ISO format, labeled as something special or unique.
Nope - it's stored on the database as a date in 3 bytes and no formatting takes place at all - formatting of dates only takes place when they are being displayed. This article explains further How SQL Server stores data types: dates and times - Born SQL[^]
Quote:
I needed a consistent way of working with dates already written, so I choose the ISO120 format. ISO120 just makes it easier to fabricate new dates, and do comparisons in PHP8.
You might want to rethink that and make sure you are using the latest version of PHP 8. I know there were some issues around creating date objects from strings, but this is the first time I've heard anyone claim it has problems with SQL dates. Your problems are probably arising from incorrectly storing the date as a string.
Quote:
I didn't want to change the database because it has 26 gigs of data.
Tbh that's not very big. If it was me I would bite that bullet - as long as there is nothing else using the database (including MI teams). Actually, even then I would probably still go for it.
-
Quote:
what's stored in the database (SQL Server) as a Date or DATETIME column, is just a string formatted to a particular ISO format, labeled as something special or unique.
Nope - it's stored on the database as a date in 3 bytes and no formatting takes place at all - formatting of dates only takes place when they are being displayed. This article explains further How SQL Server stores data types: dates and times - Born SQL[^]
Quote:
I needed a consistent way of working with dates already written, so I choose the ISO120 format. ISO120 just makes it easier to fabricate new dates, and do comparisons in PHP8.
You might want to rethink that and make sure you are using the latest version of PHP 8. I know there were some issues around creating date objects from strings, but this is the first time I've heard anyone claim it has problems with SQL dates. Your problems are probably arising from incorrectly storing the date as a string.
Quote:
I didn't want to change the database because it has 26 gigs of data.
Tbh that's not very big. If it was me I would bite that bullet - as long as there is nothing else using the database (including MI teams). Actually, even then I would probably still go for it.
Just had another thought - download Sql Server Management Studio (should be free) and then you can see the database schema to find out exactly how that data is stored on your database. I interpreted your comment about date storage as being dates in general
-
Quote:
what's stored in the database (SQL Server) as a Date or DATETIME column, is just a string formatted to a particular ISO format, labeled as something special or unique.
Nope - it's stored on the database as a date in 3 bytes and no formatting takes place at all - formatting of dates only takes place when they are being displayed. This article explains further How SQL Server stores data types: dates and times - Born SQL[^]
Quote:
I needed a consistent way of working with dates already written, so I choose the ISO120 format. ISO120 just makes it easier to fabricate new dates, and do comparisons in PHP8.
You might want to rethink that and make sure you are using the latest version of PHP 8. I know there were some issues around creating date objects from strings, but this is the first time I've heard anyone claim it has problems with SQL dates. Your problems are probably arising from incorrectly storing the date as a string.
Quote:
I didn't want to change the database because it has 26 gigs of data.
Tbh that's not very big. If it was me I would bite that bullet - as long as there is nothing else using the database (including MI teams). Actually, even then I would probably still go for it.
3 bytes to store a date? Ok, I believe that. When I went into this project, I told the customer that I wasn't going to change the database design, unless it was needed. I did have to change the size of the username, wasn't large enough. I'll start rethinking the dates again. Thanks for debunking my date theory :)
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
3 bytes to store a date? Ok, I believe that. When I went into this project, I told the customer that I wasn't going to change the database design, unless it was needed. I did have to change the size of the username, wasn't large enough. I'll start rethinking the dates again. Thanks for debunking my date theory :)
If it ain't broke don't fix it Discover my world at jkirkerx.com
You can see the storage size for each of the types in the documentation - for example:
date (Transact-SQL) - SQL Server | Microsoft Docs[^]
Storage size: 3 bytes, fixed
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You can see the storage size for each of the types in the documentation - for example:
date (Transact-SQL) - SQL Server | Microsoft Docs[^]
Storage size: 3 bytes, fixed
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Very helpful Richard So I guess I meant ISO 8601, but I called it ISO120 which is my bad. YYYY-MM-DD So if that's the native format for Date stored in bytes, I guess converting SmalDateTime to 120 to just give me the date and strip off the time isn't that bad.
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
Very helpful Richard So I guess I meant ISO 8601, but I called it ISO120 which is my bad. YYYY-MM-DD So if that's the native format for Date stored in bytes, I guess converting SmalDateTime to 120 to just give me the date and strip off the time isn't that bad.
If it ain't broke don't fix it Discover my world at jkirkerx.com
