Page 2 - Monsterous SQL Query... Suggestions?

Jan 18, 2008

Here you go:

The telephone numbers are just strings of the same integer,

1's: Calls made every day
2's: Calls made every day
3's: Two, non consecutive days without calls
4's: No calls made from 2nd till 6th
5's: Only one day with calls
6's: No calls made from 2nd to 6th, but many made on the 1st
7's: No calls made from 2nd till 13th

If you'd like a bigger sample set then I will create one.


Code:


CREATE TABLE tblCallSummary(
[CallID] [int] IDENTITY(1,1) NOT NULL,
[TelephoneNumber] [varchar](11) NOT NULL,
[StartTime] [smalldatetime] NOT NULL,
[Duration] [int] NOT NULL,
CONSTRAINT [PK_tblCallSummary] PRIMARY KEY CLUSTERED
(
[CallID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-02'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-03'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-04'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-05'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-06'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-31'),1)

INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-02'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-03'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-04'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-05'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-06'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-31'),1)

INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-02'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-03'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-05'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-31'),1)

INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-31'),1)

INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('55555555555',CONVERT(smalldatetime,'2007-12-10'),1)

INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-31'),1)

INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-31'),1)



Our requirements have just changed and we only need customers which were active in the last five days, a very easy query. So please don't spend too much of your time on this now, however, I am still keen to find an elegant way of solving this problem, if one exists so any input from you would be appreciated.

Thanks for you help

Dave

View 5 Replies


ADVERTISEMENT

Monsterous SQL Query... Suggestions?

Jan 17, 2008

Hi All,

I work for a telco. We've got a table in a database which shows phone calls made by customers and when they made them.

I need to generate a list of customers who have made phonecalls last month and have NOT had a five days in a row without making any calls.

Can any of you help? I'm not sure how to tackle this one without getting a very bloated and inelligent solution. Basically, the only solution I can think of is generating 31 tables, one for each day and then just checking calls made on each day.

Has anyone got a better idea?

I'm using SQL Server 2000.

Thanks

Dave

View 14 Replies View Related

SQL Query Suggestions/help

Jul 20, 2005

I'm trying to count the number of records in 'game_dates' where thecolumns home_team_id or away_team_id have the same value. E.g., iwant to know the number of records for each team_id where team_id ishome_team_id or away_team_id.I'm doing this in two separate select statements now. Example:SELECT count(home_team_id),home_team_id FROM gamesWHERE league_id = 218 and ((home_score IS NOT NULL OR away_score ISNOT NULL) OR (home_score <> 0 OR away_score <> 0))GROUP BY home_team_idandSELECT count(away_team_id),away_team_id FROM gamesWHERE league_id = 218 and ((home_score IS NOT NULL OR away_score ISNOT NULL) OR (home_score <> 0 OR away_score <> 0))GROUP BY away_team_idand then combining the results. Is there anyway to combine these toqueries into one query? ...and have a single result set returned withtwo columns (count,team_id)?Thanks,Glenn

View 3 Replies View Related

Suggestions For Rewriting This Query

Oct 24, 2007

Does anybody have any suggestions to rewrite the 2nd WHEN part of the query??? Thank you.

------------------------------------------------------------
update t_pgba_hdr
set HCFA_PLACE_TRMT_CD2 =
case when (select max(b.HCFA_PLACE_TRMT_CD)
from t_pgba_hdr as b
where t_pgba_hdr.clm_id2 = b.clm_id2) like '[A-Z]%'
then '99'
when (select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD)))
from t_pgba_hdr as b
where t_pgba_hdr.clm_id2 = b.clm_id2) in '[0-9]'
then '0' + (select ltrim(rtrim(max(b.HCFA_PLACE_TRMT_CD)))
from t_pgba_hdr as b
where t_pgba_hdr.clm_id2 = b.clm_id2)
end

------------------------------------------------------------


-soumya

View 4 Replies View Related

Multiple Parameter SQL Query??? Any Suggestions

Sep 19, 2007

My end result will be a query with about six parameters total. But for right now I am trying to one by one for testing purposes. This is my first SQl project. All of my information goes into an Access DB. I am basically creating a search page with parameters for the employees to use to search for data. I need the query to work if all parameters, or combinations of parameters, or just one parameter is chosen. I started with one and got to three but now I'm stuck. My first two parameters are dates, so the data can be chosen by date range, and the second is location id. If I choose date range by itself it works, If I choose location id by itself it works, but If I choose both it doesn't work. Instead of filtering it gives me everything and I have a statement for it. Can someone please help me and tell me what am I not doing right. Here is my code;

SELECT *
FROM ASFdata
WHERE
(ASFdata.reportlocid = <cfqueryparam value="#Form.reportlocid#"> AND <cfqueryparam value="#Form.datefield#"> <> datefield AND <cfqueryparam value="#Form.datefield2#"> <> datefield) OR
(ASFdata.datefield BETWEEN <cfqueryparam value="#Form.datefield#"> AND <cfqueryparam value="#Form.datefield2#"> AND NOT <cfqueryparam value="#Form.reportlocid#"> = reportlocid ) OR
(ASFdata.datefield BETWEEN <cfqueryparam value="#Form.datefield#"> AND <cfqueryparam value="#Form.datefield2#"> AND ASFdata.reportlocid = <cfqueryparam value="#Form.reportlocid#">)

View 12 Replies View Related

Any Suggestions On How To Optimize A Query Written In Dynamic SQL?

Oct 15, 2007

I added the subquery and now this thing consistently takes more than five minutes to return 7100+ rows. Any suggestions? Thanks again, you guys are the best.

ddave
----------------------------
SET @StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
FROM dbo.ViewEligibilityPHC VEL
LEFT OUTER JOIN (SELECT *
FROM dbo.MEMB_LISHISTS l
WHERE l.LISThruDate is null
AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID
WHERE VEL.OPTHRUDT is null
AND VEL.OPT LIKE ''' + @HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @Prvdr + ''')
AND VEL.HCC LIKE ''' + @HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @CaseMngr + ''')
AND VEL.OPFROMDT <= CAST(''' + @SDate + ''' AS datetime)
AND ISNULL(VEL.OPTHRUDT, CAST(''' + @TDate + ''' AS datetime)) >= CAST(''' + @EDate + ''' AS datetime)) '

View 12 Replies View Related

Please Give Me Your Suggestions On Report Query - Very Urgent

Oct 25, 2006

I need to disaplay number of Active Agencies on monthwise in one of my report. I have tbl_Agency table with ActiveDate and ActiveFlag. ActiveDate column contains always first Activation Date. If any chances in the agencies(update/delete) the same record will move to tbl_AgencyHistory table.

"If an agency is inactivated in September 10th, inactivated all of October, and then reactivated November 10th - the agency would be counted in September, not in October and counted in November"

ActiveDate column has always first activation date, I could not meet this requirement. This is very urgent issue, Could you please help me on this.

Thanks,

Malar











View 6 Replies View Related

Can I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net

Mar 1, 2007

Can I roll back certain query(insert/update) execution in one page if  query (insert/update) in other page  execution fails in asp.net.( I am using sqlserver 2000 as back end)
 scenario
In a webpage1, I have insert query  into master table and Page2 I have insert query to store data in sub table.
 I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance

View 2 Replies View Related

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
 SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

 Here is the page source
 
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="&#9;SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches&#13;&#10;&#9;FROM dbo.MAKES INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID&#13;&#10;&#9;WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )&#13;&#10;&#9;GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID&#13;&#10;&#9;HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2&#13;&#10;&#9;ORDER BY count(*) DESC&#13;&#10;">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
 AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
 Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
 Please help!
 
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
 

View 4 Replies View Related

Reporting Services :: All Record Are Displaying On One Page - How To Display Page By Page

Nov 11, 2015

I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.

View 3 Replies View Related

How To Run An Asp Page From Sql Query

May 28, 2008

Hi,
      I want to run an asp file from a query written in sql 2005.
     How can I achieve this in sql?
      Please let me know its very urgent.
 

View 1 Replies View Related

SQL Query From Web Page?

Sep 9, 2004

Hi, I have an SQl database that i want to build a simple search query for that would be accessible from an HTML page, what would be the best way of going about this? and what would be the best language to use?

Thanks in advance

View 1 Replies View Related

Page 2 - HELP With SQL Query - (complicated)

Oct 10, 2006

You can display both by referring to their position in the recordset or by aliasing them in the query and referring to the alias in your code.
Note that in order to put Products.Quantity or ProductDetails.Quantity in just one column you'll have to add some logic in the query, using a coalesce, like:

sql Code:






Original
- sql Code




SELECT
Products.ProductID, Products.ProductName, Products.ProductPict,
COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity,
ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_Color
FROM
Products
LEFT OUTER JOIN
ProductDetails
ON Products.ProductID = ProductDetails.ProductID
WHERE
Products.Quantity <= "& +rsResults__var1 & "
OR
ProductDetails.Quantity <= "& +rsResults__var1 & "
Order by Products.ProductID asc;






SELECT Products.ProductID, Products.ProductName, Products.ProductPict, COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity,ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_ColorFROM Products LEFT OUTER JOIN ProductDetails ON Products.ProductID = ProductDetails.ProductID WHERE Products.Quantity <= "& +rsResults__var1 & " OR  ProductDetails.Quantity <= "& +rsResults__var1 & " ORDER BY Products.ProductID ASC;

Untested, beware of syntax errors!
In this case you'll have just one Quantity colum and you won't have to change anything in your asp code.

View 1 Replies View Related

Query For Page Views

Apr 28, 2008

I have a table which records the amount of times a user views a webpage

tblpageview

id
pagename
view_date
userid

I would like to have a query that returns the unique pagename and the amount of times that page has been viewed. I've had a go with count etc. but cant seem to get it. Anyone can help?

View 3 Replies View Related

Page 2 - Query Problem

Sep 6, 2007

Hmm... the query should return the number of athletes with scores >= to your own.

So if you have the highest score in the world, for example, then the only score >= to your own is... yours. The count of that is 1, giving you rank 1.

If the order seems backwards to you just reverse the <= operator in the WHERE clause. Either way, as a responsible developer, never never never put a SQL query you don't understand into production.

Fixed the ServerID thing, too. It was there because I don't have a table that matches yours and wrote the query against one with a compatible structure, but different names.

View 4 Replies View Related

I Need To Pass Data Entered /created On The First Page To The Next Page And Populate The Next Page With Some Data From The Fir

Nov 28, 2006

Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.

When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.


I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)


I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.


Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦

View 2 Replies View Related

Urgent Converting A Query To A .asp Page

Aug 24, 2004

I am extremely new with .asp. We have the .net framework 1.1 installed on our server and the following query works in Crystal Reports (older version). We would like to have this converted to a .asp page with two inputs. One for the date range and one for the state. Can someone please tell me how to go about this. I do have downloaded as test. Visual Studio 2003.net and Web Matrix. I really am looking for (if there is any) a way to pretty quickly convert query to web pages so users can use them without a huge expense. IF someone already has something like this done. That would be great. The following query is for Visual Enterprise (Manufacturing software). Thanks


SELECT
SHIPPER."CUST_ORDER_ID", SHIPPER."SHIPPED_DATE", SHIPPER."INVOICE_ID",
SHIPPER_LINE."USER_SHIPPED_QTY", SHIPPER_LINE."SHIPPED_QTY", SHIPPER_LINE."UNIT_PRICE",
RECEIVABLE_CURR."CURRENCY_ID", RECEIVABLE_CURR."SELL_RATE",
CUST_ORDER_LINE."PART_ID", CUST_ORDER_LINE."ORDER_QTY", CUST_ORDER_LINE."USER_ORDER_QTY",
INVENTORY_TRANS."TYPE", INVENTORY_TRANS."ACT_MATERIAL_COST", INVENTORY_TRANS."ACT_LABOR_COST", INVENTORY_TRANS."ACT_BURDEN_COST", INVENTORY_TRANS."ACT_SERVICE_COST",
CUSTOMER_ORDER."CUSTOMER_ID", CUSTOMER_ORDER."SHIP_TO_ADDR_NO", CUSTOMER_ORDER."CURRENCY_ID",
PART."DESCRIPTION", PART."STOCK_UM",
CUSTOMER."STATE",
CUST_ADDRESS."ADDR_NO", CUST_ADDRESS."NAME", CUST_ADDRESS."STATE"
FROM
{ oj ((((((("VMFG"."dbo"."SHIPPER" SHIPPER INNER JOIN "VMFG"."dbo"."RECEIVABLE_CURR" RECEIVABLE_CURR ON SHIPPER."INVOICE_ID" = RECEIVABLE_CURR."INVOICE_ID") INNER JOIN "VMFG"."dbo"."SHIPPER_LINE" SHIPPER_LINE ON SHIPPER."PACKLIST_ID" = SHIPPER_LINE."PACKLIST_ID" AND SHIPPER."CUST_ORDER_ID" = SHIPPER_LINE."CUST_ORDER_ID") INNER JOIN "VMFG"."dbo"."INVENTORY_TRANS" INVENTORY_TRANS ON SHIPPER_LINE."TRANSACTION_ID" = INVENTORY_TRANS."TRANSACTION_ID") INNER JOIN "VMFG"."dbo"."CUSTOMER_ORDER" CUSTOMER_ORDER ON SHIPPER_LINE."CUST_ORDER_ID" = CUSTOMER_ORDER."ID") INNER JOIN "VMFG"."dbo"."CUST_ORDER_LINE" CUST_ORDER_LINE ON SHIPPER_LINE."CUST_ORDER_ID" = CUST_ORDER_LINE."CUST_ORDER_ID" AND SHIPPER_LINE."CUST_ORDER_LINE_NO" = CUST_ORDER_LINE."LINE_NO") INNER JOIN "VMFG"."dbo"."CUSTOMER" CUSTOMER ON CUSTOMER_ORDER."CUSTOMER_ID" = CUSTOMER."ID") INNER JOIN "VMFG"."dbo"."PART" PART ON CUST_ORDER_LINE."PART_ID" = PART."ID") LEFT OUTER JOIN "VMFG"."dbo"."CUST_ADDRESS" CUST_ADDRESS ON CUSTOMER_ORDER."CUSTOMER_ID" = CUST_ADDRESS."CUSTOMER_ID" AND CUSTOMER_ORDER."SHIP_TO_ADDR_NO" = CUST_ADDRESS."ADDR_NO"}
WHERE
SHIPPER."SHIPPED_DATE" >= {ts '2002-08-01 00:00:00.00'} AND
SHIPPER."SHIPPED_DATE" < {ts '2004-08-25 00:00:00.00'}
ORDER BY
SHIPPER."SHIPPED_DATE" ASC

View 3 Replies View Related

Page 2 - Some Assistance With Query Needed -_-

Oct 26, 2004

Code:

select *
from linkboxes l
left outer
join subjects s
on l.sub_link_id
= s.sub_id
left outer
join external_links e
on l.ext_link_id
= e.ext_id
where l.sub_id = subid

View 4 Replies View Related

Front Page 2002 And SQL Query

Jul 23, 2005

I am using a very basic query in Front Page 2002 against a SQL table inSQL 2000SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)and f_docnumber is a search form field.When I run I getDescription: The precision is invalid.Number: -2147467259 (0x80004005)The field is defined in the table as numberic 9(10,0)Any ideas?

View 1 Replies View Related

Conditional Query On ASPNET Page

Sep 12, 2007

Dear Friends,


I am working on search customer information page.

I have 5 search options,

Name,
Email,
Order Number,
Product Name,
Order Date


I am using check boxes, I need to allow admin to enter above information and click on search,

How will I make my query and sends to DB server to pull up records which satisfies where clause:

For example,

Select * from orders where Email = #Email#

This is simple, but I can not hardcode all queries, I don€™t know in advance what different search option ADMIN may choose.

Any suggestion for logic or query make up,

Thanks,

Fahim.

View 1 Replies View Related

How To Program A Button To Query Database On Another Page ?

Jan 16, 2008

hi.
When i click on the button, it will go to another page called fileB.aspx, it will query database based on textbox parameter from fileA.aspx and display gridview on fileB.aspx
It is based on sqldatasource. abit noob on programming visual basic.Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
'Code go here.End Sub
 
 

View 3 Replies View Related

ID Who Didn't Visited A Summary Page Per Day (was Help With Query)

Mar 9, 2006

I have 1 table:
ID pagename datevisited
1default01/01/2006
1info01/01/2006
1default02/01/2006
1info02/01/2006
1summary02/01/2006
2default02/01/2006
2info02/01/2006
2summary02/01/2006

I need to run query for ID who didn't visited a summary page(per day)
How do I do this?

View 1 Replies View Related

How To Page SQL Query Result Returned From FOR XML Statement?

Apr 25, 2006

Gurus.I do not know if it is possible, but here is what I want to do.I want to allow user to page the SQL result, so he could decides toreturn from row 10 to row 20, or row 100 to 200, without returns thewhole resultset. Every time he sends another request, I do not mind tohit the database again, (I do not want to cache the result in themiddle tier server, scalability issue), and I know that I could achievethis with CURSOR, but unfortunately the FOR XML is not allowed in aCURSOR statement .(I know that I could achieve what I want to do by writing custom codein the middle tier, but I just want to see if there is a way to do thison the database side.)Any comments & suggestion is greatly appreciated.Thanks in advance.(I am using SQL2005)John

View 2 Replies View Related

Transact SQL :: Query To Get Web Page Hits On Website

Sep 24, 2015

I'm trying to piece together some code on how to find members of our organization who have visited our website.

View 2 Replies View Related

How To Query For Master-detail Output On Same Page

Jul 27, 2007

I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below.

OrderNo OrderDate
1001 7/27/2007
+ <div to show/hide>
Item Description
AAA desc_for_itemAAA
BBB desc_for_itemBBB
+ </div>

1002 7/26/2007
+ <div to show/hide>
Item Description
CCC desc_for_itemCCC
+ </div>

I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?

View 5 Replies View Related

SQL Query That Works In SQL Server Management Studio, But Doesn't On .NET 2.0 Page

Feb 21, 2008

SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasource and a gridview, no data is displayed.anyone know what my problem is? 

View 1 Replies View Related

SQL 2012 :: Buffer Cache And PLE (Page Life Expectancy) Query

Aug 17, 2015

I'm getting an alert which states that both my Buffer Cache Hit Ratio and PLE are low on one of my SQL Servers though I'm not sure how to correctly check this.

I ran:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'

Which gives me the Buffer Cache Hit Ratio, cntr_Value of 9 though its constantly dipping between 3-3000 and is never steady and I'm unsure if this is normal.

I also ran:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'

Which gives me the Page life expectancy of 209061.

If these values would cause concern and if this is a normal Buffer Cache Hit Ratio? It's constantly dropping from high or low from what I can see. These scripts were pulled from another forum and I'm assuming they're showing the correct values.

View 1 Replies View Related

Active Directory Query 1000 Page Size Limitaion

Jul 23, 2005

Hi.We need to create a view of our active directory users (we have 2500).I found out that there is max page size of 1000, so we cannot get moredata.Anyone found a solution to that problem?Thanks

View 1 Replies View Related

DB Design :: How To Return Only 25 Rows At A Time From Query To Web Page That Interact With Server

Sep 16, 2015

How to design at database level such a way so that when I implement a SQL query that returns one hundred thousand rows only display 25 rows at the client (Web page at a time). How can I accomplish this?

Once I display first 25 rows then how do I bring next 25 rows and so on. Can it be done via paging or there are other techniques. However I asked to design this in the database level. I am using MS SQL Server 2008. Front end Visual Studio 2010. I use C# for coding. 

View 14 Replies View Related

Need Help...any Suggestions??

Jul 2, 2007

here is my schema...












Board of Zoning Appeals

Parcel#
BZACase#
ApplicantID
OwnerID
DateFiled
Size
Zoning











VU (Variance of Use)




BZACase#
ProposedUse
Comments











VDS (Variance of Developmental Standard)


BZACase#
OrdinanceReq
RequestedDim
ProposedUse
Comments
















SE (Special Exception)

BZACase#
CurrentUse
ProposedUse
OrdinanceReq
RequestedDim
Comments











Applicant

ApplicantID
FirstName
LastName
CompanyName
Line1
Line2
City


State
Zip
PhoneNum















Owner

OwnerID
FirstName
LastName
CompanyName
Line1
Line2
City


State
Zip
PhoneNum



Now i know what im doing with the applicantID and ownerID...but the BZAcase# is a number/unique identifier that looks like this....2007-VU-000, 2007-VU-001, 2007-VU-003....so my question is
1.   how do i get the last three numbers to increment each time a new application is created?
2.  how do i retrieve the last record in the table???
3.   Do you have any other suggestions?? i have to have the number and what type of form they applied for in the "case#"???

View 11 Replies View Related

Suggestions Please

Mar 26, 2003

I am requesting suggestions to solve my problem.

Background: We are changing the way we pay commissions to our rep groups. We used to pay when the order was placed, now we want to pay when the invoice is paid.

Problem: The commision information is currently stored in the customer order, not in the invoice. These orders get deleted a couple weeks after the order was completed (shipped).

I want to create another, rather dynamic, table/structure that will store the order number and the commission percentage.

This info in this table should:

Be deleted: if the order has been deleted and the invoice either does not exist or was payed some period of time ago (maybe 6 months)

Be updated: if the customer order has been updated (i.e. the commission was changed)

Be inserted: if the order exists but the order number is not in the new table.

That is it in a nutshell.

Thanks,
Brian

View 1 Replies View Related

Need Suggestions

Sep 25, 2006

hi
i have written a procedure for stock report.
its working fine. please go through the sp and give me some Suggestions. please tell me where i need to improve my code. thanks

Note: User is required to execute this procedure daily.
i am taking the sum of issues,purchases,returns,physical adjustments for each and every product from last updated date to today's date and storing it in a table i,e stock_Dump. from this table i generate the date wise stock report


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.spUpdateStock
@strReturn varchar(70) output
AS
BEGIN
declare @maxDt smalld atetime
if exists(Select * from Stock_Dump where Txn_Date=
Convert(varchar,Getdate(),101))
BEGIN
set @strReturn='Stock Table already generated
for the day. cannot generate it again'
END

ELSE
BEGIN
TRUNCATE TABLE Stock_Dump_Temp
select @maxDt=max(Txn_Date) from Stock_Dump
/* insert (Opening stock) Closing stock for all
all the products from last max Date*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,
Closing_Stock as Opening_Stock ,
0,0,0,0,0,0,0 from Stock_Dump Where
Txn_Date=Convert(varchar,@maxDt,101)
/* Issues*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,
Sum(Qty) as Issue_Qty,0,0,0,0,0,0 from Issue_Details
Where Issue_No IN(Select Issue_No from Issue_Hdr
Where Issue_Date > Convert(varchar,@maxDt,101) and
Issue_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Goods receipt*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
Sum(Qty) as Purchase,0,0,0,0,0 from Dlv_note_Details
Where Dlv_Note_No IN(Select Dlv_Note_No from
Dlv_Hdr Where Dlv_Note_Date > Convert(varchar,@maxDt,101) and
Dlv_Note_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Rejection after receipt*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,Sum(Qty) as Rejected,0,0,0,0 from
Rejection_Details Where Rejection_No IN
(Select Rejection_No from Rejection_Hdr Where
Rejection_Date > Convert(varchar,@maxDt,101) and
Rejection_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Issues returns*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,Sum(Qty) As Issue_Returns,0,0,0 from
Issue_Return_Details Where Issue_R_No
IN(Select Issue_R_No from Issue_Return_Hdr
Where Return_Date > Convert(varchar,@maxDt,101) and
Return_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Physical Stock + */
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,0,Sum(Var_Qty) as Phy_Qty_P,0,0 from
Physical_Details Where Var_Qty>0 and Txn_No
IN(Select txn_No from Physical_Hdr Where
Txn_Date > Convert(varchar,@maxDt,101) and
Txn_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Physical -*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,0,0,Sum(Var_Qty) as Phy_Qty_M,0 from
Physical_Details Where Var_Qty<0 and Txn_No
IN(Select txn_No from Physical_Hdr Where
Txn_Date > Convert(varchar,@maxDt,101) and
Txn_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* insert all the records into actual table i,e Stock_dump from Stock_dump_temp (temporory table)*/
INSERT INTO Stock_Dump Select Product_code,Txn_Date,
Sum(Opening_Stock) as Opening_Stock,Sum(Issue_Qty) as
Issue_Qty,Sum(purchase) as Purchase,Sum(Rejected) as
Rejected,Sum(Issue_Returns) as Issue_returns,
Sum(Phy_Qty_P) as Phy_Qty_P,Sum(Phy_Qty_M) as
Phy_Qty_M,0 as Closing_Stock from Stock_Dump_Temp
Group By ProducT_Code,Txn_Date
/* update closing stock*/
UPDATE Stock_Dump Set
Closing_Stock=abs((Opening_Stock+Purchase+Issue_Returns+Phy_Qty_P)-(Issue_Qty+Rejected+Phy_Qty_M))
Where Txn_Date=Convert(varchar,Getdate(),101)
/* delete unwanted records */
DELETE From Stock_Dump Where Opening_Stock=0 and
Issue_Qty=0 and Purchase=0 and Rejected=0
and Issue_Returns=0 and Phy_Qty_M=0 and Phy_Qty_P=0

set @strReturn='Stock Table Update Successfully'
return
END

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO








suji

View 1 Replies View Related

Any Suggestions??

Nov 21, 2006

I have a database which contains more than 20000 stored procedureswhich were created withansi nulls off. This i found out using the querySELECT name,AnsiNullsOn FROM(SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOnFROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0Is there any way that i can set this property to 1 for all the storedprocedures i have??I know the alternate method is to drop the procedure and execute thescripts again with AnsiNullsOn = 1.Is there any other simple ways?? It will be very helpful for me..

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved