Show All Available Appointments
Dec 17, 2007
Hi,
I have an appointment table as follows:
AppID AppTeam AppStart AppFinish
1 Team1 01/11/2007 10:00 01/11/2007 11:00
2 Team1 01/11/2007 11:01 01/11/2007 12:00
3 Team1 01/11/2007 12:01 01/11/2007 13:00
4 Team1 01/11/2007 15:00 01/11/2007 15:30
5 Team1 01/11/2007 17:00 01/11/2007 17:30
If someone needs a half hour slot for Team1,I need to show all the current appointments for Team 1 that a new half hour appointment can come after but not overlap any other appointment.
So for the example given I would show a list of:
3
4
5
As a half hour appointment could be put in after each of these and not overlap the next appointment.
Hope this makes sense and someone can help
Thanks
View 4 Replies
ADVERTISEMENT
Nov 15, 2006
i am developing a clinci software and i guess i reached to the most difficult part of it which is the appointment module.. I have made it as explaied below but not sure if i am doing it the right way or if i need to redesign my tables' structure.. please advise..
i have create doctors' duty table:
USE [shefa]
GO
/****** Object: Table [dbo].[staff_duty] Script Date: 11/16/2006 02:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[staff_duty](
[duty_id] [int] IDENTITY(1,1) NOT NULL,
[staff_file_no] [int] NULL,
[staff_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[duty_from] [datetime] NULL,
[duty_to] [datetime] NULL,
[record_locked] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_staff_duty_record_locked] DEFAULT ('N'),
[created_date] [datetime] NULL CONSTRAINT [DF_staff_duty_created_date] DEFAULT (getdate()),
[created_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_pc] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_version] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_domain] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_os] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_workingset] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_staff_duty] PRIMARY KEY CLUSTERED
(
[duty_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
and created the following appointments table:
USE [shefa]
GO
/****** Object: Table [dbo].[appointments] Script Date: 11/16/2006 02:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[appointments](
[appointment_id] [int] IDENTITY(1,1) NOT NULL,
[appointment_guid] [uniqueidentifier] ROWGUIDCOL NULL CONSTRAINT [DF_appointments_appointment_guid] DEFAULT (newid()),
[appointment_file_no] [int] NULL,
[appointment_telephone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[appointment_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[appointment_dr_id] [int] NULL,
[appointment_dr_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[appointment_start] [datetime] NULL,
[appointment_end] [datetime] NULL,
[created_by_date] [datetime] NULL CONSTRAINT [DF_appointments_created_by_date] DEFAULT (getdate()),
[created_by_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updated_by_date] [datetime] NULL,
[updated_by_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_appointments] PRIMARY KEY CLUSTERED
(
[appointment_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
now i will need in C# to list the doctor's free time (means should be between his from and to in the staff_duty table and not listed in the appointments table for the dame day).. keeping in mind that each appointment should NOT take more than 15minutes.
View 3 Replies
View Related
Jan 14, 2005
Hello,
This is my first project at the company I recently joined. It is the most challenging project I ever had.
It is a kind of web-scheduler.
it let you save appointments which could be one time or periodic.
For example, it could be like every monday, every other monday, first tuesday every monday, and so on.
once they are saved, it should be able to display only those appointments the user have on a specific day.
I'd like to know how database has to be designed to meet such a challenging requirement?
I'd like to hear anything from you if you have had similar project before.
Thank you,
Charlie
View 2 Replies
View Related
Dec 31, 2007
Hi all,
I have two problems.
1. I downloaded ChartFXRSTrial and created one chart, and able to deploy it in ReportServer, but the problem is the reports are not showing there, i checked the configuration of .dll files in the help provided by chartFX, but couldn't get anything, so could help me on this.
2. How to give Tooltip for the X- axis or Y- axis values in the ReportServer , i tried using chartproperties of .rdl file, but didnt understand it. can help me on this, and one more i tried with Dundas too, If im giving tooltip as #valy then, it is showing samething in reportserver instead the values of 'Y-axis'.
Thanks,
Mahesh Manthena
View 1 Replies
View Related
Dec 11, 2006
I'm beginner in asp.net. Can someone show me how to create coding for this page..
i have to create my own login page and database. I'm using sql sever 2005 for database. can someone show me how to make connection with my login button and my database? please....i'm really need help from you all guys.
View 2 Replies
View Related
Mar 6, 2008
I need to flag old records and new records with 1 and 0 respectively... can anyone show me how to do it without having to create a temp table? this is what i've got so far...
create table #tempLRM
([Key] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS null,
[Start Date] datetime null);
insert into #tempLRM
select [key], min([start date])
from ReportDataLRM
group by [key];
update ReportDataLRM
set multi = 0
from #tempLRM t
where ReportDataLRM.[key]= t.[key]
and ReportDataLRM.[start date] = t.[start date];
update ReportDataLRM
set multi = 1
where multi is null;
select * from ReportDataLRM
View 5 Replies
View Related
Jan 15, 2007
Hi!!
I have a bar chart and the data are agrupped by Folders (Category groups) and by FileType (Series Groups)... I want to show only the 5 folders with the biggest amount of files... how i could do it?
All suggestions will be wellcome... Thx!!
View 10 Replies
View Related
May 18, 2007
In Reports Service SQL 2005
my Fields Value is A, but I list table to show Value is B.
How do it.
Thanks
View 1 Replies
View Related
Jan 10, 2007
is have this code, and i know that i have a record with the ID=1 but it will not show the data from the record..<asp:Content ID="Main" ContentPlaceHolderID="ContentPlaceHolderMain" Runat="Server">
<asp:FormView ID="form1" runat="server" DataSourceID="SqlDataSource1"></asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnStrAccess %>"
SelectCommand="SELECT [MainID], [MainText] FROM [SiteText] WHERE ([MainID] = ?)" ProviderName="<%$ ConnectionStrings:ConnStrAccess.ProviderName %>">
<SelectParameters>
<asp:SessionParameter DefaultValue="1" Name="SiteMainID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</asp:Content> Why can't it show the record !??
View 2 Replies
View Related
Aug 26, 2007
New to asp and have no idea what I'm doing wrong here. I just want to open a pdf that I have stored in a table and show it on the page. Here is my code. I keep getting the System.NullReferenceException was unhandled by user code.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:smithsdaConnectionStringtest %>"
ProviderName="<%$ ConnectionStrings:smithsdaConnectionStringtest.ProviderName %>"
SelectCommand="SELECT * FROM [correspondence] WHERE ([facilitiesID] = ?)">
<SelectParameters>
<asp:QueryStringParameter Name="facilitiesID" QueryStringField="DACorr" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ScanDoc As New DataViewDim arg As New DataSourceSelectArguments
ScanDoc = SqlDataSource1.Select(arg)
Dim ScanLet(1) As ByteScanLet(0) = CByte(ScanDoc(0)("Letter"))
If ScanDoc IsNot "" Then
Response.Buffer = True
Response.Clear()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.BinaryWrite(ScanLet)
Response.End()
End IfEnd Sub
</script
Any Help greatly appreciated.
View 4 Replies
View Related
Dec 9, 2007
Hello!
I'm trying to show one large image and up to five small images. I can not get this to work. The five small images is in a repeater, the big picture is not in the repeater. I Think I just paste the code so you can look at it, i think that is better than me trying to explain moore:) I thougth I could use one of the events of the repeater and bind the large image when the page loads but I dont know what to use SqlDataSourceStatusEventArgs or what? I hope you all aunderstand whatI mean.<table style="background-color: #E4F9DF; margin: 10px 0px 0px 0px; border: 1px solid green;width: 545px; border-collapse: collapse;"><tr><td class="BoldText" colspan="2" style="border-bottom: 1px solid green; background-color: #b0eda2; text-align: center;">Bilder</td></tr><tr><td style="text-align:center"><asp:Image ID="Image1" Width="530px" runat="server" /></td></tr><tr><td> <asp:Repeater ID="Repeater1" OnItemCommand="imgBtnChangePic_Click" DataSourceID="sqlGetAdPics" runat="server"><ItemTemplate> The images will not appear, the shown like the path is wrong.<asp:ImageButton ID="ImageButton1" CommandName="changePic" ImageUrl='graphics/Advertise/<%#Eval("advertisePic")%>' CommandArgument='<%#Eval("advertisePic") %>' runat="server"/></ItemTemplate></asp:Repeater></td> </tr></table>
Here is the code behind
protected void Page_Load(object sender, EventArgs e){// this works, but I have to get the mage from the database...Image Image1 = (Image)DetailsView1.FindControl("Image1");Image1.ImageUrl = "graphics/Advertise/sadel2.jpg"; }protected void sqlGetAdDetails_Selected(object sender, SqlDataSourceStatusEventArgs e){if (e.Exception != null)lblError.Text = e.Exception.Message.ToString(); }protected void imgBtnChangePic_Click(object sender, RepeaterCommandEventArgs e){// This method is working.if (e.CommandName == "changePic" && Page.IsPostBack){Image Image1 = (Image)DetailsView1.FindControl("Image1");Image1.ImageUrl = "graphics/Advertise/" + e.CommandArgument.ToString();}}
View 6 Replies
View Related
Apr 4, 2001
When I set up a DailyBackup using the Backup Wizard it doesn't show in the SQL Agent jobs or in the Database maintainance plan. Is this correct??
Many Thanks
Darren
View 1 Replies
View Related
Aug 5, 2007
husband built web site, hosted by 1and1, husband can't help right now and 1and 1 won't. I can't get the connection string correct and need some advise.
This is the info 1and1 gave me for my db.
Database Name db212583089
User Name xxxxxxxxxxxxxxxx
Password xxxxxxxxxxx
Host Name mssql02.1and1.com
Description jamm
Status
My bd is uploaded to their server and should connect with the global.asa page, this is what I have as a connection right now and it's not working
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==FrontPage Generated - startspan==
Dim FrontPage_UrlVars(1)
'--Project Data Connection
Application("Database1_ConnectionString" = "PROVIDER=SQLOLEDB;DATASOURCE=msssql02.1and1.com;DATABASE=db212583089;UID=dbo212583089;PWD=xDBXzNTt"
Application("Database1_ConnectionTimeout") = 15
Application("Database1_CommandTimeout") = 30
this is the error i get on my website when i try to access my db.
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/JAMMDatabase.asp, line 131
This is the code on line 131, some before and some after:
strProvider=Application("Database1_ConnectionString")
set objConn = server.createobject("ADODB.Connection")
objConn.Open strProvider(line 131)
set cm = Server.CreateObject("ADODB.Command")
cm.ActiveConnection = objConn
right now I am totally lost with all this, if someone can help would really appreciate it, thanks, debi
if you need more info just let me know
View 2 Replies
View Related
Mar 4, 2006
Dear All,
What is the sql command to show me the tables
View 1 Replies
View Related
Sep 13, 2006
Hello,
When I create new Database User the login name input does not show on the list and in the "login name " when checking the Properties of the name. Why this behavior?
the previous user created a while ago are showing name and login name .
permissions and rights are the same for between different users having the two different behaviors.
When I created the new database user I input e.g.:
Login Name: AD<<User Name>>
Name : "First Name"
Thanks,
Dom
View 3 Replies
View Related
May 26, 2013
I want to select all those record except those record who have birthdate less then 4/13/1992 and relation= son
I tried this
SELECT * FROM Dependents WHERE code = 2
AND NOT CONTAINS
(SELECT *
FROM Dependents
WHERE BirthDate < '4/13/1992' AND relation= 'son')
But not working....
View 4 Replies
View Related
May 18, 2004
HI
I have created a stored procedure in SQL Server which outputs all records in a table.
How can I execute that procedure in access please? ( show that table in access?)
View 1 Replies
View Related
Oct 24, 2013
I have a list of OrderIDs and I'd like SQL to show me the ones that do not appear in the DB.
Lets say that my table looks like this:
idorderIdUserItem
122joepotatoes
223SteveApples
324SteveBananas
438DaveCarrots
And the query looks like this:
SELECT orderID FROM sales WHERE orderID IN ('22','51','38')
You'll notice that orderID 51 is not in the table. How do I make it tell me the orderIDs that do not appear?
View 1 Replies
View Related
Nov 7, 2006
Hello All,
I am using the folliwing sql statement:
execute whs_he_rpt_ds_summary '11/01/05', '11/30/05', '594'
It displays the headers but no information. The date range is obviously a date range and the 594 is a BranchNbr.
How do I see if there is truly any data for this date range.
TIA
Kurt
View 5 Replies
View Related
Jan 6, 2007
Hello. I work on a proprietary software package at work that has a SQL query engine. I'm able to query tables that I know exist but I would like to know what other tables are there in the db. Unfortunately I don't know the name of the database. Is there a SQL statement that will show me the name of the database that I'm running my query on?
Thanks for your help.
View 2 Replies
View Related
Feb 20, 2007
Hello Everyone,
Thanks for everyone's help on this mess so far especially Peso. Yes this was originally Oracle SQL and I now need to convert it to SQL.
Can someone start by showing me at least part of this the best way to convert this to regular SQL? Also are there any tips, guides out there that anyone knows of?
selectCSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB as TECHN,
substring(CSG_Hist.dbo.OJB_JOBS.COMPL_CDE_OJB, pstn.p, 3) as RESCODE,
Parser.dbo.OJB_JOBS.COMPL_DTE_OJB as COMPLET,
Parser.dbo.ELP_Codes.CodeDes as [DESCRIPTION],
CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB as RES_NAME,
CSG_Hist.dbo.HSE_BASE.ADDR1_HSE as [ADDRESS],
CSG_Hist.dbo.OJB_JOBS.JOB_TYP_OJB as JOB,
CSG_Hist.dbo.OJB_JOBS.JOB_CLASS_OJB as TYP,
CSG_Hist.dbo.OCR_ORDER_COMP.ORDER_NO_OCR as NUMB,
CSG_Hist.dbo.OCR_ORDER_COMP.LS_CHG_OP_ID_OCR as OPR,
Parser.dbo.ELP_Codes.CommissionAMT as NCommissionAMT,
COUNT(DISTINCT Parser.dbo.Parser_OCR.ORDER_NO_OCR) as QTYW
fromParser_OCR
Inner JoinParser.dbo.Parser_OJB on Parser.dbo.Parser_OCR.ORDER_NO_OCR = CSG_Hist.dbo.OJB_JOBS.ORDER_NO_OJB
Inner JOINParser.dbo.RGV_Codes on substring (Parser.dbo.Parser_OJB.COMPL_CDE_OJB, pstn.p, 3) = CODE -- IS THIS A VARIABLE/PARAMETER OR A COLUMN NAME?
Inner JOINCSG_Hist.dbo.SBB_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = CSG_Hist.dbo.SBB_BASE.HSE_KEY_SBB
Inner JOINCSG_Hist.dbo.HSE_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = Parser.dbo.Parser_OCR.HSE_KEY_OCR
Inner JOINCSG_NRT.dbo.NRT_OJB_JOBS ON CSG_NRT.dbo.NRT_OJB_JOBS.IR_TECH_NOJB = CSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB
cross Join(
select 1 as p union all
select 4 union all
select 7 union all
select 10 union all
select 13 union all
select 16
) as pstn
WHERECSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB between 950 and 999
and Parser.dbo.Parser_OJB.COMPL_DTE_OJB BETWEEN '2007-01-09' AND '2007-01-22'
and CSG_Hist.dbo.Parser_OCR.prin_ocr = 8600
GROUP BYCSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB,
substring(CSG_Hist.dbo.OJB_JOBS.COMPL_CDE_OJB, pstn.p, 3),
Parser.dbo.Parser_OJB.COMPL_DTE_OJB,
Parser.dbo.ELP_Codes.CodeDes,
CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB,
CSG_Hist.dbo.HSE_BASE.ADDR1_HSE,
Parser.dbo.Parser_OJB.JOB_TYP_OJB,
Parser.dbo.Parser_OJB.JOB_CLASS_OJB,
CSG_Hist.dbo.OCR_ORDER_COMP.ORDER_NO_OCR,
CSG_Hist.dbo.OCR_ORDER_COMP.LS_CHG_OP_ID_OCR,
Parser.dbo.ELP_Codes.CommissionAMT
Again thanks for everyone's help especially Peso!
Have a great day!
Kurt
View 20 Replies
View Related
Aug 2, 2007
in my MSSQL database my eventTime field is reading: 01/01/1900 12:45:00
how do i remove the date from the start?
View 6 Replies
View Related
Jul 23, 2005
Hey...I've been cracking head about this one all day, and I'm sure it'san easy answer, but here goes:I have a column entitled Sequ which is defined as a Decimal withPrecision 10 and Scale 5. In an ASP.NET page, I'm performing acalculation which is inserted into the Sequ column. I'm doing aresponse.write, which enables me to see that the numbers are in factbeing calculated correctly complete with a string of numbers after thedecimal, but when I pull the numbers out to use on the page or accessthe table, Sequ lists all numbers as integers. I'm using a storedprocedure in which I'm declaring the @calc parameter (the calculatedvalue which is being inserted) as a Decimal -- I don't know what I'mdoing wrong. Help appreciated...thanks.Erik
View 3 Replies
View Related
Jul 20, 2005
how to show all tables in current database ?
View 1 Replies
View Related
Jan 25, 2007
I am pretty much brand new to reporting services so I apologize for any simple questions I may ask.
I have created a call report that shows data broken down by day and grouped by week. Everything works great. However, if there is no data on a certain day is does not show me that day on the report. Is there a way I can make it display the day even if there is not data for that day and just give me 0 values? Or do I need to modify my query?
Thanks in advance.
View 1 Replies
View Related
Feb 4, 2007
Hi all
I have a data base for some equipment on a few windows application forms written in vb 2005 express. eg Form1 for tools , form2 for nuts &bolts ect.
I wondered if it is it possible to set data base to open when the form loads and shows the data of that particular piece of equipment instead of always opening on equipment id number 1 for instance . For example when form3 loads ,equipment id is set to 3.
Thanks
Rob
View 1 Replies
View Related
Jan 6, 2007
HiI have this code, but i can't get it to work, and if i delete <% %> then the if statsment is not working, how do i get this code to work.
1 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringMain %>"
2 SelectCommand="SELECT [SiteMainID], [SiteMainText] FROM [MainSiteText] WHERE ([SiteMainID] = @SiteMainID)">
3 <% Dim pageString As String = "Def"
4 If Not (Request.QueryString("Page") Is Nothing) Then
5 pageString = Request.QueryString("Page").ToString
6 End If
7 If pageString = "Def" Then
8 %>
9 <SelectParameters>
10 <asp:Parameter DefaultValue="1" Name="SiteMainID" Type="Int32" />
11 </SelectParameters>
12 <%
13 Else
14 If pageString = "Page1" Then
15 %>
16 <SelectParameters>
17 <asp:Parameter DefaultValue="2" Name="SiteMainID" Type="Int32" />
18 </SelectParameters>
19 <%
20 Else
21 If pageString = "Page2" Then
22 %>
23 <SelectParameters>
24 <asp:Parameter DefaultValue="3" Name="SiteMainID" Type="Int32" />
25 </SelectParameters>
26 <%
27
28 End If
29 End If
30 %>
31 </asp:SqlDataSource>
View 5 Replies
View Related
Apr 9, 2007
Hi,
My question is how can i get a page show all of my pictures i got in folder c:..Images? by using Database. Becouse I want some of the pictures to be shown in one page for theirselves.
Database looks like:
ID | Pname | Pact |
1 | picture1 | Yes |
2 | picture2 | No | etc.
Now i want picture 1 and 2 to be shown in my Allpictures.aspx
I tried by using <asp:repeater...>... But i got a problem when i wanted the <img src=<%#EVAL(Pname)/>... becouse i cant do it that way...
Thanks,
Even Knutli
View 2 Replies
View Related
May 29, 2007
Hi guys, I am using ASP.NET 2.0, In one web page i am loading a data from VIEW,This view takes more time to execute and this this data is i am showing in grid on page load event.and the web page only displayes after the view execute until it remains on old page User fills there is no response. I want to show the progress in status bar,Can any one tell me how can i do this. Thanks in advance
View 1 Replies
View Related
Dec 13, 2007
Salam to All
Plz give me Sql query which return list of Database By providing these information(Server name, Uerid ,Pasword)
Thanks
View 2 Replies
View Related
Dec 17, 2007
Hi,when i try to see the properties of database "sales" (sql server express 2005) in Management Studio Express, i get this error message: (besides, when i try to expand the database, i get the error that it's emty)Cannot show requested dialog.ADDITIONAL INFORMATION:Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)------------------------------An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)------------------------------The server principal "Myserveradmin" is not able to access the database "sales" under the current security context. (Microsoft SQL Server, Error: 916)But the webapplication still runs: i can insert records, update, delete ... from asp.net.Any way to recover it?ThanksTartuffe
View 4 Replies
View Related
Apr 1, 2008
i have one table completed tabletask_id task_due_datetime task_completed_on 1 03/21/2008 03/25/20082 03/12/2008 03/10/20083 03/10/2008 03/18/2008 i want the output:task_id on time late 1 0 1 2 0 0 3 0 1 how i get this output in sql query without the use of cursor...
View 5 Replies
View Related
Jan 29, 2004
Hello-
i have a fairly big SQL query that is used to display data into a datagrid. Each query grabs data from two seperate databases. Is there anyway to combine these queries into one so all the data appears in 1 datagrid and not 2.
here is the 1st query:
SQL = "SELECT sum(case when month(pb_report_shippers.shipper_date_time) = 1 then pb_report_shippers_lots.quantity else 0 end) as Jan ,sum(case when month(pb_report_shippers.shipper_date_time) = 2 then pb_report_shippers_lots.quantity else 0 end) as Feb ,sum(case when month(pb_report_shippers.shipper_date_time) = 3 then pb_report_shippers_lots.quantity else 0 end) as Mar ,sum(case when month(pb_report_shippers.shipper_date_time) = 4 then pb_report_shippers_lots.quantity else 0 end) as Apr ,sum(case when month(pb_report_shippers.shipper_date_time) = 5 then pb_report_shippers_lots.quantity else 0 end) as May ,sum(case when month(pb_report_shippers.shipper_date_time) = 6 then pb_report_shippers_lots.quantity else 0 end) as Jun ,sum(case when month(pb_report_shippers.shipper_date_time) = 7 then pb_report_shippers_lots.quantity else 0 end) as Jul ,sum(case when month(pb_report_shippers.shipper_date_time) = 8 then pb_report_shippers_lots.quantity else 0 end) as Aug ,sum(case when month(pb_report_shippers.shipper_date_time) = 9 then pb_report_shippers_lots.quantity else 0 end) as Sept ,sum(case when month(pb_report_shippers.shipper_date_time) = 10 then pb_report_shippers_lots.quantity else 0 end) as Oct ,sum(case when month(pb_report_shippers.shipper_date_time) = 11 then pb_report_shippers_lots.quantity else 0 end) as Nov ,sum(case when month(pb_report_shippers.shipper_date_time) = 12 then pb_report_shippers_lots.quantity else 0 end) as Dec FROM pb_customers INNER JOIN pb_jobs ON pb_customers.customer_id = pb_jobs.customer_id INNER JOIN pb_recipes_sub_recipes ON pb_jobs.recipe_id = pb_recipes_sub_recipes.recipe_id INNER JOIN pb_jobs_lots ON pb_jobs.job_id = pb_jobs_lots.job_id INNER JOIN pb_sub_recipes ON pb_recipes_sub_recipes.sub_recipe_id = pb_sub_recipes.sub_recipe_id INNER JOIN pb_report_shippers_lots ON pb_jobs_lots.intrack_lot_id = pb_report_shippers_lots.intrack_lot_id INNER JOIN pb_report_shippers ON pb_report_shippers_lots.job_id = pb_report_shippers.job_id AND pb_report_shippers_lots.shipper_id = pb_report_shippers.shipper_id WHERE pb_customers.customer_deleted <> 1 AND pb_jobs.job_deleted <> 1 AND pb_jobs_lots.lot_deleted <> 1 AND pb_report_shippers.shipper_date_time between cast('01/01/2003 00:01AM' as datetime) and cast('12/31/2003 23:59PM' as datetime)"
Here is the 2nd query:
SQL = "SELECT ISNULL(sum(case when month(nonconformance.nc_date) = 1 then nonconformance.nc_wafer_qty else 0 end),0) as Jan , ISNULL(sum(case when month(nonconformance.nc_date) = 2 then nonconformance.nc_wafer_qty else 0 end),0) as Feb ,ISNULL(sum(case when month(nonconformance.nc_date) = 3 then nonconformance.nc_wafer_qty else 0 end),0) as Mar ,ISNULL(sum(case when month(nonconformance.nc_date) = 4 then nonconformance.nc_wafer_qty else 0 end),0) as Apr , ISNULL(sum(case when month(nonconformance.nc_date) = 5 then nonconformance.nc_wafer_qty else 0 end),0) as May ,ISNULL(sum(case when month(nonconformance.nc_date) = 6 then nonconformance.nc_wafer_qty else 0 end),0) as Jun ,ISNULL(sum(case when month(nonconformance.nc_date) = 7 then nonconformance.nc_wafer_qty else 0 end),0) as Jul ,ISNULL(sum(case when month(nonconformance.nc_date) = 8 then nonconformance.nc_wafer_qty else 0 end),0) as Aug ,ISNULL(sum(case when month(nonconformance.nc_date) = 9 then nonconformance.nc_wafer_qty else 0 end),0) as Sept ,ISNULL(sum(case when month(nonconformance.nc_date) = 10 then nonconformance.nc_wafer_qty else 0 end),0) as Oct ,ISNULL(sum(case when month(nonconformance.nc_date) = 11 then nonconformance.nc_wafer_qty else 0 end),0) as Nov ,ISNULL(sum(case when month(nonconformance.nc_date) = 12 then nonconformance.nc_wafer_qty else 0 end),0) as Dec FROM nonconformance INNER JOIN nc_department on nonconformance.department_id = nc_department.department_id INNER JOIN nc_major_category ON nonconformance.major_category_id = nc_major_category.major_category_id AND nonconformance.status_id <> '5' WHERE nc_department.scrap_category = '1' AND nonconformance.nc_date between cast('01/01/2004 00:01AM' as datetime) and cast('12/31/2004 23:59PM' as datetime)"
I know there has to be someway to combine these into 1. The issue I have is they are in different databases.
ANY HELP would be appreciated.
View 2 Replies
View Related
