0

My company uses a SQL Server database.

Is it possible to use a range of cells as a condition in a SQL query if it equals ANY of those values? Can it even use date ranges on the same rows?

Reference Example:

Reference

Data Example:

Data

Output Desired:

Output

Question 1: Can I reference an entire column?

SELECT ID, sum(units) FROM sales WHERE ID = any ID in Column A

Question 2: Can I specify just a cell range?

SELECT ID, sum(units) FROM table WHERE ID = any value in A2:A10

Question 3: Can I add a date range cell reference with the possibility that the same ID may appear more than once but have a different date range (see 747375 in sample) and return results for both ranges separately?

SELECT ID, sum(units) FROM table WHERE ID = any value in A2:A10 AND DATE >= date found in column B that is next to ID in the same row AND DATE <= date found in column C that is next to ID in the same row
GigaFluxx
  • 31
  • 6
  • 1
    You should consider taking a tutorial on how to use SQL. Important concept #1: database tables and spreadsheets look similar on a screen; and there the similarities end. – Eric Brandt Jul 02 '20 at 19:29

2 Answers2

1

You can use between as following

select
     r.id,
     sum(units) as units
from reference r
join data d
on r.id = d.id
where d.date between r.start and r.end
group by
     r.id
zealous
  • 7,336
  • 4
  • 16
  • 36
1

Question 1: Can I reference an entire column?

Yes. A default select without a where clause will reference the entire column.

Your example SELECT ID, sum(units) FROM sales WHERE ID = any ID in Column A is not logically sound. From the select, I am presuming that you want the sum of units for each individual ID, not the sum of all the units without regard to the ID. For this, you want to use group by

select ID, sum(units) totalunits
from sales
group by ID

There is no need for a where clause because you want everything.

Question 2: Can I specify just a cell range?

Yes.

And no.

There is no direct concept of "cell range" in SQL (well, maybe top but not really). Data is stored unordered in SQL. In Excel, the cell range "A2:A10" means "whatever values just happen to be in those cells at this point in time". Often this will mean "the 2nd through 10th values entered in time", or "the first through 9th values entered in time" if there is a header row. But then later you can sort the data differently and now there is different data there. In SQL, there is no order in storage. You can specify an order for the output when you select data, but that is manually specified for each select.

However, the related concept is probably rather obvious. "A2:A10" is often going to mean "the first 9 values by date/time", or "the largest/smallest 9 values" etc.

Your example SELECT ID, sum(units) FROM table WHERE ID = any value in A2:A10 needs to change to define what values you expect to be in A2:A10. For example, if A2:A10 represents the first 9 values by date, you would do something like this: (untested)

select ID, sum(units) totalunits
from sales
where ID in (select top(9) ID
             from sales
             order by date
            )
group by ID

This would provide the sum of units for each of the IDs that were amongst the first 9 IDs entered by date (what to do with a tie for 9th I will not go into here).

Question 3: Can I add a date range cell reference with the possibility that the same ID may appear more than once but have a different date range (see 747375 in sample) and return results for both ranges separately?

This one is difficult to understand. And it might be meaningless based on the answer to your 2nd question. However, you can setup a query that chooses the IDs you want, and in that query you can also select the min and max dates. Finally, you can use the information from that query as a subquery to get the information by ID that has the sum of units within the min/max dates and one that is the sum of units outside the min/max dates. This would require some effort and I will not at this time try to figure that out for you.

avery_larry
  • 2,069
  • 1
  • 5
  • 17
  • I don't feel I asked my question correctly based on the responses I've been getting, but I'm looking ro reference literal cells on an excel spreadsheet and the data is in a database – GigaFluxx Jul 02 '20 at 23:18
  • 1
    Then use Excel formulas . . ? Which would have nothing to do with SQL or SQL Server. – avery_larry Jul 03 '20 at 17:49
  • I'll have to rewrite the question. I'm running a query that's pulling sales data but I can't pull all sales because it's millions of lines and rather than manually maintain the query by adding new IDs and dates, I saw it was possible to directly reference a cell (https://stackoverflow.com/questions/1285686/excel-use-a-cell-value-as-a-parameter-for-a-sql-query) but I can't find out about referencing a range and adding unique date range references. I have a better idea on how to ask. Thank you for the effort you made to help. I come here as I just started to learn because of support like yours – GigaFluxx Jul 04 '20 at 18:33