Friday, May 24, 2013

[T-SQL] Help Need in Query

[T-SQL] Help Need in Query

Help Need in Query

Posted: 23 May 2013 11:28 PM PDT

Here Is My DDL Create Table #Tab1 (Fileid int,FileName Varchar(10),FileType varchar(10) )goinsert into #Tab1 values(1,'A','txt'), (1,'A','pdf'), (2,'B','txt'), (1,'A','txt'), (3,'C','txt'), (3,'C','txt'), (4,'D','pdf'), (4,'D','txt'), (5,'E','txt'), (6,'F','pdf')go--select * from #Tab1Select Fileid,FileName,FileType,COUNT(*) as [Count]From #Tab1Group By Fileid,FileName,FileTypeAfter executing the above query i am getting the count like number of txt files and number of pdf filesgroup by fileid.What i want is for any fileid if the txt file was not existed that count should be return as zero in new row?

isnull and coalesce Comparison

Posted: 24 May 2013 12:32 AM PDT

in my case, some source columns values are nulls, i want to populate "NULL" to "0"where isnull([column_name],0) coalesce([column_name],0) i want know both queries giving same result or differentand if same which one is perfomance wise betterand else different what is the difference?:hehe:

Hierarchical Child Records with Multiple Parents

Posted: 24 May 2013 12:03 AM PDT

Hello,In my limited experience I have used a cte to write simple hierarchical result sets but this one is a bit more complicated for me. My child data records have a primary parent record, a secondary and tertiary (no more than that). This dataset will be bound to a tree for user interaction. So as mentioned in the past I had one parent and one child but this time I need to "repeat" the recursion for each child record to be recorded under each potential parent.Data Child records:FoodNameID | FoodName | FoodShortName | FoodGroupID | FoodGroupID2 | FoodGroupID3 56743 | Alumelle | Omelette | 1001 | 1007 | 1009Data Parent Records:FoodGroupID | FoodGroupName | FoodGroupDesc 1001 | Breakfast | Big Meal 1007 | High Protein | dasfsdsdf 1009 | Eggerific | qerrewqerOne thought I did have was to have a one to many relational table such as FoodName_FoodGroup with said structure:FoodNameId | FoodGroupId 56743 | 1001 56743 | 1007 56743 | 1009If using this "join" table then the query would be pretty straight forward.Ultimately I would like to learn / use the industry best practice which is somewhat subjective but sometimes there are real obvious ones like join table vs rbar cursors.Thank YouJB

MERGE & NULL Comparisons

Posted: 23 May 2013 08:13 PM PDT

OK,need a fast way to compare new data coming in to old data. Both are in seperate tables so we went for Merge. In testing I noticed that the comparison does not seem to be working. The columns are a selection of different data types so dates, chars, and numbers.Is there an easy way of doing this quickly and ensuring they are checked for change?CheersEMERGE TargetTable AS TARGETUSING (SELECT Target_ID , Column1 , Column2 , Column3 , Column4 FROM SourceTable) AS SOURCE ON (TARGET.Target_ID = SOURCE.Target_ID )--------------------------------------------------------------------------WHEN MATCH ON KEY COLUMN AND THERE IS A CHANGE TO DATA------------------------------------------------------------------------WHEN MATCHED AND ( TARGET.Column1 <> SOURCE.Column1 OR TARGET.Column2 <>SOURCE.Column2 OR TARGET.Column3 <>SOURCE.Column3 OR TARGET.Column4 <>SOURCE.Column4 ) THEN UPDATE SET TARGET.Column1 = SOURCE.Column1, TARGET.Column2 = SOURCE.Column2, TARGET.Column3 = SOURCE.Column3, TARGET.Column4 = SOURCE.Column4 ------------------------------------- WHEN NO MATCH, INSERT NEW RECORD-----------------------------------WHEN NOT MATCHED BY TARGET THEN INSERT (Target_ID, Column1, Column2, Column3, Column4) VALUES (SOURCE.Target_ID,SOURCE.Column1,SOURCE.Column2,SOURCE.Column3,SOURCE.Column4)-------------------------------------------- WHEN IN TARGET BUT NOT IN SOURCE DELETE------------------------------------------WHEN NOT MATCHED BY SOURCE THEN DELETEOUTPUT $Action

Need Help in Writing the Query

Posted: 23 May 2013 10:59 PM PDT

I have a column and the values are 0.010.20.00030.000040.5I want output like this 12345please share your thoughts?

Querying Snapshot Data

Posted: 23 May 2013 02:27 PM PDT

Can someone help me with querying the snapshot data as show in the attached file?

Hierarchical List of All tables

Posted: 21 Mar 2011 10:58 PM PDT

In a SQL Server DB, i have to find all the "Master"(Parent) tables and also build aHierarchical list of Paerent/Child tables. Finally i would like to traverse that Hierarchiallist from down and delete all the child table data at the end i can able to delete the parent data also. I have tried in one way, that is, Using system tables (like sys.objects etc) i queried the metadata of the db (like its primary and Foreign keys). But i dont know how to formulate the tree like structure.. Pls help in this regard.Thank U.

How to get date as my column not as row

Posted: 23 May 2013 03:55 AM PDT

I have data that looks like the following:Sales_2012 Plan fiscal_week_2013 Sales_2013 LY PN232.2935700 286.8642 2013-01-05 263.1448400 0.132811553931518638 -0.0826852665251378237.2016700 286.8642 2013-01-12 258.2310900 0.088656289814485707 -0.0998144463016142232.0180700 286.8642 2013-01-19 264.8221900 0.141386056698083903 -0.0768380765586006238.2087000 286.8642 2013-01-26 266.3660400 0.118204498828128443 -0.0714562634427699253.8050500 297.0714 2013-02-02 267.9389200 0.055687899039045913 -0.0980658655463566But I need the results to look like:Sales 1/5 1/12 1/19 1/26 2/2 2/9 2/16 2/232012 213 216 225 241 246 247 241 265Plan 230 233 243 260 266 267 260 2862013 217 222 226 243 250 251 261 271% to LY 101.9% 102.8% 100.4% 100.8% 101.6% 101.6% 108.3% 102.3%% to PN 94.3% 95.2% 93.0% 93.4% 94.1% 94.1% 100.3% 94.7%How can I get my data to be look like this. I need a combination of UnPivot and Pivot - but my dates vary and I don't want an IN statement with hardcoded dates. I need dynamic but not sure how to build this. Please help.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog