Click here to Skip to main content
15,907,281 members
Home / Discussions / Database
   

Database

 
AnswerRe: Using SQL Native Backup URL, multiple databases to Azure Blob Storage Pin
jschell12-Dec-22 10:33
jschell12-Dec-22 10:33 
QuestionEntity Framework Core 6 VARCHAR(MAX) Pin
Kevin Marois8-Dec-22 12:54
professionalKevin Marois8-Dec-22 12:54 
AnswerRe: Entity Framework Core 6 VARCHAR(MAX) Pin
Victor Nijegorodov8-Dec-22 18:24
Victor Nijegorodov8-Dec-22 18:24 
GeneralRe: Entity Framework Core 6 VARCHAR(MAX) Pin
Kevin Marois9-Dec-22 11:47
professionalKevin Marois9-Dec-22 11:47 
GeneralRe: Entity Framework Core 6 VARCHAR(MAX) Pin
jschell12-Dec-22 10:35
jschell12-Dec-22 10:35 
QuestionSOLVED - Pivot a result set Pin
Richard Andrew x6428-Nov-22 5:46
professionalRichard Andrew x6428-Nov-22 5:46 
AnswerRe: SOLVED - Pivot a result set Pin
Richard Deeming28-Nov-22 21:24
mveRichard Deeming28-Nov-22 21:24 
QuestionTSQL - JOIN cancels out my date filter Pin
jkirkerx27-Nov-22 13:53
professionaljkirkerx27-Nov-22 13:53 
Well today was a crappy day, thinking I can hit and run this small web page and clean it up. I spent hours modeling this in SQL Manager finally getting it to work, and then used it my PHP code, it worked but fell apart. Maybe I have it backwards, and I should call "Project" first, and then join "commission_Summary"

The first example works, but when I join the "project" table, it cancels out my "commission_summary.startup_check_date" filter. I get that or think that my JOIN is wrong, or that my JOIN is calling up all records and overriding my date filter. I tried different types of JOINs, but with no success.
SELECT 
    commission_summary.project_no,
    commission_summary.EC, 
    commission_summary.TC, 
    CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate, 
    CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate      
FROM commission_summary
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
ORDER BY commission_summary.startup_check_date DESC 

I tried adding one join at a time, and it worked or produced my desired results using just JOIN. I don't understand why this returns all the records, well I know it's JOIN related, just not sure why. My theory, is that the "Project" JOIN may have a duplicate column name. I wonder if I should of used a wildcard for testing, like SELECT * FROM. This returns all the records
SELECT 
    commission_summary.project_no,
    commission_summary.EC, 
    commission_summary.TC, 
    CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate, 
    CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate,
    project.Status, 
    project.Sales_no,
    project.swan_job, 
    employee.Employee_ID, 
    employee.FName, 
    employee.LName, 
    customer.Customer_no, customer.LName, 
    customer.FName, 
    customer.City 
FROM commission_summary 
JOIN project on commission_summary.project_no = project.project_no 
JOIN employee on commission_summary.employee_id = employee.Employee_Id 
JOIN customer on customer.Customer_No = project.Customer_no 
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
AND project.status = 'construction' OR project.status = 'finished' 
ORDER BY commission_summary.startup_check_date DESC 

I'm confused by how this works, because it produces my desired results, of about 12 records in 2022.
SELECT 
commission_summary.project_no, 
commission_summary.EC, 
commission_summary.TC, 
CONVERT(CHAR(10), 
commission_summary.startup_check_date, 120) AS StartDate, 
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate, 
project.Status, 
project.Sales_no, 
project.swan_job, 
employee.Employee_ID, 
employee.FName, 
employee.LName, 
customer.Customer_no, 
customer.LName, 
customer.FName, 
customer.City 
FROM commission_summary 
JOIN project on commission_summary.project_no = project.project_no 
JOIN employee on commission_summary.employee_id = employee.Employee_Id 
JOIN customer on customer.Customer_No = project.Customer_no 
WHERE (CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)) 
AND project.status = 'construction' OR project.status = 'finished' 
AND commission_summary.employee_id = '91' 
ORDER BY commission_summary.startup_check_date DESC 

Any point in the right direction would be appreciated, or even a simple explanation.
If it ain't broke don't fix it
Discover my world at jkirkerx.com

AnswerRe: TSQL - JOIN cancels out my date filter Pin
Graham Breach27-Nov-22 21:38
Graham Breach27-Nov-22 21:38 
GeneralRe: TSQL - JOIN cancels out my date filter Pin
jkirkerx28-Nov-22 6:32
professionaljkirkerx28-Nov-22 6:32 
GeneralRe: TSQL - JOIN cancels out my date filter Pin
jschell28-Nov-22 10:44
jschell28-Nov-22 10:44 
QuestionTSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
jkirkerx21-Nov-22 9:15
professionaljkirkerx21-Nov-22 9:15 
AnswerMaybe this is a bad idea, so I abandoned it. Did a work around instead. Pin
jkirkerx21-Nov-22 9:35
professionaljkirkerx21-Nov-22 9:35 
AnswerRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
Graham Breach21-Nov-22 10:29
Graham Breach21-Nov-22 10:29 
GeneralRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [solved] - May run with this Pin
jkirkerx21-Nov-22 11:04
professionaljkirkerx21-Nov-22 11:04 
AnswerRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
Richard Deeming21-Nov-22 22:02
mveRichard Deeming21-Nov-22 22:02 
GeneralRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
jkirkerx22-Nov-22 6:58
professionaljkirkerx22-Nov-22 6:58 
QuestionOrder by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" Pin
jkirkerx14-Nov-22 10:44
professionaljkirkerx14-Nov-22 10:44 
AnswerRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jkirkerx14-Nov-22 11:29
professionaljkirkerx14-Nov-22 11:29 
GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jsc4222-Nov-22 10:31
professionaljsc4222-Nov-22 10:31 
GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jkirkerx22-Nov-22 10:50
professionaljkirkerx22-Nov-22 10:50 
GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jsc4222-Nov-22 23:26
professionaljsc4222-Nov-22 23:26 
GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jkirkerx23-Nov-22 6:11
professionaljkirkerx23-Nov-22 6:11 
QuestionCan any body share the schema database for security and share. Pin
Nguyễn Tuấn Anh 9216-Nov-22 5:40
Nguyễn Tuấn Anh 9216-Nov-22 5:40 
AnswerRe: Can any body share the schema database for security and share. Pin
Sam Hobbs6-Nov-22 10:05
Sam Hobbs6-Nov-22 10:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.