SQL Query Help - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

SQL Query Help

Author

Topic

skatedork
Starting Member

4 Posts

Posted - 2005-12-28 : 11:16:12

Okay my head is swimming so maybe you guys can help me out. I have a SQL server with 2 tables and I Need to pull the following information:

1)Total Hits
2)Hits This Month
3)Hits Last Month

If possible I would like to keep this all in one statement, so far I have the total hits query working, but I am getting stuck with the date functions and trying to get this all in 1 query. My current SQL statement is

SELECT TOP 100 PERCENT COUNT(*) AS TotalHits, dbo.PRODUCT.PRODUCT_NAME, dbo.MTracker.PID
FROM dbo.MTracker INNER JOIN
dbo.PRODUCT ON dbo.MTracker.PID = dbo.PRODUCT.PRODUCT_ID
GROUP BY dbo.MTracker.PID, dbo.PRODUCT.PRODUCT_NAME
ORDER BY TotalHits DESC

My output is as follows:

Total Hits || Name || ProductID
29 ||Ultra High-Speed Cameras ||360
21 ||Unmanned Technologies Image Gallery ||341
17 ||Motor/Drive Design ||484
13 ||UAV Videos ||361
11 ||Test and Evaluation ||487
11 ||Motor Control Components ||346

I have a field called "Date" which I want to use so I can select Total amounts of hits for last month. Any help would be great! :)

Kristen
Test

22859 Posts

Posted - 2005-12-28 : 11:24:39

Something like this perhaps?


SELECT TOP 100 PERCENT COUNT(*) AS TotalHits, dbo.PRODUCT.PRODUCT_NAME, dbo.MTracker.PID
FROM dbo.MTracker 
    INNER JOIN dbo.PRODUCT 
        ON dbo.MTracker.PID = dbo.PRODUCT.PRODUCT_ID
WHERE     MyDateColumn >= '20051101'
      AND MyDateColumn <  '20051201'
GROUP BY dbo.MTracker.PID, dbo.PRODUCT.PRODUCT_NAME
ORDER BY TotalHits DESC

You might want to use "alias names" for your tables to reduce the amount of typing! For example:


SELECT TOP 100 PERCENT COUNT(*) AS TotalHits, P.PRODUCT_NAME, T.PID
FROM dbo.MTracker AS T
    INNER JOIN dbo.PRODUCT AS P
        ON T.PID = P.PRODUCT_ID
WHERE     MyDateColumn >= '20051101'
      AND MyDateColumn <  '20051201'
GROUP BY T.PID, P.PRODUCT_NAME
ORDER BY TotalHits DESC

Kristen

skatedork
Starting Member

4 Posts

Posted - 2005-12-28 : 11:29:06

Kristen,
Thanks for the help, but how would I go about making the months dynamic instead of hard coded into the Query. I am also having problems getting Hits this month and Hits last month working alongside with Total Hits. My goal is to have all 3 in one statement, any ideas?

Thanks!

skatedork
Starting Member

4 Posts

Posted - 2005-12-28 : 12:31:30

I kind of answered my own question, however, I have 3 queries now and I want to try an consolidate these to 1.

The first query displays total Hits:

SELECT TOP 100 PERCENT COUNT(*) AS TotalHits, dbo.PRODUCT.PRODUCT_NAME, dbo.MTracker.PID
FROM dbo.MTracker INNER JOIN
dbo.PRODUCT ON dbo.MTracker.PID = dbo.PRODUCT.PRODUCT_ID
GROUP BY dbo.MTracker.PID, dbo.PRODUCT.PRODUCT_NAME
ORDER BY TotalHits DESC

The second displays last month's hits:

SELECT TOP 100 PERCENT COUNT(*) AS TotalHits, dbo.PRODUCT.PRODUCT_NAME, dbo.MTracker.PID
FROM dbo.MTracker INNER JOIN
dbo.PRODUCT ON dbo.MTracker.PID = dbo.PRODUCT.PRODUCT_ID
WHERE (dbo.MTracker.[Date] >= DATEADD(m, DATEDIFF(m, 0, GETDATE()) - 1, 0)) AND (dbo.MTracker.[Date] < DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
GROUP BY dbo.MTracker.PID, dbo.PRODUCT.PRODUCT_NAME
ORDER BY TotalHits DESC

The third displays all current hits for this month:

SELECT TOP 100 PERCENT COUNT(*) AS TotalHits, dbo.PRODUCT.PRODUCT_NAME, dbo.MTracker.PID
FROM dbo.MTracker INNER JOIN
dbo.PRODUCT ON dbo.MTracker.PID = dbo.PRODUCT.PRODUCT_ID
WHERE (dbo.MTracker.[Date] >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dbo.MTracker.[Date] < DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1,
GETDATE())), - 1))
GROUP BY dbo.MTracker.PID, dbo.PRODUCT.PRODUCT_NAME
ORDER BY TotalHits DESC

Anyone have any suggestions on how to consolidate these 3 queries into one?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 00:29:52

If you want to combine the resultset use Union All

Query1
Union All
Query2
Union All
Query3

Madhivanan

Failing to plan is Planning to fail

i_love_techno
Starting Member

4 Posts

Posted - 2005-12-30 : 12:18:34

quote:
Originally posted by skatedork

I kind of answered my own question, however, I have 3 queries now and I want to try an consolidate these to 1.

The first query displays total Hits:

SELECT TOP 100 PERCENT COUNT(*) AS TotalHits, dbo.PRODUCT.PRODUCT_NAME, dbo.MTracker.PID
FROM dbo.MTracker INNER JOIN
dbo.PRODUCT ON dbo.MTracker.PID = dbo.PRODUCT.PRODUCT_ID
GROUP BY dbo.MTracker.PID, dbo.PRODUCT.PRODUCT_NAME
ORDER BY TotalHits DESC

The second displays last month's hits:

SELECT TOP 100 PERCENT COUNT(*) AS TotalHits, dbo.PRODUCT.PRODUCT_NAME, dbo.MTracker.PID
FROM dbo.MTracker INNER JOIN
dbo.PRODUCT ON dbo.MTracker.PID = dbo.PRODUCT.PRODUCT_ID
WHERE (dbo.MTracker.[Date] >= DATEADD(m, DATEDIFF(m, 0, GETDATE()) - 1, 0)) AND (dbo.MTracker.[Date] < DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
GROUP BY dbo.MTracker.PID, dbo.PRODUCT.PRODUCT_NAME
ORDER BY TotalHits DESC

The third displays all current hits for this month:

SELECT TOP 100 PERCENT COUNT(*) AS TotalHits, dbo.PRODUCT.PRODUCT_NAME, dbo.MTracker.PID
FROM dbo.MTracker INNER JOIN
dbo.PRODUCT ON dbo.MTracker.PID = dbo.PRODUCT.PRODUCT_ID
WHERE (dbo.MTracker.[Date] >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dbo.MTracker.[Date] < DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1,
GETDATE())), - 1))
GROUP BY dbo.MTracker.PID, dbo.PRODUCT.PRODUCT_NAME
ORDER BY TotalHits DESC

Anyone have any suggestions on how to consolidate these 3 queries into one?

hmmm... well you could do something like this... (I haven't tested this, so I'm sorry if it's off or doesn't work :P)

SELECT Count(*) As total_hits,
Sum(Case When DatePart(month, m.date) = DatePart(month, GetDate()) then 1 else 0 end) As this_month_hits,
Sum(Case When DatePart(month, m.date) = DatePart(month, DateAdd(month, -1, GetDate())) then 1 else 0 end) As last_month_hits,
p.product_name,
m.pid
FROM dbo.mtracker m
Inner Join dbo.product p On m.pid = p.product_id
WHERE m.date Between DateAdd(m, DateDiff(m, 0, GetDate()), 0) And DateAdd(m, DateDiff(m, 0, DateAdd(m, 1, GetDate())),-1)
Group By m.pid, p.product_name
Order By total_hits Desc

that is, if that's what u are looking for.

Find happiness in listening to your discontent.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources