Solved

SQL Syntax question

Posted on 2016-07-22
5
58 Views
Last Modified: 2016-07-22
I have a sql question that Im hoping I can get answered here.
I created a database that holds timesheet info for users. There are 3 different "Items" that a user can submit(Work item, Travel Item and Receipt items). I have created 2 tables for each of these scenarios (ie, WorkItem and WorkItemDetails). The WorkItem Table would hold an entry and create a WorkItemID and that id would be the foreign key to my Detail table where multiple entries can be made for that ID. That ID is also saved into my master table as a foreign key.
So the Master table would be a 1-1 with the Work Item table and the WorkItem table would be a 1-many with the Detail table.
This is all done for a given week.
What I am trying to do is create a summary(Count) of each of the WorkItems, Travel Items and Receipt Items for a given week so an admin can click on one of the weekly groups to see the details and eventually he has to Approve.
So really what Id like to do is a Select statement where I can get a Count of WorkItems, TravelItems and ReceiptItems. I have the WorkWeek date info in the Master table so I want to show, by week, how many items are in each of these tables....

I initally wrote this but want to combine all 3 items for my front end to show the admin how many items, for a given week, the user has entered. Id like to get back all the info in 1 proc though...

Any help would be greatly appreciated

Thanks
JK

If @TypeID = 1
	Select 
	COUNT(wid.WorkItemID) as 'Count',
	wi.WorkItemID as 'ItemID', 
	CONVERT(varchar(50), StartDay, 101) + ' - ' + CONVERT(varchar(50), EndDay, 101) as 'WeekRange',WeekNo as 'WeekNumber',
	ApprovedBy, ApprovedDate
	from WorkItem wi
	Inner join [WorkWeek] ww on wi.WorkWeekID = ww.WorkWeekID
	INNER JOIN WorkItemDetails wid ON wi.WorkItemID=wid.WorkItemID
	Where UserID = 1
	Group By wi.WorkItemID, WeekNo, CONVERT(varchar(50), StartDay, 101) + ' - ' + CONVERT(varchar(50), EndDay, 101),
	ApprovedBy, ApprovedDate
	Order By Weekno, ItemID
else if @TypeID = 2
	Select 
	COUNT(tid.TravelItemID) as 'Count',
	ti.TravelItemID as 'ItemID',  
	CONVERT(varchar(50), StartDay, 101) + ' - ' + CONVERT(varchar(50), EndDay, 101) as 'WeekRange',WeekNo as 'WeekNumber',
	ApprovedBy, ApprovedDate
	from TravelItem ti
	Inner join [WorkWeek] ww on ti.WorkWeekID = ww.WorkWeekID
	Inner JOIN TravelItemDetails tid ON ti.TravelItemID=tid.TravelItemID	
	Where UserID = 1
	Group By ti.TravelItemID, WeekNo, CONVERT(varchar(50), StartDay, 101) + ' - ' + CONVERT(varchar(50), EndDay, 101),
	ApprovedBy, ApprovedDate
	Order By Weekno, ItemID
else if @TypeID = 3
	Select 
	COUNT(iid.ImageItemID) as 'Count',
	ii.ImageItemID as 'ItemID',  
	CONVERT(varchar(50), StartDay, 101) + ' - ' + CONVERT(varchar(50), EndDay, 101) as 'WeekRange', WeekNo as 'WeekNumber',
	ApprovedBy, ApprovedDate
	from ImageItem ii
	Inner join [WorkWeek] ww on ii.WorkWeekID = ww.WorkWeekID
	Inner JOIN ImageItemDetails iid ON ii.ImageItemID=iid.ImageItemID	
	Where UserID = 1
	Group By ii.ImageItemID, WeekNo, CONVERT(varchar(50), StartDay, 101) + ' - ' + CONVERT(varchar(50), EndDay, 101),
	ApprovedBy, ApprovedDate
	Order By Weekno, ItemID

Open in new window

0
Comment
Question by:jknj72
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 

Author Comment

by:jknj72
ID: 41725147
I guess I should also have told you that my master table holds data by week that has the primary key for all 3 scenarios. So the table would hold a WorkItemID, TravelItemID and ReceiptID for a given submission , by Week...
So example
WorkItem
ID = 12
TravelItem
ID = 21
ImageItem
ID = 15

Master table
ID = 1
WI = 12
TI = 21
II = 15

Hope this helps!!
0
 

Author Comment

by:jknj72
ID: 41725247
This is what I am trying to do but its not working the way I need it to....If I run them individually I get the correct results. If I try to run them in one query I am getting wrong results

--This doesnt work
--It returns 4, 4, 4 for each of the counts respectively
SELECT 
COUNT(wid.WorkItemDetailID) as 'WorkCount', 
COUNT(tid.TravelItemDetailID) as 'TravelCount'
COUNT(iid.ImageItemDetailID) as 'ImageCount' 
From 
WorkWeekTracker wwt 
LEFT JOIN WorkItem wi ON wwt.WorkItemID = wi.WorkItemID
INNER JOIN WorkItemDetails wid ON wi.WorkItemID=wid.WorkItemID
LEFT JOIN TravelItem ti ON wwt.TravelItemID = ti.TravelItemID
INNER JOIN TravelItemDetails tid ON ti.TravelItemID = tid.TravelItemID
LEFT JOIN ImageItem ii ON wwt.ImageItemID = ii.ImageItemID
INNER JOIN ImageItemDetails iid ON ii.ImageItemID = iid.ImageItemID
INNER JOIN WorkWeek ww ON wwt.weekid = ww.WorkWeekID

Open in new window


But these run individually do...

--Returns 13
SELECT 
COUNT(wid.WorkItemDetailID) as 'WorkCount'
From 
WorkWeekTracker wwt 
LEFT JOIN WorkItem wi ON wwt.WorkItemID = wi.WorkItemID
INNER JOIN WorkItemDetails wid ON wi.WorkItemID=wid.WorkItemID
INNER JOIN WorkWeek ww ON wwt.weekid = ww.WorkWeekID

--Returns 5
SELECT 
COUNT(tid.TravelItemDetailID) as 'TravelCount'
From 
WorkWeekTracker wwt 
LEFT JOIN TravelItem ti ON wwt.TravelItemID = ti.TravelItemID
INNER JOIN TravelItemDetails tid ON ti.TravelItemID = tid.TravelItemID
INNER JOIN WorkWeek ww ON wwt.weekid = ww.WorkWeekID

--Returns 2
SELECT 
COUNT(iid.ImageItemDetailID) as 'ImageCount' 
From 
WorkWeekTracker wwt 
LEFT JOIN ImageItem ii ON wwt.ImageItemID = ii.ImageItemID
INNER JOIN ImageItemDetails iid ON ii.ImageItemID = iid.ImageItemID
INNER JOIN WorkWeek ww ON wwt.weekid = ww.WorkWeekID

Open in new window

I tried to find a reason with the results why this isnt working but cannot see any  pattern??

So I am trying to get the results of the 3 last queries to run in 1 query. Something like the first query I just posted...just not the same!!!

PLEASE HELP!!!
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 41725259
Try this:
SELECT 
     ISNULL((SELECT COUNT(wid.WorkItemDetailID)
         FROM WorkItem wi
             INNER JOIN WorkItemDetails wid ON wi.WorkItemID=wid.WorkItemID
         WHERE wi.WorkItemID = wwt.WorkItemID), 0) as 'WorkCount',
    ISNULL(SELECT COUNT(tid.TravelItemDetailID)
         FROM TravelItem ti 
             INNER JOIN TravelItemDetails tid ON ti.TravelItemID = tid.TravelItemID
        WHERE wwt.TravelItemID = ti.TravelItemID), 0) as 'TravelCount',
     ISNULL(SELECT COUNT(iid.ImageItemDetailID)
         FROM ImageItem ii 
             INNER JOIN ImageItemDetails iid ON ii.ImageItemID = iid.ImageItemID
          WHERE wwt.ImageItemID = ii.ImageItemID), 0) as 'ImageCount' 
FROM WorkWeek WW
INNER JOIN WorkWeekTracker WWT ON WWT.WorkWeekID = WW.WeekID

Open in new window

0
 

Author Comment

by:jknj72
ID: 41725271
that was easy..... Worked like a charm...

Thanks again
JK
0
 

Author Closing Comment

by:jknj72
ID: 41725272
perfect
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question