December 14, 2016 at 2:41 pm
Luis Cazares (12/14/2016)
Just a word of caution.Handle the quotes correctly on Scott's and Lynn's examples. You might end with strings with the value 'NULL' instead of NULLs for the columns. Or you could end with an arithmetic overflow from converting int to datetime.
If you're inserting to a varchar column, you won't get errors. But if you're using proper data types, the code will fail.
If I read the original post correctly, if the column (startDate or endDate) is null he wants the word 'NULL' in the string.
December 14, 2016 at 2:51 pm
Sonova...yep, I see where the hole is, in my logic =/
To be completely honest, I had rewritten it with ISNULL by the time I got back to reading the responses. I started with CASE logic simply cause it's easier for me to visualize, for some reason while I know HOW ISNULL works it never clicks in my head properly...I end up writing it 3 or 4 times, until I get the syntax right.
This time, I failed to realize how the logic cascaded - dumb oversight on my part.
Thank you all for your assistance!
If you want to think on it, double checking my thinking on another scenario would be appreciated:
Two tables, one to one relationship: INVOICE & PLACEMENT_INVOICE - For every record in INVOICE there is a CORRESPONDING record in PLACEMENT_INVOICE.
In INVOICE we have VOUCHER_FK - which is the FK to the Voucher which the INVOICE was printed on (don't get me started on normalization). So a single INVOICE can only appear on a single Voucher.
In PLACEMENT_INVOICE we have batchNo - which is the Batch Number, for when all the INVOICES were scanned in at a single time.
The scenario is: A single BATCHNO can appear on multiple INVOICES, but a single BATCH should not appear on more than one Voucher. We need to check to see if there are any occurences, where a BATCH appears on more than a single VOUCHER.
My thinking: join the two tables on Invoice_ID > get a count over a window function on the Voucher and batchNo > filter out the records where VoucherCount <= BatchCount > Get another count of Voucher's & Batches from the filtered result set via Window function > Filter this result set for where FilteredVoucherCount < FilteredBatchCount
I can write it out more elaborately if necessary, specifically the notes that led me to that conclusions and the scenarios I drew out to see if that would meet all the criteria to get this result set.
December 14, 2016 at 2:52 pm
Lynn Pettis (12/14/2016)
Luis Cazares (12/14/2016)
Just a word of caution.Handle the quotes correctly on Scott's and Lynn's examples. You might end with strings with the value 'NULL' instead of NULLs for the columns. Or you could end with an arithmetic overflow from converting int to datetime.
If you're inserting to a varchar column, you won't get errors. But if you're using proper data types, the code will fail.
If I read the original post correctly, if the column (startDate or endDate) is null he wants the word 'NULL' in the string.
That's why it's not an error, but something that the OP might not be considering.
December 14, 2016 at 2:54 pm
Just a word of caution.
Handle the quotes correctly on Scott's and Lynn's examples. You might end with strings with the value 'NULL' instead of NULLs for the columns. Or you could end with an arithmetic overflow from converting int to datetime.
If you're inserting to a varchar column, you won't get errors. But if you're using proper data types, the code will fail.
Appreciate the warning, I was aware - the whole underlying reason for this SQL, was just getting a quick list of the records and trying to prepare them for a script to be run in the future when I kick off all the other ETL's.
And yes, I could have done it in SSIS with no problems - it's easier to provide my dev's with a Script # though, for when they commit their changes and are ready for me to propagate the changes to our other environments.
December 14, 2016 at 3:01 pm
Doug.F (12/14/2016)
Sonova...yep, I see where the hole is, in my logic =/To be completely honest, I had rewritten it with ISNULL by the time I got back to reading the responses. I started with CASE logic simply cause it's easier for me to visualize, for some reason while I know HOW ISNULL works it never clicks in my head properly...I end up writing it 3 or 4 times, until I get the syntax right.
This time, I failed to realize how the logic cascaded - dumb oversight on my part.
Thank you all for your assistance!
If you want to think on it, double checking my thinking on another scenario would be appreciated:
Two tables, one to one relationship: INVOICE & PLACEMENT_INVOICE - For every record in INVOICE there is a CORRESPONDING record in PLACEMENT_INVOICE.
In INVOICE we have VOUCHER_FK - which is the FK to the Voucher which the INVOICE was printed on (don't get me started on normalization). So a single INVOICE can only appear on a single Voucher.
In PLACEMENT_INVOICE we have batchNo - which is the Batch Number, for when all the INVOICES were scanned in at a single time.
The scenario is: A single BATCHNO can appear on multiple INVOICES, but a single BATCH should not appear on more than one Voucher. We need to check to see if there are any occurences, where a BATCH appears on more than a single VOUCHER.
My thinking: join the two tables on Invoice_ID > get a count over a window function on the Voucher and batchNo > filter out the records where VoucherCount <= BatchCount > Get another count of Voucher's & Batches from the filtered result set via Window function > Filter this result set for where FilteredVoucherCount < FilteredBatchCount
I can write it out more elaborately if necessary, specifically the notes that led me to that conclusions and the scenarios I drew out to see if that would meet all the criteria to get this result set.
If I understand that correctly, you have a Voucher, for which multiple invoices may be included, and a Batch, which has at least 1 Voucher, but maybe more than one? I would think that COUNT(DISTINCT PI.batchNo) needs to be unequal to COUNT(DISTINCT I.VOUCHER_FK) in an INNER JOIN query between INVOICE AS I and PLACEMENT_INVOICE AS PI. Group By and HAVING, perhaps?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 14, 2016 at 3:11 pm
sgmunson (12/14/2016)
Doug.F (12/14/2016)
Sonova...yep, I see where the hole is, in my logic =/To be completely honest, I had rewritten it with ISNULL by the time I got back to reading the responses. I started with CASE logic simply cause it's easier for me to visualize, for some reason while I know HOW ISNULL works it never clicks in my head properly...I end up writing it 3 or 4 times, until I get the syntax right.
This time, I failed to realize how the logic cascaded - dumb oversight on my part.
Thank you all for your assistance!
If you want to think on it, double checking my thinking on another scenario would be appreciated:
Two tables, one to one relationship: INVOICE & PLACEMENT_INVOICE - For every record in INVOICE there is a CORRESPONDING record in PLACEMENT_INVOICE.
In INVOICE we have VOUCHER_FK - which is the FK to the Voucher which the INVOICE was printed on (don't get me started on normalization). So a single INVOICE can only appear on a single Voucher.
In PLACEMENT_INVOICE we have batchNo - which is the Batch Number, for when all the INVOICES were scanned in at a single time.
The scenario is: A single BATCHNO can appear on multiple INVOICES, but a single BATCH should not appear on more than one Voucher. We need to check to see if there are any occurences, where a BATCH appears on more than a single VOUCHER.
My thinking: join the two tables on Invoice_ID > get a count over a window function on the Voucher and batchNo > filter out the records where VoucherCount <= BatchCount > Get another count of Voucher's & Batches from the filtered result set via Window function > Filter this result set for where FilteredVoucherCount < FilteredBatchCount
I can write it out more elaborately if necessary, specifically the notes that led me to that conclusions and the scenarios I drew out to see if that would meet all the criteria to get this result set.
If I understand that correctly, you have a Voucher, for which multiple invoices may be included, and a Batch, which has at least 1 Voucher, but maybe more than one? I would think that COUNT(DISTINCT PI.batchNo) needs to be unequal to COUNT(DISTINCT I.VOUCHER_FK) in an INNER JOIN query between INVOICE AS I and PLACEMENT_INVOICE AS PI. Group By and HAVING, perhaps?
Invoice: Single payable record
BatchNo: The # assigned to a batch of INVOICES uploaded simultaneously.
Voucher: Voucher to pay Invoice(s), one or multiple invoices can be associated with a single Voucher.
Scenario: A single Batch, should not have Invoices assigned to multiple Vouchers. IE - While a single batch can/will include more than one invoice, any/all of those Invoices will either NOT be part of a voucher or be on a single Voucher.
Problem: How do we check to ensure, that there are no occurrences of a single batch being assigned to more than a single voucher?
EDIT* - Additional Caveat: Multiple batches can be assigned to a single voucher, we just cannot have more than one voucher assigned to a single batch.
December 14, 2016 at 9:14 pm
Doug.F (12/14/2016)
sgmunson (12/14/2016)
Doug.F (12/14/2016)
Sonova...yep, I see where the hole is, in my logic =/To be completely honest, I had rewritten it with ISNULL by the time I got back to reading the responses. I started with CASE logic simply cause it's easier for me to visualize, for some reason while I know HOW ISNULL works it never clicks in my head properly...I end up writing it 3 or 4 times, until I get the syntax right.
This time, I failed to realize how the logic cascaded - dumb oversight on my part.
Thank you all for your assistance!
If you want to think on it, double checking my thinking on another scenario would be appreciated:
Two tables, one to one relationship: INVOICE & PLACEMENT_INVOICE - For every record in INVOICE there is a CORRESPONDING record in PLACEMENT_INVOICE.
In INVOICE we have VOUCHER_FK - which is the FK to the Voucher which the INVOICE was printed on (don't get me started on normalization). So a single INVOICE can only appear on a single Voucher.
In PLACEMENT_INVOICE we have batchNo - which is the Batch Number, for when all the INVOICES were scanned in at a single time.
The scenario is: A single BATCHNO can appear on multiple INVOICES, but a single BATCH should not appear on more than one Voucher. We need to check to see if there are any occurences, where a BATCH appears on more than a single VOUCHER.
My thinking: join the two tables on Invoice_ID > get a count over a window function on the Voucher and batchNo > filter out the records where VoucherCount <= BatchCount > Get another count of Voucher's & Batches from the filtered result set via Window function > Filter this result set for where FilteredVoucherCount < FilteredBatchCount
I can write it out more elaborately if necessary, specifically the notes that led me to that conclusions and the scenarios I drew out to see if that would meet all the criteria to get this result set.
If I understand that correctly, you have a Voucher, for which multiple invoices may be included, and a Batch, which has at least 1 Voucher, but maybe more than one? I would think that COUNT(DISTINCT PI.batchNo) needs to be unequal to COUNT(DISTINCT I.VOUCHER_FK) in an INNER JOIN query between INVOICE AS I and PLACEMENT_INVOICE AS PI. Group By and HAVING, perhaps?
Invoice: Single payable record
BatchNo: The # assigned to a batch of INVOICES uploaded simultaneously.
Voucher: Voucher to pay Invoice(s), one or multiple invoices can be associated with a single Voucher.
Scenario: A single Batch, should not have Invoices assigned to multiple Vouchers. IE - While a single batch can/will include more than one invoice, any/all of those Invoices will either NOT be part of a voucher or be on a single Voucher.
Problem: How do we check to ensure, that there are no occurrences of a single batch being assigned to more than a single voucher?
EDIT* - Additional Caveat: Multiple batches can be assigned to a single voucher, we just cannot have more than one voucher assigned to a single batch.
SELECT batchId, COUNT(DISTINCT VOUCHER_FK) AS VOUCHER_COUNT
FROM INVOICE AS I
INNER JOIN INVOICE_PLACEMENT AS IP
ON I.JOIN_FIELD = IP.JOIN_FIELD
GROUP BY batchId
HAVING COUNT(DISTINCT VOUCHER_FK) > 1
ORDER BY batchId;
Or something of that general variety. If everything is good, zero records should be returned. You'll have to determine what field or fields to use to JOIN the two tables, but the rest should be good to go.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 15, 2016 at 7:18 am
sgmunson (12/14/2016)
Doug.F (12/14/2016)
sgmunson (12/14/2016)
Doug.F (12/14/2016)
Sonova...yep, I see where the hole is, in my logic =/To be completely honest, I had rewritten it with ISNULL by the time I got back to reading the responses. I started with CASE logic simply cause it's easier for me to visualize, for some reason while I know HOW ISNULL works it never clicks in my head properly...I end up writing it 3 or 4 times, until I get the syntax right.
This time, I failed to realize how the logic cascaded - dumb oversight on my part.
Thank you all for your assistance!
If you want to think on it, double checking my thinking on another scenario would be appreciated:
Two tables, one to one relationship: INVOICE & PLACEMENT_INVOICE - For every record in INVOICE there is a CORRESPONDING record in PLACEMENT_INVOICE.
In INVOICE we have VOUCHER_FK - which is the FK to the Voucher which the INVOICE was printed on (don't get me started on normalization). So a single INVOICE can only appear on a single Voucher.
In PLACEMENT_INVOICE we have batchNo - which is the Batch Number, for when all the INVOICES were scanned in at a single time.
The scenario is: A single BATCHNO can appear on multiple INVOICES, but a single BATCH should not appear on more than one Voucher. We need to check to see if there are any occurences, where a BATCH appears on more than a single VOUCHER.
My thinking: join the two tables on Invoice_ID > get a count over a window function on the Voucher and batchNo > filter out the records where VoucherCount <= BatchCount > Get another count of Voucher's & Batches from the filtered result set via Window function > Filter this result set for where FilteredVoucherCount < FilteredBatchCount
I can write it out more elaborately if necessary, specifically the notes that led me to that conclusions and the scenarios I drew out to see if that would meet all the criteria to get this result set.
If I understand that correctly, you have a Voucher, for which multiple invoices may be included, and a Batch, which has at least 1 Voucher, but maybe more than one? I would think that COUNT(DISTINCT PI.batchNo) needs to be unequal to COUNT(DISTINCT I.VOUCHER_FK) in an INNER JOIN query between INVOICE AS I and PLACEMENT_INVOICE AS PI. Group By and HAVING, perhaps?
Invoice: Single payable record
BatchNo: The # assigned to a batch of INVOICES uploaded simultaneously.
Voucher: Voucher to pay Invoice(s), one or multiple invoices can be associated with a single Voucher.
Scenario: A single Batch, should not have Invoices assigned to multiple Vouchers. IE - While a single batch can/will include more than one invoice, any/all of those Invoices will either NOT be part of a voucher or be on a single Voucher.
Problem: How do we check to ensure, that there are no occurrences of a single batch being assigned to more than a single voucher?
EDIT* - Additional Caveat: Multiple batches can be assigned to a single voucher, we just cannot have more than one voucher assigned to a single batch.
SELECT batchId, COUNT(DISTINCT VOUCHER_FK) AS VOUCHER_COUNT
FROM INVOICE AS I
INNER JOIN INVOICE_PLACEMENT AS IP
ON I.JOIN_FIELD = IP.JOIN_FIELD
GROUP BY batchId
HAVING COUNT(DISTINCT VOUCHER_FK) > 1
ORDER BY batchId;
Or something of that general variety. If everything is good, zero records should be returned. You'll have to determine what field or fields to use to JOIN the two tables, but the rest should be good to go.
Okay, honest question here. I dislike DISTINCT (seen it abused too often), I prefer to do the grouping myself and to only use it in quick dirty manual queries - which I'm betting is the same reason you used it in your example.
I have to admit, I was using a nested select approach and like how eloquent yours looks in comparison.
In this instance, is there a performance increase to be had by using something like:
COUNT(DISTINCT VOUCHER_FK) AS VOUCHER_COUNT
Instead of what I'd typically use, which would be something similar to:
COUNT(*) OVER (PARTITION BY VOUCHER_FK) AS VOUCHER_COUNT
December 15, 2016 at 8:06 am
Lynn Pettis (12/14/2016)
Not necessarily, being older I mean. Where were you when the original Star Wars hit the theaters?
In the fourth row!!! LOL.
I know you have me by a few years Lynn. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 15, 2016 at 7:48 pm
Doug.F (12/15/2016)
sgmunson (12/14/2016)
Doug.F (12/14/2016)
sgmunson (12/14/2016)
Doug.F (12/14/2016)
Sonova...yep, I see where the hole is, in my logic =/To be completely honest, I had rewritten it with ISNULL by the time I got back to reading the responses. I started with CASE logic simply cause it's easier for me to visualize, for some reason while I know HOW ISNULL works it never clicks in my head properly...I end up writing it 3 or 4 times, until I get the syntax right.
This time, I failed to realize how the logic cascaded - dumb oversight on my part.
Thank you all for your assistance!
If you want to think on it, double checking my thinking on another scenario would be appreciated:
Two tables, one to one relationship: INVOICE & PLACEMENT_INVOICE - For every record in INVOICE there is a CORRESPONDING record in PLACEMENT_INVOICE.
In INVOICE we have VOUCHER_FK - which is the FK to the Voucher which the INVOICE was printed on (don't get me started on normalization). So a single INVOICE can only appear on a single Voucher.
In PLACEMENT_INVOICE we have batchNo - which is the Batch Number, for when all the INVOICES were scanned in at a single time.
The scenario is: A single BATCHNO can appear on multiple INVOICES, but a single BATCH should not appear on more than one Voucher. We need to check to see if there are any occurences, where a BATCH appears on more than a single VOUCHER.
My thinking: join the two tables on Invoice_ID > get a count over a window function on the Voucher and batchNo > filter out the records where VoucherCount <= BatchCount > Get another count of Voucher's & Batches from the filtered result set via Window function > Filter this result set for where FilteredVoucherCount < FilteredBatchCount
I can write it out more elaborately if necessary, specifically the notes that led me to that conclusions and the scenarios I drew out to see if that would meet all the criteria to get this result set.
If I understand that correctly, you have a Voucher, for which multiple invoices may be included, and a Batch, which has at least 1 Voucher, but maybe more than one? I would think that COUNT(DISTINCT PI.batchNo) needs to be unequal to COUNT(DISTINCT I.VOUCHER_FK) in an INNER JOIN query between INVOICE AS I and PLACEMENT_INVOICE AS PI. Group By and HAVING, perhaps?
Invoice: Single payable record
BatchNo: The # assigned to a batch of INVOICES uploaded simultaneously.
Voucher: Voucher to pay Invoice(s), one or multiple invoices can be associated with a single Voucher.
Scenario: A single Batch, should not have Invoices assigned to multiple Vouchers. IE - While a single batch can/will include more than one invoice, any/all of those Invoices will either NOT be part of a voucher or be on a single Voucher.
Problem: How do we check to ensure, that there are no occurrences of a single batch being assigned to more than a single voucher?
EDIT* - Additional Caveat: Multiple batches can be assigned to a single voucher, we just cannot have more than one voucher assigned to a single batch.
SELECT batchId, COUNT(DISTINCT VOUCHER_FK) AS VOUCHER_COUNT
FROM INVOICE AS I
INNER JOIN INVOICE_PLACEMENT AS IP
ON I.JOIN_FIELD = IP.JOIN_FIELD
GROUP BY batchId
HAVING COUNT(DISTINCT VOUCHER_FK) > 1
ORDER BY batchId;
Or something of that general variety. If everything is good, zero records should be returned. You'll have to determine what field or fields to use to JOIN the two tables, but the rest should be good to go.
Okay, honest question here. I dislike DISTINCT (seen it abused too often), I prefer to do the grouping myself and to only use it in quick dirty manual queries - which I'm betting is the same reason you used it in your example.
I have to admit, I was using a nested select approach and like how eloquent yours looks in comparison.
In this instance, is there a performance increase to be had by using something like:
COUNT(DISTINCT VOUCHER_FK) AS VOUCHER_COUNT
Instead of what I'd typically use, which would be something similar to:
COUNT(*) OVER (PARTITION BY VOUCHER_FK) AS VOUCHER_COUNT
Part of the reason for COUNT(DISTINCT VOUCHER_FK) AS VOUCHER_COUNT was to ensure that if for any reason your data had multiple rows of the same VOUCHER_FK value, it would not give you a false positive. As I had no access to your actual data, I wanted to be sure the solution would work out of the box, so to speak.
As to the performance of one count method over another, I don't know. Test it and find out...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply