Populating a query through a drop down menu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Populating a query through a drop down menu

Author

Topic

mchism
Starting Member

3 Posts

Posted - 2013-12-13 : 14:53:22

Hello,

I have written a command that populates an Excel pivot table with data from our SQL Server. I am wondering if there is a way to populate a coulple of the fields with the results of dropdown menus from the spreadsheet. Specifically the FROM and Date_only fields as there are 3 tables and multiple dates. Query is below. Thank you!

select a.*,
b.Total_Trx AS '100%',
c.Total_Trx AS '50%',
d.Total_Trx AS 'TP1',
e.Total_Trx AS 'CO_ExemptTrip',
f.Total_Trx AS 'CO_NotTrip',
g.Total_Trx AS 'CO_Other',
h.Total_Trx AS 'Trip'
from (SELECT CAST(CONVERT(varchar(10),Date_only,110) as datetime) as "Date_Only",
Hour_only, COUNT (*) Total_Trx
FROM TRIPRATDETPLUS
Where Date_only > ('2013-09-30')
Group by Date_only, Hour_only) a

full join (SELECT CAST(CONVERT(varchar(10),Date_only,110) as datetime) as "Date_Only",
Hour_only, COUNT (*) Total_Trx
FROM TRIPRATDETPLUS
Where Trip_Rec_Type = '100%' AND Date_only > ('2013-09-30')
Group by Date_only, Hour_only
) b on a.Date_only = b.Date_only AND a.Hour_only = b.Hour_only

full join (SELECT CAST(CONVERT(varchar(10),Date_only,110) as datetime) as "Date_Only",
Hour_only, COUNT (*) Total_Trx
FROM TRIPRATDETPLUS
Where Trip_Rec_Type = '50%' AND Date_only > ('2013-09-30')
Group by Date_only, Hour_only
) c on a.Date_only = c.Date_only AND a.Hour_only = c.Hour_only

full Join (SELECT CAST(CONVERT(varchar(10),Date_only,110) as datetime) as "Date_Only",
Hour_only, COUNT (*) Total_Trx
FROM TRIPRATDETPLUS
Where Trip_Rec_Type = 'TP1' AND Date_only > ('2013-09-30')
Group by Date_only, Hour_only
) d on a.Date_only = d.Date_only AND a.Hour_only = d.Hour_only

full Join (SELECT CAST(CONVERT(varchar(10),Date_only,110) as datetime) as "Date_Only",
Hour_only, COUNT (*) Total_Trx
FROM TRIPRATDETPLUS
Where Trip_Rec_Type = 'CO_ExemptTrip' AND Date_only > ('2013-09-30')
Group by Date_only, Hour_only
) e on a.Date_only = e.Date_only AND a.Hour_only = e.Hour_only

full Join (SELECT CAST(CONVERT(varchar(10),Date_only,110) as datetime) as "Date_Only",
Hour_only, COUNT (*) Total_Trx
FROM TRIPRATDETPLUS
Where Trip_Rec_Type = 'CO_NotTrip' AND Date_only > ('2013-09-30')
Group by Date_only, Hour_only
) f on a.Date_only = f.Date_only AND a.Hour_only = f.Hour_only

full Join (SELECT CAST(CONVERT(varchar(10),Date_only,110) as datetime) as "Date_Only",
Hour_only, COUNT (*) Total_Trx
FROM TRIPRATDETPLUS
Where Trip_Rec_Type = 'CO_Other' AND Date_only > ('2013-09-30')
Group by Date_only, Hour_only
) g on a.Date_only = g.Date_only AND a.Hour_only = g.Hour_only

full Join (SELECT CAST(CONVERT(varchar(10),Date_only,110) as datetime) as "Date_Only",
Hour_only, COUNT (*) Total_Trx
FROM TRIPRATDETPLUS
Where Trip_Rec_Type = 'Trip' AND Date_only > ('2013-09-30')
Group by Date_only, Hour_only
) h on a.Date_only = h.Date_only AND a.Hour_only = h.Hour_only
order by 1, 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-14 : 07:32:40

It would be much better if you can post which values you're trying to display and which fields they come from

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mchism
Starting Member

3 Posts

Posted - 2013-12-16 : 09:35:24

What I am trying to do is have the query function dynamically. It works fines now as fields are hardcoded, however I have 2 cells in the spreadsheet that I would like to have operate the query. Cell B2 will change the FROM field. B2 has a dropdown menu containing the names of the 3 different tables: TRIPRATDET, TRIPRATDET_M,and TRIPRATDET_Q. The Date_only field will pull from cell D2, which will be manually entered as there are many dates.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources